Show / Hide Table of Contents

Insert a 3D Area Chart with OpenXML SDK

The example shows how to insert a 3D Area Chart with OpenXML SDK.

Output: InsertArea3DChart.xlsx

public void InsertArea3DChart()
{
using (var spreadsheetDocument = SpreadsheetDocument.Create("InsertArea3DChart.xlsx", SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
        Workbook workbook = new Workbook();
        workbookPart.Workbook = workbook;
        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet();
        FileVersion fv = new FileVersion();
        fv.ApplicationName = "Microsoft Office Excel";

        Sheets sheets = new Sheets();
        Sheet sheet = new Sheet();
        sheet.Name = "Sheet1";
        sheet.SheetId = 1;
        sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
        sheets.Append(sheet);
        workbook.Append(fv);
        workbook.Append(sheets);

        this.AddData(workbookPart, worksheetPart);

        DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
        worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
                { Id = worksheetPart.GetIdOfPart(drawingsPart) });

        ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>();
        chartPart.ChartSpace = new ChartSpace();
        chartPart.ChartSpace.RoundedCorners = new RoundedCorners() { Val = false };

        //Set default style
        var alternateContent = new AlternateContent();
        var choice = alternateContent.AppendNewAlternateContentChoice();
        var c14Style = new DocumentFormat.OpenXml.Office2010.Drawing.Charts.Style() { Val = 102 };
        choice.Requires = "c14";
        choice.AppendChild(c14Style);
        var fallback = alternateContent.AppendNewAlternateContentFallback();
        var cStyle = new Drawing.Charts.Style() { Val = 2 };
        fallback.AppendChild(cStyle);

        var chart = chartPart.ChartSpace.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Chart>(
                    new DocumentFormat.OpenXml.Drawing.Charts.Chart());
        chart.AutoTitleDeleted = new AutoTitleDeleted() { Val = true };
        #region 3D
        chart.View3D = new View3D();
        chart.View3D.RotateX = new RotateX(){Val = 20};
        chart.View3D.RotateY = new RotateY() { Val = 15 };
        chart.View3D.DepthPercent = new DepthPercent(){Val = 100};
        chart.View3D.Perspective = new Perspective(){Val = 0};
        #endregion
        PlotArea plotArea = chart.AppendChild<PlotArea>(new PlotArea());
        var areaChart = plotArea.AppendChild<Area3DChart>(new Area3DChart());
        areaChart.AppendChild(new Grouping() { Val = new EnumValue<GroupingValues>(GroupingValues.Standard) });

        var ser0 = areaChart.AppendChild(new AreaChartSeries());
        ser0.Index = new Index() { Val = new UInt32Value(0u) };
        ser0.Order = new Order() { Val = new UInt32Value(0u) };
        ser0.SeriesText = new SeriesText();
        ser0.SeriesText.StringReference = AddStringReference("Sheet1!$J$1", "Series 1");
        var cat0 = new CategoryAxisData();
        cat0.StringReference =
                    this.AddStringReference("Sheet1!$I$2:$I$5", "Point 1", "Point 2", "Point 3", "Point 4");
        ser0.AppendChild(cat0);
        var val0 = new DocumentFormat.OpenXml.Drawing.Charts.Values();
        val0.NumberReference = this.AddNumberReference("Sheet1!$J$2:$J$5", "2", "4", "6", "8");
        ser0.AppendChild(val0);

        var ser1 = areaChart.AppendChild(new AreaChartSeries());
        ser1.Index = new Index() { Val = new UInt32Value(1u) };
        ser1.Order = new Order() { Val = new UInt32Value(1u) };
        ser1.SeriesText = new SeriesText();
        ser1.SeriesText.StringReference = AddStringReference("Sheet1!$K$1", "Series 2");
        var cat1 = new CategoryAxisData();
        cat1.StringReference =
        this.AddStringReference("Sheet1!$I$2:$I$5", "Point 1", "Point 2", "Point 3", "Point 4");
        ser1.AppendChild(cat1);
        var val1 = new DocumentFormat.OpenXml.Drawing.Charts.Values();
        val1.NumberReference = this.AddNumberReference("Sheet1!$K$2:$K$5", "4", "3", "2", "1");
        ser1.AppendChild(val1);

        var ser2 = areaChart.AppendChild(new AreaChartSeries());
        ser2.Index = new Index() { Val = new UInt32Value(2u) };
        ser2.Order = new Order() { Val = new UInt32Value(2u) };
        ser2.SeriesText = new SeriesText();
        ser2.SeriesText.StringReference = AddStringReference("Sheet1!$L$1", "Series 3");
        var cat2 = new CategoryAxisData();
        cat2.StringReference =
        this.AddStringReference("Sheet1!$I$2:$I$5", "Point 1", "Point 2", "Point 3", "Point 4");
        ser2.AppendChild(cat2);
        var val2 = new DocumentFormat.OpenXml.Drawing.Charts.Values();
        val2.NumberReference = this.AddNumberReference("Sheet1!$L$2:$L$5", "2", "1", "-1", "-2");
        ser2.AppendChild(val2);

        areaChart.Append(new AxisId() { Val = new UInt32Value(1250099810u) });
        areaChart.Append(new AxisId() { Val = new UInt32Value(1687146081u) });

        CategoryAxis catAx = plotArea.AppendChild<CategoryAxis>(new CategoryAxis());
        catAx.Append(new AxisId() { Val = new UInt32Value(1250099810u) });
        catAx.Append(new Scaling() { Orientation = new Orientation() { Val = new EnumValue<OrientationValues>(OrientationValues.MinMax) } });
        catAx.Append(new Delete() { Val = new BooleanValue(false) });
        catAx.Append(new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) });
        catAx.Append(new Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = new BooleanValue(false) });
        catAx.Append(new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.Low) });
        catAx.Append(new CrossingAxis() { Val = new UInt32Value(1687146081u) });
        catAx.Append(new LabelOffset() { Val = new UInt16Value((ushort)100) });

        ValueAxis valAx = plotArea.AppendChild<ValueAxis>(new ValueAxis());
        valAx.Append(new AxisId() { Val = new UInt32Value(1687146081u) });
        valAx.Append(new Scaling() { Orientation = new Orientation() { Val = new EnumValue<OrientationValues>(OrientationValues.MinMax) } });
        valAx.Append(new Delete() { Val = new BooleanValue(false) });
        valAx.Append(new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) });
        valAx.Append(new Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = new BooleanValue(false) });
        valAx.Append(new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) });
        valAx.Append(new CrossingAxis() { Val = new UInt32Value(1250099810u) });
        valAx.Append(new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) });
        valAx.Append(new CrossBetween() { Val = new EnumValue<CrossBetweenValues>(CrossBetweenValues.Between) });

        chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) });
        chart.Append(new DisplayBlanksAs() { Val = new EnumValue<DisplayBlanksAsValues>(DisplayBlanksAsValues.Zero) });
        chart.Append(new ShowDataLabelsOverMaximum() { Val = new BooleanValue(false) });

        // Save the chart part.
        chartPart.ChartSpace.Save();

        // Position the chart on the worksheet using a TwoCellAnchor object.
        drawingsPart.WorksheetDrawing = new WorksheetDrawing();
        var twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild<TwoCellAnchor>(new TwoCellAnchor());
        twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new              ColumnId("0"),
            new ColumnOffset("5"),
            new RowId("0"),
            new RowOffset("5")));
        twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new                ColumnId("5"),
            new ColumnOffset("152405"),
            new RowId("12"),
            new RowOffset("5")));

        // Append a GraphicFrame to the TwoCellAnchor object.
        var graphicFrame =
            twoCellAnchor.AppendChild(new GraphicFrame());
        graphicFrame.Macro = "";
        graphicFrame.NonVisualGraphicFrameProperties = new Drawing.Spreadsheet.NonVisualGraphicFrameProperties();
        graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Drawing.Spreadsheet.NonVisualDrawingProperties();
        graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Id = new UInt32Value(1u);
        graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name = "Chart 1";
        graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties();

        graphicFrame.Transform = new Transform();
        graphicFrame.Transform.Offset = new Offset();
        graphicFrame.Transform.Offset.X = 0;
        graphicFrame.Transform.Offset.Y = 0;
        graphicFrame.Transform.Extents = new Extents();
        graphicFrame.Transform.Extents.Cx = 0;
        graphicFrame.Transform.Extents.Cy = 0;

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

        twoCellAnchor.Append(new ClientData());

        drawingsPart.WorksheetDrawing.Save();
        workbookPart.Workbook.Save();
        spreadsheetDocument.Close();
    }
}

internal NumberReference AddNumberReference(string formula, params string[] values)
{
    var numRef = new NumberReference();

    numRef.Formula = new Drawing.Charts.Formula() { Text = formula };
    numRef.NumberingCache = new NumberingCache();
    numRef.NumberingCache.FormatCode = new FormatCode("General");
    numRef.NumberingCache.PointCount = new PointCount() { Val = new UInt32Value(Convert.ToUInt32(values.Length)) };

    uint index = 0;
    foreach (var value in values)
    {
        numRef.NumberingCache.AppendChild(new NumericPoint() { Index = new UInt32Value(index) })
            .Append(new NumericValue(value));
        index++;
    }

    return numRef;
}

internal StringReference AddStringReference(string formula, params string[] values)
{
    var strRef = new StringReference();
    strRef.Formula = new Drawing.Charts.Formula() { Text = formula };
    strRef.StringCache = new StringCache();
    strRef.StringCache.PointCount = new PointCount() { Val = new UInt32Value(Convert.ToUInt32(values.Length)) };

    uint index = 0;
    foreach (var value in values)
    {
        strRef.StringCache.AppendChild(new StringPoint { Index = new UInt32Value(index) })
            .Append(new NumericValue(value));
        index++;
    }
            
    return strRef;
}

internal void AddData(WorkbookPart workbookPart,WorksheetPart worksheetPart)
{
    SheetData sheetData = new SheetData();

    var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
    sharedStringPart.SharedStringTable = new SharedStringTable();
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Series 1")));
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Series 2")));
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Series 3")));
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Point 1")));
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Point 2")));
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Point 3")));
    sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text("Point 4")));

    var row1 = new Row() { RowIndex = 1 };
    var row2 = new Row() { RowIndex = 2 };
    var row3 = new Row() { RowIndex = 3 };
    var row4 = new Row() { RowIndex = 4 };
    var row5 = new Row() { RowIndex = 5 };
    sheetData.Append(row1);
    sheetData.Append(row2);
    sheetData.Append(row3);
    sheetData.Append(row4);
    sheetData.Append(row5);

    var cellJ1 = new Cell() { CellReference = "J1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
    cellJ1.CellValue = new CellValue("0"); //Index to the shared string "Series 1"
    var cellK1 = new Cell() { CellReference = "K1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
    cellK1.CellValue = new CellValue("1");
    var cellL1 = new Cell() { CellReference = "L1", DataType = new EnumValue<CellValues>(CellValues.SharedString) };
    cellL1.CellValue = new CellValue("2");

    row1.Append(cellJ1);
    row1.Append(cellK1);
    row1.Append(cellL1);

    row2.Append(new Cell() { CellReference = "I2", CellValue = new CellValue("3"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
    row2.Append(new Cell() { CellReference = "J2", CellValue = new CellValue("2") });
    row2.Append(new Cell() { CellReference = "K2", CellValue = new CellValue("4") });
    row2.Append(new Cell() { CellReference = "L2", CellValue = new CellValue("2") });

    row3.Append(new Cell() { CellReference = "I3", CellValue = new CellValue("4"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
    row3.Append(new Cell() { CellReference = "J3", CellValue = new CellValue("4") });
    row3.Append(new Cell() { CellReference = "K3", CellValue = new CellValue("3") });
    row3.Append(new Cell() { CellReference = "L3", CellValue = new CellValue("1") });

    row4.Append(new Cell() { CellReference = "I4", CellValue = new CellValue("5"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
    row4.Append(new Cell() { CellReference = "J4", CellValue = new CellValue("6") });
    row4.Append(new Cell() { CellReference = "K4", CellValue = new CellValue("2") });
    row4.Append(new Cell() { CellReference = "L4", CellValue = new CellValue("-1") });

    row5.Append(new Cell() { CellReference = "I5", CellValue = new CellValue("6"), DataType = new EnumValue<CellValues>(CellValues.SharedString) });
    row5.Append(new Cell() { CellReference = "J5", CellValue = new CellValue("8") });
    row5.Append(new Cell() { CellReference = "K5", CellValue = new CellValue("1") });
    row5.Append(new Cell() { CellReference = "L5", CellValue = new CellValue("-2") });

    worksheetPart.Worksheet.Append(sheetData);
}
Back to top Generated by DocFX