C# Export Dataset or Datatable to Excel

The following function provides the functionality to export a DataSet or DataTable into Excel file / format. It takes two arguments: Source and fileName. Source is in DataSet datatype. So if you are using DataTable, convert it to DataSet before calling the function.

public static void exportToExcel(DataSet source, string fileName)
        {

            System.IO.StreamWriter excelDoc;

            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "\r\n\r\n \r\n " +
                  "\r\n " +
                  "\r\n " +
                  "\r\n \r\n " +
                  "\r\n \r\n " +
                  "\r\n ";
            const string endExcelXML = "";

            int rowCount = 0;
            int sheetCount = 1;
            /*
           
           
           
           
           
           
           
           
           
           
           
           
           
           */
            excelDoc.Write(startExcelXML);
            excelDoc.Write("");
            excelDoc.Write("");             excelDoc.Write("");             for (int x = 0; x < source.Tables[0].Columns.Count; x++)             {                 excelDoc.Write("");                 excelDoc.Write(source.Tables[0].Columns[x].ColumnName);                 excelDoc.Write("");             }             excelDoc.Write("");             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("");
                    excelDoc.Write(" ");
                    excelDoc.Write("");
                    excelDoc.Write("");                 }                 excelDoc.Write(""); //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("" +                                            "");                             excelDoc.Write(XMLstring);                             excelDoc.Write("");                             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("" +                                          "");                             excelDoc.Write(XMLDatetoString);                             excelDoc.Write("");                             break;                         case "System.Boolean":                             excelDoc.Write("" +                                         "");                             excelDoc.Write(x[y].ToString());                             excelDoc.Write("");                             break;                         case "System.Int16":                         case "System.Int32":                         case "System.Int64":                         case "System.Byte":                             excelDoc.Write("" +                                     "");                             excelDoc.Write(x[y].ToString());                             excelDoc.Write("");                             break;                         case "System.Decimal":                         case "System.Double":                             excelDoc.Write("" +                                   "");                             excelDoc.Write(x[y].ToString());                             excelDoc.Write("");                             break;                         case "System.DBNull":                             excelDoc.Write("" +                                   "");                             excelDoc.Write("");                             excelDoc.Write("");                             break;                         default:                             throw (new Exception(rowType.ToString() + " not handled."));                     }                 }                 excelDoc.Write("");             }             excelDoc.Write("");
            excelDoc.Write(" ");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }