Click here to Skip to main content
11,573,432 members (58,180 online)
Click here to Skip to main content

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

, 28 May 2005 438.3K 119
Rate this:
Please Sign up or sign in to vote.
A simple function that writes a DataSet to a Microsoft Excel document.

Introduction

This function takes in a DataSet and file name and writes the DataSet to an Excel worksheet. The code is pretty straightforward. Great thing about this function is that, it's technically an XML file that is saved as an XLS file. So it can be used as either file format. No more leading zero truncation on numbers that look like strings. Example, if you made a tab delimited file and put a field such as "00036" (a field that looks like a number but should be regarded as a string), MS Excel would truncate the leading zeros... This problem is solved with this method.

Here is the code:

public static void exportToExcel(DataSet source, string fileName)

{

    System.IO.StreamWriter excelDoc;

    excelDoc = new System.IO.StreamWriter(fileName);
    const string startExcelXML = "<xml version>\r\n<Workbook " + 
          "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" + 
          " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " + 
          "xmlns:x=\"urn:schemas-    microsoft-com:office:" + 
          "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" + 
          "office:spreadsheet\">\r\n <Styles>\r\n " + 
          "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " + 
          "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" + 
          "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" + 
          "\r\n <Protection/>\r\n </Style>\r\n " + 
          "<Style ss:ID=\"BoldColumn\">\r\n <Font " + 
          "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " + 
          "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" + 
          " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " + 
          "ss:ID=\"Decimal\">\r\n <NumberFormat " + 
          "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " + 
          "<Style ss:ID=\"Integer\">\r\n <NumberFormat " + 
          "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " + 
          "ss:ID=\"DateLiteral\">\r\n <NumberFormat " + 
          "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " + 
          "</Styles>\r\n ";
     const string endExcelXML = "</Workbook>";

     int rowCount = 0;
     int sheetCount = 1;
     /*
    <xml version>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom"/>
      <Borders/>
      <Font/>
      <Interior/>
      <NumberFormat/>
      <Protection/>
    </Style>
    <Style ss:ID="BoldColumn">
      <Font x:Family="Swiss" ss:Bold="1"/>
    </Style>
    <Style ss:ID="StringLiteral">
      <NumberFormat ss:Format="@"/>
    </Style>
    <Style ss:ID="Decimal">
      <NumberFormat ss:Format="0.0000"/>
    </Style>
    <Style ss:ID="Integer">
      <NumberFormat ss:Format="0"/>
    </Style>
    <Style ss:ID="DateLiteral">
      <NumberFormat ss:Format="mm/dd/yyyy;@"/>
    </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
    </Worksheet>
    </Workbook>
    */
    excelDoc.Write(startExcelXML);
    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
    excelDoc.Write("<Table>");
    excelDoc.Write("<Row>");
    for(int x = 0; x < source.Tables[0].Columns.Count; x++)
    {
      excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
      excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
      excelDoc.Write("</Data></Cell>");
    }
    excelDoc.Write("</Row>");
    foreach(DataRow x in source.Tables[0].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=\"Sheet" + sheetCount + "\">");
        excelDoc.Write("<Table>");
      }
      excelDoc.Write("<Row>"); //ID=" + rowCount + "
      for(int y = 0; y < source.Tables[0].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("&","&");
             XMLstring = XMLstring.Replace(">",">");
             XMLstring = XMLstring.Replace("<","<");
             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();
    }

Note

To see what generated, just pass the file name with a .txt extension. For Excel format, the file name will be .xls. For XML format, the file name will be .xml.

The Export Routine does have one side effect! (if anyone can figure out a solution to this, it would be greatly appreciated). The file is saved as an .XLS file, but it technically is still an XML file. This little nuance makes the file size larger then it really should be. A quick fix to this is to just do File Save As.... after the file has been exported. When you do the Save As in Excel, it will reconstruct it as a "real" Excel file, and it will bring the file size down to what it should be.

Feed Back is always welcome.

Simple as that...Enjoy.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Xodiak
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionMessage Automatically Removed Pin
6-Dec-13 5:31
membernilesh316-Dec-13 5:31 
GeneralMy vote of 5 Pin
BrianLaF3-Aug-12 10:32
memberBrianLaF3-Aug-12 10:32 
QuestionHow to define 1000 grouping symbol for decimal number Pin
botngot835-Feb-12 23:03
memberbotngot835-Feb-12 23:03 
AnswerRe: How to define 1000 grouping symbol for decimal number Pin
botngot838-Mar-12 15:08
memberbotngot838-Mar-12 15:08 
GeneralMy vote of 5 Pin
SavindraSingh29-Jan-12 23:51
memberSavindraSingh29-Jan-12 23:51 
QuestionMicrosoft Office Excel cannot open or save any more documents Pin
squidder1411-Jan-12 4:44
membersquidder1411-Jan-12 4:44 
GeneralA simpler, and free, class to export from a DataSet into an Excel 2007 .xlsx file. Pin
MikeGledhill1-Dec-11 2:03
memberMikeGledhill1-Dec-11 2:03 
GeneralMy vote of 5 Pin
Rajesh Kumar Chekuri16-Nov-11 20:09
memberRajesh Kumar Chekuri16-Nov-11 20:09 
AnswerVery nice code Pin
Victorio A Pellicano7-Nov-11 8:31
memberVictorio A Pellicano7-Nov-11 8:31 
Question*FIX* blank worksheet Pin
aarharden30-Oct-11 11:14
memberaarharden30-Oct-11 11:14 
GeneralMy vote of 5 Pin
murat8938-Jul-11 2:34
membermurat8938-Jul-11 2:34 
QuestionJust a quick note about your System.DateTime case.... Pin
jp2code27-Jun-11 6:07
memberjp2code27-Jun-11 6:07 
GeneralMy vote of 5 Pin
Arjun14521-Jun-11 23:46
memberArjun14521-Jun-11 23:46 
GeneralHello hello Pin
skelmannen10-Nov-10 10:03
memberskelmannen10-Nov-10 10:03 
GeneralNew VB version... Pin
Jalapeno Bob5-Nov-10 6:00
memberJalapeno Bob5-Nov-10 6:00 
QuestionWould this approach create a lot of xls files on the web server? Pin
eric.yu.cn12-Oct-10 23:28
membereric.yu.cn12-Oct-10 23:28 
GeneralGiving error in office 2007. Please help. Pin
raghav196-Sep-10 1:46
memberraghav196-Sep-10 1:46 
GeneralExcel created as blank Pin
Peeter1231-Sep-10 19:14
memberPeeter1231-Sep-10 19:14 
The excel shows up as blank when opened when i am using response.contenttype. Actually i want the user to have open/save option.
Any help on this will be highly appreciated. Thanks in Advance
Generalgood article~~~ ^^ Pin
Metalzang11-Aug-10 14:41
memberMetalzang11-Aug-10 14:41 
Question[question] Re: good article~~~ ^^ Pin
Metalzang11-Aug-10 15:43
memberMetalzang11-Aug-10 15:43 
GeneralVery useful Pin
PoojaGRD10-Aug-10 23:52
memberPoojaGRD10-Aug-10 23:52 
GeneralSchema source question Pin
Consulting Mechanic20-May-10 11:55
memberConsulting Mechanic20-May-10 11:55 
GeneralRe: Schema source question Pin
Xodiak20-May-10 12:07
memberXodiak20-May-10 12:07 
GeneralExporting Dataset to Excel 2007 with Hyperlink in vb.net or C#.net Pin
mohankundenagoud29-Mar-10 1:00
membermohankundenagoud29-Mar-10 1:00 
GeneralRe: Exporting Dataset to Excel 2007 with Hyperlink in vb.net or C#.net Pin
CikaPero16-Jul-10 0:12
memberCikaPero16-Jul-10 0:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150624.2 | Last Updated 28 May 2005
Article Copyright 2005 by Xodiak
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid