Import Excel to DataTable string's are empty

问题:

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:

回答1:

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.

回答2:

MD.Unicorn's answer is not 100% correct. Your OLEDB provider uses a settings named TypeGuessRows to determine how many rows are read to decide the data type of a column. Unfortunately this setting cannot be specified in the connection string and must be changed in the system registry. See this question for more details.

回答3:

Try to change your oledbconnection string as following format:

Code Snippet OleDbConnection con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\book1.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");

Note: "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

回答4:

use IMEX=1 in connection string. hope it will resolve this issue..