IF YOU ARE USING SQL SERVER TO DOWNLOAD DATA IN EXCEL USE THIS CODE
FETCH THE DATA WITH SQL CONNECTION AND HERE CMD1 IS SQLCOMMAND
SqlDataAdapter da = new SqlDataAdapter(cmd1);
DataTable dt1 = new DataTable();
da.Fill(dt1);
DataColumnCollection dcCollection = dt1.Columns;
Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook wb = ExcelApp.Workbooks.Add();
int i = 1;
int j = 1;
foreach (DataColumn col in dt1.Columns)
{
ExcelApp.Cells[i, j] = col.ColumnName;
j++;
}
i++;
foreach (DataRow row in dt1.Rows)
{
for (int k = 1; k < dt1.Columns.Count + 1; k++)
{
ExcelApp.Cells[i, k] = row[k - 1].ToString();
}
i++;
}
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ExcelApp.Cells[1,8];
rg.EntireColumn.NumberFormat = "MM/DD/YYYY";
Microsoft.Office.Interop.Excel.Range rg1 = (Microsoft.Office.Interop.Excel.Range)ExcelApp.Cells[1,9];
rg1.EntireColumn.NumberFormat = "MM/DD/YYYY";
ExcelApp.Columns.AutoFit();
wb.SaveAs("C:\\EmployeeDetails.xls", Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
Response.Write("<script>alert('Data Downloaded Successfully ');</script>");