Create table in Excel using CSharp ADO.NET

The CREATE TABLE command will create a table in an Excel workbook. The workbook for the connection will be created if it does not exist.

string connectionString = @"Provider=Microsoft.Jet.
   OLEDB.4.0;Data Source=Book1.xls;Extended
   Properties=""Excel 8.0;HDR=YES;""";

DbProviderFactory factory =
   DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;

    using (DbCommand command = connection.CreateCommand())
    {
         command.CommandText="CREATE TABLE NewSheet (Field1 char(10), Field2 float, Field3 date)";

        connection.Open();

        command.ExecuteNonQuery();
    }
}



Some rules and restriction for using datatype for excel sheet

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