读取Excel文件的最佳方式(.xls/.xlsx)

I know that there are different ways to read an Excel file:

我知道读取Excel文件有不同的方法:

Iterop
Iterop
Oledb
Oledb
Open Xml SDK
Open Xml SDK

Compatibility is not a question because the program will be executed in a controlled environment.

兼容性不是问题，因为程序将在受控环境中执行。

My Requirement :
Read a file to a DataTable / CUstom Entities (I don't know how to make dynamic properties/fields to an object[column names will be variating in an Excel file])

我的要求是:将文件读入DataTable / CUstom实体(我不知道如何将动态属性/字段读入对象[列名将在Excel文件中变化])

Use DataTable/Custom Entities to perform some operations using its data.

使用DataTable/Custom实体使用其数据执行一些操作。

Update DataTable with the results of the operations

使用操作的结果更新DataTable

Write it back to excel file.

把它写回excel文件。

Which would be simpler.

这将是更简单。

Also if possible advice me on custom Entities (adding properties/fields to an object dynamically)

如果可能的话，还可以给我一些关于自定义实体的建议(动态地向对象添加属性/字段)

4 个解决方案

#1

Take a look at Linq-to-Excel. It's pretty neat.

看看Linq-to-Excel。它很整洁。

var book = new LinqToExcel.ExcelQueryFactory(@"File.xlsx");

var query =
    from row in book.Worksheet("Stock Entry")
    let item = new
    {
        Code = row["Code"].Cast<string>(),
        Supplier = row["Supplier"].Cast<string>(),
        Ref = row["Ref"].Cast<string>(),
    }
    where item.Supplier == "Walmart"
    select item;

It also allows for strongly-typed row access too.

它还允许强类型的行访问。

#2

Using OLE Query, it's quite simple (e.g. sheetName is Sheet1$):

使用OLE查询，非常简单(例如sheetName is Sheet1$):

DataTable LoadWorksheetInDataTable(string fileName, string sheetName)
{           
    DataTable sheetData = new DataTable();
    using (OleDbConnection conn = this.returnConnection(fileName))
    {
       conn.Open();
       // retrieve the data using data adapter
       OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
        sheetAdapter.Fill(sheetData);
    }                        
    return sheetData;
}

private OleDbConnection returnConnection(string fileName)
{
    return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
}

For newer Excel versions:

更新的Excel版本:

return new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0;");

You can also use Excel Data Reader an open source project on CodePlex. Its works really well to export data from Excel sheets.

您还可以使用Excel数据读取器在CodePlex上进行一个开源项目。它的工作很好地从Excel表导出数据。

The sample code given on the link specified:

指定链接上给出的示例代码:

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

Reference: How do I import from Excel to a DataSet using Microsoft.Office.Interop.Excel?

如何使用Microsoft.Office.Interop.Excel从Excel导入数据集?

#3

Try to use this free way to this, https://freenetexcel.codeplex.com

尝试使用这个免费的方式，https://freenetexcel.codeplex.com

 Workbook workbook = new Workbook();

 workbook.LoadFromFile(@"..\..\parts.xls",ExcelVersion.Version97to2003);
 //Initialize worksheet
 Worksheet sheet = workbook.Worksheets[0];

 DataTable dataTable = sheet.ExportDataTable();

#4

If you can restrict it to just (Open Office XML format) *.xlsx files, then probably the most popular library would be EPPLus.

如果您可以将其限制为(Open Office XML格式)*。xlsx文件，那么可能最流行的库是EPPLus。

Bonus is, there are no other dependencies. Just install using nuget:

额外的好处是，没有其他依赖项。安装使用nuget:

Install-Package EPPlus

#1