Click here to Skip to main content
11,432,427 members (65,465 online)
Click here to Skip to main content

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

, 28 May 2005
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

Comments and Discussions

 
GeneralGenerates XML but Excel Sheet is blank Pin
E P Sharp11-Feb-10 3:05
memberE P Sharp11-Feb-10 3:05 
GeneralRe: Generates XML but Excel Sheet is blank Pin
Peeter1231-Sep-10 0:19
memberPeeter1231-Sep-10 0:19 
GeneralLoad Bulk Data ( DAtaset Having 50,000 Records) Pin
menukutti25-Nov-09 0:56
membermenukutti25-Nov-09 0:56 
GeneralVery useful Pin
kdwarak22-Sep-09 2:12
memberkdwarak22-Sep-09 2:12 
Generalgood one Pin
mansuri.isteyaq8-Sep-09 0:55
membermansuri.isteyaq8-Sep-09 0:55 
QuestionHow to open this file after you export Pin
MK_00713-Jul-09 12:42
memberMK_00713-Jul-09 12:42 
AnswerRe: How to open this file after you export Pin
Xodiak25-Nov-09 3:52
memberXodiak25-Nov-09 3:52 
QuestionDoes anyone have a similar method to export a DataSet (or even a single DataTable) to a format OpenOffice.org 3.1 Calc can view? Pin
Geoffrey D. Roberts18-Jun-09 13:20
memberGeoffrey D. Roberts18-Jun-09 13:20 
AnswerRe: Does anyone have a similar method to export a DataSet (or even a single DataTable) to a format OpenOffice.org 3.1 Calc can view? Pin
Geoffrey D. Roberts18-Jun-09 13:41
memberGeoffrey D. Roberts18-Jun-09 13:41 
GeneralVery Helpful Pin
corcaigh6-Jun-09 1:05
membercorcaigh6-Jun-09 1:05 
QuestionHow can you set the path where the file is saved? Pin
asianx27-Apr-09 10:43
memberasianx27-Apr-09 10:43 
AnswerRe: How can you set the path where the file is saved? Pin
KilManish@gmail.com6-May-09 4:27
memberKilManish@gmail.com6-May-09 4:27 
GeneralWarning [modified] Pin
ElectronikBean24-Apr-09 2:36
memberElectronikBean24-Apr-09 2:36 
GeneralConverted to VB, also handles multiple Data Tables and creates a new Sheet for every Data Table and names the Sheet same as Data Table Name Pin
UniBond16-Apr-09 7:47
memberUniBond16-Apr-09 7:47 
GeneralSaving it as an Microsoft Office Excel Workbook Pin
JimmyJamz15-Apr-09 5:13
memberJimmyJamz15-Apr-09 5:13 
GeneralRe: Saving it as an Microsoft Office Excel Workbook Pin
Xodiak15-Apr-09 6:42
memberXodiak15-Apr-09 6:42 
QuestionTime Calulation Pin
nishant pathak1-Apr-09 4:19
membernishant pathak1-Apr-09 4:19 
AnswerRe: Time Calulation Pin
Xodiak1-Apr-09 5:50
memberXodiak1-Apr-09 5:50 
GeneralExporting multiple datatable to multiple sheets in excel using C# Pin
venkateswaran0223-Feb-09 21:20
membervenkateswaran0223-Feb-09 21:20 
GeneralRe: Exporting multiple datatable to multiple sheets in excel using C# Pin
Xodiak24-Feb-09 5:47
memberXodiak24-Feb-09 5:47 
GeneralRe: Exporting multiple datatable to multiple sheets in excel using C# Pin
vikram_asv4-Mar-09 8:17
membervikram_asv4-Mar-09 8:17 
GeneralVariation on a theme Pin
Ger Rietman11-Feb-09 22:59
memberGer Rietman11-Feb-09 22:59 
GeneralConverted it to VB.NET and then changed from StreamWriter to StringWriter Pin
Member 161590819-Nov-08 5:20
memberMember 161590819-Nov-08 5:20 
QuestionVB ? Pin
Fernando Velazco29-Sep-08 14:46
memberFernando Velazco29-Sep-08 14:46 
GeneralMultiple DataTables inside DataSet Pin
James Luterek3-Sep-08 5:41
memberJames Luterek3-Sep-08 5:41 
Questionhow to convert datagridview content to dataset? Pin
Saeed.3949-May-08 22:31
memberSaeed.3949-May-08 22:31 
AnswerRe: how to convert datagridview content to dataset? Pin
Xodiak9-Jun-08 5:04
memberXodiak9-Jun-08 5:04 
AnswerRe: how to convert datagridview content to dataset? Pin
gg423717-Dec-09 2:33
membergg423717-Dec-09 2:33 
QuestionExcel Dates with a year less than 1900 Pin
jeff0077-Mar-08 4:39
memberjeff0077-Mar-08 4:39 
GeneralRe: Excel Dates with a year less than 1900 Pin
jeff00710-Mar-08 4:07
memberjeff00710-Mar-08 4:07 
AnswerRe: Excel Dates with a year less than 1900 Pin
Kronass9-Jun-08 4:45
memberKronass9-Jun-08 4:45 
GeneralExport for Office 2000 Pin
Member 383654719-Feb-08 15:36
memberMember 383654719-Feb-08 15:36 
GeneralRe: Export for Office 2000 Pin
Member 383654725-Feb-08 10:24
memberMember 383654725-Feb-08 10:24 
GeneralRe: Export for Office 2000 Pin
Xodiak26-Feb-08 17:49
memberXodiak26-Feb-08 17:49 
GeneralRe: Export for Office 2000 Pin
Kronass9-Jun-08 4:53
memberKronass9-Jun-08 4:53 
GeneralFill with color a Cell Pin
Member 38365478-Feb-08 8:41
memberMember 38365478-Feb-08 8:41 
GeneralRe: Fill with color a Cell Pin
Xodiak8-Feb-08 9:47
memberXodiak8-Feb-08 9:47 
GeneralWorks great Pin
GutterBoy23-Jan-08 8:23
memberGutterBoy23-Jan-08 8:23 
GeneralGread Article!! [modified] Pin
NingNing28-Nov-07 20:23
memberNingNing28-Nov-07 20:23 
GeneralReg exporting data Pin
girishganeshan3-Oct-07 0:53
membergirishganeshan3-Oct-07 0:53 
GeneralDrawback Pin
Giorgi Dalakishvili15-Sep-07 8:45
memberGiorgi Dalakishvili15-Sep-07 8:45 
GeneralRe: Drawback Pin
Xodiak16-Sep-07 16:09
memberXodiak16-Sep-07 16:09 
GeneralRe: Drawback Pin
Giorgi Dalakishvili16-Sep-07 21:32
memberGiorgi Dalakishvili16-Sep-07 21:32 
Generalmodification Pin
Hoinarut15-Sep-07 7:40
memberHoinarut15-Sep-07 7:40 
GeneralGOOD Pin
-BoBo13-Sep-07 14:05
member-BoBo13-Sep-07 14:05 
QuestionHow to add Group and Outline in this example Pin
happy_she26-Aug-07 18:08
memberhappy_she26-Aug-07 18:08 
AnswerRe: How to add Group and Outline in this example Pin
Xodiak27-Aug-07 14:04
memberXodiak27-Aug-07 14:04 
GeneralUse DataGridView Extension Pin
Alexander Alexandrov20-Aug-07 2:23
memberAlexander Alexandrov20-Aug-07 2:23 
QuestionHow to use HTML string instead of dataset Pin
Mustakim Mansuri12-Aug-07 22:13
memberMustakim Mansuri12-Aug-07 22:13 
AnswerRe: How to use HTML string instead of dataset Pin
Xodiak13-Aug-07 14:37
memberXodiak13-Aug-07 14:37 

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
Web01 | 2.8.150428.2 | Last Updated 28 May 2005
Article Copyright 2005 by Xodiak
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid