javascript - 谷歌折线图 - 从arraydatatable动态添加列/行

Question

我想绘制折线图，​​将两条线生成一张图表，以便显示每天每条线的总数。

当我运行程序时，错误消息“第 1 行有 1 列，但必须有 27 列”。
事实上，我背后的代码是从 SQL 中检索数据，表中有 26 行，而脚本让我很困惑，有 27 行。

我试图一一添加列，但仍然是同样的错误。

我在想如何添加动态列和行而不是手动添加列？

这是我的脚本：

function drawMultiLineChart() {
            var options = {
                title: 'Multi Line Chart',
                width: 1000,
                height: 500,                                         
            };

       $.ajax({
            type: "POST",
            url: "Chart.aspx/GetMultiLineData",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                var data = google.visualization.arrayToDataTable(r.d);                                        
                data.addColumn('string', 'Line');
                data.addColumn('string', 'F1');
                data.addColumn('string', 'T1');
                data.addColumn('string', 'T10');
                data.addColumn('string', 'T11');
                data.addColumn('string', 'T12');
                data.addColumn('string', 'T13');
                data.addColumn('string', 'T14');
                data.addColumn('string', 'T15');
                data.addColumn('string', 'T16');
                data.addColumn('string', 'T19');
                data.addColumn('string', 'T2');
                data.addColumn('string', 'T21');
                data.addColumn('string', 'T22');
                data.addColumn('string', 'T23');
                data.addColumn('string', 'T24');
                data.addColumn('string', 'T25');
                data.addColumn('string', 'T26');
                data.addColumn('string', 'T27');
                data.addColumn('string', 'T28');
                data.addColumn('string', 'T3');
                data.addColumn('string', 'T4');
                data.addColumn('string', 'T5');
                data.addColumn('string', 'T6');
                data.addColumn('string', 'T7');
                data.addColumn('string', 'T8');
                data.addColumn('string', 'T9');                                        
                var chart = new google.visualization.LineChart($("#divMultiLineChart")[0]);
                chart.draw(data, options);
            },
            failure: function (r) {
                alert(r.d);
            },
            error: function (r) {
                alert(r.d);
            }
        });
    }

标签： javascriptjsonajaxchartsgoogle-visualization

Answer 1

为了在图表上绘制多条线。
每个系列/行必须在数据表中的单独列中。

行和列的格式需要如下格式化...

[
  ["SelectedDate","F1","T1","T2","T3", ...]
  ["08/01/2020",8,10,12,14, ...]
  ...
]

如果这种格式在 SQL 中很难生成，
我们可以在绘制图表之前在客户端对数据进行操作。

当前的数据格式如下...

[
  ["SelectedDate","Line","TotalCavities"],
  ["08/01/2020","F1",8],
  ["08/01/2020","T1",21],
  ...
]

我们可以使用 google 提供的方法将数据转换为正确的格式。

首先，我们创建数据表。在第二列上分组，然后创建一个数据视图，
每行有一列。

请参阅以下工作片段...

google.charts.load("current", {
  packages: ["corechart"]
}).then(function () {
  var options = {
    title: "Multi Line Chart",
    width: 1800,
    height: 700,
    legend: { position: "bottom" }
  };

  $.ajax({
    type: "POST",
    url: "Chart.aspx/GetMultiLineData",
    data: "{}",
    contentType: "application/json; charset=utf-8",
    dataType: "json"
  }).done(drawMultiLineChart).fail(function () {
    drawMultiLineChart({d: [["SelectedDate","Line","TotalCavities"],["08/01/2020","F1",8],["08/01/2020","T1",21],["08/01/2020","T10",0],["08/01/2020","T11",24],["08/01/2020","T12",24],["08/01/2020","T13",24],["08/01/2020","T16",24],["08/01/2020","T19",20],["08/01/2020","T21",21],["08/01/2020","T22",29],["08/01/2020","T23",32],["08/01/2020","T26",16],["08/01/2020","T27",16],["08/01/2020","T4",0],["08/01/2020","T5",30],["08/01/2020","T6",15],["08/01/2020","T7",26],["08/01/2020","T8",24],["08/01/2020","T9",32],["08/02/2020","T1",15],["08/02/2020","T10",0],["08/02/2020","T11",23],["08/02/2020","T12",32],["08/02/2020","T13",14],["08/02/2020","T16",24],["08/02/2020","T19",30],["08/02/2020","T21",21],["08/02/2020","T22",19],["08/02/2020","T23",32],["08/02/2020","T26",16],["08/02/2020","T3",0],["08/02/2020","T4",0],["08/02/2020","T5",20],["08/02/2020","T6",15],["08/02/2020","T7",8],["08/02/2020","T8",32],["08/02/2020","T9",24],["08/03/2020","T1",18],["08/03/2020","T10",8],["08/03/2020","T11",23],["08/03/2020","T12",24],["08/03/2020","T13",24],["08/03/2020","T16",24],["08/03/2020","T19",18],["08/03/2020","T21",21],["08/03/2020","T22",21],["08/03/2020","T23",32],["08/03/2020","T27",24],["08/03/2020","T4",0],["08/03/2020","T5",18],["08/03/2020","T6",17],["08/03/2020","T7",20],["08/03/2020","T8",23],["08/03/2020","T9",24],["08/04/2020","F1",24],["08/04/2020","T1",24],["08/04/2020","T10",0],["08/04/2020","T11",24],["08/04/2020","T12",32],["08/04/2020","T13",16],["08/04/2020","T16",8],["08/04/2020","T19",18],["08/04/2020","T21",21],["08/04/2020","T22",30],["08/04/2020","T23",32],["08/04/2020","T5",30],["08/04/2020","T6",17],["08/04/2020","T7",24],["08/04/2020","T8",23],["08/04/2020","T9",24],["08/05/2020","F1",24],["08/05/2020","T1",11],["08/05/2020","T11",31],["08/05/2020","T12",24],["08/05/2020","T13",30],["08/05/2020","T19",18],["08/05/2020","T21",29],["08/05/2020","T23",32],["08/05/2020","T4",24],["08/05/2020","T5",18],["08/05/2020","T6",18],["08/05/2020","T7",24],["08/05/2020","T8",24],["08/05/2020","T9",32],["08/06/2020","F1",32],["08/06/2020","T1",25],["08/06/2020","T11",23],["08/06/2020","T12",40],["08/06/2020","T13",28],["08/06/2020","T16",22],["08/06/2020","T19",26],["08/06/2020","T2",8],["08/06/2020","T21",28],["08/06/2020","T22",6],["08/06/2020","T23",36],["08/06/2020","T25",8],["08/06/2020","T27",32],["08/06/2020","T4",16],["08/06/2020","T5",26],["08/06/2020","T6",18],["08/06/2020","T8",24],["08/06/2020","T9",23],["08/07/2020","F1",32],["08/07/2020","T1",17],["08/07/2020","T11",21],["08/07/2020","T12",24],["08/07/2020","T13",18],["08/07/2020","T19",26],["08/07/2020","T21",21],["08/07/2020","T23",22],["08/07/2020","T25",32],["08/07/2020","T27",24],["08/07/2020","T3",14],["08/07/2020","T4",16],["08/07/2020","T5",34],["08/07/2020","T6",21],["08/07/2020","T7",20],["08/07/2020","T8",32],["08/07/2020","T9",29],["08/08/2020","F1",24],["08/08/2020","T1",24],["08/08/2020","T10",0],["08/08/2020","T11",21],["08/08/2020","T12",32],["08/08/2020","T13",26],["08/08/2020","T19",18],["08/08/2020","T21",21],["08/08/2020","T22",14],["08/08/2020","T23",21],["08/08/2020","T25",22],["08/08/2020","T27",24],["08/08/2020","T3",12],["08/08/2020","T4",0],["08/08/2020","T5",26],["08/08/2020","T6",15],["08/08/2020","T7",8],["08/08/2020","T8",22],["08/08/2020","T9",13],["08/09/2020","F1",24],["08/09/2020","T1",23],["08/09/2020","T11",24],["08/09/2020","T12",16],["08/09/2020","T13",28],["08/09/2020","T14",8],["08/09/2020","T19",18],["08/09/2020","T21",36],["08/09/2020","T22",20],["08/09/2020","T23",21],["08/09/2020","T25",21],["08/09/2020","T27",24],["08/09/2020","T3",0],["08/09/2020","T4",21],["08/09/2020","T5",18],["08/09/2020","T6",15],["08/09/2020","T7",0],["08/09/2020","T8",18],["08/09/2020","T9",10],["08/10/2020","F1",24],["08/10/2020","T1",17],["08/10/2020","T11",12],["08/10/2020","T12",32],["08/10/2020","T13",17],["08/10/2020","T14",8],["08/10/2020","T19",17],["08/10/2020","T2",23],["08/10/2020","T21",19],["08/10/2020","T22",18],["08/10/2020","T23",32],["08/10/2020","T25",24],["08/10/2020","T26",24],["08/10/2020","T27",24],["08/10/2020","T3",0],["08/10/2020","T4",15],["08/10/2020","T5",18],["08/10/2020","T6",15],["08/10/2020","T7",12],["08/10/2020","T8",14],["08/10/2020","T9",24],["08/11/2020","F1",29],["08/11/2020","T1",18],["08/11/2020","T11",0],["08/11/2020","T12",16],["08/11/2020","T13",23],["08/11/2020","T14",24],["08/11/2020","T19",20],["08/11/2020","T2",24],["08/11/2020","T21",28],["08/11/2020","T22",18],["08/11/2020","T23",40],["08/11/2020","T25",8],["08/11/2020","T26",21],["08/11/2020","T27",24],["08/11/2020","T3",0],["08/11/2020","T4",7],["08/11/2020","T5",20],["08/11/2020","T6",19],["08/11/2020","T7",29],["08/11/2020","T8",16],["08/11/2020","T9",24],["08/12/2020","F1",7],["08/12/2020","T1",18],["08/12/2020","T10",6],["08/12/2020","T11",8],["08/12/2020","T12",21],["08/12/2020","T13",21],["08/12/2020","T14",24],["08/12/2020","T15",14],["08/12/2020","T16",16],["08/12/2020","T19",24],["08/12/2020","T2",24],["08/12/2020","T21",21],["08/12/2020","T22",18],["08/12/2020","T23",32],["08/12/2020","T26",7],["08/12/2020","T27",24],["08/12/2020","T3",0],["08/12/2020","T5",12],["08/12/2020","T6",20],["08/12/2020","T7",24],["08/12/2020","T8",14],["08/12/2020","T9",31],["08/13/2020","F1",20],["08/13/2020","T1",18],["08/13/2020","T10",18],["08/13/2020","T11",22],["08/13/2020","T12",28],["08/13/2020","T13",23],["08/13/2020","T14",24],["08/13/2020","T15",28],["08/13/2020","T16",0],["08/13/2020","T19",20],["08/13/2020","T2",32],["08/13/2020","T21",15],["08/13/2020","T22",20],["08/13/2020","T23",22],["08/13/2020","T25",16],["08/13/2020","T27",24],["08/13/2020","T3",16],["08/13/2020","T4",0],["08/13/2020","T5",18],["08/13/2020","T6",18],["08/13/2020","T8",24],["08/13/2020","T9",31],["08/14/2020","F1",24],["08/14/2020","T1",18],["08/14/2020","T10",14],["08/14/2020","T11",32],["08/14/2020","T12",19],["08/14/2020","T13",24],["08/14/2020","T14",24],["08/14/2020","T15",22],["08/14/2020","T16",0],["08/14/2020","T19",20],["08/14/2020","T2",24],["08/14/2020","T21",21],["08/14/2020","T22",18],["08/14/2020","T23",24],["08/14/2020","T25",24],["08/14/2020","T27",32],["08/14/2020","T3",22],["08/14/2020","T5",18],["08/14/2020","T6",24],["08/14/2020","T7",8],["08/14/2020","T8",8],["08/14/2020","T9",32],["08/15/2020","F1",24],["08/15/2020","T1",18],["08/15/2020","T10",20],["08/15/2020","T11",23],["08/15/2020","T12",20],["08/15/2020","T13",7],["08/15/2020","T14",30],["08/15/2020","T15",14],["08/15/2020","T19",15],["08/15/2020","T2",24],["08/15/2020","T21",14],["08/15/2020","T22",18],["08/15/2020","T23",24],["08/15/2020","T25",24],["08/15/2020","T27",24],["08/15/2020","T3",18],["08/15/2020","T4",0],["08/15/2020","T5",14],["08/15/2020","T6",18],["08/15/2020","T8",16],["08/15/2020","T9",32],["08/16/2020","F1",18],["08/16/2020","T1",18],["08/16/2020","T10",16],["08/16/2020","T11",24],["08/16/2020","T12",21],["08/16/2020","T14",28],["08/16/2020","T15",28],["08/16/2020","T19",15],["08/16/2020","T2",13],["08/16/2020","T22",18],["08/16/2020","T23",32],["08/16/2020","T25",24],["08/16/2020","T27",8],["08/16/2020","T3",14],["08/16/2020","T4",0],["08/16/2020","T5",20],["08/16/2020","T6",0],["08/16/2020","T8",8],["08/16/2020","T9",24],["08/17/2020","F1",18],["08/17/2020","T1",24],["08/17/2020","T10",8],["08/17/2020","T11",32],["08/17/2020","T12",28],["08/17/2020","T13",22],["08/17/2020","T14",20],["08/17/2020","T15",23],["08/17/2020","T19",15],["08/17/2020","T2",8],["08/17/2020","T22",18],["08/17/2020","T23",24],["08/17/2020","T25",24],["08/17/2020","T27",24],["08/17/2020","T3",0],["08/17/2020","T5",23],["08/17/2020","T6",12],["08/17/2020","T8",32],["08/17/2020","T9",24],["08/18/2020","F1",12],["08/18/2020","T1",21],["08/18/2020","T11",32],["08/18/2020","T12",21],["08/18/2020","T13",24],["08/18/2020","T14",32],["08/18/2020","T15",22],["08/18/2020","T19",15],["08/18/2020","T2",11],["08/18/2020","T21",21],["08/18/2020","T22",26],["08/18/2020","T23",24],["08/18/2020","T25",21],["08/18/2020","T27",24],["08/18/2020","T3",0],["08/18/2020","T5",31],["08/18/2020","T6",24],["08/18/2020","T8",45],["08/18/2020","T9",16],["08/19/2020","T1",15],["08/19/2020","T11",24],["08/19/2020","T12",28],["08/19/2020","T13",24],["08/19/2020","T14",24],["08/19/2020","T15",23],["08/19/2020","T16",16],["08/19/2020","T19",15],["08/19/2020","T2",5],["08/19/2020","T21",21],["08/19/2020","T22",18],["08/19/2020","T23",24],["08/19/2020","T25",15],["08/19/2020","T27",32],["08/19/2020","T3",0],["08/19/2020","T5",7],["08/19/2020","T6",24],["08/19/2020","T8",23],["08/19/2020","T9",24],["08/20/2020","T1",20],["08/20/2020","T10",24],["08/20/2020","T11",24],["08/20/2020","T12",21],["08/20/2020","T13",24],["08/20/2020","T14",32],["08/20/2020","T15",24],["08/20/2020","T16",24],["08/20/2020","T19",18],["08/20/2020","T21",29],["08/20/2020","T22",12],["08/20/2020","T23",24],["08/20/2020","T25",8],["08/20/2020","T27",24],["08/20/2020","T3",0],["08/20/2020","T5",16],["08/20/2020","T6",17],["08/20/2020","T8",30],["08/20/2020","T9",16],["08/21/2020","T1",18],["08/21/2020","T10",24],["08/21/2020","T11",31],["08/21/2020","T12",28],["08/21/2020","T13",32],["08/21/2020","T14",24],["08/21/2020","T15",16],["08/21/2020","T16",24],["08/21/2020","T19",18],["08/21/2020","T21",21],["08/21/2020","T22",20],["08/21/2020","T23",24],["08/21/2020","T25",14],["08/21/2020","T27",24],["08/21/2020","T3",0],["08/21/2020","T5",24],["08/21/2020","T6",15],["08/21/2020","T8",23],["08/21/2020","T9",32],["08/22/2020","T1",18],["08/22/2020","T10",24],["08/22/2020","T11",38],["08/22/2020","T12",21],["08/22/2020","T13",21],["08/22/2020","T14",39],["08/22/2020","T15",24],["08/22/2020","T16",24],["08/22/2020","T19",20],["08/22/2020","T21",27],["08/22/2020","T22",18],["08/22/2020","T23",32],["08/22/2020","T25",23],["08/22/2020","T27",32],["08/22/2020","T3",0],["08/22/2020","T5",32],["08/22/2020","T6",18],["08/22/2020","T8",32],["08/22/2020","T9",24],["08/23/2020","T1",24],["08/23/2020","T10",13],["08/23/2020","T11",15],["08/23/2020","T12",28],["08/23/2020","T13",21],["08/23/2020","T14",15],["08/23/2020","T15",32],["08/23/2020","T16",24],["08/23/2020","T19",14],["08/23/2020","T21",18],["08/23/2020","T22",18],["08/23/2020","T23",32],["08/23/2020","T25",7],["08/23/2020","T27",24],["08/23/2020","T5",24],["08/23/2020","T6",5],["08/23/2020","T8",20],["08/23/2020","T9",22],["08/24/2020","F1",8],["08/24/2020","T1",18],["08/24/2020","T10",24],["08/24/2020","T11",8],["08/24/2020","T12",23],["08/24/2020","T13",21],["08/24/2020","T14",0],["08/24/2020","T15",16],["08/24/2020","T16",32],["08/24/2020","T19",15],["08/24/2020","T21",20],["08/24/2020","T22",18],["08/24/2020","T23",32],["08/24/2020","T25",15],["08/24/2020","T27",24],["08/24/2020","T5",24],["08/24/2020","T6",28],["08/24/2020","T8",19],["08/24/2020","T9",24],["08/25/2020","T1",18],["08/25/2020","T10",24],["08/25/2020","T11",24],["08/25/2020","T12",24],["08/25/2020","T13",24],["08/25/2020","T14",0],["08/25/2020","T15",32],["08/25/2020","T16",32],["08/25/2020","T19",15],["08/25/2020","T21",21],["08/25/2020","T22",20],["08/25/2020","T23",40],["08/25/2020","T25",21],["08/25/2020","T27",32],["08/25/2020","T5",24],["08/25/2020","T6",13],["08/25/2020","T8",23],["08/25/2020","T9",48],["08/26/2020","F1",24],["08/26/2020","T1",18],["08/26/2020","T10",23],["08/26/2020","T11",24],["08/26/2020","T12",32],["08/26/2020","T13",24],["08/26/2020","T14",0],["08/26/2020","T15",32],["08/26/2020","T16",24],["08/26/2020","T19",20],["08/26/2020","T21",0],["08/26/2020","T22",11],["08/26/2020","T23",40],["08/26/2020","T25",21],["08/26/2020","T27",24],["08/26/2020","T5",32],["08/26/2020","T6",14],["08/26/2020","T9",23],["08/27/2020","F1",24],["08/27/2020","T1",31],["08/27/2020","T10",16],["08/27/2020","T11",22],["08/27/2020","T12",32],["08/27/2020","T13",28],["08/27/2020","T14",32],["08/27/2020","T15",32],["08/27/2020","T16",32],["08/27/2020","T19",15],["08/27/2020","T21",22],["08/27/2020","T22",0],["08/27/2020","T23",24],["08/27/2020","T25",7],["08/27/2020","T27",16],["08/27/2020","T5",24],["08/27/2020","T6",14],["08/27/2020","T8",24],["08/27/2020","T9",24],["08/28/2020","F1",23],["08/28/2020","T1",24],["08/28/2020","T10",16],["08/28/2020","T11",21],["08/28/2020","T12",32],["08/28/2020","T13",18],["08/28/2020","T14",24],["08/28/2020","T15",32],["08/28/2020","T16",24],["08/28/2020","T19",10],["08/28/2020","T21",27],["08/28/2020","T22",0],["08/28/2020","T23",24],["08/28/2020","T24",24],["08/28/2020","T25",16],["08/28/2020","T27",8],["08/28/2020","T5",32],["08/28/2020","T6",28],["08/28/2020","T7",24],["08/28/2020","T8",24],["08/28/2020","T9",32],["08/29/2020","F1",24],["08/29/2020","T1",24],["08/29/2020","T10",16],["08/29/2020","T11",28],["08/29/2020","T12",24],["08/29/2020","T13",14],["08/29/2020","T14",0],["08/29/2020","T15",38],["08/29/2020","T16",14],["08/29/2020","T19",15],["08/29/2020","T21",20],["08/29/2020","T22",0],["08/29/2020","T23",24],["08/29/2020","T24",32],["08/29/2020","T25",24],["08/29/2020","T27",16],["08/29/2020","T5",32],["08/29/2020","T6",21],["08/29/2020","T7",32],["08/29/2020","T8",24],["08/29/2020","T9",24],["08/30/2020","F1",32],["08/30/2020","T1",16],["08/30/2020","T10",23],["08/30/2020","T11",21],["08/30/2020","T12",24],["08/30/2020","T13",7],["08/30/2020","T14",0],["08/30/2020","T15",24],["08/30/2020","T16",24],["08/30/2020","T19",29],["08/30/2020","T21",21],["08/30/2020","T23",24],["08/30/2020","T24",32],["08/30/2020","T25",32],["08/30/2020","T27",16],["08/30/2020","T5",24],["08/30/2020","T6",7],["08/30/2020","T7",24],["08/30/2020","T8",24],["08/30/2020","T9",21],["08/31/2020","F1",24],["08/31/2020","T1",24],["08/31/2020","T10",27],["08/31/2020","T11",30],["08/31/2020","T12",32],["08/31/2020","T13",24],["08/31/2020","T14",0],["08/31/2020","T15",30],["08/31/2020","T16",24],["08/31/2020","T19",14],["08/31/2020","T21",21],["08/31/2020","T23",40],["08/31/2020","T24",16],["08/31/2020","T25",32],["08/31/2020","T27",24],["08/31/2020","T28",16],["08/31/2020","T5",24],["08/31/2020","T6",0],["08/31/2020","T8",24],["08/31/2020","T9",7]]});
  });

  function drawMultiLineChart(r) {
    // data table
    var data = google.visualization.arrayToDataTable(r.d);

    // data view
    var view = new google.visualization.DataView(data);

    // init column arrays
    var aggColumns = [];
    var viewColumns = [0];

    // build view & agg columns for each line
    data.getDistinctValues(1).forEach(function (line, index) {
      // add view column for each line
      viewColumns.push({
        calc: function (dt, row) {
          if (dt.getValue(row, 1) === line) {
            return dt.getValue(row, 2);
          }
          return null;
        },
        label: line,
        type: "number"
      });

      // add agg column
      aggColumns.push({
        aggregation: google.visualization.data.sum,
        column: index + 1,
        label: line,
        type: "number"
      });
    });

    // set view columns
    view.setColumns(viewColumns);

    // agg view by line
    var aggData = google.visualization.data.group(
      view,
      [0],
      aggColumns
    );

    // draw chart
    var chart = new google.visualization.LineChart($("#divMultiLineChart").get(0));
    chart.draw(aggData, options);
  }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="divMultiLineChart"></div>