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

Question

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.

Solution

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

Comments

[gsharp]

I can't use the object model, because i have to create the excel on a server.

[TFD]

Have a look at the Open XML SDK 2.0 then. Still a much more efficient and precise way of generating .xlsx. If you need .xls you might be better off with some third party components?

[TFD]

There is an update for Office 2003 to r/w .xslx docs etc. IIRC it's called "2003 Compatibility FileFormatConverters"