C# NPOI 是否支持 .xlsx 格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16079956/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does NPOI have support to .xlsx format?
提问by Ganeshja
Will NPOI DLL recognize .xlsxfile?
NPOI DLL 会识别.xlsx文件吗？
Currently I'm using NPOI 1.2.5 version DLL for Microsoft Excel 97-2003, but I need to access Excel sheets of extension .xlsxalso.
目前，我正在为 Microsoft Excel 97-2003 使用 NPOI 1.2.5 版本的 DLL，但我还需要访问 Excel 扩展表.xlsx。
Will NPOI support the above?
NPOI 会支持以上吗？
Code snippet:
代码片段：
static void Main(string[] args) {
XSSFWorkbook xssfwb;
using(FileStream file=new FileStream(
@"C:\Users7702\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);
}
采纳答案by Demi
Yes it does. NPOI 2.0 beta works. Here's a sample code to get you started:
是的，它确实。NPOI 2.0 测试版有效。这是帮助您入门的示例代码：
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("-------------------------------------------");
}
}
}
回答by Tony Qu
NPOI 2.0 supports xlsx. You can download it from https://npoi.codeplex.com/releases/view/112932
NPOI 2.0 支持 xlsx。您可以从https://npoi.codeplex.com/releases/view/112932下载
回答by AragornMx
You can read Excel files in .xls and .xlsx extensions with NPOI, you only need to add the next in the using section
您可以使用 NPOI 读取 .xls 和 .xlsx 扩展名的 Excel 文件，您只需要在 using 部分添加下一个
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
主要是在打开文件时，您必须区分扩展名，以便使用适当的组件，并使用 ISheet 接口，以便您可以独立于文件扩展名引用工作表
//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)
一旦你有了 excel 对象，你只需要阅读它（在 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 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.
要读取单元格值，您可以使用 .ToString() 方法或 StringCellValue 属性，但要注意 StringCellValue 仅适用于字符串单元格，数字和日期单元格会引发异常。
回答by Uchitha
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.
可能是该库在提供原始答案时没有此功能，但现在您可以使用相同的代码库处理 xls 和 xlsx，而无需检查文件扩展名。
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.
诀窍是使用 WorkbookFactory 类透明地加载这两种类型的文件。只要您不使用特定于任一版本的特殊功能，这就会起作用。
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);
}
