首页 > 代码库 > 转换DataSet中的多个表为Excel中的多个Sheets
转换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
转换DataSet中的多个表为Excel中的多个Sheets
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。