Post
Ask Question

Export Multiple Excel Sheets from Multiple Gridview.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using System.IO;  
  10. using OfficeOpenXml;  
  11. using System.Configuration;  
  12.   
  13. public partial class _Default: System.Web.UI.Page {  
  14.     protected void Page_Load(object sender, EventArgs e) {  
  15.   
  16.     }  
  17.   
  18.     protected void Button1_Click(object sender, EventArgs e) {  
  19.         GetExcel ge = new GetExcel();  
  20.         ge.ProcessRequest(HttpContext.Current);  
  21.     }  
  22.   
  23.     public class GetExcel: IHttpHandler {  
  24.         public void ProcessRequest(HttpContext context) {  
  25.             DataSet dataSet = new DataSet();  
  26.   
  27.             SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISALog1ConnectionString"].ToString());  
  28.             SqlCommand cmd = new SqlCommand("exec ProxyReport", conn);  
  29.             cmd.CommandTimeout = 200;  
  30.             SqlDataAdapter ad = new SqlDataAdapter(cmd);  
  31.             ad.Fill(dataSet);  
  32.   
  33.             GridView1.DataSource = dataSet.Tables[0];  
  34.             GridView1.DataBind();  
  35.             GridView2.DataSource = dataSet.Tables[1];  
  36.             GridView2.DataBind();  
  37.             GridView3.DataSource = dataSet.Tables[2];  
  38.             GridView3.DataBind();  
  39.   
  40.             dataSet.Tables[0].TableName = "1";  
  41.             dataSet.Tables[1].TableName = "2";  
  42.             dataSet.Tables[2].TableName = "3";  
  43.   
  44.             int count = 3;  
  45.   
  46.             MemoryStream ms = GetExcel.DataTableToExcelXlsx(dataSet, count);  
  47.             ms.WriteTo(context.Response.OutputStream);  
  48.             context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  49.             context.Response.AddHeader("Content-Disposition", "attachment;filename=EPPlusData.xlsx");  
  50.             context.Response.StatusCode = 200;  
  51.             context.Response.End();  
  52.   
  53.         }  
  54.   
  55.         public bool IsReusable {  
  56.             get {  
  57.                 return false;  
  58.             }  
  59.         }  
  60.   
  61.         public static MemoryStream DataTableToExcelXlsx(DataSet ds, int count) {  
  62.             MemoryStream Result = new MemoryStream();  
  63.             ExcelPackage pack = new ExcelPackage();  
  64.   
  65.             for (int i = 1; i <= count; i++) {  
  66.                 DataTable table = ds.Tables[i.ToString()];  
  67.                 ExcelWorksheet ws = pack.Workbook.Worksheets.Add("MySheet" + i.ToString());  
  68.   
  69.                 int col = 1;  
  70.                 int row = 1;  
  71.   
  72.                 foreach(DataColumn cl in table.Columns) {  
  73.                     ws.Cells[row, col].Value = cl.ColumnName;  
  74.                     col++;  
  75.                 }  
  76.                 col = 1;  
  77.                 foreach(DataRow rw in table.Rows) {  
  78.                     foreach(DataColumn cl in table.Columns) {  
  79.                         if (rw[cl.ColumnName] != DBNull.Value) ws.Cells[row + 1, col].Value = rw[cl.ColumnName].ToString();  
  80.                         col++;  
  81.                     }  
  82.                     row++;  
  83.                     col = 1;  
  84.                 }  
  85.             }  
  86.             pack.SaveAs(Result);  
  87.             return Result;  
  88.         }  
  89.     }  
  90. }