Convert multiple tables in a dataset to multiple sheets in Excel

The first method:

1. On the design page, there is a button that transitions when the user clicks the button

<asp:button id="Export " runat="server" text= " Export " onclick="export_click" />

View Code

2. Code in which the conversion occurred

   protected voidExport_click (Objectsender, EventArgs e) {          using(SqlConnection conn =NewSqlConnection (configurationmanager.connectionstrings["demosdatabaseconnectionstring"]. ConnectionString)) {DataSet DS=NewSystem.Data.DataSet (); stringSQL1 ="SELECT * from Customers"; SqlDataAdapter sda1=NewSqlDataAdapter (SQL1, Conn); Sda1. Fill (DS,"Table1"); stringSQL2 ="SELECT * from Users"; SqlDataAdapter sda2=NewSqlDataAdapter (SQL2, Conn); Sda2. Fill (DS,"Table2"); stringSQ3 ="SELECT * from Accordioncontent"; SqlDataAdapter Sda3=NewSqlDataAdapter (SQ3, Conn); Sda3. Fill (DS,"Table3"); //The file save path              stringFileName ="D:\\testing.xls"; Application Excelapp=Newapplication (); Workbook ExcelWorkbook=NULL; Worksheet Excelworksheet=NULL; Excelapp.visible=true; ExcelWorkbook=ExcelApp.Workbooks.Add (Xlwbatemplate.xlwbatworksheet); List<string> sheetnames =Newlist<string>(); Sheetnames.add ("Customers Information"); Sheetnames.add ("Users Information"); Sheetnames.add ("accordioncontent Information"); Try            {                 for(inti =1; I < DS. Tables.count; i++) ExcelWorkBook.Worksheets.Add (); //Adding New sheet in Excel Workbook                 for(inti =0; I < DS. Tables.count; i++)                {                    intR =1;//Initialize Excel Row Start Position = 1Excelworksheet = Excelworkbook.worksheets[i +1]; //Writing Columns Name in Excel Sheet                     for(intCol =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(introw =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(intCol =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 processinchProcess.getprocessesbyname ("Excel") ) process.            Kill (); }} Response.Write ("successfully!"); }

View Code

Data Source:
Http://www.c-sharpcorner.com/Blogs/10767/generate-excel-with-multiple-sheet-from-dataset.aspx

The second method:

2. How the conversion occurs

 protected voidButton1_Click (Objectsender, EventArgs e) {          using(SqlConnection conn =NewSqlConnection (configurationmanager.connectionstrings["demosdatabaseconnectionstring"]. ConnectionString)) {DataSet DS=NewSystem.Data.DataSet (); stringSQL1 ="SELECT * from Customers"; SqlDataAdapter sda1=NewSqlDataAdapter (SQL1, Conn); Sda1. Fill (DS,"Table1"); stringSQL2 ="SELECT * from Users"; SqlDataAdapter sda2=NewSqlDataAdapter (SQL2, Conn); Sda2. Fill (DS,"Table2"); stringSQ3 ="SELECT * from Accordioncontent"; SqlDataAdapter Sda3=NewSqlDataAdapter (SQ3, Conn); Sda3. Fill (DS,"Table3"); varExcel =NewMicrosoft.Office.Interop.Excel.Application (); varworkbook = Excel. Workbooks.Add (true); Addexcelsheet (ds. tables[1], workbook); Addexcelsheet (ds. tables[0], workbook); Workbook. SaveAs (@"D:\MyExcelWorkBook2.xls"); Workbook.          Close (); }      }

View Code

Data Source:

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

Convert multiple tables in a dataset to multiple sheets in Excel