Load Excel sheet into DataTable but only the first

问题:

I have an excel sheet that I want to load into a datatable withe OleDb. The sheet contains a multiline text column with up to 1000 chars.

However, using this code below, I only have 256 chars in my DataTable per cell after the import.

Is this a limitation from the provider or is it possible to tell it to read the whole column?

var connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\file.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
var sheetName = "Sheet1";

using (var con = new OleDbConnection(connectionString))
{
    con.Open();

    var table = new DataTable(sheetName);
    var query = "SELECT * FROM [" + sheetName + "]";
    OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
    adapter.Fill(table);
    return table;
}

回答1:

I found a solution.

The problem is that OleDb is guessing, which dbtype to choose. And, if the first few rows only contain data shorter than 256 chars, that is applied to all rows.

Howevery, as a workaround I just moved one row with large data to the beginning of the sheet and now the whole data gets imported.

Here is a link that describes the problem. There is also a workaround with a registry key, but I haven't tried that.

http://www.xtremevbtalk.com/showthread.php?t=206454

回答2:

This registry fix worked for me.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel]
"TypeGuessRows"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]
"TypeGuessRows"=dword:00000000