Creating an Excel Sheet with ADO.NET (OleDb) --> What DataTypes are Supported?
ado.net, c#, excel, oledb
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
Problem
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.