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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
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


Gopikrishna

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment