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.
- protected void btnSubmit_Click(object sender, EventArgs e) {
- this.GenerateExcel();
- }
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.
- private void GenerateExcel() {
- try {
- DataTable dtStudent = this.FetchFromDataBase();
- if (dtStudent.Rows.Count > 0) {
- string FileName = "Training Register " + DateTime.Now.ToString("ddMMMyyyyHHmmss") + ".xlsx";
- HttpContext.Current.Response.Clear();
- HttpContext.Current.Response.ClearContent();
- HttpContext.Current.Response.ClearHeaders();
- HttpContext.Current.Response.Buffer = true;
- HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
- HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
- HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
- HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + FileName);
- using(ExcelPackage pack = new ExcelPackage()) {
- ExcelWorksheet ws = pack.Workbook.Worksheets.Add(Server.MapPath("..\\files") + "\\" + FileName);
- ws.Row(1).Height = 55;
- ws.Cells["A2:L2"].Value = "Student Details” ;
- ws.Cells["A2:L2"].Merge = true;
- ws.Cells["A2:L2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
- ws.Row(2).Style.Font.Bold = true;
- ws.Row(3).Style.Font.Bold = true;
- ws.Row(2).Style.Font.Size = 18;
- int TotRows = dtStudent.Rows.Count + 4;
- ws.InsertRow(TotRows, 5);
- ws.Cells[TotRows + 2, 1].Style.Font.Bold = true;
- ws.Cells[TotRows + 2, 1].Value = "Excel Generated By";
- ws.Cells[TotRows + 2, 2].Style.Font.Bold = true;
- ws.Cells[TotRows + 2, 2].Value = "Sundar";;
- ws.Cells[TotRows + 2, 3].Style.Font.Bold = true;
- ws.Cells[TotRows + 2, 3].Value = "Generated On";
- ws.Cells[TotRows + 2, 4].Style.Font.Bold = true;
- ws.Cells[TotRows + 2, 4].Value = DateTime.Now.ToString();
- ws.Cells["A3"].LoadFromDataTable(dtStudent, true);
- System.IO.MemoryStream ms = new System.IO.MemoryStream();
- pack.SaveAs(ms);
- ms.WriteTo(HttpContext.Current.Response.OutputStream);
- }
- HttpContext.Current.Response.Flush();
- HttpContext.Current.Response.End();
- } else {
- Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "showmessage('No Training Records Found !!2')", true);
- }
- } catch {
- Page.ClientScript.RegisterStartupScript(this.GetType(), "message", "showmessage('Excel Generation Failed !!2')", true);
- }
- }
The below ADO.NET code is already written. You can use normal ADO.NET code to fetch data using Stored Procedures.
-
- private void FetchFromDataBase() {
- DBConnection db = new DBConnection();
- DataTable dt = db.ExecuteDataSet("sp_training_emp_excel", CommandType.StoredProcedure).Tables[0];
- db = null;
- return dt;
- }
My Stored Procedure.
- CREATE PROCEDURE [dbo].[sp_select_all]
- AS
- BEGIN
- SELECT name as Name, dept as Department,total as Total,average as Average,result as Result
- FROM [Local].[dbo].[tbl_Student_details]
- END