Create a sheet into a workbook without headers ace.oledb

Question

How can I create a named sheet without headers - just like the default sheet - via ace.oledb?

The create command for a sheet must be something like:

CREATE TABLE [MySheet] (field1 type, field2 type ..., fieldn type )

It creates MySheet and always insert (regardless of HDR extended property in connection string or the registry setting FirstRowHasNames) a first line in MySheet containing field1, field2...fieldn

Basically I don't want a "Table Header" there, I just need to insert values in a newly created named empty sheet.

Answer 1

This isn't pretty, but it's the only way I've found to create a new worksheet with nothing in it. The only problem I've discovered is that Oledb automatically creates a named range on the header cells specified in the CREATE command, but assuming you don't care about that then this should work fine.

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
    ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 XML;HDR=NO\"";

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();

    using (OleDbCommand cmd = new OleDbCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "CREATE TABLE [MySheet] (<colname> <col type>)";  // Doesn't matter what the field is called
        cmd.ExecuteNonQuery();

        cmd.CommandText = "UPDATE [MySheet$] SET F1 = \"\"";
        cmd.ExecuteNonQuery();
    }

    conn.Close();
}