vb.net - 如何根据 DataTable 中的数据创建多个饼图 - VB.Net

Question

我对公司中一个旧网络程序的修改遇到了一些障碍。基本上它的功能是根据DataTable中的数据创建多个饼图。

以前，SQL 语句和饼图生成在 For 循环中连接在一起，导致加载时间变慢（40 个图表 = 30+ 秒）。

所以我做的第一件事就是将 SQL 语句与 For 循环分开。

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")
    Dim dt1 As DataTable = GetData(query1)

    Dim result As String = dt1.AsEnumerable().[Select](Function(x) x("MacId").ToString()).Aggregate(Function(a, b) String.Concat(a & "'" & "," & "'" & b))

    Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _
                                            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & result & "') ) Z " _
                                            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _
                                            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _
                                            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _
                                            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & result & "') group by A.MacID, A.EventName) a " _
                                            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")
    Dim dt As DataTable = GetData(query)

DataTabledt将用于创建饼图。为了举例说明数据的外观，这里有一个示例：

----------------------------
| MacID | EventName | DIFF |  'DIFF is total time recorded in seconds
----------------------------
|  A01  |    BLK    |  65  |
|  A01  |    COM    |  0   |
|  A01  |    MDL    |  0   |
|  A01  |    MTL    |  0   |
|  A01  |    OFF    |  0   |
|  A01  |    RPR    |  0   |
|  A01  |    RUN    |  0   |
|  Z09  |    BLK    |  0   |
|  Z09  |    COM    |  0   |
|  Z09  |    MDL    |  0   |
|  Z09  |    MTL    | 256  |
|  Z09  |    OFF    | 3220 |
|  Z09  |    RPR    | 1164 |
|  Z09  |    RUN    | 19874|
----------------------------

每台机器有 7 个不同EventName，并记录每个事件下的时间。现在我需要创建一个饼图，每台机器一个，覆盖 7 EventName。

但是，当我将 SQL 从 For 循环中移出时，饼图会奇怪地生成。这是生成饼图的代码，在同一个子目录下。

Dim check2
PlaceHolder1.Dispose()
PlaceHolder2.Dispose()

For a As Integer = 0 To dt1.Rows.Count - 1

        check2 = dt1.Rows(a)(0).ToString()

        Dim mychart As Chart = New Chart
        Dim ChartArea1 As ChartArea = New ChartArea
        Dim Legend1 As Legend = New Legend

        Dim x As String() = New String(dt.Rows.Count - 1) {}
        Dim y As Integer() = New Integer(dt.Rows.Count - 1) {}
        For i As Integer = 0 To dt.Rows.Count - 1

            x(i) = dt.Rows(i)(1).ToString()
            y(i) = Convert.ToInt32(dt.Rows(i)(2))
        Next

        mychart.Width = 600
        mychart.Height = 400

        mychart.ChartAreas.Clear()
        mychart.ChartAreas.Add("ChartArea1")

        mychart.Series.Clear()
        mychart.Series.Add(0)
        mychart.Series(0).Points.DataBindXY(x, y)

        mychart.Titles.Clear()
        mychart.Titles.Add("[" & a + 1 & "] " & check2.ToString.ToUpper)
        mychart.Titles(0).Font = New System.Drawing.Font("Tahoma", 12, System.Drawing.FontStyle.Bold)
        mychart.Titles(0).BackColor = Color.PaleTurquoise
        mychart.Titles(0).ForeColor = Color.Black

        mychart.Series(0).ChartType = SeriesChartType.Pie
        mychart.Series(0).LegendText = "#VALX"
        mychart.Series(0)("BarLabelStyle") = "Center"
        mychart.Series(0)("pointWidth") = "1"
        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid
        mychart.Series(0).BorderWidth = 2
        mychart.Series(0).Label = "#PERCENT"
        mychart.Series(0).ShadowColor = Color.Gray
        mychart.Series(0).ShadowOffset = 10

        mychart.Series(0).LabelBackColor = Drawing.Color.Cornsilk
        mychart.Series(0).Font = New Font("Tahoma", 9, FontStyle.Bold)

        mychart.Series(0).LegendToolTip = "#VALX - #PERCENT"
        mychart.Series(0).ToolTip = "#VALX - #PERCENT"

        mychart.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True

        mychart.Series(0).CustomProperties = "DrawingStyle=LightToDark"
        'new
        Chart1.Series(0).CustomProperties = "PieLabelStyle=Outside"

        mychart.ChartAreas("ChartArea1").BorderDashStyle = BorderStyle.Solid
        mychart.Palette = ChartColorPalette.None
        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid
        mychart.Series(0).BorderWidth = 2
        mychart.Series(0).BorderColor = Color.Black

        mychart.PaletteCustomColors = {Drawing.Color.Black, Drawing.Color.White, Drawing.Color.Blue, Drawing.Color.Yellow, Drawing.Color.Red, Drawing.Color.Orange, Drawing.Color.Green}

        mychart.Legends.Clear()
        mychart.Legends.Add(0)
        mychart.Legends(0).Font = New Font("Tahoma", 10, FontStyle.Bold)
        mychart.Legends(0).Docking = System.Web.UI.DataVisualization.Charting.Docking.Bottom

        mychart.DataBind()

        If (a + 1) Mod 2 <> 0 Then

            PlaceHolder1.Controls.Add(mychart)

        End If

        If (a + 1) Mod 2 = 0 Then

            PlaceHolder2.Controls.Add(mychart)

        End If

    Next

我得到的只是所有具有太多象限的饼图（如饼图中的 40 多个切片）并且对于所有机器都是相同的。知道要为我的饼图代码更改什么吗？

标签： vb.netcharts