Retrieve data in two different excel sheets.

See more:

C#3.0

C#

ASP

Hi I want to display result in two different excel sheets.
I use 2 data table and assign them to dataset. In dataset i got the proper values of 2 data table but at the time of display in different sheets it gives problem. Please correct my code its urgent.

Thanx

protected void btn_download_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        DataColumn dc = new DataColumn("id");
        //dt.Columns.Add(dc);
        //dc = new DataColumn("name");
        //dt.Columns.Add(dc);
        //dt.Rows.Add("1", "a");
        //dt.Rows.Add("2", "b");
        //dt.Rows.Add("3", "c");

        DataTable dt1 = new DataTable();
        //DataColumn dc1 = new DataColumn("id");
        //dt1.Columns.Add(dc1);
        //dc1 = new DataColumn("name");
        //dt1.Columns.Add(dc1);
        //dt1.Rows.Add("4", "j");
        //dt1.Rows.Add("5", "k");
        //dt1.Rows.Add("6", "l");
        


        from = (fromCalendar.SelectedDate);
        to = (ToCalendar.SelectedDate);
        client_id = Convert.ToInt32(drp_Client.SelectedValue);
        Compaign = drp_Compaign.SelectedValue;


        try
        {

            SqlCommand cmd_down_tw = new SqlCommand("proc_GetSemDataFromToDate", con);
            cmd_down_tw.CommandType = CommandType.StoredProcedure;

            cmd_down_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
            cmd_down_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
            cmd_down_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
            cmd_down_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

            SqlDataAdapter da_down_tw = new SqlDataAdapter(cmd_down_tw);
            da_down_tw.SelectCommand = cmd_down_tw;
            DataTable dt_down_tw = new DataTable();
            da_down_tw.Fill(dt);

            SqlCommand cmd_comp_tw = new SqlCommand("proc_GetAllSemDataFromToDate", con);
            cmd_comp_tw.CommandType = CommandType.StoredProcedure;

            cmd_comp_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
            cmd_comp_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
            cmd_comp_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
            cmd_comp_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

            da_comp_tw = new SqlDataAdapter(cmd_comp_tw);
            da_comp_tw.SelectCommand = cmd_comp_tw;
            dt_comp_tw = new DataTable();
            da_comp_tw.Fill(dt1);

            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            ds.Tables.Add(dt1);
            ConvertToExcel(ds);

           // dt_down_tw.Merge(dt_comp_tw);

            //DataTable[] data_tables = new DataTable[2];
            //data_tables[0] = dt_comp_tw;
            //data_tables[1] = dt_down_tw;

            //ExportDataSetToExcel(dt_down_tw, "Report.xls");
            //dt_down_tw.Dispose();
        }
        catch
        {

        }
    }
    public string ConvertToExcel(DataSet ds)
    {
        string FilePath;
        Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        ExcelApp.Application.Workbooks.Add(Type.Missing);

        DataTable dt = ds.Tables[0];
        DataTable dt1 = ds.Tables[1];

        Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[1];

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            Sheet1.Cells[1, i + 1] = dt.Columns[i].ColumnName;
        }

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                Sheet1.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
            }
        }

        Microsoft.Office.Interop.Excel.Worksheet Sheet2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets[2];
        for (int i = 0; i < dt1.Columns.Count; i++)
        {
            Sheet2.Cells[1, i + 1] = dt1.Columns[i].ColumnName;
        }

        for (int i = 0; i < dt1.Rows.Count; i++)
        {
            for (int j = 0; j < dt1.Columns.Count; j++)
            {
                Sheet2.Cells[i + 2, j + 1] = dt1.Rows[i][j].ToString();
            }
        }

        FilePath = "d:\\" + Guid.NewGuid() + ".xls";
        if (FilePath != string.Empty)
        {
            ExcelApp.ActiveWorkbook.SaveAs(FilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
        }
        return FilePath;
    }