Export to Excel Workbook with multiple sheets with out COM component

Wished to export reports in to Excel workbook containing multiple worksheets with out COM component?

Try this method

Input: dataset as source and the XLS file name

Output: Excel work book with multiple sheets corresponding to each of the data table in the data set. It also takes care of the row limit (64,000) in a worksheet.

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);

                  bool selected = false;

                  foreach(DataTable currentDataTable in source.Tables)

                  {

                        excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");

                        if (!selected)

                        {

                              excelDoc.Write("<WorksheetOptions><Selected/></WorksheetOptions>");

                              selected = true;

                        }

                        excelDoc.Write("<Table>");

                        excelDoc.Write("<Row>");

                        for(int x = 0; x < currentDataTable.Columns.Count; x++)

                        {

                              excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");

                              excelDoc.Write(currentDataTable.Columns[x].ColumnName);

                              excelDoc.Write("</Data></Cell>");

                        }

                        excelDoc.Write("</Row>");

                        foreach(DataRow x in currentDataTable.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 < currentDataTable.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()) +

                       &n bsp; ".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>");

                        sheetCount++;

                       

                  }

                 

                  excelDoc.Write(endExcelXML);

                  excelDoc.Close();

            }

Sample usage:

private void btnSaveXLSFormat_Click(object sender, System.EventArgs e)

            {

                  // Set the content type to Excel

DataSet ds = new DataSet();

                  for( int i =0; i < drp_Segment.Items.Count; i++ )

                  {

                        ListItem li = drp_Segment.Items[i];

                        if (li.Selected == true)

                        {

                              ds.Tables.Add((DataTable)CalculateQuesFrequencies(i));

                              //repeaterDataSource.Add(de.Key, de);

                        }

                  }

                  //WRITING AS AN XLS

string filePath="";

                  string fileName="";

filePath = ConfigurationSettings.AppSettings.Get("Reports").ToString() + "Report_QuesFreq.xls";

                  fileName = "Report_QuesFreq.xls";

                  ExportToExcel(ds, filePath);

                 

                  Response.Clear();

                  Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);

                  Response.ContentType = "Application/x-msexcel";

                  try

                  {

                        Response.WriteFile(filePath);

                  }

                  catch (Exception ex)

                  {

                        throw new Exception("I/O error");

                  }

                  Response.End();

            }