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.

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) { // File Name of Excel.
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; // Header In the Excel.
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);
}
}