Creating an Excel Sheet with ADO.NET (OleDb) --> What DataTypes are Supported?

Question

Hi

I try to create an Excel (2003) Sheet with ADO.NET (OleDb).

I was able to create the Sheet with an OleDbCommand:

var cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;...";
var cnn = new OleDbConnection(cnnString);
var cmd = cnn.CreateCommand();
cnn.Open();
cmd.CommandText = "CREATE TABLE MySheet (ID char(255), Field1 char(255))";
cmd.ExecuteNonQuery();

That works as expected.

Here my Question: What DataTypes (like char(255)) are Supported by Excel within the CREATE TABLE command? I did google but didn't find any documentation or hints.

Thanks for your help.

Answer 1

Excel recognizes only a limited set of data types. For example:

• All numeric columns are doubles
• All string columns (other than memo columns) are 255-character Unicode strings

Numbers

All versions of Excel:

• 8-byte double
• [signed] short [int] – used for Boolean values and also integers
• unsigned short [int]
• [signed long] int

Strings

All versions of Excel:

• [signed] char * – null-terminated byte strings of up to 255 characters
• unsigned char * – length-counted byte strings of up to 255 characters

Excel 2007+ only:

• unsigned short * – Unicode strings of up to 32,767 characters, which can be null-terminated or length-counted

Answer 2

You will always get better results using the Excel object model directly, and there isn't much more code required. See example on SO

or if you wanted to get really keen you could try the Open XML SDK 2.0 for Microsoft Office