Export a DataSet to Microsoft Excel without the use of COM objects

thts may b the max size of a excel worksheet is 65k .....
i have a prob as my dataset can return more than 65k records ....
any soln for making it in one sheet ????

Hi,

This is a very useful way of generating Excel sheets as there is option if having multiple worksheets and font colours and size can also be given.
I have used the above method to generate excel file. But in some machines the file opens as as XML document [with all the tags visible]. How can it be solved. Confused | :confused:

Is there any way to convert the file XML-Excel generated to be programatically converted to Binary-Excel file without using COM? Smile | :)

Thanks
Vikky.

there is actually, there is a thread a couple of lines down that shows how to put it in a binary format without the use of com.

Hi,
I tried the option but was not able to have multiple worksheets in it; also the font size and colors cannot be set.
Hence any help in creating multiple work sheets in binary-excel without COM will be greatly appreciated.
Thanks,
Vikky.

Where is this "thread a couple of lines down" ..? A couple lines down from *where*? Confused | :confused:

Thanks for the code.

I have created an XML document. It opens in Explorer and looks the way XML should. The formatting issue comes when I open it (sample.xml) in Excel 2000. All the values are concatenated into row0,col0.

What am I missing?

Thanks

TuneFish

does your data have any less than or greater than signs? that might be cause an issue in formating. the way i figured out how to format the file was to simply go into excel and type out the data how i wanted it. i would then save the file in xml format, and see what i came up with. you might want to check that version with the one created by your code to see if there is a difference. let me know if you need any more help

The solution given in this article generates an xml file which is understood by excel application. It doesn't generates a excel file in binary format. To do so you need to do "Save as" while file is open in excel application, and choose appropriate file type.

If you want to generate excel-binary file programaticaly then here is the code. Just call exportToExcel(dataset, filePath), where dataset is the DataSet containing data for which excel file has to be generated, and filePath is the string containing complete filePath where the file should be generated.

using System;<br />
using System.IO;<br />
using System.Collections;<br />
using System.Data;<br />
using System.Text;<br />
using System.Data.OleDb;<br />
<br />
private static  OleDbConnection GetConnection(string filePath ) <br />
  {<br />
   return new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;\"");<br />
  }<br />
<br />
  public static void exportToExcel(DataTable dt, string fileName)<br />
  {<br />
   #region initialization<br />
   //if the file already exists then delete it<br />
   System.IO.FileInfo fi = new System.IO.FileInfo(fileName);<br />
   if (fi.Exists)<br />
   {<br />
    fi.Delete();<br />
   }<br />
   <br />
   //set the table name if its not there<br />
   if (dt.TableName == string.Empty)<br />
   {<br />
    dt.TableName = "Sheet1";<br />
   }<br />
   else//remove $ from table name if it is there<br />
   {<br />
    dt.TableName = dt.TableName.Replace("$",string.Empty);<br />
   }<br />
   #endregion<br />
<br />
   <br />
   string sql = GetTableCreationSql(dt);<br />
   OleDbConnection connection =  GetConnection(fileName);<br />
   OleDbCommand command = new OleDbCommand();<br />
   command.Connection = connection;<br />
   try<br />
   {<br />
    #region Create table<br />
    command.CommandText = GetTableCreationSql(dt);<br />
    command.Connection.Open();<br />
    command.ExecuteNonQuery();<br />
    #endregion<br />
    <br />
    #region Insert Into Table<br />
    <br />
    command.CommandText = GetInsertSql(dt);<br />
<br />
    foreach(DataRow row in dt.Rows)<br />
    {<br />
     SetParametersInCommand(row, command);<br />
     command.ExecuteNonQuery();<br />
    }<br />
    <br />
    #endregion<br />
   }<br />
   finally<br />
   {<br />
    command.Connection.Close();<br />
   }<br />
<br />
  }<br />
<br />
 <br />
<br />
  /// <summary><br />
  /// form a string which should be the sqlCommand for creating Table in oleDB<br />
  /// </summary><br />
  /// <param name="dt"></param><br />
  /// <returns>create table [tableName] ( Column1 type, ..., Columnn Type)</returns><br />
  private static string GetTableCreationSql(DataTable dt)<br />
  {<br />
   StringBuilder sqlBuilder = new StringBuilder();<br />
   sqlBuilder.Append("create table [");<br />
   sqlBuilder.Append(dt.TableName);<br />
   sqlBuilder.Append( "] ( " );<br />
<br />
   #region Get Column List<br />
   //Create a list of column names and their data types, e.g<br />
   //[ColumnName1] nvarchar, [ColumnName2] nvarchar,...[ColumnNamen] nvarchar<br />
   foreach(DataColumn col in dt.Columns)<br />
   {<br />
    if (col.Ordinal== 0)<br />
     sqlBuilder.Append( "[" );<br />
    else<br />
     sqlBuilder.Append( ", [" );<br />
<br />
    sqlBuilder.Append(col.ColumnName.Trim());<br />
    sqlBuilder.Append("] nvarchar");<br />
   }<br />
   #endregion<br />
<br />
   sqlBuilder.Append(" )"); <br />
<br />
   return sqlBuilder.ToString();<br />
  }<br />
<br />
  /// <summary><br />
  /// form a insert statement to insert data into oleDB<br />
  /// </summary><br />
  /// <param name="dt"></param><br />
  /// <returns>Insert Into tableName ([ColumnName1], [ColumnName2] ,...[ColumnNamen]) values (?,?,..,?) </returns><br />
  private static string GetInsertSql(DataTable dt)<br />
  {<br />
   StringBuilder sqlBuilder = new StringBuilder();<br />
   StringBuilder paramMarks = new StringBuilder();<br />
   sqlBuilder.Append("Insert Into [");<br />
   sqlBuilder.Append(dt.TableName);<br />
   sqlBuilder.Append( "] ( " );<br />
<br />
<br />
   #region Get Column List<br />
   //Create a list of column names and their place holders<br />
   //[ColumnName1] , [ColumnName2] ,...[ColumnNamen] <br />
   // ?,?,...?<br />
   foreach(DataColumn col in dt.Columns)<br />
   {<br />
    if (col.Ordinal == 0)<br />
    {<br />
     sqlBuilder.Append( "[" );<br />
     paramMarks.Append( "?" );<br />
    }<br />
    else<br />
    {<br />
     sqlBuilder.Append( ", [");<br />
     paramMarks.Append(",?");<br />
    }<br />
<br />
    sqlBuilder.Append(col.ColumnName.Trim());<br />
    sqlBuilder.Append( "] " );<br />
   }<br />
   #endregion<br />
<br />
   sqlBuilder.Append(" ) values ("); <br />
   sqlBuilder.Append(paramMarks.ToString() );<br />
   sqlBuilder.Append( ")" );<br />
<br />
   return sqlBuilder.ToString();<br />
  }<br />
<br />
<br />
  private static void SetParametersInCommand(DataRow row, OleDbCommand command)<br />
  {<br />
   UnicodeEncoding en = new UnicodeEncoding();<br />
   string argumentName= string.Empty;<br />
   int index = 0;<br />
   command.Parameters.Clear();<br />
   foreach(DataColumn col in row.Table.Columns)<br />
   {<br />
    argumentName = "@Args" + index;<br />
    command.Parameters.Add( new OleDbParameter(argumentName, OleDbType.VarBinary)).Value = <br />
     en.GetBytes(row[ col ].ToString());<br />
    index++;<br />
   }   <br />
  }

Sanjeev Kumar Singh,(sanjeevofbcs@hotmail.com)

modified on Friday, April 17, 2009 4:51 AM

Hi Sanjeev,

Is there any method by which the cell can have font colour and size as well.
Also if the dataset has multiple tables are they stored in diffrent worksheets in Excel document?

Thanks
Vikky.

Hi Vikky,
1> I don't know a way to apply formating for font color and size.
2> If the dataset has multiple tables then you will need to slightly change the code. For each datatable in DataSet call GetTableCreationSql(DataTable dt) and GetInsertSql(DataTable dt) functions. It will add different sheets for different tables in DataSet.

~Sanjeev.

The above code works well except for an extra ' in each Excel column. Where does this extra ' come from?

Header: 'EMPLOYEE ID

Row: ' 101922

This is exactly what I was looking for. It works great. I only wish there was a way to format the columns.

Thanks.

Ed Smile | :)

great and useful code, btw it seems to me there is an error, in SetParametersInCommand parameters are added to the command object, but the list is not cleared, so the insert query only addes repetively the first row, just add a command.Parameters.Clear(); before the foreach and it's ok.

thanks a LOT

Mike,You are right. I had updated the code. Thanks for pointing it out.

modified on Friday, April 17, 2009 5:44 AM

Excellent it works flawlessly. Bear in mind though that JET does not support 64bits so if you planning on developing 64bits apps then use the XML version instead.

Thanks again for the solutions guys

Wow- works like a charm! Nice job!

there is a small error in the code, so be sure to check my thread that explains the error and a couple of tweaks to make it run faster

could you provide a link to your thread please?

Fantastic job. This works very well. I added a foreach statement so that it could handle multiple tables and name each sheet with the name. Here is the minimally tested code:

C#

    excelDoc.Write(startExcelXML);

    foreach (DataTable currentTable in source.Tables)
    {
        excelDoc.Write("<Worksheet ss:Name=\"" + currentTable.TableName + "\">");
        excelDoc.Write("<Table>");
        excelDoc.Write("<Row>");
        for (int x = 0; x < currentTable.Columns.Count; x++)
        {
            excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
            excelDoc.Write(currentTable.Columns[x].ColumnName);
            excelDoc.Write("</Data></Cell>");
        }
        excelDoc.Write("</Row>");
        foreach (DataRow x in currentTable.Rows)
        {
            rowCount++;
            //if the number of rows is > 64000 create a new page to continue output
            if (rowCount == 64000)
            {
                rowCount = 0;
                sheetCount++;
                excelDoc.Write("</Table>");
                excelDoc.Write(" </Worksheet>");
                excelDoc.Write("<Worksheet ss:Name=\"" + currentTable.TableName + sheetCount + "\">");
                excelDoc.Write("<Table>");
            }
            excelDoc.Write("<Row>"); //ID=" + rowCount + "
            for (int y = 0; y < currentTable.Columns.Count; y++)
            {
                System.Type rowType;
                rowType = x[y].GetType();
                switch (rowType.ToString())
                {
                    case "System.String":
                        string XMLstring = x[y].ToString();
                        XMLstring = XMLstring.Trim();
                        XMLstring = XMLstring.Replace("&","&amp;");
                        XMLstring = XMLstring.Replace(">","&gt;");
                        XMLstring = XMLstring.Replace("<","&lt;");
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                       "<Data ss:Type=\"String\">");
                        excelDoc.Write(XMLstring);
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.DateTime":
                        //Excel has a specific Date Format of YYYY-MM-DD followed by
                        //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                        //The Following Code puts the date stored in XMLDate
                        //to the format above
                        DateTime XMLDate = (DateTime)x[y];
                        string XMLDatetoString = ""; //Excel Converted Date
                        XMLDatetoString = XMLDate.Year.ToString() +
                             "-" +
                             (XMLDate.Month < 10 ? "0" +
                             XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                             "-" +
                             (XMLDate.Day < 10 ? "0" +
                             XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                             "T" +
                             (XMLDate.Hour < 10 ? "0" +
                             XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                             ":" +
                             (XMLDate.Minute < 10 ? "0" +
                             XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                             ":" +
                             (XMLDate.Second < 10 ? "0" +
                             XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                             ".000";
                        excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                     "<Data ss:Type=\"DateTime\">");
                        excelDoc.Write(XMLDatetoString);
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.Boolean":
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                    "<Data ss:Type=\"String\">");
                        excelDoc.Write(x[y].ToString());
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.Int16":
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                "<Data ss:Type=\"Number\">");
                        excelDoc.Write(x[y].ToString());
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.Decimal":
                    case "System.Double":
                        excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                              "<Data ss:Type=\"Number\">");
                        excelDoc.Write(x[y].ToString());
                        excelDoc.Write("</Data></Cell>");
                        break;
                    case "System.DBNull":
                        excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                              "<Data ss:Type=\"String\">");
                        excelDoc.Write("");
                        excelDoc.Write("</Data></Cell>");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + " not handled."));
                }
            }
            excelDoc.Write("</Row>");
        }
        excelDoc.Write("</Table>");
        excelDoc.Write(" </Worksheet>");
    }

    excelDoc.Write(endExcelXML);
    excelDoc.Close();
}

Alright guys, a small error in the code. Note: it was correct until it became not just a user submission.
Here is the error, the error occured because of html conversion.

Its in the System.String case statement. Please be sure to change it.
Also, performance overall is improved if you combine all the excelDoc.Write() commands under each case statment Smile | :)

helps out alot.

C#

case "System.String":
            string XMLstring = x[y].ToString();
            XMLstring = XMLstring.Trim();
            XMLstring = XMLstring.Replace("&","&amp;");
            XMLstring = XMLstring.Replace(">","&gt;");
            XMLstring = XMLstring.Replace("<","&lt;");
            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                           "<Data ss:Type=\"String\">");
            excelDoc.Write(XMLstring);
            excelDoc.Write("</Data></Cell>");
            break;

Alternative way is to use some of Excel reading/writing components. Probably the cheapest .NET component is ExcelLite from GemBox Software (http://www.gemboxsoftware.com/ExcelLite.htm[New Window]). They also claim to be the fastest on the market.

And I always get aggravated by people advertising commercial products in response to articles here. You may always buy a component and start using it, but an article will teach you something and it's ok if you don't want to learn it but if you want to share an alternative post the code, not ask others to buy something. It's always an option, but that's what this site is for - to see HOW something can be done and learn from the authors.

I'll probably try and get this into one of our products, because using the Excel interop DLL's proved to be a little nightmare.

I look forward to seeing more info about the Excel format in your next articles.

Good one.

Cheers,
Simon

sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.

article :: animation mechanics in SVG blog:: brokenkeyboards
"Most of us are programmers, but a few use VB", Christian Graus

the office PIA's are apparently geared for use with VB.NET... which doesn't help us C#-ers. I was thinking of writing classes to generate Excel-format XML - a bit of an API. I just wonder if there are any potential issues - legal or technical - by not using the interop DLLs and writing the XML instead.

Just curious... I created a VB.NET class that does something similar, but for a pretty large datatable, I am getting horrible performance (5 to 10 minutes before I get a return document). I was wondering if using an XML Transform on the Dataset XML improves the performance some.

I was thinking of creating an XML Transform based on the dataset then generating the dataset XML and then applying the transform.

For smaller tables, the current function works great...

Export a DataSet to Microsoft Excel without the use of COM objects

Introduction

Here is the code:

Note

License

Comments and Discussions