How to create an Excel file in .NET using OpenXML – Part 4: Draw chart
This is the 4th part of OpenXML Spreadsheet Tutorial Series:
-
Part 4: Draw chart on Spreadsheet
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.
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.Collections.Generic; namespace OpenXMLSample { class ReportWithChart { public void CreateExcelDoc(string fileName) { List<Student> students = new List<Student>(); Initizalize(students); using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students" }; sheets.Append(sheet); workbookPart.Workbook.Save(); SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); // Constructing header Row row = new Row(); row.AppendChild(ConstructCell(string.Empty, CellValues.String)); foreach (var month in Months.Short) { row.AppendChild(ConstructCell(month, CellValues.String)); } // Insert the header row to the Sheet Data sheetData.AppendChild(row); // Inserting each employee foreach (var student in students) { row = new Row(); row.AppendChild(ConstructCell(student.Name, CellValues.String)); foreach (var value in student.Values) { row.AppendChild(ConstructCell(value.ToString(), CellValues.Number)); } sheetData.AppendChild(row); } worksheetPart.Worksheet.Save(); } } private Cell ConstructCell(string value, CellValues dataType) { return new Cell() { CellValue = new CellValue(value), DataType = new EnumValue<CellValues>(dataType), }; } private void Initizalize(List<Student> students) { students.AddRange(new Student[] { new Student { Name = "Liza", Values = new byte[] { 10, 25, 30, 15, 20, 19 } }, new Student { Name = "Macy", Values = new byte[] { 20, 15, 26, 30, 10, 15 } } }); } } public class Student { public string Name { get; set; } public byte[] Values { get; set; } } public struct Months { public static string[] Short = { "Jan", "Feb", "Mar", "Apr", "May", "Jun" }; } }
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:
static void Main(string[] args) { ReportWithChart report = new ReportWithChart(); report.CreateExcelDoc(@"D:\DispatcherTimer\ReportWithChart.xlsx"); Console.WriteLine("Excel file has created!"); }
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:
DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>(); worksheetPart.Worksheet.Append(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); worksheetPart.Worksheet.Save(); drawingsPart.WorksheetDrawing = new WorksheetDrawing();
Add Chart to the DrawingPart:
ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.AppendChild(new EditingLanguage() { Val = "en-US" }); Chart chart = chartPart.ChartSpace.AppendChild(new Chart()); chart.AppendChild(new AutoTitleDeleted() { Val = true }); // We don't want to show the chart title
Add PlotArea to the chart and append a Layout and BarChart as its children:
PlotArea plotArea = chart.AppendChild(new PlotArea()); Layout layout = plotArea.AppendChild(new Layout()); BarChart barChart = plotArea.AppendChild(new BarChart( new BarDirection() { Val = new EnumValue<BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue<BarGroupingValues>(BarGroupingValues.Clustered) }, new VaryColors() { Val = false } ));
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.
for (int i = 0; i < students.Count; i++) { BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries( new Index() { Val = (uint)i }, new Order() { Val = (uint)i }, new SeriesText(new NumericValue() { Text = students[i].Name }) )); // Adding category axis to the chart CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData()); // Category // Constructing the chart category string formulaCat = "Students!$B$1:$G$1"; StringReference stringReference = categoryAxisData.AppendChild(new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaCat } }); StringCache stringCache = stringReference.AppendChild(new StringCache()); stringCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (int j = 0; j < Months.Short.Length; j++) { stringCache.AppendChild(new NumericPoint() { Index = (uint)j }).Append(new NumericValue(Months.Short[j])); } }
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.
var chartSeries = barChart.Elements<BarChartSeries>().GetEnumerator(); for (int i = 0; i < students.Count; i++) { row = new Row(); row.AppendChild(ConstructCell(students[i].Name, CellValues.String)); chartSeries.MoveNext(); string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex); DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values()); NumberReference numberReference = values.AppendChild(new NumberReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaVal } }); NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache()); numberingCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (uint j = 0; j < students[i].Values.Length; j++) { var value = students[i].Values[j]; row.AppendChild(ConstructCell(value.ToString(), CellValues.Number)); numberingCache.AppendChild(new NumericPoint() { Index = j }).Append(new NumericValue(value.ToString())); } sheetData.AppendChild(row); rowIndex++; }
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.
// Adding Category Axis plotArea.AppendChild( new CategoryAxis( new AxisId() { Val = 48650112u }, new Scaling(new Orientation() { Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) }, new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48672768u }, new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) }, new AutoLabeled() { Val = true }, new LabelAlignment() { Val = new EnumValue<LabelAlignmentValues>(LabelAlignmentValues.Center) } )); // Adding Value Axis plotArea.AppendChild( new ValueAxis( new AxisId() { Val = 48672768u }, new Scaling(new Orientation() { Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Left) }, new MajorGridlines(), new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = true }, new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48650112u }, new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) }, new CrossBetween() { Val = new EnumValue<CrossBetweenValues>(CrossBetweenValues.Between) } )); chart.Append( new PlotVisibleOnly() { Val = true }, new DisplayBlanksAs() { Val = new EnumValue<DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap) }, new ShowDataLabelsOverMaximum() { Val = false } );
TwoCellAnchor
At the end we need define where the chart should be physically appearing. That is the TwoCellAnchor responsibility.
// Positioning the chart on the spreadsheet TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor()); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker( new ColumnId("0"), new ColumnOffset("0"), new RowId((rowIndex + 2).ToString()), new RowOffset("0") )); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker( new ColumnId("8"), new ColumnOffset("0"), new RowId((rowIndex + 12).ToString()), new RowOffset("0") )); // Append GraphicFrame to TwoCellAnchor GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame()); graphicFrame.Macro = string.Empty; graphicFrame.Append(new NonVisualGraphicFrameProperties( new NonVisualDrawingProperties() { Id = 2u, Name = "Sample Chart" }, new NonVisualGraphicFrameDrawingProperties() )); graphicFrame.Append(new Transform( new DocumentFormat.OpenXml.Drawing.Offset() { X = 0L, Y = 0L }, new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0L, Cy = 0L } )); graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic( new DocumentFormat.OpenXml.Drawing.GraphicData( new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) } ) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" } )); twoCellAnchor.Append(new ClientData());
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
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; namespace OpenXMLSample { class ReportWithChart { public void CreateExcelDoc(string fileName) { List<Student> students = new List<Student>(); Initizalize(students); using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students" }; SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); // Add drawing part to WorksheetPart DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>(); worksheetPart.Worksheet.Append(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); worksheetPart.Worksheet.Save(); drawingsPart.WorksheetDrawing = new WorksheetDrawing(); sheets.Append(sheet); workbookPart.Workbook.Save(); // Add a new chart and set the chart language ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.AppendChild(new EditingLanguage() { Val = "en-US" }); Chart chart = chartPart.ChartSpace.AppendChild(new Chart()); chart.AppendChild(new AutoTitleDeleted() { Val = true }); // We don't want to show the chart title // Create a new Clustered Column Chart PlotArea plotArea = chart.AppendChild(new PlotArea()); Layout layout = plotArea.AppendChild(new Layout()); BarChart barChart = plotArea.AppendChild(new BarChart( new BarDirection() { Val = new EnumValue<BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue<BarGroupingValues>(BarGroupingValues.Clustered) }, new VaryColors() { Val = false } )); // Constructing header Row row = new Row(); int rowIndex = 1; row.AppendChild(ConstructCell(string.Empty, CellValues.String)); foreach (var month in Months.Short) { row.AppendChild(ConstructCell(month, CellValues.String)); } // Insert the header row to the Sheet Data sheetData.AppendChild(row); rowIndex++; // Create chart series for (int i = 0; i < students.Count; i++) { BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries( new Index() { Val = (uint)i }, new Order() { Val = (uint)i }, new SeriesText(new NumericValue() { Text = students[i].Name }) )); // Adding category axis to the chart CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData()); // Category // Constructing the chart category string formulaCat = "Students!$B$1:$G$1"; StringReference stringReference = categoryAxisData.AppendChild(new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaCat } }); StringCache stringCache = stringReference.AppendChild(new StringCache()); stringCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (int j = 0; j < Months.Short.Length; j++) { stringCache.AppendChild(new NumericPoint() { Index = (uint)j }).Append(new NumericValue(Months.Short[j])); } } var chartSeries = barChart.Elements<BarChartSeries>().GetEnumerator(); for (int i = 0; i < students.Count; i++) { row = new Row(); row.AppendChild(ConstructCell(students[i].Name, CellValues.String)); chartSeries.MoveNext(); string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex); DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values()); NumberReference numberReference = values.AppendChild(new NumberReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaVal } }); NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache()); numberingCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (uint j = 0; j < students[i].Values.Length; j++) { var value = students[i].Values[j]; row.AppendChild(ConstructCell(value.ToString(), CellValues.Number)); numberingCache.AppendChild(new NumericPoint() { Index = j }).Append(new NumericValue(value.ToString())); } sheetData.AppendChild(row); rowIndex++; } barChart.AppendChild(new DataLabels( new ShowLegendKey() { Val = false }, new ShowValue() { Val = false }, new ShowCategoryName() { Val = false }, new ShowSeriesName() { Val = false }, new ShowPercent() { Val = false }, new ShowBubbleSize() { Val = false } )); barChart.Append(new AxisId() { Val = 48650112u }); barChart.Append(new AxisId() { Val = 48672768u }); // Adding Category Axis plotArea.AppendChild( new CategoryAxis( new AxisId() { Val = 48650112u }, new Scaling(new Orientation() { Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) }, new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48672768u }, new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) }, new AutoLabeled() { Val = true }, new LabelAlignment() { Val = new EnumValue<LabelAlignmentValues>(LabelAlignmentValues.Center) } )); // Adding Value Axis plotArea.AppendChild( new ValueAxis( new AxisId() { Val = 48672768u }, new Scaling(new Orientation() { Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Left) }, new MajorGridlines(), new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = true }, new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48650112u }, new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) }, new CrossBetween() { Val = new EnumValue<CrossBetweenValues>(CrossBetweenValues.Between) } )); chart.Append( new PlotVisibleOnly() { Val = true }, new DisplayBlanksAs() { Val = new EnumValue<DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap) }, new ShowDataLabelsOverMaximum() { Val = false } ); chartPart.ChartSpace.Save(); // Positioning the chart on the spreadsheet TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor()); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker( new ColumnId("0"), new ColumnOffset("0"), new RowId((rowIndex + 2).ToString()), new RowOffset("0") )); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker( new ColumnId("8"), new ColumnOffset("0"), new RowId((rowIndex + 12).ToString()), new RowOffset("0") )); // Append GraphicFrame to TwoCellAnchor GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame()); graphicFrame.Macro = string.Empty; graphicFrame.Append(new NonVisualGraphicFrameProperties( new NonVisualDrawingProperties() { Id = 2u, Name = "Sample Chart" }, new NonVisualGraphicFrameDrawingProperties() )); graphicFrame.Append(new Transform( new DocumentFormat.OpenXml.Drawing.Offset() { X = 0L, Y = 0L }, new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0L, Cy = 0L } )); graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic( new DocumentFormat.OpenXml.Drawing.GraphicData( new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) } ) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" } )); twoCellAnchor.Append(new ClientData()); drawingsPart.WorksheetDrawing.Save(); worksheetPart.Worksheet.Save(); } } private Cell ConstructCell(string value, CellValues dataType) { return new Cell() { CellValue = new CellValue(value), DataType = new EnumValue<CellValues>(dataType), }; } private void Initizalize(List<Student> students) { students.AddRange(new Student[] { new Student { Name = "Liza", Values = new byte[] { 10, 25, 30, 15, 20, 19 } }, new Student { Name = "Macy", Values = new byte[] { 20, 15, 26, 30, 10, 15 } } }); } } public class Student { public string Name { get; set; } public byte[] Values { get; set; } } public struct Months { public static string[] Short = { "Jan", "Feb", "Mar", "Apr", "May", "Jun" }; } }
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’
Hi
I have run this sample code and it works well, but when I try to change the ‘clustered columns chart’ to ‘stacked column chart’, the columns become both stacked and clustered, what have I done wrong??
Code that I have changed…
BarChart barChart = plotArea.AppendChild(new BarChart(
new BarDirection() { Val = new EnumValue(BarDirectionValues.Column) },
new BarGrouping() { Val = new EnumValue(BarGroupingValues.Stacked) },
new VaryColors() { Val = false }
));