转换DataSet中的多个表为Excel中的多个Sheets

第一种方法：

1. 在设计页面，有一个button按钮，当用户单击按钮的时候，发生转换

 <asp:Button ID="Export" runat="server" Text="Export" OnClick="Export_Click" />

2. 发生转换的代码

   protected void Export_Click(object sender, EventArgs e)
      {
          using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DemosDatabaseConnectionString"].ConnectionString))
          {
              DataSet ds = new System.Data.DataSet();

              string sql1 = "select * from Customers";
              SqlDataAdapter sda1 = new SqlDataAdapter(sql1, conn);
              sda1.Fill(ds, "Table1");

              string sql2 = "select * from Users";
              SqlDataAdapter sda2 = new SqlDataAdapter(sql2, conn);
              sda2.Fill(ds, "Table2");

              string sq3 = "select * from AccordionContent";
              SqlDataAdapter sda3 = new SqlDataAdapter(sq3, conn);
              sda3.Fill(ds, "Table3");


              //The file save path
              string FileName = "D:\\Testing.xls";
              Application ExcelApp = new Application();
              Workbook ExcelWorkBook = null;
              Worksheet ExcelWorkSheet = null;
             ExcelApp.Visible = true;
             ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            List<string> SheetNames = new  List<string>();
            SheetNames.Add("Customers Information");
            SheetNames.Add("Users Information");
            SheetNames.Add("AccordionContent Information");
             try
            {
                for (int i = 1; i < ds.Tables.Count; i++)
                    ExcelWorkBook.Worksheets.Add();  //Adding New sheet in Excel Workbook
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    int r = 1;  // Initialize Excel Row Start Position  = 1
                    ExcelWorkSheet = ExcelWorkBook.Worksheets[i + 1];
                     //Writing Columns Name in Excel Sheet
                    for (int col = 1; col < ds.Tables[i].Columns.Count; col++)
                        ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Columns[col - 1].ColumnName;
                    r++;
                     //Writing Rows into Excel Sheet
                    for (int row = 0; row < ds.Tables[i].Rows.Count; row++)  //r stands for ExcelRow and col for ExcelColumn
                    {
                        // Excel row and column start positions for writing Row=1 and Col=1
                        for (int col = 1; col < ds.Tables[i].Columns.Count; col++)
                            ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Rows[row][col - 1].ToString();
                        r++;
                    }
                    ExcelWorkSheet.Name = SheetNames[i];//Renaming the ExcelSheets
                }
                ExcelWorkBook.SaveAs(FileName);
                ExcelWorkBook.Close();
                ExcelApp.Quit();
                Marshal.ReleaseComObject(ExcelWorkSheet);
                Marshal.ReleaseComObject(ExcelWorkBook);
                Marshal.ReleaseComObject(ExcelApp);
            }
            catch (Exception exHandle)
            {
                Console.WriteLine("Exception: " + exHandle.Message);
                Console.ReadLine();
            }
             finally
            {
                foreach (Process process in Process.GetProcessesByName("Excel"))
                    process.Kill();
            }
        }
          Response.Write("Successfully!");
    }

资料来源：
http://www.c-sharpcorner.com/Blogs/10767/generate-excel-with-multiple-sheet-from-dataset.aspx

第二种方法：

2. 发生转换的方法

 

 protected void Button1_Click(object sender, EventArgs e)
      {
          using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DemosDatabaseConnectionString"].ConnectionString))
          {
              DataSet ds = new System.Data.DataSet();

              string sql1 = "select * from Customers";
              SqlDataAdapter sda1 = new SqlDataAdapter(sql1, conn);
              sda1.Fill(ds, "Table1");

              string sql2 = "select * from Users";
              SqlDataAdapter sda2 = new SqlDataAdapter(sql2, conn);
              sda2.Fill(ds, "Table2");

              string sq3 = "select * from AccordionContent";
              SqlDataAdapter sda3 = new SqlDataAdapter(sq3, conn);
              sda3.Fill(ds, "Table3");


              var excel = new Microsoft.Office.Interop.Excel.Application();
              var workbook = excel.Workbooks.Add(true);

              AddExcelSheet(ds.Tables[1], workbook);
              AddExcelSheet(ds.Tables[0], workbook);

              workbook.SaveAs(@"D:\MyExcelWorkBook2.xls");
              workbook.Close();


          }


      }

 

资料来源：

 

http://stackoverflow.com/questions/12980640/how-to-add-additional-worksheets-to-an-excel-from-datatable