c# - Does NPOI have support to .xlsx format?

Question

Will NPOI DLL recognize .xlsx file?

Currently I'm using NPOI 1.2.5 version DLL for Microsoft Excel 97-2003, but I need to access Excel sheets of extension .xlsx also.

Will NPOI support the above?

Code snippet:

static void Main(string[] args) {
    XSSFWorkbook xssfwb;

    using(FileStream file=new FileStream(
            @"C:Users347702DesktopHello.xlsx",
            FileMode.Open, FileAccess.Read)) {
        xssfwb=new XSSFWorkbook(file);
    }

    ISheet sheet=xssfwb.GetSheet("sheet1");
    sheet.GetRow(1048576);
    Console.WriteLine(sheet.GetRow(1048576).GetCell(0).StringCellValue);
}

Answer 1

You can read Excel files in .xls and .xlsx extensions with NPOI, you only need to add the next in the using section

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

The main thing is at the time you open the file, you have to distinguish between the extensions so you use the appropiate componente, and use an ISheet interface so you can reference the sheet independently of the file extension

//We get the file extension
fileExt = Path.GetExtension(fileName);

//Declare the sheet interface
ISheet sheet;

//Get the Excel file according to the extension
if (fileExt.ToLower() == ".xls")
{
    //Use the NPOI Excel xls object
    HSSFWorkbook hssfwb;
    using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
    {
        hssfwb = new HSSFWorkbook(file);
    }

    //Assign the sheet
    sheet = hssfwb.GetSheet(sheetName);
}
else //.xlsx extension
{
    //Use the NPOI Excel xlsx object
    XSSFWorkbook hssfwb;
    using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
    {
        hssfwb = new XSSFWorkbook(file);
    }

    //Assign the sheet
    sheet = hssfwb.GetSheet(sheetName);
}

Once you have the excel object you only need to read it (in NPOI rows and columns are zero based)

//Loop through the rows until we find an empty one
for (int row = 0; row <= sheet.LastRowNum; row++)
{
    //Get the cell value
    string cellValue = sheet.GetRow(row).GetCell(0).ToString().Trim(); //In the method GetCell you specify the column number you want to read, in the method GetRow you spacify the row
    string cellValue2 = sheet.GetRow(row).GetCell(0).StringCellValue.Trim();
}

To read the cell valur you can use the .ToString() method or the StringCellValue property, but be careful the StringCellValue only works with string cells, with number and date cells it throws an exception.