0

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);
}
Brian Rogers
  • 125,747
  • 31
  • 299
  • 300
Godrules500
  • 467
  • 6
  • 25

2 Answers2

1

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_"));
}
HeliG
  • 63
  • 7
0

try something like 

     FileStream sw = File.Create(excelLocation);

     wbXLS.Write(sw);

     sw.Close();
Sarah Akram
  • 1
  • 1