如何阅读XLSX (Excel)?

时间：2020-12-13 16:56:31

I have a problem with reading from .xlsx (Excel) file. I tried to use:

我有一个从.xlsx (Excel)文件读取的问题。我试图使用:

var fileName = @"C:\automated_testing\ProductsUploadTemplate-2015-10-22.xlsx";
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "XLSData");
DataTable data = ds.Tables["XLSData"];

// ... Loop over all rows.
StringBuilder sb = new StringBuilder();
foreach (DataRow row in data.Rows)
{
    sb.AppendLine(string.Join(",", row.ItemArray));
}

but if failed due to connectionString. So I updated the line to support .xlsx:

但是如果因为connectionString而失败了。所以我更新了行以支持。xlsx:

var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", fileName);

but I get:

但我得到:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

“microsoft.ac . oledb .12.0”提供程序没有在本地机器上注册。

(Problem here is that, I am not able to install new software on my remote-testing machine, so I am not able to fix it and need to find other solution.)

(这里的问题是，我无法在我的远程测试机上安装新软件，所以我无法修复它，需要找到其他解决方案。)

I do also need to be sure that imported data will be stored in some simple way (I am beginner programmer) to let me iterate through it i.e. to create objects with row's data.

我还需要确保导入的数据将以某种简单的方式存储(我是初学者程序员)，这样我就可以对其进行迭代，例如，使用row的数据创建对象。

Other approaches I checked:

我检查了其他的方法:

comment: seems to probably work for me, but doesn't support Excel files of unknown dimensions (random number of rows and columns).

注释:似乎对我有用，但不支持未知维度的Excel文件(随机的行数和列数)。

comment: doesn't support settings column names from different row than first one (in some of my Excel files, there are comments in 4-6 first rows and then is headers row and data below).

注释:不支持与第一行不同的设置列名(在我的一些Excel文件中，第4-6行有注释，然后是标题行和下面的数据)。

comment: same problem as above.

评论:同样的问题。

comment: downloaded package weight was over 60MB and it requires me to install it on system, which is not possible in my situation. Anyway, people comment that it is limited to 150 rows.

备注:下载的包重量超过60MB，需要我在系统上安装，在我的情况下是不可能的。不管怎样，人们说它被限制在150行。

Meanwhile I will try to check https://code.google.com/p/linqtoexcel/, but all other ideas are more than welcome!

与此同时，我将尝试检查https://code.google.com/p/linqtoexcel/，但是所有其他的想法都非常受欢迎!

EDIT: Just checked that LinqToExcel, same issue as above:

编辑:刚刚查看了LinqToExcel，和上面一样的问题:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

“microsoft.ac . oledb .12.0”提供程序没有在本地机器上注册。

EDIT2: Ultimately, it seems that this solution solved my issue:

最终，这个解决方案似乎解决了我的问题:

https://*.com/a/19065266/3146582

https://*.com/a/19065266/3146582

3 个解决方案

#1

15  

If you are reading data from Excel file, you can use EPPlus NuGet package, and use following code

如果您正在从Excel文件读取数据，可以使用EPPlus NuGet包，并使用以下代码

//using OfficeOpenXml;
using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(@"C:\YourDirectory\sample.xlsx")))
{
    var myWorksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here
    var totalRows = myWorksheet.Dimension.End.Row;
    var totalColumns = myWorksheet.Dimension.End.Column;

    var sb = new StringBuilder(); //this is your your data
    for (int rowNum = 1; rowNum <= totalRows; rowNum++) //selet starting row here
    {
        var row = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
        sb.AppendLine(string.Join(",", row));
    }
}

#2

1  

Reading Excel files with OLE provider is possible only if MS Jet engine (MS Access) is installed. I noticed that you decided to use .NET interop to API but this is not a good idea: it requires installed MS Excel and doesn't recommended to use for automation on servers.

只有安装了MS Jet engine (MS Access)，才能使用OLE提供程序读取Excel文件。我注意到您决定使用. net interop到API，但这不是一个好主意:它需要安装MS Excel，不建议在服务器上使用自动化。

If you don't need to support old (binary) Excel formats (xls) and reading XLSX is enough I recommend to use EPPlus library. It provides simple and powerful API for both reading and writing XLSX files (and has a lot of examples):

如果您不需要支持旧的(二进制)Excel格式(xls)并阅读XLSX就足够了，我建议您使用EPPlus库。它为阅读和编写XLSX文件提供了简单而强大的API(并有很多示例):

var existingFile = new FileInfo(filePath);
// Open and read the XlSX file.
using (var package = new ExcelPackage(existingFile)) {
   // access worksheets, cells etc
}

#3

-2  

Are you developing on this machine too? If no, I would suggest using OpenXml SDK you have to install it only on developer's machine.

你也在这台机器上开发吗?如果没有，我建议您只能在开发人员的机器上安装OpenXml SDK。

#1

15  

If you are reading data from Excel file, you can use EPPlus NuGet package, and use following code

如果您正在从Excel文件读取数据，可以使用EPPlus NuGet包，并使用以下代码

//using OfficeOpenXml;
using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(@"C:\YourDirectory\sample.xlsx")))
{
    var myWorksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here
    var totalRows = myWorksheet.Dimension.End.Row;
    var totalColumns = myWorksheet.Dimension.End.Column;

    var sb = new StringBuilder(); //this is your your data
    for (int rowNum = 1; rowNum <= totalRows; rowNum++) //selet starting row here
    {
        var row = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
        sb.AppendLine(string.Join(",", row));
    }
}

#2

1  

Reading Excel files with OLE provider is possible only if MS Jet engine (MS Access) is installed. I noticed that you decided to use .NET interop to API but this is not a good idea: it requires installed MS Excel and doesn't recommended to use for automation on servers.

只有安装了MS Jet engine (MS Access)，才能使用OLE提供程序读取Excel文件。我注意到您决定使用. net interop到API，但这不是一个好主意:它需要安装MS Excel，不建议在服务器上使用自动化。

If you don't need to support old (binary) Excel formats (xls) and reading XLSX is enough I recommend to use EPPlus library. It provides simple and powerful API for both reading and writing XLSX files (and has a lot of examples):

如果您不需要支持旧的(二进制)Excel格式(xls)并阅读XLSX就足够了，我建议您使用EPPlus库。它为阅读和编写XLSX文件提供了简单而强大的API(并有很多示例):

var existingFile = new FileInfo(filePath);
// Open and read the XlSX file.
using (var package = new ExcelPackage(existingFile)) {
   // access worksheets, cells etc
}

#3

-2  

Are you developing on this machine too? If no, I would suggest using OpenXml SDK you have to install it only on developer's machine.

你也在这台机器上开发吗?如果没有，我建议您只能在开发人员的机器上安装OpenXml SDK。

标签：

相关文章