export dataset to excel

[Sam Jost]

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

 

[Paul Clement]

¤ 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?


That is the way the Excel ISAM driver was designed to work. It adds the apostrophe to discriminate
between text and numeric values. I don't believe it shows up in the cell, just the formula bar.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Sam Jost]

My bad - is there any way to get rid of this 'Feature by design' so I
don't have these apostrophs in every cell?
Maybe using a different type instead of NVARCHAR for the field,
anything?

Thanks,
Sam

 

[Paul Clement]

¤ My bad - is there any way to get rid of this 'Feature by design' so I
¤ don't have these apostrophs in every cell?
¤ Maybe using a different type instead of NVARCHAR for the field,
¤ anything?
¤

I seem to recall that if the Excel driver is not used to create the Worksheet then the apostrophe is
not included upon insert. Of course if you're using a Worksheet created in Excel then it must have
column headers to perform the Insert.

I also seem to remember that the behavior was specific to ADO/ADO.NET and not DAO.


Paul
~~~~
Microsoft MVP (Visual Basic)