Creating Excel reports using EPPlus

EPPlus is an powerful tool to generate Excel Sheets using C#.NET without Microsoft Excel Software installed in the Server. Using this tool we can easily create excel reports with Charts, Pictures, Shapes, Tables, Comments, Formulas etc.

Following is the sample code for generating the Sample Excel Report

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Data;

protected void GenerateExcel()
{
    DataTable dtData = GetExcelData();

    using (ExcelPackage p = new ExcelPackage())
    {
        //Document properties
        p.Workbook.Properties.Author = "XYZ Company";
        p.Workbook.Properties.Title = "XYZ Company Salaries";

        p.Workbook.Worksheets.Add("Employee Salaries");
        ExcelWorksheet ws = p.Workbook.Worksheets[1];
        ws.Cells.Style.Font.Size = 12; //Default font size for whole sheet
        ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet

        int i = 1, j = 1;

        // First Row
        ws.Cells[i, j].Value = "Employee Salaries";
        // Merging the columns
        ws.Cells[i, j, i, dtData.Columns.Count + 1].Merge = true;
        ws.Cells[i, j, i, dtData.Columns.Count + 1].Style.Font.Bold = true;
        ws.Cells[i, j, i, dtData.Columns.Count + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        i++;
        j = 1;

        // Heading row
        foreach (DataColumn col in dtData.Columns)
        {
            ws.Cells[i, j].Value = col.Caption;
            ws.Cells[i, j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            j++;
        }

        ws.Cells[i, j].Value = "Earned";
        ws.Cells[i, j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        // Adding comment
        ws.Cells[i, j].AddComment("Earned= Basic + HRA + DA", "");

        // Header row formatting
        ExcelRange range = ws.Cells[i, 1, i, j];
        range.Style.Font.Bold = true;
        // Header Background color
        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
        range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
        // Header Text color
        range.Style.Font.Color.SetColor(System.Drawing.Color.Maroon);

        i++;
        j = 1;
        int iStartingRow = i;
        foreach (DataRow row in dtData.Rows)
        {
            foreach (DataColumn col in dtData.Columns)
            {
                ws.Cells[i, j].Value = row[col];
                j++;
            }
            // Formula for Earned: Sum(B4:D4)
            ws.Cells[i, j].Formula = "Sum(" + ws.Cells[i, j - 3].Address + ":" + ws.Cells[i, j - 1].Address + ")";

            i++;
            j = 1;
        }

        // Footer row
        ws.Cells[i, j].Value = "Total:";
        ws.Cells[i, j, i, 2].Merge = true;
        ws.Cells[i, j, i, 2].Style.Font.Bold = true;
        ws.Cells[i, j, i, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        j = j + 2;

        // Formula for Basic Total
        ws.Cells[i, j].Formula = "Sum(" + ws.Cells[iStartingRow, j].Address + ":" + ws.Cells[i - 1, j].Address + ")";
        j++;
        // Formula for HRA Total
        ws.Cells[i, j].Formula = "Sum(" + ws.Cells[iStartingRow, j].Address + ":" + ws.Cells[i - 1, j].Address + ")";
        j++;
        // Formula for DA Total
        ws.Cells[i, j].Formula = "Sum(" + ws.Cells[iStartingRow, j].Address + ":" + ws.Cells[i - 1, j].Address + ")";
        j++;
        // Formula for Sum of Total
        ws.Cells[i, j].Formula = "Sum(" + ws.Cells[iStartingRow, j].Address + ":" + ws.Cells[i - 1, j].Address + ")";

        // Footer row formatting
        range = ws.Cells[i, 1, i, j];
        range.Style.Font.Bold = true;
        // Footer background color
        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
        range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
        // Footer Text color
        range.Style.Font.Color.SetColor(System.Drawing.Color.Maroon);

        // now we resize the columns
        range = ws.Cells[1, 1, i, j]; // complete document range
        range.AutoFitColumns();
        // Border formatting
        Border border = range.Style.Border;
        border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;

        //ws.Cells.AutoFitColumns();
        string sFilePath = Server.MapPath("~/Temp/") + "EmployeeSalaries.xlsx";
        System.IO.File.WriteAllBytes(sFilePath, p.GetAsByteArray());
    }
}

private DataTable GetExcelData()
{
    DataTable dtData = new DataTable();
    dtData.Columns.Add("Employee Id");
    dtData.Columns.Add("Name");
    dtData.Columns.Add("Basic", typeof(Double));
    dtData.Columns.Add("HRA", typeof(Double));
    dtData.Columns.Add("DA", typeof(Double));

    string[] Names = { "AAAA", "BBBB", "CCCC", "DDDD", "EEEE", "FFFF", "GGGG" };
    string[] Basic = { "10000", "11000", "12000", "13000", "14000", "15000", "16000" };

    for (int i = 0; i < Names.Length; i++)
    {
        DataRow dr = dtData.NewRow();
        dr["Employee Id"] = "EMP0000" + i.ToString();
        dr["Name"] = Names[i];
        dr["Basic"] = Basic[i];
        dr["HRA"] = Convert.ToDouble(Basic[i]) * 20 / 100;
        dr["DA"] = Convert.ToDouble(Basic[i]) * 10 / 100;
        dtData.Rows.Add(dr);
    }

    return dtData;
}

Here is the snapshot for the Excel Sheet generated using EPPlus

Following are links for reference

• EPPlus Homepage
• Sample Snippets 
• Formula Calculations
• Supporting Functions

Gopikrishna