How to create an Excel file in .NET using OpenXML – Part 4: Draw chart

02. December 2015 Tutorial 1

This is the 4th part of OpenXML Spreadsheet Tutorial Series:

Introduction

We can easily draw any type of charts in an Excel sheet using OpenXML. The only thing you need to know is the hierarchy of the XML elements in the Excel document. The best way to understand the hierarchy of the XML elements is to create a simple Spreadsheet with a simple chart in Excel and open it using Open XML Package Editor.

Open Microsoft Excel and add the following sample data in it.

After saving the Excel file open it in the Visual Studio. You might see a hierarchy similar to the following figure.

Take note that a reference to ‘drawing1.xml’ is added to the ‘sheet 1’. Under the drawings folder you can find the ‘drawings1.xml’ which it has a reference to chart1.xml. Under charts folder you can find the ‘chart1.xml’. By now we ignore the coloring and styling of the chart. We focus on how to draw the chart from our data.

Drawing

Let’s open the ‘drawing1.xml’ and have a look on how it constructs. Press <Ctrl + D> in visual studio to format the XML document.

The TwoCellAnchor is used to specify the coordinates where the chart should be drew. It has three children. From and To specifies the rows and cells of the start and end points where the chart will be drew, along with the RowOffset and ColumnOffset. In our example you can see the chart coordinates start from col 0, row 4, until col 7, row18.

GraphicFrame is the third child node in TwoCellAnchor where the actual reference to the chart exists.

Chart

Open chart1.xml in Visual Studio and let’s inspect the hierarchy of elements. Below is the beginning of the chart1/xml.

The first element in the hierarchy is a ChartSpace with Chart as its child. The hierarchy go deeper with PlotArea, BarChart, and BarChartSeriese. We will have 2 Series for the 2 rows of data we have. Each Series has Index, Order, and SeriesText. A Series has CategoryAxisData and Values. Category will store the reference to data headers, which is month name in our sample, and value is the actual data for each category. Each Category and Value has reference to the cells the data come from as well as Cache which is the hard coded data in the sheet. The reason to have the Reference is to make the chart be updated whenever the user change the data in the sheet. At the end we need to define the CategoryAxis and ValueAxis. There are a lot more settings we need setup which we will look in more detail in next section.

Sample Project

Create spreadsheet with data

For the chart sample project I won’t use the code from previous parts. We start a new one.

The following snippet is the complete code to create an excel sheet with data. This should be easy for you to understand and follow. If not check the previous parts of this tutorial series.

The code is initializing some sample data using Student class and is using the data to create a spreadsheet. No styling or customization have been applied to the spreadsheet.

Modify the Main method as bellow:

The generated excel file will be as below:

Draw chart on Spreadsheet

Add Drawing Part

After creating the Sheet, add a DrawingPart to the worksheet and initialize the worksheet drawing:

Add Chart to the DrawingPart:

Add PlotArea to the chart and append a Layout and BarChart as its children:

Add Chart Series and Categories

After constructing the header row, for each student we are going to add a ChartSeries to the BarChart.

For each BarSeries we add a reference cells in spreadsheet by create a formula Students!$B$0:$G$0. After adding the reference we will create a StringCache for the actual data.

Add chart values

For each student we will add the actual values to each series. Take note that the same way as with Categories we will add reference to the data on spreadsheet using formula, as well as adding the actual data to cache.

Setting up CategoyAxis and ValueAxis

Up until now we have added the Drawing to the worksheet and append a Chart to it. We then have added ChartSeries to the Chart. The actual Category and Values have been added to each ChartSeries. Now we need to setup the Category and Value Axes.

TwoCellAnchor

At the end we need define where the chart should be physically appearing. That is the TwoCellAnchor responsibility.

Save the Drawing Part

At the end save the Drawing Part:

drawingsPart.WorksheetDrawing.Save();

Result

Open the generated file and see the result.

Complete Code

Download

Download the sample code


1 thought on “How to create an Excel file in .NET using OpenXML – Part 4: Draw chart”

  • 1
    Jack on November 24, 2016 Reply

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Drawing.Charts;
    using DocumentFormat.OpenXml.Drawing.Spreadsheet;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Collections.Generic;

    None of these libraries have a reference to ‘PlotArea’

Leave a Reply

Your email address will not be published. Required fields are marked *