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

In this article, we will see how to insert data from datatable 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);
    }
}

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

//ADO.NET Code to Fetch from Datatable
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