C# Alter add column on excel with OLEDB

Question

I am trying to add a column in Excel sheet with OLEDB by using ALTER statement. I am not able to do it. I tried with following code but its not working out

sqladd = "ALTER TABLE Data ADD NewRow varchar(50)";

Here, Data is the Sheet name in Excel and NewRow is column name to be added.

Answer 1

Sadly OleDb does not support the Alter Table statement but you can add columns by using a Create Table statement on an existing sheet with the new column defined at the end. This will actually only add the new column and leave the existing data untouched.

You must open the workbook in ReadWrite mode and not include an IMEX=1 in your extended properties. So first define and open your connection...

string sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                           "Data Source=e:\\Test.xlsx;Mode=ReadWrite;" +
                           "Extended Properties=\"Excel 12.0;HDR=Yes\"";

OleDbConnection oConnection = new OleDbConnection();
oConnection.ConnectionString = sConnectionString;
oConnection.Open();

This spreadsheet already has Col1, Col2 and Col3 on Sheet1, so then...

string sSql = "Create Table [Sheet1$] (Col1 Int, Col2 Int, Col3 Int, NewColumn Int)";

using (OleDbCommand oCmd = new OleDbCommand(sSql, oConnection))
{
    oCmd.ExecuteNonQuery();
}

You can also copy data into a new sheet and drop the original BUT the drop only clears the contents as you cannot actually delete sheets using OleDB...

Select [Sheet1$].*, Null As NewColumn Into [Sheet2] From [Sheet1$]

Drop Table [Sheet1$]