How to: Insert a chart into a spreadsheet document (Open XML SDK)

In the Open XML SDK, the SpreadsheetDocument class represents an Excel document package. To open and work with an Excel document, you create an instance of the SpreadsheetDocument class from the document. After you create the instance from the document, you can then obtain access to the main workbook part that contains the worksheets. The content in the document is represented in the package as XML using SpreadsheetML markup.

To create the class instance from the document, you call one of the Open() methods. Several are provided, each with a different signature. The sample code in this topic uses the Open(String, Boolean) method with a signature that requires two parameters. The first parameter takes a full path string that represents the document that you want to open. The second parameter is either true or false and represents whether you want the file to be opened for editing. Any changes to the document will not be saved if this parameter is false.

The code that calls the Open method is shown in the following using statement.

The using statement provides a recommended alternative to the typical .Open, .Save, .Close sequence. It ensures that the Dispose method (internal method used by the Open XML SDK to clean up resources) is automatically called when the closing brace is reached. The block that follows the using statement establishes a scope for the object that is created or named in the using statement, in this case document.

The basic document structure of a SpreadsheetML document consists of the <sheets> and <sheet> elements, which reference the worksheets in the Workbook. A separate XML file is created for each Worksheet. For example, the SpreadsheetML for a workbook that has three worksheets named MySheet1, MySheet2, and Chart1 is located in the Workbook.xml file and is shown in the following code example.

<x:workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:fileVersion appName="xl" lastEdited="5" lowestEdited="4" rupBuild="9302" />
  <x:workbookPr filterPrivacy="1" defaultThemeVersion="124226" />
  <x:bookViews>
    <x:workbookView xWindow="240" yWindow="108" windowWidth="14808" windowHeight="8016" activeTab="1" />
  </x:bookViews>
  <x:sheets>
    <x:sheet name="MySheet1" sheetId="1" r:id="rId1" />
    <x:sheet name="MySheet2" sheetId="2" r:id="rId2" />
    <x:sheet name="Chart1" sheetId="3" type="chartsheet" r:id="rId3"/>
  </x:sheets>
  <x:calcPr calcId="122211" />
</x:workbook>

The worksheet XML files contain one or more block level elements such as <sheetData>, which represents the cell table and contains one or more row (<row>) elements. A row element contains one or more cell elements (<c>). Each cell element contains a cell value element (<v>) that represents the value of the cell. For example, the SpreadsheetML for the first worksheet in a workbook, that only has the value 100 in cell A1, is located in the Sheet1.xml file and is shown in the following code example.

<?xml version="1.0" encoding="UTF-8" ?> 
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <sheetData>
        <row r="1">
            <c r="A1">
                <v>100</v> 
            </c>
        </row>
    </sheetData>
</worksheet>

Using the Open XML SDK 2.5, you can create document structure and content that uses strongly-typed classes that correspond to SpreadsheetML elements. You can find these classes in the DocumentFormat.OpenXml.Spreadsheet namespace. The following table lists the class names of the classes that correspond to the workbook, Sheets, Sheet, worksheet, and SheetData elements.

In this how-to, you are going to deal with the row, cell, and cell value elements. Therefore it is useful to familiarize yourself with these elements. The following text from the ISO/IEC 29500 specification introduces row (<row>) element.

<row r="2" spans="2:12">
  <c r="C2" s="1">
    <f>PMT(B3/12,B4,-B5)</f>
    <v>672.68336574300008</v>
  </c>
  <c r="D2">
    <v>180</v>
  </c>
  <c r="E2">
    <v>360</v>
  </c>
</row>

The following XML Schema code example defines the contents of the row element.

<complexType name="CT_Row">
   <sequence>
       <element name="c" type="CT_Cell" minOccurs="0" maxOccurs="unbounded"/>
       <element name="extLst" minOccurs="0" type="CT_ExtensionList"/>
   </sequence>
   <attribute name="r" type="xsd:unsignedInt" use="optional"/>
   <attribute name="spans" type="ST_CellSpans" use="optional"/>
   <attribute name="s" type="xsd:unsignedInt" use="optional" default="0"/>
   <attribute name="customFormat" type="xsd:boolean" use="optional" default="false"/>
   <attribute name="ht" type="xsd:double" use="optional"/>
   <attribute name="hidden" type="xsd:boolean" use="optional" default="false"/>
   <attribute name="customHeight" type="xsd:boolean" use="optional" default="false"/>
   <attribute name="outlineLevel" type="xsd:unsignedByte" use="optional" default="0"/>
   <attribute name="collapsed" type="xsd:boolean" use="optional" default="false"/>
   <attribute name="thickTop" type="xsd:boolean" use="optional" default="false"/>
   <attribute name="thickBot" type="xsd:boolean" use="optional" default="false"/>
   <attribute name="ph" type="xsd:boolean" use="optional" default="false"/>
</complexType>

The following text from the ISO/IEC 29500 specification introduces cell (<c>) element.

<c r="C6" s="1" vm="15">
  <f>CUBEVALUE("xlextdat9 Adventure Works",C$5,$A6)</f>
  <v>2838512.355</v>
</c>

The following XML Schema code example defines the contents of this element.

<complexType name="CT_Cell">
   <sequence>
       <element name="f" type="CT_CellFormula" minOccurs="0" maxOccurs="1"/>
       <element name="v" type="ST_Xstring" minOccurs="0" maxOccurs="1"/>
       <element name="is" type="CT_Rst" minOccurs="0" maxOccurs="1"/>
       <element name="extLst" minOccurs="0" type="CT_ExtensionList"/>
   </sequence>
   <attribute name="r" type="ST_CellRef" use="optional"/>
   <attribute name="s" type="xsd:unsignedInt" use="optional" default="0"/>
   <attribute name="t" type="ST_CellType" use="optional" default="n"/>
   <attribute name="cm" type="xsd:unsignedInt" use="optional" default="0"/>
   <attribute name="vm" type="xsd:unsignedInt" use="optional" default="0"/>
   <attribute name="ph" type="xsd:boolean" use="optional" default="false"/>
</complexType>

The following text from the ISO/IEC 29500 specification introduces Cell Value (<c>) element.

In the following example cell B4 contains the number 360.

<c r="B4">
  <v>360</v>
</c>

After opening the spreadsheet file for read/write access, the code verifies if the specified worksheet exists. It then adds a new DrawingsPart object using the AddNewPart method, appends it to the worksheet, and saves the worksheet part. The code then adds a new ChartPart object, appends a new ChartSpace object to the ChartPart object, and then appends a new EditingLanguage object to the ChartSpace object that specifies the language for the chart is English-US.

The code creates a new clustered column chart by creating a new BarChart object with BarDirectionValues object set to Column and BarGroupingValues object set to Clustered.

The code then iterates through each key in the Dictionary class. For each key, it appends a BarChartSeries object to the BarChart object and sets the SeriesText object of the BarChartSeries object to equal the key. For each key, it appends a NumberLiteral object to the Values collection of the BarChartSeries object and sets the NumberLiteral object to equal the Dictionary class value corresponding to the key.

The code adds the CategoryAxis object and ValueAxis object to the chart and sets the value of the following properties: Scaling, AxisPosition, TickLabelPosition, CrossingAxis, Crosses, AutoLabeled, LabelAlignment, and LabelOffset. It also adds the Legend object to the chart and saves the chart part.

The code positions the chart on the worksheet by creating a WorksheetDrawing object and appending a TwoCellAnchor object. The TwoCellAnchor object specifies how to move or resize the chart if you move the rows and columns between the FromMarker and ToMarker anchors. The code then creates a GraphicFrame object to contain the chart and names the chart "Chart 1," and saves the worksheet drawing.

In the following code, you add a clustered column chart to a SpreadsheetDocument document package using the data from a Dictionary<TKey, TValue> class. For instance, you can call the method InsertChartInSpreadsheet by using this code segment.

After you have run the program, take a look the file named "Sheet6.xlsx" to see the inserted chart.

Note
This code can be run only once. You cannot create more than one instance of the chart.

The following is the complete sample code in both C# and Visual Basic.

Other resources