NPOI - After saving to file corrupts .xlsx workbook

Question

I used this code to write to an exciting excel file. After writing the file, when I open the file manually it is corrupted. I am using NPOI binary 2.3.0.0 Please tell how to avoid excel getting corrupted.

[Authorize]
public void ExportUsers()
{
    var path = Server.MapPath(@"~\Content\ExportTemplates\") + "sample.xlsx";

    FileStream sw = new FileStream(path, FileMode.Open, FileAccess.Read);

    IWorkbook workbook = WorkbookFactory.Create(sw);       

    ISheet sheet = workbook.GetSheetAt(0);
    IRow row = sheet.GetRow(12);

    ICell cell = row.CreateCell(row.LastCellNum);
    cell.SetCellValue("test");
    workbook.CreateSheet("Ripon");            
    sw.Close();

    using (var exportData = new MemoryStream())
    {
        workbook.Write(exportData);
        string saveAsFileName = string.Format("Export-{0:d}.xls", DateTime.Now).Replace("/", "-");
        System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
        System.Web.HttpContext.Current.Response.Clear();
        System.Web.HttpContext.Current.Response.BinaryWrite(exportData.GetBuffer());
        System.Web.HttpContext.Current.Response.End();                    
    }
}

New file is created but corrupted. I've seen people say this is fixed in version 2.0.6, but still not working for me

score 2 · Answer 1 · answered Jun 25 '17 at 02:58

There are several problems going on here.

First, you are starting with an .xlsx file but then changing the download file extension to .xls. .xls and .xlsx are not the same file format; the former is a binary format, while the latter is a zipped XML format. If you save the file with the wrong extension, then Excel will report the file as corrupted when it is opened.

Second, you are using the wrong method to get the data from the MemoryStream. GetBuffer will return the entire allocated internal buffer array, which will include any unused bytes that are beyond the end of the data if the buffer is not completely full. The extra null bytes will cause the downloaded file to be corrupted. If you want to get just the data in the buffer then you should use ToArray instead.

Third, it looks like you are using the ASP.NET MVC framework (based on the presence of the [Authorize] attribute on your method) but you are directly manipulating the current response instead of using the controller's built-in File method for returning a downloadable file. I would recommend using the built-in methods where possible, as it will make your code much cleaner.

Here is the corrected code:

[Authorize]
public ActionResult ExportUsers()
{
    var path = Server.MapPath(@"~\Content\ExportTemplates\") + "sample.xlsx";

    FileStream sw = new FileStream(path, FileMode.Open, FileAccess.Read);

    IWorkbook workbook = WorkbookFactory.Create(sw);

    ISheet sheet = workbook.GetSheetAt(0);
    IRow row = sheet.GetRow(12);

    ICell cell = row.CreateCell(row.LastCellNum);
    cell.SetCellValue("test");
    workbook.CreateSheet("Ripon");
    sw.Close();

    var exportData = new MemoryStream();
    workbook.Write(exportData);
    exportData.Seek(0, SeekOrigin.Begin);  // reset stream to beginning so it can be read

    string saveAsFileName = string.Format("Export-{0:d}.xlsx", DateTime.Now).Replace("/", "-");

    return File(exportData, "application/vnd.ms-excel", saveAsFileName);
}

NPOI - After saving to file corrupts .xlsx workbook

1 Answers1