I'm using c# and ADO.NET to create and write to an excel spreadsheet via the jet4.0 ole provider. Works great except that excel seems to be prepending every cell with an apostrophe! Here's some of my code:
//Create a new file
xlscnn.Open();
cmd.CommandText = "CREATE TABLE " + tablename + " (";
for (int ix = 0; ix < dt.Columns.Count; ix++)
{
cmd.CommandText += dt.Columns[ix].ColumnName + " text";
if (ix != dt.Columns.Count - 1)
cmd.CommandText += ", ";
}
cmd.CommandText += ")";
cmd.ExecuteNonQuery();
//loop through the columns and contruct the insert paratmeters
for (int ix = 0; ix < dt.Columns.Count; ix++)
{
cmd.Parameters.Add ( "@param" + ix.ToString(), System.Data.OleDb.OleDbType.VarChar, 1000);
cmd.Parameters["@param" + ix.ToString()].Value = dt.Rows[iRow][ix].ToString();
}
//Execute the insert statement
cmd.ExecuteNonQuery();
//Create a new file
xlscnn.Open();
cmd.CommandText = "CREATE TABLE " + tablename + " (";
for (int ix = 0; ix < dt.Columns.Count; ix++)
{
cmd.CommandText += dt.Columns[ix].ColumnName + " text";
if (ix != dt.Columns.Count - 1)
cmd.CommandText += ", ";
}
cmd.CommandText += ")";
cmd.ExecuteNonQuery();
//loop through the columns and contruct the insert paratmeters
for (int ix = 0; ix < dt.Columns.Count; ix++)
{
cmd.Parameters.Add ( "@param" + ix.ToString(), System.Data.OleDb.OleDbType.VarChar, 1000);
cmd.Parameters["@param" + ix.ToString()].Value = dt.Rows[iRow][ix].ToString();
}
//Execute the insert statement
cmd.ExecuteNonQuery();