导出Excel多个表多个sheet

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

巴特西

导出Excel多个表多个sheet

最新文章

热门文章