将Excel导入DataTable字符串为空 [英] Import Excel to DataTable string's are empty

问题描述

  string connectionString = string.Format（Provider = Microsoft.ACE.OLEDB.12.0; data source = {0};扩展属性= Excel 12.0;，physicalFolder + FileUpload1.FileName）; 
 OleDbDataAdapter adapter = new OleDbDataAdapter（SELECT * FROM [Sheet1 $]，connectionString）; 
 DataSet ds = new DataSet（）; 
  

当在其中一行excel中，如果我的行看起来像下面

字符串被忽略，我的数据集看起来像这样

但是，如果我添加一些字符串，如果我的上传如下所示：

然后我的数据集看起来不会省略字符串：

解决方案

这是因为提供者决定列的第一行的列类型（行后标题行）。当第一行包含一个数字时，列的类型是 double 或另一个数字类型，因此它不能包含字符串值。

我尝试了所有可能的方法（事先设置表结构，使用 DataReader ，更改单元格的格式...），并且它们都失败。这似乎是 Microsoft.Jet.OLEDB 提供者的问题。我高估了你使用第三方excel阅读库。有很多开源库可用。

如果您的文件是Excel 2007（.xlsx）文件，我强烈建议您使用 EPPluse 。它也可作为 NuGet软件包获得。

<否则，您可以查看此答案，以查找更多图书馆。

To import excel to datatable, I am using the simple code:

string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", physicalFolder + FileUpload1.FileName);
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
DataSet ds = new DataSet();

When in one of the rows of excel, if my row looks like below

strings are ommited and my data set looks like this

However if I add some strings and if my upload looks like this:

Then my dataset looks like it does not omit the strings:

解决方案

This is because the provider decides on the type of the column from first row of the column (the row after the header row). When first row contains a number, the type of column is double or another number type, so it cannot contain string values.

I tried every possible way (setting the table structure beforehand, using a DataReader, changing the format of the cell, ...) and they all failed. It seem to be the problem with Microsoft.Jet.OLEDB provider. I highly recomment you to use a third party excel reading library. There are plenty of open source libraries available.

If your file is a Excel 2007 (.xlsx) file, I highly recommend using EPPluse. It is also available as a NuGet package.

Otherwise, you can take a look at this answer to find a few more libraries.

这篇关于将Excel导入DataTable字符串为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！