导入Excel电子数据表来串的都是空 [英] Import Excel to DataTable string's are empty

问题描述

导入Excel电子数据表来，我现在用的是简单的code：

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

字符串ommited和我的数据集看起来像这样

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.

我试过各种可能的方式（设置表结构事前，使用的DataReader ，更改单元格的格式，...），他们都失败了。这似乎与 Microsoft.Jet.OLEDB 供应商的问题。我强烈电子书籍，您可以使用第三方练成阅读库。有很多开源库可供选择。

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.

否则，你可以看看这个答案以多找几个库。

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

这篇关于导入Excel电子数据表来串的都是空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！