要将excel导入数据表,我使用的是简单的代码:
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();在excel的某一行中,如果我的行如下所示
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:
推荐答案这是因为提供者从列的第一行(标题行之后的行)决定列的类型.当第一行包含数字时,列的类型为double或其他数字类型,因此不能包含字符串值.
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.
我尝试了所有可能的方法(预先设置表结构,使用 DataReader,更改单元格的格式,...)但都失败了.这似乎是 Microsoft.Jet.OLEDB 提供程序的问题.我强烈建议您使用第三方的 Excel 阅读库.有大量可用的开源库.
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.
如果您的文件是 Excel 2007 (.xlsx) 文件,我强烈建议使用 EPPluse.它也可用作 NuGet 包.
If your file is a Excel 2007 (.xlsx) file, I highly recommend using EPPluse. It is also available as a NuGet package.
否则,您可以查看this answer以查找更多库.
Otherwise, you can take a look at this answer to find a few more libraries.
更多推荐
将 Excel 导入 DataTable 字符串为空
发布评论