如何在不使用 Microsoft.Office.Interop.Excel 库的情况下在 C# 中读取 Excel 文件

How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries

提问by ASD

I have a .Net-Windows application in C#. I need to open an excel and process it. How can I do this without using Microsoft.Office.Interop.Excel libraries?

我在 C# 中有一个 .Net-Windows 应用程序。我需要打开一个excel并处理它。如何在不使用 Microsoft.Office.Interop.Excel 库的情况下执行此操作？

采纳答案by Aleksandar Vucetic

var fileName = @"C:\ExcelFile.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();

    var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

        var adapter = new OleDbDataAdapter(cmd);
        var ds = new DataSet();
        adapter.Fill(ds);
    }
}

回答by Thit Lwin Oo

You can try OleDB to read data from excel file. Please try as follow..

您可以尝试使用 OleDB 从 excel 文件中读取数据。请尝试如下..

DataSet ds_Data = new DataSet();
OleDbConnection oleCon = new OleDbConnection();

string strExcelFile = @"C:\Test.xlsx";
oleCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelFile + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";;

 string SpreadSheetName = "";

OleDbDataAdapter Adapter = new OleDbDataAdapter();
OleDbConnection conn = new OleDbConnection(sConnectionString);

string strQuery;
conn.Open();

int workSheetNumber = 0;

DataTable ExcelSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

SpreadSheetName = ExcelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString();

strQuery = "select * from [" + SpreadSheetName + "] ";
OleDbCommand cmd = new OleDbCommand(strQuery, conn);
Adapter.SelectCommand = cmd;
DataSet dsExcel = new DataSet();
Adapter.Fill(dsExcel);
conn.Close();

回答by Jeremy Thompson

I would urge against using OleDB, especially if its going to be run on a server. Its likely to cost you more in the long run - eg we had a SSIS job calling a Stored Procedure with the OleDB reading an excel file in the sptroc and kept crashing the SQL box! I took the OleDB stuff out of the sproc and it stopped crashing the server.

我强烈反对使用 OleDB，特别是如果它要在服务器上运行。从长远来看，它可能会让您花费更多 - 例如，我们有一个 SSIS 作业调用存储过程，OleDB 读取 stroc 中的 excel 文件并不断使 SQL 框崩溃！我从 sproc 中取出了 OleDB 的东西，它停止了服务器崩溃。

A better method I've found is to do it with Office 2003 and the XML files - in respect of Considerations for server-side Automation of Office. Note: Office 2003 is a minimum requirement for this to fly:

我发现的一个更好的方法是使用 Office 2003 和 XML 文件来实现 - 关于Office 服务器端自动化的注意事项。注意：Office 2003 是此飞行的最低要求：

Reffor reading from Excel: http://www.roelvanlisdonk.nl/?p=924(please do more research to find other examples)

从 Excel 读取的参考：http: //www.roelvanlisdonk.nl/?p= 924（请做更多研究以找到其他示例）

Reffor writing a Excel spreadsheet: http://weblogs.asp.net/jgaylord/archive/2008/08/11/use-linq-to-xml-to-generate-excel-documents.aspx

参考编写 Excel 电子表格：http: //weblogs.asp.net/jgaylord/archive/2008/08/11/use-linq-to-xml-to-generate-excel-documents.aspx

public void ReadExcelCellTest()
        {
            XDocument document = XDocument.Load(@"C:\BDATA\Cars.xml");
            XNamespace workbookNameSpace = @"urn:schemas-microsoft-com:office:spreadsheet";

            // Get worksheet
            var query = from w in document.Elements(workbookNameSpace + "Workbook").Elements(workbookNameSpace + "Worksheet")
                        where w.Attribute(workbookNameSpace + "Name").Value.Equals("Settings")
                        select w;
            List<XElement> foundWoksheets = query.ToList<XElement>();
            if (foundWoksheets.Count() <= 0) { throw new ApplicationException("Worksheet Settings could not be found"); }
            XElement worksheet = query.ToList<XElement>()[0];

            // Get the row for "Seat"
            query = from d in worksheet.Elements(workbookNameSpace + "Table").Elements(workbookNameSpace + "Row").Elements(workbookNameSpace + "Cell").Elements(workbookNameSpace + "Data")
                    where d.Value.Equals("Seat")
                    select d;
            List<XElement> foundData = query.ToList<XElement>();
            if (foundData.Count() <= 0) { throw new ApplicationException("Row 'Seat' could not be found"); }
            XElement row = query.ToList<XElement>()[0].Parent.Parent;

            // Get value cell of Etl_SPIImportLocation_ImportPath setting
            XElement cell = row.Elements().ToList<XElement>()[1];

            // Get the value "Leon"
            string cellValue = cell.Elements(workbookNameSpace + "Data").ToList<XElement>()[0].Value;

            Console.WriteLine(cellValue);
        }

回答by Hand-E-Food

I recently found this library that converts an Excel workbook file into a DataSet: Excel Data Reader

最近，我发现这个库的Excel工作簿文件转换成一个数据集：Excel数据读取器

回答by Mohammad Dehghan

I highly recommend CSharpJExcelfor reading Excel 97-2003 files (xls) and ExcelPackagefor reading Excel 2007/2010 files (Office Open XML format, xlsx).

我强烈推荐CSharpJExcel用于读取 Excel 97-2003 文件 (xls) 和ExcelPackage用于读取 Excel 2007/2010 文件（Office Open XML 格式，xlsx）。

They both work perfectly. They have absolutely no dependency on anything.

他们都完美地工作。他们绝对不依赖任何东西。

Sample using CSharpJExcel:

使用CSharpJExcel 的示例：

Workbook workbook = Workbook.getWorkbook(new System.IO.FileInfo(fileName));
var sheet = workbook.getSheet(0);
...
var content = sheet.getCell(colIndex, rowIndex).getContents();
...
workbook.close();

Sample using ExcelPackage:

使用ExcelPackage 的示例：

using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
{
  // get the first worksheet in the workbook
  ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
  int iCol = 2;  // the column to read

  // output the data in column 2
  for (int iRow = 1; iRow < 6; iRow++)
    Console.WriteLine("Cell({0},{1}).Value={2}", iRow, iCol, 
      worksheet.Cell(iRow, iCol).Value);

  // output the formula in row 6
  Console.WriteLine("Cell({0},{1}).Formula={2}", 6, iCol, 
    worksheet.Cell(6, iCol).Formula);

} // the using statement calls Dispose() which closes the package.

EDIT:

编辑：

There is another project, ExcelDataReader, that seems to have the ability to handle both formats. It is also easy like the other ones I've mentioned.

还有另一个项目ExcelDataReader，它似乎能够处理这两种格式。就像我提到的其他方法一样，它也很容易。

There are also other libraries:

还有其他库：

• NPOI: Port of the Apache POI library to .NET:
  Very powerfull, free, and open source. In addition to Excel (97-2010) it also supports Word and PowerPoint files.

• ExcelLibrary:
  It only support Excel 97-2003 (xls) files.

• EPPlus:
  An extension to ExcelPackage. Easier to use (I guess).

• NPOI：Apache POI 库到 .NET 的端口：
  非常强大、免费和开源。除了 Excel (97-2010) 之外，它还支持 Word 和 PowerPoint 文件。

• ExcelLibrary：
  它只支持 Excel 97-2003 (xls) 文件。

• EPPlus：ExcelPackage
  的扩展。更容易使用（我猜）。

回答by Nick Martin

If you don't wish to use interop, you may want to try out OfficeWriter. Depending on how much processing you really need to do on the file, it might be overkill though. You can request a free trial. There's a fully documented api available at the documentation site.

如果您不想使用互操作，则可能需要尝试OfficeWriter。根据您真正需要对文件进行多少处理，这可能有点矫枉过正。您可以申请免费试用。文档站点上有一个完整记录的 api 。

DISCLAIMER: I'm one of the engineers who built the latest version.

免责声明：我是构建最新版本的工程师之一。

回答by Jonx

You could also do what I do and by a commercial control like this one: http://www.syncfusion.com/products/reporting-edition/xlsio

你也可以做我做的事情，通过像这样的商业控制：http: //www.syncfusion.com/products/reporting-edition/xlsio

I have been struging for years before ending with a commercial solution. I first tried the OLEDB approach that is very easy to use in my development environment but can be a knightmare to deploy. Then I tried the open source solutions but most of the are outdated and have bad support.

在以商业解决方案结束之前，我已经奋斗了多年。我首先尝试了 OLEDB 方法，该方法在我的开发环境中非常易于使用，但部署起来却是一场噩梦。然后我尝试了开源解决方案，但大多数已经过时并且支持不佳。

The xlsio controls from syncfusion are just the ones I use and are happy with but others exists. If you can affort it, do not hesitate, it's the best solution. Why? Because it has no dependencies with the system and supports all version of office right away. Among other advantages like, it's really fast.

来自 syncfusion 的 xlsio 控件只是我使用的并且很满意的控件，但其他控件也存在。如果你能负担得起，不要犹豫，这是最好的解决方案。为什么？因为它与系统没有任何依赖关系，并且立即支持所有版本的office。除了其他优点外，它真的很快。

And no, I do not work for synfusion ;)

不，我不为合成而工作 ;)

回答by Pete McKinney

If you need to open XLS files rather than XLSX files, http://npoi.codeplex.com/is a great choice. We've used it to good effect on our projects.

如果您需要打开 XLS 文件而不是 XLSX 文件，http://npoi.codeplex.com/是一个不错的选择。我们已经在我们的项目中使用了它，效果很好。

回答by Kunal Kakkad

I have used Excel.dlllibrary which is:

我使用了Excel.dll库，它是：

• open source
• lightweight
• fast
• compatible with xls and xlsx

• 开源
• 轻的
• 快速地
• 与 xls 和 xlsx 兼容

The documentation available over here: https://exceldatareader.codeplex.com/

此处提供的文档：https: //exceldatareader.codeplex.com/

Strongly recommendable.

强烈推荐。

回答by Nick

Ive just been searching for a solution and come across Spreadsheetlight

我一直在寻找解决方案并遇到了 Spreadsheetlight

which looks very promising. Its open source and available as a nuget package.

这看起来很有希望。它是开源的，可作为 nuget 包使用。