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");
DataTable dt1 = new DataTable();
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);
}
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;
}