.net - 数据表中的 OpenXML 折线图

Question

我正在生成一份报告，该报告是根据查询字符串在网页的页面加载时生成的。我在电子表格中生成的单元格数据完全符合我的要求。现在我需要添加一个折线图。数据是动态的，行数会有所不同。

搜索后没有信息，这与在 .net 中执行 openXML 折线图相关，因此我尝试遵循条形图示例，该示例将字典作为数据源，并尝试破解它，以便从中制作折线图一个数据表。

折线图出现在我想要的工作表中，它正在添加基于数据表的 column1 的系列。我希望每个点都基于第 2 到 13 列，但我只得到第 13 列，并且没有添加点名称。

老实说，我讨厌 Excel 和电子表格，对它们知之甚少，也没有多少时间学习。到目前为止，这是我尝试添加数据的代码:

For i As UInteger = 0UI To CUInt(dataTable.Rows.Count - 2) 

    Dim seriesName As String = dataTable.Rows(CInt(i))(dataTable.Columns(CInt(0)).ColumnName).ToString()
    Dim lineChartSeries As LineChartSeries
    lineChartSeries = lineChart.AppendChild(New LineChartSeries(New Index() With {.Val = New UInt32Value(i)}, New Order() With {.Val = New UInt32Value(i)}, New SeriesText(New NumericValue() With {.Text = seriesName})))

    For j As UInteger = 1UI To CUInt(dataTable.Columns.Count - 3) 

        Try
            Dim pointName As String = dataTable.Columns(CInt(j)).ColumnName
            Dim pointValue As Integer = CInt(dataTable.Rows(CInt(i))(pointName))

            Dim strLit As New StringLiteral
            strLit.Append(New PointCount() With {.Val = New UInt32Value(1UI)})
            strLit.AppendChild(New StringPoint() With {.Index = New UInt32Value(1UI)}).Append(New NumericValue(pointName))
            lineChartSeries.AppendChild(New CategoryAxisData()).AppendChild(strLit)

            Dim numLit As New NumberLiteral
            numLit.Append(New FormatCode("General"))
            numLit.Append(New PointCount() With {.Val = New UInt32Value(1UI)})
            numLit.AppendChild(New NumericPoint() With {.Index = New UInt32Value(1UI)}).Append(New NumericValue(pointValue.ToString()))
            lineChartSeries.AppendChild(New DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild(numLit)

        Catch ex As Exception

        End Try
    Next j
Next i

Answer 1

我通过检查chart1.xml 源并构建复制XML 结构的方法设法创建了一个图表。包含此方法的类具有数据开始和结束的局部点变量，以及工作表中表格的偏移量。

我毫不怀疑这可以做得更优雅。

Private Sub GenerateChart(ByRef spreadsheetDocument As SpreadsheetDocument, ByVal dataTable As System.Data.DataTable, ByVal docName As String, ByVal worksheetName As String) 

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

        Dim workSheetPart As WorksheetPart = CType(spreadsheetDocument.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)

        Dim drawingsPart As DrawingsPart = workSheetPart.AddNewPart(Of DrawingsPart)()
        workSheetPart.Worksheet.Append(New DocumentFormat.OpenXml.Spreadsheet.Drawing() With {.Id = workSheetPart.GetIdOfPart(drawingsPart)})
        workSheetPart.Worksheet.Save()

        Dim chartPart As ChartPart = drawingsPart.AddNewPart(Of ChartPart)()
        chartPart.ChartSpace = New ChartSpace()
        chartPart.ChartSpace.Append(New EditingLanguage() With {.Val = New StringValue("en-GB")})

        Dim chart As DocumentFormat.OpenXml.Drawing.Charts.Chart = chartPart.ChartSpace.AppendChild(Of DocumentFormat.OpenXml.Drawing.Charts.Chart)(New DocumentFormat.OpenXml.Drawing.Charts.Chart())
        Dim autoTitleDeleted As AutoTitleDeleted = chart.AppendChild(New AutoTitleDeleted() With {.Val = New BooleanValue(False)})
        Dim plotArea As PlotArea = chart.AppendChild(Of PlotArea)(New PlotArea())
        Dim layout As Layout = plotArea.AppendChild(Of Layout)(New Layout())

        Dim lineChart As LineChart = plotArea.AppendChild(New LineChart())
        Dim grouping As Grouping = lineChart.AppendChild(New Grouping() With {.Val = New EnumValue(Of GroupingValues)(GroupingValues.Standard)})
        Dim smoooth As Smooth = lineChart.AppendChild(New Smooth() With {.Val = New BooleanValue(False)})

        For i As UInteger = 0UI To CUInt(dataTable.Rows.Count - 3) '-3?.... to remove table headers, totals, and the extra because 0 based index'

            Dim seriesName As String = dataTable.Rows(CInt(i))(0).ToString
            Dim lineChartSeries As LineChartSeries = lineChart.AppendChild(New LineChartSeries(New Index With {.Val = i}, New Order With {.Val = i}))
            Dim chartText As ChartText = lineChartSeries.AppendChild(New ChartText())

            Dim stringReference As StringReference = chartText.AppendChild(New StringReference())
            stringReference.AppendChild(New DocumentFormat.OpenXml.Drawing.Charts.Formula() With {.Text = worksheetName + "!$" + GetXAxisName(CUInt(__rangeStart.X)) + "$" + (CUInt(__topRowOffset + __previousTableRowCount + i + 2)).ToString})

            Dim stringCache As StringCache = stringReference.AppendChild(New StringCache)
            stringCache.Append(New PointCount() With {.Val = 1})
            stringCache.AppendChild(New StringPoint() With {.Index = New UInt32Value(CUInt(0))}).Append(New NumericValue(seriesName))

            Dim marker As Marker = lineChartSeries.AppendChild(New Marker() With {.Symbol = New Symbol() With {.Val = New EnumValue(Of MarkerStyleValues)(MarkerStyleValues.None)}})

            Dim categoryAxisData As CategoryAxisData = lineChartSeries.AppendChild(New CategoryAxisData())
            Dim catStringReference As StringReference = categoryAxisData.AppendChild(New StringReference)
            catStringReference.AppendChild(New DocumentFormat.OpenXml.Drawing.Charts.Formula() With {.Text = worksheetName + "!$" + GetXAxisName(CUInt(__rangeStart.X) + 1UI) + "$" + (CUInt(__rangeStart.Y)).ToString + ":$" + GetXAxisName(CUInt(__rangeEnd.X) + 1UI) + "$" + (CUInt(__rangeStart.Y)).ToString})
            Dim catStringCache As StringCache = catStringReference.AppendChild(New StringCache(New PointCount() With {.Val = 12UI}))

            Dim values As DocumentFormat.OpenXml.Drawing.Charts.Values = lineChartSeries.AppendChild(New DocumentFormat.OpenXml.Drawing.Charts.Values())
            Dim numberReference As NumberReference = values.AppendChild(New NumberReference())
            numberReference.AppendChild(New DocumentFormat.OpenXml.Drawing.Charts.Formula() With {.Text = worksheetName + "!$" + GetXAxisName(CUInt(__rangeStart.X) + 1UI) + "$" + (i + 1UI + __topRowOffset).ToString + ":$" + GetXAxisName(CUInt(__rangeEnd.X) + 1UI) + "$" + (i + 1UI + __topRowOffset).ToString})
            Dim numberingCache As NumberingCache = numberReference.AppendChild(New NumberingCache(New FormatCode("General")))

            numberingCache.AppendChild(New PointCount() With {.Val = 12UI})

            Dim jUpper As UInteger = CUInt(dataTable.Columns.Count - 5)
            For j As UInteger = 1UI To jUpper

                Dim pointName As String = dataTable.Columns(CInt(j)).ColumnName
                Dim pointValue As Integer = CInt(dataTable.Rows(CInt(i))(pointName))

                Dim numericPoint As NumericPoint = catStringCache.AppendChild(New NumericPoint() With {.Index = New UInt32Value(j)})
                numericPoint.Append(New NumericValue(pointName))

                Dim numericValue As NumericPoint = numberingCache.AppendChild(New NumericPoint() With {.Index = New UInt32Value(j)})
                numericValue.Append(New NumericValue(dataTable.Rows(CInt(i))(CInt(j)).ToString()))

            Next j

        Next i

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

        Dim categoryAxis As CategoryAxis =
            plotArea.AppendChild(New CategoryAxis(
                                      New AxisId() With {.Val = New UInt32Value(48650112UI)} _
                                    , New Delete() With {.Val = False} _
                                    , 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)}
                                    )
                                )

        Dim valueAxis As ValueAxis =
            plotArea.AppendChild(New ValueAxis(
                                      New AxisId() With {.Val = New UInt32Value(48672768UI)} _
                                    , New Delete() With {.Val = False} _
                                    , 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 TickLabelPosition() With {.Val = New EnumValue(Of TickLabelPositionValues)(TickLabelPositionValues.NextTo)} _
                                        , New CrossingAxis() With {.Val = New UInt32Value(48650112UI)} _
                                        , New MajorGridlines()
                                    )
                                )
        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)})
        chartPart.ChartSpace.Save()
        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("17"), New ColumnOffset("0"), New RowId("3"), New RowOffset("0")))
        twoCellAnchor.Append(New DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(New ColumnId("26"), New ColumnOffset("0"), New RowId("26"), New RowOffset("0")))

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

        drawingsPart.WorksheetDrawing.Save()

    End Sub