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. 
  1. ExcelPackage package = new ExcelPackage(); //To create excel package  
  2. package.Workbook.Worksheets.Add("xyz"); //To add new sheet to Excel package with name‘ xyz’  
  3. ExcelWorksheet testWorksheet = package.Workbook.Worksheets["xyz"]; //to get excel worksheet object by name  
  4. 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.

  1. chart.XAxis.Title.Text = "Months"//give label to x-axis of chart  
  2. chart.XAxis.Title.Font.Size = 10;  
  3. chart.YAxis.Title.Text = "Usage(kwh)"//give label to Y-axis of chart  
  4. chart.YAxis.Title.Font.Size = 10;  
  5. chart.SetSize(1200, 300);  
  6. chart.SetPosition(1, 0, 5, 0);  
  7. 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.

  1. consumptionWorksheet.Cells[1, 1].Value = "Month";  
  2. consumptionWorksheet.Cells[1, 2].Value = "Current Year Consumption";  
  3. consumptionWorksheet.Cells[1, 3].Value = "Previous Year Consumption";  
  4. consumptionWorksheet.Cells[1, 1].Style.Font.Bold = true;  
  5. consumptionWorksheet.Cells[1, 2].Style.Font.Bold = true;  
  6. 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.

  1. var row = 1;  
  2. var consumptionCurrentYearSeries = chart.Series.Add(("B" + (row + 1) + ":" + "B" + (consumptionComparisonDetails.Count + 1)), ("A" + (row + 1) + ":" + "A" + (consumptionComparisonDetails.Count + 1)));  
  3. consumptionCurrentYearSeries.Header = "Current Year";  
  4. var consumptionPreviousYearSeries = chart.Series.Add(("C" + (row + 1) + ":" + "C" + (consumptionComparisonDetails.Count + 1)), ("A" + (row + 1) + ":" + "A" + (consumptionComparisonDetails.Count + 1)));  
  5. 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.

  1. 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.

  1. 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.

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

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

To Create Excel With DataTable And Chart Using EPPLUS C#