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();
}