protected void Page_Load(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");
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.Tables.Add(dt1);
ConvertToExcel(ds);
}
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;
}