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]

On 28 Jun 2005 03:17:13 -0700, "Sam Jost" <sam...@web.de> wrote:

¤ I written myself some small generic class to export any dataset to an

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 schrieb:

[Paul Clement]

On 28 Jun 2005 07:43:11 -0700, "Sam Jost" <sam...@web.de> wrote:

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