c# - 将数字添加到图表的轴

Question

所以我有一个相当标准的图表方法，它使用 c# DocumentFormat.OpenXML 命名空间，效果很好。但我不知道如何在轴上放置数字标签，特别是值轴。不幸的是，c# openxml 几乎完全没有记录。如果这是一个重复的问题，我会很高兴在其他地方看到答案，因为我找不到它。如何向坐标轴添加数字标签？

我的完整代码在下面，但这是我创建值轴的部分，所以我假设我必须在此处的括号中添加一些内容，例如 new ???? 但我不知道是什么。

ValueAxis valAx = plotArea.AppendChild<ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
    new Scaling(new DocumentFormat.OpenXml.Drawing.Charts.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) })
    //, new ???????() { ???? } //I think I need to add something here
    );

这是我现在得到的图表:

这是我想要的样子:

完整代码如下:

private static void InsertChartInSpreadsheet(SpreadsheetDocument document, string title, Dictionary<string, int> data)
{
    WorksheetPart graphWorksheetPart = (WorksheetPart)document.WorkbookPart.AddNewPart<WorksheetPart>();
    graphWorksheetPart.Worksheet = new Worksheet(new SheetData());
    Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>() ;

    Sheet sheet = new Sheet()
    {
        Id = document.WorkbookPart.GetIdOfPart(graphWorksheetPart),
        SheetId = 2,
        Name = "Graph"
    };
    sheets.Append(sheet);

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

    // 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 DocumentFormat.OpenXml.Drawing.Charts.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 DocumentFormat.OpenXml.Drawing.Charts.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) });

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

    // 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("1"),
        new ColumnOffset("581025"),
        new RowId("1"),
        new RowOffset("114300")));
    twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("9"),
        new ColumnOffset("276225"),
        new RowId("16"),
        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());

    // Save the WorksheetDrawing object.
    drawingsPart.WorksheetDrawing.Save();


}

Answer 1

随附的代码应该为您的 x 轴和 y 轴获得所需的外观。新的代码段被注释包围着

//START NEW CODE
//END NEW CODE

所有新代码都位于底部的类别和值(value)轴区域:

    private static void InsertChartInSpreadsheet(SpreadsheetDocument document, string title, Dictionary<string, int> data)
    {
        WorksheetPart graphWorksheetPart = (WorksheetPart)document.WorkbookPart.AddNewPart<WorksheetPart>();
        graphWorksheetPart.Worksheet = new Worksheet(new SheetData());
        Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>() ;

        Sheet sheet = new Sheet()
        {
            Id = document.WorkbookPart.GetIdOfPart(graphWorksheetPart),
            SheetId = 2,
            Name = "Graph"
        };
        sheets.Append(sheet);

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

        // 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 DocumentFormat.OpenXml.Drawing.Charts.Orientation()
        {
            Val = new EnumValue<DocumentFormat.
OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
        }),
            //START NEW CODE
            new Delete() {Val = false},
            //END NEW CODE
            new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) },
            //START NEW CODE
            new NumberingFormat() {FormatCode = "General", SourceLinked = false},
            new MajorTickMark() {Val = TickMarkValues.Outside},
            new MinorTickMark() {Val = TickMarkValues.Cross},
            //END NEW CODE
            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) },
            //START NEW CODE
            new NoMultiLevelLabels() {Val = true}
            //END NEW CODE
            ));

        // Add the Value Axis.
        ValueAxis valAx = plotArea.AppendChild<ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
            new Scaling(new DocumentFormat.OpenXml.Drawing.Charts.Orientation()
            {
                Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
                DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
            }),
            //START NEW CODE
            new Delete() {Val = false},
            //END NEW CODE
            new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Left) },
            new MajorGridlines(),
            new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
            {
                FormatCode = new StringValue("General"),
                SourceLinked = new BooleanValue(true)
            },
            //START NEW CODE
                new MajorTickMark() {Val = TickMarkValues.Outside},
                new MinorTickMark() {Val = TickMarkValues.Cross},
            //END NEW CODE
            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) });

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

        // 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("1"),
            new ColumnOffset("581025"),
            new RowId("1"),
            new RowOffset("114300")));
        twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("9"),
            new ColumnOffset("276225"),
            new RowId("16"),
            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());

        // Save the WorksheetDrawing object.
        drawingsPart.WorksheetDrawing.Save();


    }

我使用 OpenXML Productivity Tool 将您生成的文件与包含轴详细信息的类似文件进行比较。通过比较，您可以看到需要将哪些调用添加到平面轴才能打开标记。