Insertar un gráfico en un documento de hoja de cálculo

En este tema se muestra cómo usar las clases del SDK de Open XML para Office para insertar un gráfico en un documento de hoja de cálculo mediante programación.

Elemento Row

En este tema sobre procedimientos, se tratarán los elementos de fila, de celda y de valor de celda. Por lo tanto, le resultará útil familiarizarse con estos elementos. El siguiente texto de la especificación ISO/IEC 29500 presenta el elemento row (<row/>).

El elemento de fila expresa la información de una fila completa de una hoja de cálculo y contiene todas las definiciones de celda de una fila concreta de la hoja de cálculo.

Esta fila expresa la información de la fila 2 de la hoja de cálculo y contiene tres definiciones de celda.

    <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/IEC 29500: 2016

El siguiente ejemplo de código de esquema XML define el contenido del elemento de fila.

    <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>

Elemento Cell

El siguiente texto de la especificación ISO/IEC 29500 presenta el elemento cell (<c/>).

Esta colección representa una celda de la hoja de cálculo. Aquí se expresa la información sobre la ubicación (referencia), el valor, el tipo de datos, el formato y la fórmula de la celda.

En este ejemplo se muestra la información almacenada para una celda cuya dirección en la cuadrícula es C6, cuyo índice de estilo es 6 y cuyo índice de metadatos de valor es 15. La celda contiene una fórmula y un resultado calculado de dicha fórmula.

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

© ISO/IEC 29500: 2016

El siguiente ejemplo de código de esquema XML define el contenido de este elemento.

    <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>

Elemento de valor de celda

El siguiente texto de la especificación ISO/IEC 29500 presenta el elemento Cell Value (<c/>).

Este elemento expresa el valor que contiene una celda. Si la celda contiene una cadena, este valor será un índice en la tabla de cadenas compartidas que apunta al valor de cadena real. De lo contrario, el valor de la celda se expresará directamente en este elemento. Las celdas que contienen fórmulas expresan el último resultado calculado de la fórmula en este elemento.

Para las aplicaciones que no quieren implementar la tabla de cadenas compartidas, se puede expresar una "cadena insertada" en un <is/> elemento bajo <c/> (en lugar de un <v/> elemento en <c/>), de la misma manera que se expresaría una cadena en la tabla de cadenas compartidas.

© ISO/IEC 29500: 2016

En el siguiente ejemplo, la celda B4 contiene el número 360.

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

Funcionamiento del código de ejemplo

Una vez abierto el archivo de hoja de cálculo para acceso de lectura y escritura, el código comprueba si existe la hoja de cálculo especificada. A continuación, agrega un nuevo DrawingsPart objeto mediante el AddNewPart método , lo anexa a la hoja de cálculo y guarda la parte de la hoja de cálculo. A continuación, el código agrega un nuevo ChartPart objeto, anexa un nuevo ChartSpace objeto al objeto y, a ChartPart continuación, anexa un nuevo EditingLanguage objeto al ChartSpace objeto que especifica que el idioma del gráfico es inglés-EE. UU.

C#
Visual Basic

IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);

if (sheets is null || sheets.Count() == 0)
{
    // The specified worksheet does not exist.
    return;
}

string? id = sheets.First().Id;

if (id is null)
{
    // The worksheet does not have an ID.
    return;
}

WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(id);

// Add a new drawing to the worksheet.
DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{ Id = worksheetPart.GetIdOfPart(drawingsPart) });

// Add a new chart and set the chart language to English-US.
ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") });
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Chart>(
    new DocumentFormat.OpenXml.Drawing.Charts.Chart());

Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart?.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = worksheetName)

If sheets Is Nothing OrElse sheets.Count() = 0 Then
    ' The specified worksheet does not exist.
    Return
End If

Dim id As String = sheets.First().Id

If id Is Nothing Then
    ' The worksheet does not have an ID.
    Return
End If

Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(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)})

' 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())

El código crea un nuevo gráfico de columnas agrupadas mediante la creación de un nuevo BarChart objeto con BarDirectionValues el objeto establecido en Column y BarGroupingValues el objeto establecido en Clustered.

A continuación, el código recorre en iteración cada clave de la Dictionary clase . Para cada clave, anexa un BarChartSeries objeto al BarChart objeto y establece el SeriesText objeto del BarChartSeries objeto para que sea igual a la clave. Para cada clave, anexa un NumberLiteral objeto a la Values colección del BarChartSeries objeto y establece el NumberLiteral objeto para que sea igual al valor de clase Dictionary correspondiente a la clave.

C#
Visual Basic

        // Create a new clustered column chart.
        PlotArea plotArea = chart.AppendChild<PlotArea>(new PlotArea());
        Layout layout = plotArea.AppendChild<Layout>(new Layout());
        BarChart barChart = plotArea.AppendChild<BarChart>(new BarChart(new BarDirection()
        { Val = new EnumValue<BarDirectionValues>(BarDirectionValues.Column) },
            new BarGrouping() { Val = new EnumValue<BarGroupingValues>(BarGroupingValues.Clustered) }));

        uint i = 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.
        foreach (string key in data.Keys)
        {
            BarChartSeries barChartSeries = barChart.AppendChild<BarChartSeries>(new BarChartSeries(new Index()
            {
                Val = new UInt32Value(i)
            },
                new Order() { Val = new UInt32Value(i) },
                new SeriesText(new NumericValue() { Text = key })));

            StringLiteral strLit = barChartSeries.AppendChild<CategoryAxisData>(new CategoryAxisData()).AppendChild<StringLiteral>(new StringLiteral());
            strLit.Append(new PointCount() { Val = new UInt32Value(1U) });
            strLit.AppendChild<StringPoint>(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(title));

            NumberLiteral numLit = barChartSeries.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Values>(
                new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild<NumberLiteral>(new NumberLiteral());
            numLit.Append(new FormatCode("General"));
            numLit.Append(new PointCount() { Val = new UInt32Value(1U) });
            numLit.AppendChild<NumericPoint>(new NumericPoint() { Index = new UInt32Value(0u) }).Append
(new NumericValue(data[key].ToString()));

            i++;
        }

' 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

El código agrega el CategoryAxis objeto y ValueAxis el objeto al gráfico y establece el valor de las siguientes propiedades: Scaling, AxisPosition, TickLabelPosition, CrossingAxis, Crosses, AutoLabeledy LabelAlignment LabelOffset. También agrega el Legend objeto al gráfico y guarda la parte del gráfico.

C#
Visual Basic

        barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) });
        barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) });

        // Add the Category Axis.
        CategoryAxis catAx = plotArea.AppendChild<CategoryAxis>(new CategoryAxis(new AxisId()
        { Val = new UInt32Value(48650112u) }, new Scaling(new Orientation()
        {
            Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
        }),
            new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) },
            new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) },
            new CrossingAxis() { Val = new UInt32Value(48672768U) },
            new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
            new AutoLabeled() { Val = new BooleanValue(true) },
            new LabelAlignment() { Val = new EnumValue<LabelAlignmentValues>(LabelAlignmentValues.Center) },
            new LabelOffset() { Val = new UInt16Value((ushort)100) }));

        // Add the Value Axis.
        ValueAxis valAx = plotArea.AppendChild<ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
            new Scaling(new Orientation()
            {
                Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
                DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
            }),
            new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Left) },
            new MajorGridlines(),
            new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
            {
                FormatCode = new StringValue("General"),
                SourceLinked = new BooleanValue(true)
            }, new TickLabelPosition()
            {
                Val = new EnumValue<TickLabelPositionValues>
(TickLabelPositionValues.NextTo)
            }, new CrossingAxis() { Val = new UInt32Value(48650112U) },
            new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
            new CrossBetween() { Val = new EnumValue<CrossBetweenValues>(CrossBetweenValues.Between) }));

        // Add the chart Legend.
        Legend legend = chart.AppendChild<Legend>(new Legend(new LegendPosition() { Val = new EnumValue<LegendPositionValues>(LegendPositionValues.Right) },
            new Layout()));

        chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) });

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)})

El código coloca el gráfico en la hoja de cálculo creando un WorksheetDrawing objeto y anexando un TwoCellAnchor objeto. El TwoCellAnchor objeto especifica cómo mover o cambiar el tamaño del gráfico si mueve las filas y columnas entre los FromMarker delimitadores y ToMarker . A continuación, el código crea un GraphicFrame objeto que contiene el gráfico y asigna el nombre "Gráfico 1".

C#
Visual Basic

        // Position the chart on the worksheet using a TwoCellAnchor object.
        drawingsPart.WorksheetDrawing = new WorksheetDrawing();
        TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild<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.
        DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
            twoCellAnchor.AppendChild<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() { Id = new UInt32Value(2u), Name = "Chart 1" },
            new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));

        graphicFrame.Append(new Transform(new Offset() { X = 0L, Y = 0L },
                                                                new Extents() { Cx = 0L, Cy = 0L }));

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

        twoCellAnchor.Append(new ClientData());

' 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())

Código de ejemplo

Nota:

[!NOTA] Este código sólo se puede ejecutar una vez. No se puede crear más de una instancia del gráfico.

A continuación se incluye el código muestra completo en C# y Visual Basic.

C#
Visual Basic

static void InsertChartInSpreadsheet(string docName, string worksheetName, string title, Dictionary<string, int> data)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);

        if (sheets is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }

        string? id = sheets.First().Id;

        if (id is null)
        {
            // The worksheet does not have an ID.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(id);

        // Add a new drawing to the worksheet.
        DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
        worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
        { Id = worksheetPart.GetIdOfPart(drawingsPart) });

        // Add a new chart and set the chart language to English-US.
        ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>();
        chartPart.ChartSpace = new ChartSpace();
        chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") });
        DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Chart>(
            new DocumentFormat.OpenXml.Drawing.Charts.Chart());

        // Create a new clustered column chart.
        PlotArea plotArea = chart.AppendChild<PlotArea>(new PlotArea());
        Layout layout = plotArea.AppendChild<Layout>(new Layout());
        BarChart barChart = plotArea.AppendChild<BarChart>(new BarChart(new BarDirection()
        { Val = new EnumValue<BarDirectionValues>(BarDirectionValues.Column) },
            new BarGrouping() { Val = new EnumValue<BarGroupingValues>(BarGroupingValues.Clustered) }));

        uint i = 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.
        foreach (string key in data.Keys)
        {
            BarChartSeries barChartSeries = barChart.AppendChild<BarChartSeries>(new BarChartSeries(new Index()
            {
                Val = new UInt32Value(i)
            },
                new Order() { Val = new UInt32Value(i) },
                new SeriesText(new NumericValue() { Text = key })));

            StringLiteral strLit = barChartSeries.AppendChild<CategoryAxisData>(new CategoryAxisData()).AppendChild<StringLiteral>(new StringLiteral());
            strLit.Append(new PointCount() { Val = new UInt32Value(1U) });
            strLit.AppendChild<StringPoint>(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(title));

            NumberLiteral numLit = barChartSeries.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Values>(
                new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild<NumberLiteral>(new NumberLiteral());
            numLit.Append(new FormatCode("General"));
            numLit.Append(new PointCount() { Val = new UInt32Value(1U) });
            numLit.AppendChild<NumericPoint>(new NumericPoint() { Index = new UInt32Value(0u) }).Append
(new NumericValue(data[key].ToString()));

            i++;
        }

        barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) });
        barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) });

        // Add the Category Axis.
        CategoryAxis catAx = plotArea.AppendChild<CategoryAxis>(new CategoryAxis(new AxisId()
        { Val = new UInt32Value(48650112u) }, new Scaling(new Orientation()
        {
            Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
        }),
            new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) },
            new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) },
            new CrossingAxis() { Val = new UInt32Value(48672768U) },
            new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
            new AutoLabeled() { Val = new BooleanValue(true) },
            new LabelAlignment() { Val = new EnumValue<LabelAlignmentValues>(LabelAlignmentValues.Center) },
            new LabelOffset() { Val = new UInt16Value((ushort)100) }));

        // Add the Value Axis.
        ValueAxis valAx = plotArea.AppendChild<ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
            new Scaling(new Orientation()
            {
                Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
                DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
            }),
            new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Left) },
            new MajorGridlines(),
            new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
            {
                FormatCode = new StringValue("General"),
                SourceLinked = new BooleanValue(true)
            }, new TickLabelPosition()
            {
                Val = new EnumValue<TickLabelPositionValues>
(TickLabelPositionValues.NextTo)
            }, new CrossingAxis() { Val = new UInt32Value(48650112U) },
            new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
            new CrossBetween() { Val = new EnumValue<CrossBetweenValues>(CrossBetweenValues.Between) }));

        // Add the chart Legend.
        Legend legend = chart.AppendChild<Legend>(new Legend(new LegendPosition() { Val = new EnumValue<LegendPositionValues>(LegendPositionValues.Right) },
            new Layout()));

        chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) });

        // Position the chart on the worksheet using a TwoCellAnchor object.
        drawingsPart.WorksheetDrawing = new WorksheetDrawing();
        TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild<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.
        DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
            twoCellAnchor.AppendChild<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() { Id = new UInt32Value(2u), Name = "Chart 1" },
            new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));

        graphicFrame.Append(new Transform(new Offset() { X = 0L, Y = 0L },
                                                                new Extents() { Cx = 0L, Cy = 0L }));

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

        twoCellAnchor.Append(new ClientData());
    }

}

Sub InsertChartInSpreadsheet(docName As String, worksheetName As String, title As String, 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 Is Nothing OrElse sheets.Count() = 0 Then
            ' The specified worksheet does not exist.
            Return
        End If

        Dim id As String = sheets.First().Id

        If id Is Nothing Then
            ' The worksheet does not have an ID.
            Return
        End If

        Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(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)})

        ' 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

        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)})

        ' 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())
    End Using
End Sub

Recursos adicionales

Referencia de la biblioteca de clases del SDK de Open XML

Last updated on 2025-01-21

Compartir a través de

Insertar un gráfico en un documento de hoja de cálculo

Elemento Row

Elemento Cell

Elemento de valor de celda

Funcionamiento del código de ejemplo

Código de ejemplo

Recursos adicionales

Recursos adicionales