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:\Users\347702\Desktop\Hello.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);
}

Petulant

Yes, I'm regularly importing .xlsx files with NPOI - absolutely no problem - just go code it! – Lamprophyre

but when i try to load the file with .xlsx it fails to import ??? – Petulant

According to this blog post, NPOI supports .xlsx from version 1.6 on. The blog post also shows a "workaround" for earlier versions of NPOI – Lamprophyre

yeah but in above piece of code m trying to load .xlsx file and trying to get the last row value , last row : 1,048,576 , so i have mentioned a sample value called "Test" at row 1048576 column: A.. but it throws me following error on console.writeline : Object reference not set to an instance of an object – Petulant

so you use column 0 (getcell(0). In that case I would guess rows go from 0 to...? 1048575! – Squinteyed

Just adding the note that NPOI uses Microsoft's Open XML SDK for Office behind the scenes. – Mckeehan

Answer 1

Yes it does. NPOI 2.0 beta works. Here's a sample code to get you started:

class Program
{
static XSSFWorkbook hssfworkbook;
static DataSet dataSet1 = new DataSet();

static void Main(string[] args)
{
    InitializeWorkbook(@"E:\Docs\HoursWidget_RTM.xlsx");
    xlsxToDT();

    DisplayData(dataSet1.Tables[0]);

    Console.ReadLine();
}

static void InitializeWorkbook(string path)
{
    using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
    {
        hssfworkbook = new XSSFWorkbook(file);
    }
}

static void xlsxToDT()
{
    DataTable dt = new DataTable();
    ISheet sheet = hssfworkbook.GetSheetAt(1);
    IRow headerRow = sheet.GetRow(0);
    IEnumerator rows = sheet.GetRowEnumerator();

    int colCount = headerRow.LastCellNum;
    int rowCount = sheet.LastRowNum;

    for (int c = 0; c < colCount; c++)
    {

        dt.Columns.Add(headerRow.GetCell(c).ToString());
    }

    bool skipReadingHeaderRow = rows.MoveNext();
    while (rows.MoveNext())
    {
        IRow row = (XSSFRow)rows.Current;
        DataRow dr = dt.NewRow();

        for (int i = 0; i < colCount; i++)
        {
            ICell cell = row.GetCell(i);

            if (cell != null)
            {
                dr[i] = cell.ToString();
            }
        }
        dt.Rows.Add(dr);
    }

    hssfworkbook = null;
    sheet = null;
    dataSet1.Tables.Add(dt);
}

static void DisplayData(DataTable table)
{
    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn col in table.Columns)
        {
            Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
        }
        Console.WriteLine("-------------------------------------------");
    }
}
}

Slink

The OP says "Currently I'm using NPOI 1.2.5 version" and not version 2.0... – Mortie

Answer 2

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.

Murguia

Answer 3

May be the library didn't had this feature when the original answer(s) was provided, but now you can handle both xls and xlsx using the same code base without checking for file extensions.

The trick is to use WorkbookFactory class to transparently load both types of files. This will work as long as you are not using special features specific to either version.

using (FileStream fileStream = File.OpenRead(fullPathToExcelFile)) //fullPathToExcelFile can hold either a xls or xlsx, we don't care
{
   IWorkbook workbook = WorkbookFactory.Create(fileStream);
   ISheet worksheet = workbook.GetSheet("SampleSheet");

   //Now read from the worksheet anyway you like
   var value = worksheet.GetRow(1).GetCell(1);
}

Judaize

Answer 4

NPOI 2.0 supports xlsx. You can download it from https://npoi.codeplex.com/releases/view/112932

Bodice