How To Insert Data From Database To Excel Sheet Using EPPlus in C#

I have created a sample code where when a button is pressed, the data from the database will be loaded to the Excel sheet.

In Button event, I have called the function to generate the Excel, as GenerateExcel(). While the Event name is btn_Submit.

  1. protected void btnSubmit_Click(object sender, EventArgs e) {  
  2.     this.GenerateExcel();  
  3. }   

Now, let’s see the code of loading data from database to Excel using EPPlus in C#. In the code, I have explained the purpose of the code in comments.

  1. private void GenerateExcel() {  
  2.     try {  
  3.         DataTable dtStudent = this.FetchFromDataBase();  
  4.         if (dtStudent.Rows.Count > 0) { // File Name of Excel.  
  5.             string FileName = "Training Register " + DateTime.Now.ToString("ddMMMyyyyHHmmss") + ".xlsx";  
  6.             HttpContext.Current.Response.Clear();  
  7.             HttpContext.Current.Response.ClearContent();  
  8.             HttpContext.Current.Response.ClearHeaders();  
  9.             HttpContext.Current.Response.Buffer = true;  
  10.             HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;  
  11.             HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);  
  12.             HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  13.             HttpContext.Current.Response.AddHeader("content-disposition""attachment;filename=" + FileName);  
  14.             using(ExcelPackage pack = new ExcelPackage()) {  
  15.                 ExcelWorksheet ws = pack.Workbook.Worksheets.Add(Server.MapPath("..\\files") + "\\" + FileName);  
  16.                 ws.Row(1).Height = 55; // Header In the Excel.  
  17.                 ws.Cells["A2:L2"].Value = "Student Details” ;  
  18.                 ws.Cells["A2:L2"].Merge = true;  
  19.                 ws.Cells["A2:L2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;  
  20.                 ws.Row(2).Style.Font.Bold = true;  
  21.                 ws.Row(3).Style.Font.Bold = true;  
  22.                 ws.Row(2).Style.Font.Size = 18;  
  23.                 int TotRows = dtStudent.Rows.Count + 4;  
  24.                 ws.InsertRow(TotRows, 5);  
  25.                 ws.Cells[TotRows + 2, 1].Style.Font.Bold = true;  
  26.                 ws.Cells[TotRows + 2, 1].Value = "Excel Generated By";  
  27.                 ws.Cells[TotRows + 2, 2].Style.Font.Bold = true;  
  28.                 ws.Cells[TotRows + 2, 2].Value = "Sundar";;  
  29.                 ws.Cells[TotRows + 2, 3].Style.Font.Bold = true;  
  30.                 ws.Cells[TotRows + 2, 3].Value = "Generated On";  
  31.                 ws.Cells[TotRows + 2, 4].Style.Font.Bold = true;  
  32.                 ws.Cells[TotRows + 2, 4].Value = DateTime.Now.ToString();  
  33.                 ws.Cells["A3"].LoadFromDataTable(dtStudent, true);  
  34.                 System.IO.MemoryStream ms = new System.IO.MemoryStream();  
  35.                 pack.SaveAs(ms);  
  36.                 ms.WriteTo(HttpContext.Current.Response.OutputStream);  
  37.             }  
  38.             HttpContext.Current.Response.Flush();  
  39.             HttpContext.Current.Response.End();  
  40.         } else {  
  41.             Page.ClientScript.RegisterStartupScript(this.GetType(), "message""showmessage('No Training Records Found !!2')"true);  
  42.         }  
  43.     } catch {  
  44.         Page.ClientScript.RegisterStartupScript(this.GetType(), "message""showmessage('Excel Generation Failed !!2')"true);  
  45.     }  
  46. }  

The below ADO.NET code is already written. You can use normal ADO.NET code to fetch data using Stored Procedures.

  1. //ADO.NET Code to Fetch from Datatable  
  2. private void FetchFromDataBase() {  
  3.     DBConnection db = new DBConnection();  
  4.     DataTable dt = db.ExecuteDataSet("sp_training_emp_excel", CommandType.StoredProcedure).Tables[0];  
  5.     db = null;  
  6.     return dt;  
  7. }  

My Stored Procedure.

  1. CREATE PROCEDURE [dbo].[sp_select_all]  
  2. AS  
  3. BEGIN  
  4. SELECT name as Name, dept as Department,total as Total,average as Average,result as Result  
  5. FROM [Local].[dbo].[tbl_Student_details]  
  6. END  
Ebook Download
View all
Learn
View all