Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does NPOI have support to .xlsx format?

Tags:

c#

excel

npoi

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);
}
like image 660
Ganeshja Avatar asked Apr 18 '13 10:04

Ganeshja


People also ask

What programs can open XLSX?

An XLSX file is a Microsoft Excel Open XML Format Spreadsheet file. Open one with Excel, Excel Viewer, Google Sheets, or another spreadsheet program.

Is XLS and XLSX same?

The . xlsx files are used in Microsoft Excel (2007/2010) for Workbooks, spreadsheet, and document files. They serve the same purpose as the corresponding . xls Microsoft Excel 97 to 2003 Workbook files, but use the new file extension.

Which is better XLSX or XLS?

For compatibility, XLS has higher compatibility than XLSX. XLS is readable by all Microsoft Excel versions while XLSX is only readable by Excel 2007 and later versions. besides, XLS is able to hold the spreadsheets either including Macros or not, while XLSX isn't capable to support Macros.


4 Answers

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.

like image 96
AragornMx Avatar answered Oct 27 '22 01:10

AragornMx


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("-------------------------------------------");
    }
}
}
like image 24
Demi Avatar answered Oct 27 '22 01:10

Demi


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);
}
like image 21
Uchitha Avatar answered Oct 27 '22 01:10

Uchitha


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

like image 1
Tony Qu Avatar answered Oct 27 '22 01:10

Tony Qu