Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#
Article

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

Rate me:
Please Sign up or sign in to vote.
3.84/5 (84 votes)
28 May 20051 min read 656.2K   120   158
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:

C#
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


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
BugThe XML directive and xmlns spacing both need this simple fix. Pin
mkamoski21-Feb-19 4:49
mkamoski21-Feb-19 4:49 
GeneralExcel will complain "The file format and extension of 'test.xls' don't match" here, but is still works. Pin
mkamoski21-Feb-19 4:47
mkamoski21-Feb-19 4:47 
GeneralMy vote of 5 Pin
BrianLaF3-Aug-12 10:32
BrianLaF3-Aug-12 10:32 
QuestionHow to define 1000 grouping symbol for decimal number Pin
botngot835-Feb-12 23:03
botngot835-Feb-12 23:03 
AnswerRe: How to define 1000 grouping symbol for decimal number Pin
botngot838-Mar-12 15:08
botngot838-Mar-12 15:08 
GeneralMy vote of 5 Pin
SavindraSingh29-Jan-12 23:51
SavindraSingh29-Jan-12 23:51 
QuestionMicrosoft Office Excel cannot open or save any more documents Pin
squidder1411-Jan-12 4:44
squidder1411-Jan-12 4:44 
GeneralA simpler, and free, class to export from a DataSet into an Excel 2007 .xlsx file. Pin
Michael Gledhill1-Dec-11 2:03
Michael Gledhill1-Dec-11 2:03 
GeneralMy vote of 5 Pin
Rajesh Kumar Chekuri16-Nov-11 20:09
Rajesh Kumar Chekuri16-Nov-11 20:09 
AnswerVery nice code Pin
Victorio A Pellicano7-Nov-11 8:31
Victorio A Pellicano7-Nov-11 8:31 
Question*FIX* blank worksheet Pin
aarharden30-Oct-11 11:14
aarharden30-Oct-11 11:14 
GeneralMy vote of 5 Pin
murat8938-Jul-11 2:34
murat8938-Jul-11 2:34 
QuestionJust a quick note about your System.DateTime case.... Pin
jp2code27-Jun-11 6:07
professionaljp2code27-Jun-11 6:07 
GeneralMy vote of 5 Pin
Arjun14521-Jun-11 23:46
Arjun14521-Jun-11 23:46 
Great article
GeneralHello hello Pin
skelmannen10-Nov-10 10:03
skelmannen10-Nov-10 10:03 
GeneralNew VB version... Pin
Jalapeno Bob5-Nov-10 6:00
professionalJalapeno 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
eric.yu.cn12-Oct-10 23:28 
GeneralGiving error in office 2007. Please help. Pin
raghav196-Sep-10 1:46
raghav196-Sep-10 1:46 
GeneralExcel created as blank Pin
Peeter1231-Sep-10 19:14
Peeter1231-Sep-10 19:14 
Generalgood article~~~ ^^ Pin
Metalzang11-Aug-10 14:41
Metalzang11-Aug-10 14:41 
Question[question] Re: good article~~~ ^^ Pin
Metalzang11-Aug-10 15:43
Metalzang11-Aug-10 15:43 
GeneralVery useful Pin
PoojaGRD10-Aug-10 23:52
PoojaGRD10-Aug-10 23:52 
GeneralSchema source question Pin
Consulting Mechanic20-May-10 11:55
Consulting Mechanic20-May-10 11:55 
GeneralRe: Schema source question Pin
Xodiak20-May-10 12:07
Xodiak20-May-10 12:07 
GeneralExporting Dataset to Excel 2007 with Hyperlink in vb.net or C#.net Pin
MohanMbnr29-Mar-10 1:00
MohanMbnr29-Mar-10 1:00 

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

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