Skip to main content
Email Password   helpLost your password?

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.

You must Sign In to use this message board.
 
 
Per page   
 FirstPrevNext
GeneralVery useful Pin
kdwarak		2:12 22 Sep '09  
Generalgood one Pin
mansuri.isteyaq		0:55 8 Sep '09  
GeneralHow to open this file after you export Pin
MK_007		12:42 13 Jul '09  
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. Roberts		13:20 18 Jun '09  
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. Roberts		13:41 18 Jun '09  
GeneralVery Helpful Pin
corcaigh		1:05 6 Jun '09  
GeneralHow can you set the path where the file is saved? Pin
asianx		10:43 27 Apr '09  
GeneralRe: How can you set the path where the file is saved? Pin
KilManish@gmail.com		4:27 6 May '09  
GeneralWarning [modified] Pin
ElectronikBean		2:36 24 Apr '09  
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
UniBond		7:47 16 Apr '09  
GeneralSaving it as an Microsoft Office Excel Workbook Pin
JimmyJamz		5:13 15 Apr '09  
GeneralRe: Saving it as an Microsoft Office Excel Workbook Pin
Xodiak		6:42 15 Apr '09  
QuestionTime Calulation Pin
nishant pathak		4:19 1 Apr '09  
AnswerRe: Time Calulation Pin
Xodiak		5:50 1 Apr '09  
GeneralExporting multiple datatable to multiple sheets in excel using C# Pin
venkateswaran02		21:20 23 Feb '09  
GeneralRe: Exporting multiple datatable to multiple sheets in excel using C# Pin
Xodiak		5:47 24 Feb '09  
GeneralRe: Exporting multiple datatable to multiple sheets in excel using C# Pin
vikram_asv		8:17 4 Mar '09  
GeneralVariation on a theme Pin
Ger Rietman		22:59 11 Feb '09  
GeneralConverted it to VB.NET and then changed from StreamWriter to StringWriter Pin
Member 1615908		5:20 19 Nov '08  
GeneralVB ? Pin
Fernando Velazco		14:46 29 Sep '08  
GeneralMultiple DataTables inside DataSet Pin
James Luterek		5:41 3 Sep '08  
Generalhow to convert datagridview content to dataset? Pin
Saeed.394		22:31 9 May '08  
GeneralRe: how to convert datagridview content to dataset? Pin
Xodiak		5:04 9 Jun '08  
QuestionExcel Dates with a year less than 1900 Pin
jeff007		4:39 7 Mar '08  
GeneralRe: Excel Dates with a year less than 1900 Pin
jeff007		4:07 10 Mar '08  
Last Visit: 7:05 22 Nov '09     Last Update: 7:05 22 Nov '09 12345 Next »


Last Updated 28 May 2005 | Advertise | Privacy | Terms of Use | Copyright © CodeProject, 1999-2009