exporting data to excel using NPOI in c#

Question

I t but if I try to select the open option it errors out. Could someone tell me why this is happening. Could also someone tell me how do I export the records to xlsx

    var sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet 1");

    //make a header row

    var row1 = (HSSFRow)sheet1.CreateRow(0);

    var cell0 = (HSSFCell)row1.CreateCell(0);
    cell0.SetCellValue("Firm Id");

    var cell1 = (HSSFCell)row1.CreateCell(1);
    cell1.SetCellValue("Account Number");

    var cell2 = (HSSFCell)row1.CreateCell(2);
    cell2.SetCellValue("Portfolio Name");


    for (int i = 0; i < result.Count; i++)
    {
        var row = (HSSFRow)sheet1.CreateRow(i + 1);

        var cellFirmID = (HSSFCell)row.CreateCell(0);
        cellFirmID.SetCellValue(result[i].FirmID);

        var cellAccountNumber = (HSSFCell)row.CreateCell(1);
        cellAccountNumber.SetCellValue(result[i].AccountNumber);
    }

    System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
    response.ContentType = "application/vnd.ms-excel";
    response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}",          "BESTINVEST_fee_rebate_loader_.xls"));
    response.Clear();

    response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());
    response.End();

private MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
{
    MemoryStream file = new MemoryStream();
    hssfworkbook.Write(file);
    return file;
}

Answer 1

NPOI 2.0+ uses different namespaces for old and new Excel formats. The XSSFWorkbook produces Excel 2007 (.xlsx) documents while the older HSSFWorkbook produces the older format.

You should use the XSSF namespace in your code to target the newer format. You should also remove the explicit casts, as CreateCell, CreateRow return interfaces with the methods you need without requiring a cast.

The correct ContentType for Excel 2007 documents is

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet