protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)), new DataColumn("Name", typeof(string)), new DataColumn("Country", typeof(string)) });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ExportDataSetToExcelAndDownload(ds);
}
private void ExportDataSetToExcelAndDownload(DataSet ds)
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(Server.MapPath("~/Test.xls"));
foreach (DataTable table in ds.Tables)
{
Excel.Worksheet worksheet = excelWorkBook.Worksheets[1] as Excel.Worksheet;
worksheet.Cells.Clear();
for (int i = 1; i < table.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
worksheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
}
}
}
excelWorkBook.Save();
excelWorkBook.Close();
excelApp.Quit();
Response.ContentType = ContentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");
Response.WriteFile(Server.MapPath("~/Test.xls"));
Response.End();
}