I written myself some small generic class to export any dataset to an
excel spreadsheet:
public static void Export(DataSet data, String excelFileName)
{
System.IO.File.Delete(excelFileName);
string strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="
+ System.IO.Path.GetDirectoryName(excelFileName) + @"\" +
System.IO.Path.GetFileName(excelFileName)
+ @";Extended Properties='Excel 8.0;HDR=YES'";
using (System.Data.OleDb.OleDbConnection objConn = new
System.Data.OleDb.OleDbConnection(strConnectionString))
using (System.Data.OleDb.OleDbCommand cmd = new
System.Data.OleDb.OleDbCommand("", objConn))
{
objConn.Open();
foreach (DataTable dt in data.Tables)
{
cmd.CommandText = "CREATE TABLE [" + dt.TableName + "] (";
String valueNames = "(";
Boolean first = true;
foreach (DataColumn dc in dt.Columns)
{
if (!first)
{
cmd.CommandText += ",\r\n";
valueNames += ", ";
}
cmd.CommandText += " [" + dc.ColumnName + "] NVARCHAR(100)";
valueNames += " [" + dc.ColumnName + "]";
first = false;
}
cmd.CommandText += ")";
valueNames += ")";
cmd.ExecuteNonQuery();
foreach (DataRow dr in dt.Rows)
{
String values = "(";
first = true;
foreach (DataColumn dc in dt.Columns)
{
if (!first)
values += ", ";
values += " '" + dr[dc] + "'";
first = false;
}
values += ")";
cmd.CommandText = "INSERT INTO [" + dt.TableName + "$] " +
valueNames + " VALUES " + values;
cmd.ExecuteNonQuery();
}
}
}
}
This does work quite ok for my uses, the only problem is: After export
the first character in every single cell of the excel spreadsheet is
the quotation mark '
Somehow the export does not strip the leading quotation marks from my
values - anyone can give me a hint how I do get rid of these?
thanks,
Sam