transfering the data from 2 gridviews to 2 excel sheets in asp.net with c#

Question

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
I wrote this code but at the opening time of excel sheet it showing log error.Can any one solve this.




using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Web.UI.HtmlControls;
public partial class Default3 : System.Web.UI.Page
{
    DataSet ds;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            OleDbConnection cn = new OleDbConnection("user id=sa;password=123;provider=sqloledb.1;database=northwind");
            OleDbDataAdapter da1 = new OleDbDataAdapter("select * from employees", cn);
            OleDbDataAdapter da2 = new OleDbDataAdapter("select * from orders", cn);
             ds = new DataSet();
            cn.Open();
            da1.Fill(ds, "e");
            da2.Fill(ds, "d");
            GridView1.DataSource = ds.Tables[0];
            GridView2.DataSource = ds.Tables[1];
            GridView1.DataBind();
            GridView2.DataBind();
            ExportToExcel(ds, "e:\\Export.xls");
        }
      //  Response.Write(ds.Tables.Count);
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        ExportToExcel(ds, "e:\\Export.xls");
       // ExportFromHtmlForm(GridView1);
       // ExportFromHtmlForm(GridView2);
    }

  

 /*   public void ExportFromHtmlForm(GridView gv)
    {
        HtmlForm form = new HtmlForm();

        string attachment = "attachment; filename=e:\\PrintDetails.xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";

        //namespace (using system.IO)    
        StringWriter stw = new StringWriter();
        HtmlTextWriter htextw = new HtmlTextWriter(stw);

        gv.Parent.Controls.Add(form);
        form.Attributes["runat"] = "server";
        form.Controls.Add(gv);
        this.Controls.Add(form);
        form.RenderControl(htextw);
        Response.Write(stw.ToString());

        Response.End();
    }*/









    public static void ExportToExcel(DataSet dsInput, string ExcelFileName)
    {

        System.IO.StreamWriter ExportToExcelDoc;

        ExportToExcelDoc = new System.IO.StreamWriter(ExcelFileName);
        //Header Part of the Excel file is created here.
        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 ";
        //Footer Part of the Excel is declared here
        const string endExcelXML = "</Workbook>";

        int rowCount = 0;
        int sheetCount = 1;
        for (int i = 0; i < dsInput.Tables.Count; i++)
        {
        ExportToExcelDoc.Write(startExcelXML);
        ExportToExcelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
        ExportToExcelDoc.Write("<Table>");
        ExportToExcelDoc.Write("<Row>");
        //Creating Column Cell in Excel file from the datatable columns
      
            for (int x = 0; x < dsInput.Tables[i].Columns.Count; x++)
            {
                ExportToExcelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                ExportToExcelDoc.Write(dsInput.Tables[0].Columns[x].ColumnName);
                ExportToExcelDoc.Write("</Data></Cell>");
            }
            ExportToExcelDoc.Write("</Row>");
            //Storing the Row values from the tables.

            foreach (DataRow x in dsInput.Tables[i].Rows)
            {
                rowCount++;
                //Condition Check to Create a New Sheet If the row exceeds 64000
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    ExportToExcelDoc.Write("</Table>");
                    ExportToExcelDoc.Write(" </Worksheet>");
                    ExportToExcelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    ExportToExcelDoc.Write("<Table>");
                }
                ExportToExcelDoc.Write("<Row>");
                for (int y = 0; y < dsInput.Tables[i].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("<", "<");
                            ExportToExcelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            ExportToExcelDoc.Write(XMLstring);
                            ExportToExcelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = "";
                            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";
                            ExportToExcelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            ExportToExcelDoc.Write(XMLDatetoString);
                            ExportToExcelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            ExportToExcelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            ExportToExcelDoc.Write(x[y].ToString());
                            ExportToExcelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            ExportToExcelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            ExportToExcelDoc.Write(x[y].ToString());
                            ExportToExcelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            ExportToExcelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            ExportToExcelDoc.Write(x[y].ToString());
                            ExportToExcelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            ExportToExcelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            ExportToExcelDoc.Write("");
                            ExportToExcelDoc.Write("</Data></Cell>");
                            break;
                        //default:
                        //  throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                ExportToExcelDoc.Write("</Row>");
            }
            ExportToExcelDoc.Write("</Table>");
            ExportToExcelDoc.Write(" </Worksheet>");

            ExportToExcelDoc.Write(endExcelXML);
        }
        ExportToExcelDoc.Close();
    }

}