To Create Excel With DataTable And Chart Using EPPLUS C#

EPPLUS library can be installed by using the NuGet Package Manager. In this blog, we will learn how to create an Excel with DataTable and Chart.

Before following the steps to create an Excel chart, let's discuss the details of the library EPPLUS that I have used to implement the Excel charts.

About EPPLUS

EPPLUS is a very helpful open-source 3rd party dll for exporting data to Excel.

EPPLUS Installation

EPPLUS library can be installed by using the NuGet Package Manager. Just right-click on your project where you want to implement this Excel Chart logic, select "Manage NuGet Packages" and search for EPPLUS and install it.

C# code to create Excel charts using EPPLUS

Lets start with the coding part. First create an excel package and add a worksheet to it where you want to add your data table and chart. AddChart method adds a chart, I have used here a clustered column as my requirement was to compare 2 years' of data. 

ExcelPackage package = new ExcelPackage(); //To create excel package
package.Workbook.Worksheets.Add("xyz"); //To add new sheet to Excel package with name‘ xyz’
ExcelWorksheet testWorksheet = package.Workbook.Worksheets["xyz"]; //to get excel worksheet object by name
ExcelChart chart = testWorksheet.Drawings.AddChart("chart", eChartType.ColumnClustered); //To add chart to added sheet of type column clustered chart

Now, in my case, I have my own list which has columns named as Month, Current Year Consumption, and Previous Year Consumption data details.These column names are important for mapping data in Excel Charts that I will explain in the next steps.

In the previous step, we have added a chart to an Excel sheet. In this case chart has a X-axis for month value and Y- axis is showing a consumption/usage for the respective month.

Now, let’s use the chart object named ’chart’ we created above to set further chart properties.

chart.XAxis.Title.Text = "Months"; //give label to x-axis of chart
chart.XAxis.Title.Font.Size = 10;
chart.YAxis.Title.Text = "Usage(kwh)"; //give label to Y-axis of chart
chart.YAxis.Title.Font.Size = 10;
chart.SetSize(1200, 300);
chart.SetPosition(1, 0, 5, 0);
consumptionWorksheet.Cells[1, 1].LoadFromCollection(consumptionComparisonDetails, false, OfficeOpenXml.Table.TableStyles.Medium1);

The function LoadFromCollection is used to load data from your list to an Excel. If you noticed one thing, I am using cell[1,1] that means whenever my data table will get created using LoadFromCollection function inside my Excel sheet, it starts loading data from row 1 and column 1.Also, in this statement, I have used the second parameter value as false, this parameter is used to decide if you also want to display headers to the data table mentioned in your list. If I set it true, it will display a table with headers that have names of the columns we get in our list. However, I am manually giving header names so i set to the false as mentioned below.

consumptionWorksheet.Cells[1, 1].Value = "Month";
consumptionWorksheet.Cells[1, 2].Value = "Current Year Consumption";
consumptionWorksheet.Cells[1, 3].Value = "Previous Year Consumption";
consumptionWorksheet.Cells[1, 1].Style.Font.Bold = true;
consumptionWorksheet.Cells[1, 2].Style.Font.Bold = true;
consumptionWorksheet.Cells[1, 3].Style.Font.Bold = true;

This code will display a DataTable inside the Excel sheet we have created.

My requirement was to display the comparison chart for previous and current year with respect to the month; so, I will be using a bar chart to show the comparison of 2 years. For this purpose, I need to add Series for each column (here series means each bar denotes different column) like given below.

var row = 1;
var consumptionCurrentYearSeries = chart.Series.Add(("B" + (row + 1) + ":" + "B" + (consumptionComparisonDetails.Count + 1)), ("A" + (row + 1) + ":" + "A" + (consumptionComparisonDetails.Count + 1)));
consumptionCurrentYearSeries.Header = "Current Year";
var consumptionPreviousYearSeries = chart.Series.Add(("C" + (row + 1) + ":" + "C" + (consumptionComparisonDetails.Count + 1)), ("A" + (row + 1) + ":" + "A" + (consumptionComparisonDetails.Count + 1)));
consumptionPreviousYearSeries.Header = "Previous Year";

In the above statements, A, B, C are Excel sheet columns and consumptionComparisonDetails.Count will be the count of records from the list. For example, consider the first statement, row=1 and count of list is 12; that means the statement will be like below.

var consumptionCurrentYearSeries = chart.Series.Add("B2:B13","A2:A13" )

This means series for bar chart of current year will take data from column B from B2 to B13 as shown in the data table given above and same for the previous year statement will be like below.

var consumptionCurrentYearSeries = chart.Series.Add("C2:C13","A2:A13" )

Here, B and C are the Y-axis values and A denotes X-axis month values.

Now, the chart creation logic is complete. We need to save the Excel package. So, we will use the following statement.

package.SaveAs(filePath); //this statement will save created excel to the passed file path
package.Dispose(); //will dispose the package

This code will display the graph and table as shown below.