Creating an Excel Workbook via OLEDB without headers

Question

I have a C# program to write to Excel which is to support files without headers and files with.

If I write where I want headers, this is fine, but in the case of reading a file without headers, I then want to write a table without headers. If I use the same headings to create a table, I still get F1, F2, F3 etc in the content.

My connection string for writing is:-

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=filename;Extended Properties="Excel 8.0;HDR=No;IMEX=0;"

Any ideas on how to lose the headers?

possible duplicate of OleDB & mixed Excel datatypes : missing data — user2140173

Sid Holland Sid Holland · Accepted Answer · 2013-02-17T22:17:19

This question's a bit old now, but the following is how I answered another similar question:

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 8.0;HDR=NO\"";

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

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

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

    conn.Close();
}

Creating an Excel Workbook via OLEDB without headers

1 Answers