In my last post I showed how to write data to an Excel file using the EPPlus library. I this post we will look at a more advanced topic, how to create a graph on an Excel worksheet.
We will start by using the same code as in my last post to create a new workbook, worksheet, and then add some sample data to it.
using (var package = new ExcelPackage())
{
var workbook = package.Workbook;
var worksheet = workbook.Worksheets.Add("Sheet1");
worksheet.Cells["C1"].Value = "Widgets";
worksheet.Cells["C2"].Value = 20;
worksheet.Cells["C3"].Value = 5;
worksheet.Cells["C4"].Value = 30;
worksheet.Cells["C5"].Value = 32;
worksheet.Cells["C6"].Value = 17;
worksheet.Cells["B2"].Value = "Jan";
worksheet.Cells["B3"].Value = "Feb";
worksheet.Cells["B4"].Value = "Mar";
worksheet.Cells["B5"].Value = "Apr";
worksheet.Cells["B6"].Value = "May";
In this sample data we have labels in column B and values in column C. The first step in creating the chart is to create a new chart object in our worksheet, we do this with the AddChart function:
var chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnStacked);
AddChart takes two parameters, the first is a name for the chart and the second is the type of chart. EPPlus supports a large number of different chart types, in this case we will use a stacked column (bar) chart. Next we need to add a data series to our chart:
var series = chart.Series.Add(“C2:C6", "B2:B6");
The Series.Add function takes two parameters. The first specifies a range of cells that will be used for the values in the series, so we set this to C2:C6 which contains our numeric data. We skip C1 since it contains the column label, we will come back to this in a minute. The second parameter specifies a range of cells that contain the labels for our X-Axis, so we point this to B2-B6 which contains our month names.
If we were to run this now the series would end up with a default name in the legend of the chart. We can fix this using this line:
series.HeaderAddress = new ExcelAddress("'Sheet1'!C1");
With the HeaderAddress property of the series we can set which cell we want the name of the series to come from. Note that for this to work you must specify the full address of the cell including the sheet name. If you leave out the sheet name the name of the series will remain blank.
Since this is a stacked column chart we can add more then one series. If we had additional numeric data in column D we could create a second series like this:
var series2 = chart.Series.Add("D2:D6", "B2:B6");
series2.HeaderAddress = new ExcelAddress("'Sheet1'!D1");
One thing that is missing from EPPlus is the ability to change the style (color, etc.) of a series, you have to stay with the default styles.
The final step is to save the workbook:
package.SaveAs(new System.IO.FileInfo(@"c:\temp\demoOut.xlsx"));
If you run this you will get the following results:
