Does NPOI have support to .xlsx format?

问题:

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);
}

回答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("-------------------------------------------");
    }
}
}

回答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.

回答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);
}

回答4:

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