当在其中一行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字符串为空
发布评论