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

问题描述

要将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 字符串为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！