Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

How to: Insert a Chart into a Spreadsheet Document

Last modified: January 13, 2011

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

In this article
Getting a SpreadsheetDocument Object
Basic Structure of a SpreadsheetML Document
Row Element
Cell Element
Cell Value Element
How the Sample Code Works
Sample Code

This topic shows how to use the classes in the Open XML SDK 2.0 for Microsoft Office to insert a chart into a spreadsheet document programmatically.

The following assembly directives are required to compile the code in this topic.

Imports System.Collections.Generic
Imports System.Linq
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports DocumentFormat.OpenXml.Drawing
Imports DocumentFormat.OpenXml.Drawing.Charts
Imports DocumentFormat.OpenXml.Drawing.Spreadsheet

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.

' Open the document for editing.
Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
    ' Insert other code here.
End Using

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.0, 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.

SpreadsheetML Element

Open XML SDK 2.0 Class

Description

workbook

DocumentFormat.OpenXml.Spreadsheet.Workbook

The root element for the main document part.

sheets

DocumentFormat.OpenXml.Spreadsheet.Sheets

The container for the block level structures such as sheet, fileVersion, and others specified in the ISO/IEC 29500 specification.

sheet

DocumentFormat.OpenXml.Spreadsheet.Sheet

A sheet that points to a sheet definition file.

worksheet

DocumentFormat.OpenXml.Spreadsheet.Worksheet

A sheet definition file that contains the sheet data.

sheetData

DocumentFormat.OpenXml.Spreadsheet.SheetData

The cell table, grouped together by rows.

row

Row

A row in the cell table.

c

Cell

A cell in a row.

v

CellValue

The value of a cell.

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.

The row element expresses information about an entire row of a worksheet, and contains all cell definitions for a particular row in the worksheet.This row expresses information about row 2 in the worksheet, and contains 3 cell definitions.
<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>
© ISO/IEC29500: 2008.

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.

This collection represents a cell in the worksheet. Information about the cell's location (reference), value, data type, formatting, and formula is expressed here.This example shows the information stored for a cell whose address in the grid is C6, whose style index is 6, and whose value metadata index is 15. The cell contains a formula as well as a calculated result of that formula.
<c r="C6" s="1" vm="15">
  <f>CUBEVALUE("xlextdat9 Adventure Works",C$5,$A6)</f>
  <v>2838512.355</v>
</c>
© ISO/IEC29500: 2008.

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.

This element expresses the value contained in a cell. If the cell contains a string, then this value is an index into the shared string table, pointing to the actual string value. Otherwise, the value of the cell is expressed directly in this element. Cells containing formulas express the last calculated result of the formula in this element.For applications not wanting to implement the shared string table, an "inline string" may be expressed in an <is> element under <c> (instead of a <v> element under <c>), in the same way a string would be expressed in the shared string table.© ISO/IEC29500: 2008.

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.

Dim sheets As IEnumerable(Of Sheet) = _
    document.WorkbookPart.Workbook.Descendants(Of Sheet)() _
    .Where(Function(s) s.Name = worksheetName)
If sheets.Count() = 0 Then
    ' The specified worksheet does not exist.
    Return
End If
Dim worksheetPart As WorksheetPart = _
    CType(document.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)

' Add a new drawing to the worksheet.
Dim drawingsPart As DrawingsPart = worksheetPart.AddNewPart(Of DrawingsPart)()
worksheetPart.Worksheet.Append(New DocumentFormat.OpenXml.Spreadsheet.Drawing() With {.Id = _
              worksheetPart.GetIdOfPart(drawingsPart)})
worksheetPart.Worksheet.Save()

' Add a new chart and set the chart language to English-US.
Dim chartPart As ChartPart = drawingsPart.AddNewPart(Of ChartPart)()
chartPart.ChartSpace = New ChartSpace()
chartPart.ChartSpace.Append(New EditingLanguage() With {.Val = _
                            New StringValue("en-US")})
Dim chart As DocumentFormat.OpenXml.Drawing.Charts.Chart = _
    chartPart.ChartSpace.AppendChild(Of DocumentFormat.OpenXml.Drawing.Charts _
        .Chart)(New DocumentFormat.OpenXml.Drawing.Charts.Chart())

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.

' Create a new clustered column chart.
Dim plotArea As PlotArea = chart.AppendChild(Of PlotArea)(New PlotArea())
Dim layout As Layout = plotArea.AppendChild(Of Layout)(New Layout())
Dim barChart As BarChart = plotArea.AppendChild(Of BarChart)(New BarChart _
    (New BarDirection() With {.Val = New EnumValue(Of BarDirectionValues) _
    (BarDirectionValues.Column)}, New BarGrouping() With {.Val = New EnumValue _
    (Of BarGroupingValues)(BarGroupingValues.Clustered)}))

Dim i As UInteger = 0

' Iterate through each key in the Dictionary collection and add the key to the chart Series
' and add the corresponding value to the chart Values.
For Each key As String In data.Keys
    Dim barChartSeries As BarChartSeries = barChart.AppendChild(Of BarChartSeries) _
        (New BarChartSeries(New Index() With {.Val = New UInt32Value(i)}, New Order() _
        With {.Val = New UInt32Value(i)}, New SeriesText(New NumericValue() With {.Text = key})))

    Dim strLit As StringLiteral = barChartSeries.AppendChild(Of CategoryAxisData) _
        (New CategoryAxisData()).AppendChild(Of StringLiteral)(New StringLiteral())
    strLit.Append(New PointCount() With {.Val = New UInt32Value(1UI)})
    strLit.AppendChild(Of StringPoint)(New StringPoint() With {.Index = _
        New UInt32Value(0UI)}).Append(New NumericValue(title))

    Dim numLit As NumberLiteral = barChartSeries.AppendChild _
        (Of DocumentFormat.OpenXml.Drawing.Charts.Values)(New DocumentFormat _
        .OpenXml.Drawing.Charts.Values()).AppendChild(Of NumberLiteral)(New NumberLiteral())
    numLit.Append(New FormatCode("General"))
    numLit.Append(New PointCount() With {.Val = New UInt32Value(1UI)})
    numLit.AppendChild(Of NumericPoint)(New NumericPoint() With {.Index = _
        New UInt32Value(0UI)}).Append(New NumericValue(data(key).ToString()))

    i += 1
Next 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.

barChart.Append(New AxisId() With {.Val = New UInt32Value(48650112UI)})
barChart.Append(New AxisId() With {.Val = New UInt32Value(48672768UI)})

' Add the Category Axis.
Dim catAx As CategoryAxis = plotArea.AppendChild(Of CategoryAxis) _ 
    (New CategoryAxis(New AxisId() With {.Val = New UInt32Value(48650112UI)}, _ 
New Scaling(New Orientation() With {.Val = New EnumValue(Of _
 DocumentFormat.OpenXml.Drawing.Charts.OrientationValues) _
(DocumentFormat.Open Xml.Drawing.Charts.OrientationValues.MinMax)}), New AxisPosition() With _
{.Val = New EnumValue(Of AxisPositionValues)(AxisPositionValues.Bottom)}, _
New TickLabelPosition() With {.Val = New EnumValue(Of TickLabelPositionValues) _
(TickLabelPositionValues.NextTo)}, New CrossingAxis() With {.Val = New UInt32Value(48672768UI)}, _
New Crosses() With {.Val = New EnumValue(Of CrossesValues)(CrossesValues.AutoZero)} _
, New AutoLabeled() With {.Val = New BooleanValue(True)}, New LabelAlignment()_
 With {.Val = New EnumValue(Of LabelAlignmentValues)(LabelAlignmentValues.Center)} _
, New LabelOffset() With {.Val = New UInt16Value(CUShort(100))}))

' Add the Value Axis.
Dim valAx As ValueAxis = plotArea.AppendChild(Of ValueAxis)(New ValueAxis _
    (New AxisId() With {.Val = New UInt32Value(48672768UI)}, New Scaling(New  _
    Orientation() With {.Val = New EnumValue(Of DocumentFormat.OpenXml.Drawing _
    .Charts.OrientationValues)(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)}), _
    New AxisPosition() With {.Val = New EnumValue(Of AxisPositionValues)(AxisPositionValues.Left)}, _
    New MajorGridlines(), New DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat() With {.FormatCode = _
    New StringValue("General"), .SourceLinked = New BooleanValue(True)}, New TickLabelPosition() With _
    {.Val = New EnumValue(Of TickLabelPositionValues)(TickLabelPositionValues.NextTo)}, New CrossingAxis() _
    With {.Val = New UInt32Value(48650112UI)}, New Crosses() With {.Val = New EnumValue(Of CrossesValues) _
    (CrossesValues.AutoZero)}, New CrossBetween() With {.Val = New EnumValue(Of CrossBetweenValues) _
    (CrossBetweenValues.Between)}))

' Add the chart Legend.
Dim legend As Legend = chart.AppendChild(Of Legend)(New Legend(New LegendPosition() _
    With {.Val = New EnumValue(Of LegendPositionValues)(LegendPositionValues.Right)}, New Layout()))

chart.Append(New PlotVisibleOnly() With {.Val = New BooleanValue(True)})

' Save the chart part.
chartPart.ChartSpace.Save()

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.

' Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = New WorksheetDrawing()
Dim twoCellAnchor As TwoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(Of  _
    TwoCellAnchor)(New TwoCellAnchor())
twoCellAnchor.Append(New DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(New  _
    ColumnId("9"), New ColumnOffset("581025"), New RowId("17"), New RowOffset("114300")))
twoCellAnchor.Append(New DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(New  _
    ColumnId("17"), New ColumnOffset("276225"), New RowId("32"), New RowOffset("0")))

' Append a GraphicFrame to the TwoCellAnchor object.
Dim graphicFrame As DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame = _
    twoCellAnchor.AppendChild(Of DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame) _
    (New DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame())
graphicFrame.Macro = ""

graphicFrame.Append(New DocumentFormat.OpenXml.Drawing.Spreadsheet _
    .NonVisualGraphicFrameProperties(New DocumentFormat.OpenXml.Drawing.Spreadsheet. _
    NonVisualDrawingProperties() With {.Id = New UInt32Value(2UI), .Name = "Chart 1"}, _
    New DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()))

graphicFrame.Append(New Transform(New Offset() With {.X = 0L, .Y = 0L}, _
    New Extents() With {.Cx = 0L, .Cy = 0L}))

graphicFrame.Append(New Graphic(New GraphicData(New ChartReference() With _
    {.Id = drawingsPart.GetIdOfPart(chartPart)}) With {.Uri = _
    "http://schemas.openxmlformats.org/drawingml/2006/chart"}))

twoCellAnchor.Append(New ClientData())

' Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save()

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.

Dim docName As String = "C:\Users\Public\Documents\Sheet6.xlsx"
Dim worksheetName As String = "Joe"
Dim title As String = "New Chart"
Dim data As New Dictionary(Of String, Integer)()
data.Add("abc", 1)
InsertChartInSpreadsheet(docName, worksheetName, title, data)

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

NoteNote

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.

' Given a document name, a worksheet name, a chart title, and a Dictionary collection of text keys 
' and corresponding integer data, creates a column chart with the text as the series 
' and the integers as the values.
Private Sub InsertChartInSpreadsheet(ByVal docName As String, ByVal worksheetName As String, _
ByVal title As String, ByVal data As Dictionary(Of String, Integer))
    ' Open the document for editing.
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim sheets As IEnumerable(Of Sheet) = _
            document.WorkbookPart.Workbook.Descendants(Of Sheet)() _
            .Where(Function(s) s.Name = worksheetName)
        If sheets.Count() = 0 Then
            ' The specified worksheet does not exist.
            Return
        End If
        Dim worksheetPart As WorksheetPart = _
            CType(document.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)

        ' Add a new drawing to the worksheet.
        Dim drawingsPart As DrawingsPart = worksheetPart.AddNewPart(Of DrawingsPart)()
        worksheetPart.Worksheet.Append(New DocumentFormat.OpenXml.Spreadsheet.Drawing() With {.Id = _
              worksheetPart.GetIdOfPart(drawingsPart)})
        worksheetPart.Worksheet.Save()

        ' Add a new chart and set the chart language to English-US.
        Dim chartPart As ChartPart = drawingsPart.AddNewPart(Of ChartPart)()
        chartPart.ChartSpace = New ChartSpace()
        chartPart.ChartSpace.Append(New EditingLanguage() With {.Val = _
                                    New StringValue("en-US")})
        Dim chart As DocumentFormat.OpenXml.Drawing.Charts.Chart = _
            chartPart.ChartSpace.AppendChild(Of DocumentFormat.OpenXml.Drawing.Charts _
                .Chart)(New DocumentFormat.OpenXml.Drawing.Charts.Chart())

        ' Create a new clustered column chart.
        Dim plotArea As PlotArea = chart.AppendChild(Of PlotArea)(New PlotArea())
        Dim layout As Layout = plotArea.AppendChild(Of Layout)(New Layout())
        Dim barChart As BarChart = plotArea.AppendChild(Of BarChart)(New BarChart _
            (New BarDirection() With {.Val = New EnumValue(Of BarDirectionValues) _
            (BarDirectionValues.Column)}, New BarGrouping() With {.Val = New EnumValue _
            (Of BarGroupingValues)(BarGroupingValues.Clustered)}))

        Dim i As UInteger = 0

        ' Iterate through each key in the Dictionary collection and add the key to the chart Series
        ' and add the corresponding value to the chart Values.
        For Each key As String In data.Keys
            Dim barChartSeries As BarChartSeries = barChart.AppendChild(Of BarChartSeries) _
                (New BarChartSeries(New Index() With {.Val = New UInt32Value(i)}, New Order() _
                With {.Val = New UInt32Value(i)}, New SeriesText(New NumericValue() With {.Text = key})))

            Dim strLit As StringLiteral = barChartSeries.AppendChild(Of CategoryAxisData) _
                (New CategoryAxisData()).AppendChild(Of StringLiteral)(New StringLiteral())
            strLit.Append(New PointCount() With {.Val = New UInt32Value(1UI)})
            strLit.AppendChild(Of StringPoint)(New StringPoint() With {.Index = _
                New UInt32Value(0UI)}).Append(New NumericValue(title))

            Dim numLit As NumberLiteral = barChartSeries.AppendChild _
                (Of DocumentFormat.OpenXml.Drawing.Charts.Values)(New DocumentFormat _
                .OpenXml.Drawing.Charts.Values()).AppendChild(Of NumberLiteral)(New NumberLiteral())
            numLit.Append(New FormatCode("General"))
            numLit.Append(New PointCount() With {.Val = New UInt32Value(1UI)})
            numLit.AppendChild(Of NumericPoint)(New NumericPoint() With {.Index = _
                New UInt32Value(0UI)}).Append(New NumericValue(data(key).ToString()))

            i += 1
        Next key

        barChart.Append(New AxisId() With {.Val = New UInt32Value(48650112UI)})
        barChart.Append(New AxisId() With {.Val = New UInt32Value(48672768UI)})

        ' Add the Category Axis.
        Dim catAx As CategoryAxis = plotArea.AppendChild(Of CategoryAxis) _
            (New CategoryAxis(New AxisId() With {.Val = New UInt32Value(48650112UI)}, New Scaling(New Orientation() With {.Val = New EnumValue(Of DocumentFormat.OpenXml.Drawing.Charts.OrientationValues)(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)}), New AxisPosition() With {.Val = New EnumValue(Of AxisPositionValues)(AxisPositionValues.Bottom)}, New TickLabelPosition() With {.Val = New EnumValue(Of TickLabelPositionValues)(TickLabelPositionValues.NextTo)}, New CrossingAxis() With {.Val = New UInt32Value(48672768UI)}, New Crosses() With {.Val = New EnumValue(Of CrossesValues)(CrossesValues.AutoZero)}, New AutoLabeled() With {.Val = New BooleanValue(True)}, New LabelAlignment() With {.Val = New EnumValue(Of LabelAlignmentValues)(LabelAlignmentValues.Center)}, New LabelOffset() With {.Val = New UInt16Value(CUShort(100))}))

        ' Add the Value Axis.
        Dim valAx As ValueAxis = plotArea.AppendChild(Of ValueAxis)(New ValueAxis _
            (New AxisId() With {.Val = New UInt32Value(48672768UI)}, New Scaling(New  _
            Orientation() With {.Val = New EnumValue(Of DocumentFormat.OpenXml.Drawing _
            .Charts.OrientationValues)(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)}), _
            New AxisPosition() With {.Val = New EnumValue(Of AxisPositionValues)(AxisPositionValues.Left)}, _
            New MajorGridlines(), New DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat() With {.FormatCode = _
            New StringValue("General"), .SourceLinked = New BooleanValue(True)}, New TickLabelPosition() With _
            {.Val = New EnumValue(Of TickLabelPositionValues)(TickLabelPositionValues.NextTo)}, New CrossingAxis() _
            With {.Val = New UInt32Value(48650112UI)}, New Crosses() With {.Val = New EnumValue(Of CrossesValues) _
            (CrossesValues.AutoZero)}, New CrossBetween() With {.Val = New EnumValue(Of CrossBetweenValues) _
            (CrossBetweenValues.Between)}))

        ' Add the chart Legend.
        Dim legend As Legend = chart.AppendChild(Of Legend)(New Legend(New LegendPosition() _
            With {.Val = New EnumValue(Of LegendPositionValues)(LegendPositionValues.Right)}, New Layout()))

        chart.Append(New PlotVisibleOnly() With {.Val = New BooleanValue(True)})

        ' Save the chart part.
        chartPart.ChartSpace.Save()

        ' Position the chart on the worksheet using a TwoCellAnchor object.
        drawingsPart.WorksheetDrawing = New WorksheetDrawing()
        Dim twoCellAnchor As TwoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(Of  _
            TwoCellAnchor)(New TwoCellAnchor())
        twoCellAnchor.Append(New DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(New  _
            ColumnId("9"), New ColumnOffset("581025"), New RowId("17"), New RowOffset("114300")))
        twoCellAnchor.Append(New DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(New  _
            ColumnId("17"), New ColumnOffset("276225"), New RowId("32"), New RowOffset("0")))

        ' Append a GraphicFrame to the TwoCellAnchor object.
        Dim graphicFrame As DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame = _
            twoCellAnchor.AppendChild(Of DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame) _
            (New DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame())
        graphicFrame.Macro = ""

        graphicFrame.Append(New DocumentFormat.OpenXml.Drawing.Spreadsheet _
            .NonVisualGraphicFrameProperties(New DocumentFormat.OpenXml.Drawing.Spreadsheet. _
            NonVisualDrawingProperties() With {.Id = New UInt32Value(2UI), .Name = "Chart 1"}, _
            New DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()))

        graphicFrame.Append(New Transform(New Offset() With {.X = 0L, .Y = 0L}, _
            New Extents() With {.Cx = 0L, .Cy = 0L}))

        graphicFrame.Append(New Graphic(New GraphicData(New ChartReference() With _
            {.Id = drawingsPart.GetIdOfPart(chartPart)}) With {.Uri = _
            "http://schemas.openxmlformats.org/drawingml/2006/chart"}))

        twoCellAnchor.Append(New ClientData())

        ' Save the WorksheetDrawing object.
        drawingsPart.WorksheetDrawing.Save()
    End Using

End Sub
Show:
© 2015 Microsoft