This function takes in a DataSet and file name and writes the DataSet to an Excel worksheet.

This function creates an XML file and save 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 "00045" (a field that looks like a number but should be regarded as a string), Microsoft Excel would truncate the leading zeros. This problem is solved with this method.

Here is the source code

  public void Export_To_Excel(System.Data.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;
   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();
  }