Does NPOI support .xlsx format?

Question

Will the DLOI DLL recognize the .xlsx file?

I am currently using the NPOI 1.2.5 version of the DLL for Microsoft Excel 97-2003, but I also need to access the Excel .xlsx extension worksheets.

Will NPOI support be higher?

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

Answer 1

Yes Yes. Beta version of NPOI 2.0 is working. Here is 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("-------------------------------------------"); } } } 

Answer 2

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

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

The main thing is that you open the file, you must distinguish between extensions to use the appropriate component, and use the ISheet interface so that you can link to the sheet regardless 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 an excel object, you only need to read it (null values ​​are set in the rows and columns of the NPOI)

 //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 valer cells, you can use the .ToString () method or the StringCellValue property, but be careful that StringCellValue only works with string cells, and date and number cells throw an exception.

Answer 3

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

Answer 4

Maybe the library did not have this function when the original answer was provided, but now you can process both xls and xlsx using the same code base without checking file extensions.

The trick is to use the WorkbookFactory class to transparently load both types of files. This will work until you use special features specific to both versions.

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