To Create Excel With DataTable And Chart Using EPPLUS C#

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.

To Create Excel With DataTable And Chart Using EPPLUS C#

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.

C# code to create Excel charts using EPPLUS

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