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

Optimal way to Read an Excel file (.xls/.xlsx)

提问by Ankesh

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

我知道有多种方法可以读取 Excel 文件：

• Iterop
• Oledb
• Open Xml SDK

• Iterop
• Oledb
• 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 Entities（我不知道如何为对象创建动态属性/字段[列名将在 Excel 文件中变化]）

Use DataTable/Custom Entitiesto perform some operations using its data.

用于DataTable/Custom Entities使用其数据执行某些操作。

Update DataTablewith the results of the operations

更新DataTable操作结果

Write it back to excel file.

写回excel file.

Which would be simpler.

哪个会更简单。

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

此外，如果可能的话，建议我自定义实体（动态地向对象添加属性/字段）

回答by Furqan Safdar

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

使用 OLE Query，它非常简单（例如 sheetName 是 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);
       conn.Close();
    }                        
    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 Readeran open source project on CodePlex. Its works really well to export data from Excel sheets.

您还可以使用Excel Data Reader一个 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 导入数据集？

回答by Enigmativity

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.

它还允许强类型行访问。

回答by Hark.Tenl

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

回答by davewasthere

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

回答by Dan

I realize this question was asked nearly 7 years ago but it's still a top Google search result for certain keywords regarding importing excel data with C#, so I wanted to provide an alternative based on some recent tech developments.

我意识到这个问题是在近 7 年前被问到的，但它仍然是有关使用 C# 导入 excel 数据的某些关键字的顶级 Google 搜索结果，因此我想根据最近的一些技术发展提供替代方案。

Importing Excel data has become such a common task to my everyday duties, that I've streamlined the process and documented the method on my blog: best way to read excel file in c#.

导入 Excel 数据已成为我日常工作中的一项常见任务，因此我简化了流程并在我的博客中记录了该方法：best way to read excel file in c#。

I use NPOIbecause it can read/write Excel files without Microsoft Office installed and it doesn't use COM+ or any interops. That means it can work in the cloud!

我使用NPOI是因为它可以在没有安装 Microsoft Office 的情况下读取/写入 Excel 文件，并且它不使用 COM+ 或任何互操作。这意味着它可以在云端工作！

But the real magic comes from pairing up with NPOI Mapper from Donny Tianbecause it allows me to map the Excel columns to properties in my C# classes without writing any code. It's beautiful.

但真正的魔法来自与Donny Tian 的NPOI Mapper 配对，因为它允许我将 Excel 列映射到我的 C# 类中的属性，而无需编写任何代码。真漂亮。

Here is the basic idea:

这是基本思想：

I create a .net class that matches/maps the Excel columns I'm interested in:

我创建了一个匹配/映射我感兴趣的 Excel 列的 .net 类：

        class CustomExcelFormat
        {
            [Column("District")]
            public int District { get; set; }

            [Column("DM")]
            public string FullName { get; set; }

            [Column("Email Address")]
            public string EmailAddress { get; set; }

            [Column("Username")]
            public string Username { get; set; }

            public string FirstName
            {
                get
                {
                    return Username.Split('.')[0];
                }
            }

            public string LastName
            {
                get
                {
                    return Username.Split('.')[1];
                }
            }
        }

Notice, it allows me to map based on column name if I want to!

请注意，如果我愿意，它允许我根据列名进行映射！

Then when I process the excel file all I need to do is something like this:

然后当我处理 excel 文件时，我需要做的就是这样：

        public void Execute(string localPath, int sheetIndex)
        {
            IWorkbook workbook;
            using (FileStream file = new FileStream(localPath, FileMode.Open, FileAccess.Read))
            {
                workbook = WorkbookFactory.Create(file);
            }

            var importer = new Mapper(workbook);
            var items = importer.Take<CustomExcelFormat>(sheetIndex);
            foreach(var item in items)
            {
                var row = item.Value;
                if (string.IsNullOrEmpty(row.EmailAddress))
                    continue;

                UpdateUser(row);
            }

            DataContext.SaveChanges();
        }

Now, admittedly, my code does not modify the Excel file itself. I am instead saving the data to a database using Entity Framework (that's why you see "UpdateUser" and "SaveChanges" in my example). But there is already a good discussion on SO about how to save/modify a file using NPOI.

现在，无可否认，我的代码不会修改 Excel 文件本身。我改为使用实体框架将数据保存到数据库中（这就是您在我的示例中看到“UpdateUser”和“SaveChanges”的原因）。但是关于如何使用 NPOI 保存/修改文件已经有一个很好的讨论。

回答by fr0ga

Try to use Aspose.cells library (not free, but trial is enough to read), it is quite good

尝试使用 Aspose.cells 库（不是免费的，但试用就够了），相当不错

Install-package Aspose.cells

Install-package Aspose.cells

There is sample code:

有示例代码：

using Aspose.Cells;
using System;

namespace ExcelReader
{
    class Program
    {
        static void Main(string[] args)
        {
            // Replace path for your file
            readXLS(@"C:\MyExcelFile.xls"); // or "*.xlsx"
            Console.ReadKey();
        }

        public static void readXLS(string PathToMyExcel)
        {
            //Open your template file.
            Workbook wb = new Workbook(PathToMyExcel);

            //Get the first worksheet.
            Worksheet worksheet = wb.Worksheets[0];

            //Get cells
            Cells cells = worksheet.Cells;

            // Get row and column count
            int rowCount = cells.MaxDataRow;
            int columnCount = cells.MaxDataColumn;

            // Current cell value
            string strCell = "";

            Console.WriteLine(String.Format("rowCount={0}, columnCount={1}", rowCount, columnCount));

            for (int row = 0; row <= rowCount; row++) // Numeration starts from 0 to MaxDataRow
            {
                for (int column = 0; column <= columnCount; column++)  // Numeration starts from 0 to MaxDataColumn
                {
                    strCell = "";
                    strCell = Convert.ToString(cells[row, column].Value);
                    if (String.IsNullOrEmpty(strCell))
                    {
                        continue;
                    }
                    else
                    {
                        // Do your staff here
                        Console.WriteLine(strCell);
                    }
                }
            }
        }
    }
}

回答by Nithin Chandran

Read from excel, modify and write back

从excel读取，修改并写回

 /// <summary>
/// /Reads an excel file and converts it into dataset with each sheet as each table of the dataset
/// </summary>
/// <param name="filename"></param>
/// <param name="headers">If set to true the first row will be considered as headers</param>
/// <returns></returns>
public DataSet Import(string filename, bool headers = true)
{
    var _xl = new Excel.Application();
    var wb = _xl.Workbooks.Open(filename);
    var sheets = wb.Sheets;
    DataSet dataSet = null;
    if (sheets != null && sheets.Count != 0)
    {
        dataSet = new DataSet();
        foreach (var item in sheets)
        {
            var sheet = (Excel.Worksheet)item;
            DataTable dt = null;
            if (sheet != null)
            {
                dt = new DataTable();
                var ColumnCount = ((Excel.Range)sheet.UsedRange.Rows[1, Type.Missing]).Columns.Count;
                var rowCount = ((Excel.Range)sheet.UsedRange.Columns[1, Type.Missing]).Rows.Count;

                for (int j = 0; j < ColumnCount; j++)
                {
                    var cell = (Excel.Range)sheet.Cells[1, j + 1];
                    var column = new DataColumn(headers ? cell.Value : string.Empty);
                    dt.Columns.Add(column);
                }

                for (int i = 0; i < rowCount; i++)
                {
                    var r = dt.NewRow();
                    for (int j = 0; j < ColumnCount; j++)
                    {
                        var cell = (Excel.Range)sheet.Cells[i + 1 + (headers ? 1 : 0), j + 1];
                        r[j] = cell.Value;
                    }
                    dt.Rows.Add(r);
                }

            }
            dataSet.Tables.Add(dt);
        }
    }
    _xl.Quit();
    return dataSet;
}



 public string Export(DataTable dt, bool headers = false)
    {
        var wb = _xl.Workbooks.Add();
        var sheet = (Excel.Worksheet)wb.ActiveSheet;
        //process columns
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            var col = dt.Columns[i];
            //added columns to the top of sheet
            var currentCell = (Excel.Range)sheet.Cells[1, i + 1];
            currentCell.Value = col.ToString();
            currentCell.Font.Bold = true;
            //process rows
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                var row = dt.Rows[j];
                //added rows to sheet
                var cell = (Excel.Range)sheet.Cells[j + 1 + 1, i + 1];
                cell.Value = row[i];
            }
            currentCell.EntireColumn.AutoFit();
        }
        var fileName="{somepath/somefile.xlsx}";
        wb.SaveCopyAs(fileName);
        _xl.Quit();
        return fileName;
    }