Try something like this:
//create random file so as not to conflict
string dataFile = "";
//make sure the temp file is not read only (copied from template) as this stops the OLE connection from updating
File.SetAttributes(dataFile, FileAttributes.Normal);
//inject data
System.Data.OleDb.OleDbConnection tempExcelConnection = new System.Data.OleDb.OleDbConnection();
tempExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";
tempExcelConnection.ConnectionString += "Data Source=" + dataFile + ";";
tempExcelConnection.ConnectionString += @"Extended Properties=""Excel 8.0;HDR=Yes;"";";
System.Data.OleDb.OleDbCommand tempExcelCommand = new System.Data.OleDb.OleDbCommand();
tempExcelCommand.Connection = tempExcelConnection;
System.Data.SqlClient.SqlDataReader dr = null;
tempExcelCommand.Connection.Open();
// TODO: Get data for SqlDataReader
if (dr.HasRows)
{
while(dr.Read())
{
//NB. Ths query expects a named range called "mynamedrange" containing 5 columns
//the data will be inserted after the named range so should only include the column headings
tempExcelCommand.CommandText = String.Format("INSERT INTO [mynamedrange] VALUES ({0}, {1}, {2}, {3}, {4})", dr[0], dr[1], dr[2], dr[3], dr[4]);
tempExcelCommand.ExecuteNonQuery();
}
}
dr.Close();
tempExcelCommand.Connection.Close();
Regards Paul
