Show / Hide Table of Contents

Set Category Axis Font with OpenXML SDK

The example shows how to set font to bold and red color on the category axis with OpenXML SDK.

Output: CategoryAxisFont.xlsx

       public void CategoryAxisFont()
        {
            using (var spreadsheetDocument = SpreadsheetDocument.Create("CategoryAxisFont.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) });


                // Add a new chart and set the chart language to English-US.
                ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>();
                chartPart.ChartSpace = new ChartSpace();
                chartPart.ChartSpace.RoundedCorners = new RoundedCorners() { Val = false };

                //Set default style
                var alternateContent = chartPart.ChartSpace.AppendChild(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);


                //chartPart.ChartSpace.EditingLanguage = new EditingLanguage{Val = new StringValue("en-US")};

                var chart = chartPart.ChartSpace.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Chart>(
                    new DocumentFormat.OpenXml.Drawing.Charts.Chart());
                chart.AutoTitleDeleted = new AutoTitleDeleted() { Val = true };
                PlotArea plotArea = chart.AppendChild<PlotArea>(new PlotArea());
                //Layout layout = plotArea.AppendChild<Layout>(new Layout());
                var barChart = plotArea.AppendChild(new BarChart());
                barChart.BarDirection = new BarDirection() { Val = BarDirectionValues.Bar };
                barChart.AppendChild(new Grouping() { Val = new EnumValue<GroupingValues>(GroupingValues.Standard) });
                barChart.VaryColors = new VaryColors() { Val = true };

                var ser0 = barChart.AppendChild(new BarChartSeries());
                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");
                ser0.InvertIfNegative = new InvertIfNegative() { Val = false };
                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 = barChart.AppendChild(new BarChartSeries());
                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");
                ser1.InvertIfNegative = new InvertIfNegative() { Val = false };
                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 = barChart.AppendChild(new BarChartSeries());
                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");
                ser2.InvertIfNegative = new InvertIfNegative() { Val = false };
                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);

                barChart.Append(new GapWidth() { Val = 150 });
                barChart.Append(new Overlap() { Val = 0 });
                barChart.Append(new AxisId() { Val = 1250099810u });
                barChart.Append(new AxisId() { Val = 1687146081u });

                var catAx = plotArea.AppendChild<CategoryAxis>(new CategoryAxis());
                catAx.Append(new AxisId() { Val = 1250099810u });
                catAx.Append(new Scaling()
                { Orientation = new Orientation() { Val = Drawing.Charts.OrientationValues.MinMax } });
                catAx.Append(new Delete() { Val = false });
                catAx.Append(new AxisPosition() { Val = AxisPositionValues.Bottom });
                catAx.Append(new Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = false });
                catAx.Append(new TickLabelPosition() { Val = TickLabelPositionValues.Low });

                #region CategoryAxisFont
                catAx.TextProperties = new Drawing.Charts.TextProperties();
                catAx.TextProperties.BodyProperties = new BodyProperties();
                catAx.TextProperties.BodyProperties.RightToLeftColumns = new BooleanValue(false);
                catAx.TextProperties.BodyProperties.Anchor = new EnumValue<TextAnchoringTypeValues>(TextAnchoringTypeValues.Top);
                catAx.TextProperties.ListStyle = new ListStyle();
                //The actual formatting is set on the default run properties
                var catAxP = new Paragraph();
                catAxP.ParagraphProperties = new ParagraphProperties();
                var catAxDefRPr = new DefaultRunProperties();
                //Set the Bold attribute
                catAxDefRPr.Bold = true;
                var catAxDefRPrSolidFill = catAxDefRPr.AppendChild(new SolidFill());
                catAxDefRPrSolidFill.PresetColor = new PresetColor() { Val = PresetColorValues.Red };
                catAxP.ParagraphProperties.AppendChild(catAxDefRPr);
                catAx.TextProperties.Append(catAxP);
                #endregion
                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 = 1687146081u });
                valAx.Append(new Scaling() { Orientation = new Orientation() { Val = Drawing.Charts.OrientationValues.MinMax } });
                valAx.Append(new Delete() { Val = new BooleanValue(false) });
                valAx.Append(new AxisPosition() { Val = AxisPositionValues.Bottom });
                valAx.Append(new Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = false });
                valAx.Append(new TickLabelPosition() { Val = TickLabelPositionValues.NextTo });
                valAx.Append(new CrossingAxis() { Val = 1250099810u });
                valAx.Append(new Crosses() { Val = CrossesValues.AutoZero });
                valAx.Append(new CrossBetween() { Val = CrossBetweenValues.Between });

                chart.Append(new PlotVisibleOnly() { Val = true });
                chart.Append(new DisplayBlanksAs() { Val = DisplayBlanksAsValues.Zero });
                chart.Append(new ShowDataLabelsOverMaximum() { Val = 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 Drawing.Spreadsheet.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 = "http://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 Spreadsheet.Text("Series 1")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Series 2")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Series 3")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Point 1")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Point 2")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.Text("Point 3")));
            sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Spreadsheet.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