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


 


            }


Comments (3)

  1. sotto says:

    Thanks for this method that "Just Works"!

    Although people might want to check the XMLstring = XMLstring.Replace("&","&"); stuff after copy/paste from the web.

    Btw, I changed those lines with:

    XMLstring = System.Security.SecurityElement.Escape(XMLstring);

    which does the xml-escaping nice-and-clean.

    And for the datetime-formatting i prefer:

    string XMLDatetoString = XMLDate.ToString("yyyy-MM-ddThh:mm:ss.fff",                                     CultureInfo.InvariantCulture);

Skip to main content