Click here to Skip to main content
15,845,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
    }
Posted
Comments
lailac88 20-Mar-13 5:04am    
have you already tried this one??

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900