Write Excel File with Two DataTables

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I have an issue where I want to merge two DataTables into one Excel *** (in
C#).

The first datatable is read from an existing Excel file, and is three rows
of header information.

The second datatable is the information to fill in the report.

I can merge the datatables with no problem. The issue is that I cannot
figure out how to write it out.

I believe that the issue is with getting the appropriate column name for the
INSERT INTO command. The first column of the header data is actually a
merged cell (or 8 cells or so) with a lot of text.

I use something like the following to get the "column name" but still get an
error about an unknown field name.

<CODE>
OleDbCommand myCommand = new OleDbCommand("Select * from
[Sheet1$];");
myConnection = new OleDbConnection(connectionString);

myConnection.Open();
myCommand.Connection = myConnection;

OleDbDataReader reader = myCommand.ExecuteReader();
DataTable columnNames = reader.GetSchemaTable();
reader.Close();

OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCommand);
myAdapter.Fill(excelData);

// Do some processing


StringBuilder cmd = new StringBuilder("INSERT INTO [Sheet1$]
([");
cmd.Append(columnNames.Rows[0][0]);
cmd.Append("], [");
cmd.Append(columnNames.Rows[1][0]);
cmd.Append("]) VALUES (\"");
cmd.Append(value1);
cmd.Append("\", \"");
cmd.Append(value2);
cmd.Append("\")");
myCommand = new OleDbCommand(cmd.ToString());
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();
</CODE>

I've tried to name Column A in the Excel file, but it doesn't seem to help.

Any pointers would be appreciated.

Thanks,
WtS
.

Quantcast