CODEWITHSUNDEEP
c#excelxlsexcel-2003npoi
Godrules500
Godrules500

Reputation: 469

Npoi xls file save corrupts excel file

So I am able to open and dig through an xls (Excel 97-2003) file. However, the problem is when I try to save it. After I save it, and it succeeds, I then go and manually open the Excel file and get an error saying that it cannot open the Excel file and that it is corrupt. This happens regardless of whether I make any changes or not.

I am still able to, within the program, open the excel file and read through the data.

I am using NPOI 2.2.1 and 2.3.0 (which I installed via Nuget). Both versions have the same results.

string excelLocation = settings.GetExcelDirectory() + week.ExcelLocation;
HSSFWorkbook wbXLS;

// Try to open and read existing workbook
using (FileStream stream = new FileStream(excelLocation, FileMode.Open, FileAccess.Read))
{
    wbXLS = new HSSFWorkbook(stream);
}

ISheet sheet = wbXLS.GetSheet("Schedule");
using (FileStream stream = new FileStream(excelLocation, FileMode.Create, FileAccess.Write))
{
    wbXLS.Write(stream);
}

Upvotes: 0

Views: 1815

Answers (2)

HeliG
HeliG

Reputation: 63

Do you have multi-line cell contents (with a line break)?
I just came across such a problem with my column headers in row 0. Excel encodes a line break by replacing it with _x000a_ (i.e. line1_x000a_line2), NPOI doesn't do that.
Tried with nuget 2.3.0 and xlsx file, but might also be helpful in your case.

I worked around (and verified the cause) by replacing line feeds before saving:

// Encode LineFeeds in column headers (row 0)
IRow rowColHeaders = sheet.GetRow(0);
foreach (ICell cell in rowColHeaders.Cells)
{
    string content = cell.StringCellValue;
    if (content.Contains("\n"))
        cell.SetCellValue(content.Replace("\n", "_x000a_"));
}

Upvotes: 1

Sarah Akram
Sarah Akram

Reputation: 1

try something like 

     FileStream sw = File.Create(excelLocation);

     wbXLS.Write(sw);

     sw.Close();

Upvotes: 0

Related Questions