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