在谷歌图表上取消显示空值

Question

我试图不显示空值， 我正在使用网络方法将值获取为JSON， 您将看到以下代码： 我正在尝试这种方法，但它对我不起作用，我不知道为什么 错误是：

All series on a given axis must be of the same data type

如果有人知道问题在哪里，请帮助我，谢谢:) 这是我的代码：

google.charts.load('current', {packages: ['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var options = {
title: 'Reparation par RSP',
width: '100%',
height: 500,
bar: { groupWidth: "75%" },
seriesType: 'bars',
series: { 5: { type: 'line' } },
colors: ['#dacfcf','#fee812','#ff7900','#ff0000','#ff0000'],
legend: 'right',
curveType: 'function',
isStacked: true,
hAxis: { format: '###' },
titleTextStyle: {
fontSize: 32,
},
};
$.ajax({
type: "POST",
url: "ReparationParRSP.aspx/GetChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = google.visualization.arrayToDataTable(r.d);
var view = new google.visualization.DataView(data);
view.setColumns([0,
1, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 1);}, type: "string", role: "annotation" ,sourceColumn: 1},
2, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 2);}, type: "string", role: "annotation" ,sourceColumn: 2},
3, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 3);}, type: "string", role: "annotation" ,sourceColumn: 3},
4, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 4);}, type: "string", role: "annotation" ,sourceColumn: 4},
5, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 5);}, type: "string", role: "annotation" ,sourceColumn: 5}
]);
function getAnnotation(dataTable, rowIndex, columnIndex) {
return dataTable.getFormattedValue(rowIndex, columnIndex) || null;
}
var chart = new google.visualization.BarChart($("#chart_div")[0]);
chart.draw(view, options);
// updateChart();
},
});

我正在使用网络方法将值获取为JSON， 这是我获取数据的地方：

[WebMethod]
public static List<object> GetChartData()
{
string query = "select * from tblrespo";
List<object> chartData = new List<object>();
chartData.Add(new object[]
{
"Nome","0_2","2_4","4_8","8_24","+24"
});
using (SqlConnection con = new SqlConnection("Data Source=DESKTOP-BCMH2U1\SQLEXPRESS;Initial Catalog=DB_B2B;Integrated Security=True"))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
var rsp = sdr[0];
if (rsp.ToString()=="")
{
rsp = "aucune info";
}
chartData.Add(new object[]
{
rsp, sdr[1],sdr[2],sdr[3],sdr[4],sdr[5]
});
}
}
con.Close();
return chartData;
}
}
}

Answer 1

问题来自使用arrayToDataTable创建数据表。
使用arrayToDataTable为数据表创建列时，
该方法会查看数据以确定应创建哪种类型的列。
如果列中的所有值均为 null，则不知道它应该是什么类型的列，

默认情况下创建'string'列。

当您将一列作为字符串时，数字列应该是其中，
这会导致错误...

All series on a given axis must be of the same data type

不要使用arrayToDataTable，使用以下格式，
并直接创建数据表。
这样，空列将创建为数字，而不是字符串。

var data = new google.visualization.DataTable({
"cols": [
{"label": "Month", "type": "string"},
{"label": "Y0", "type": "number"},
{"label": "Y1", "type": "number"},
{"label": "Y2", "type": "number"},
{"label": "Y3", "type": "number"},
{"label": "Y4", "type": "number"},
{"label": "Y5", "type": "number"}
],
"rows": [
{"c":[{"v":"FEB"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]},
{"c":[{"v":"JAN"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]},
{"c":[{"v":"DEC"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]},
{"c":[{"v":"NOV"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]}
]
});

---

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

google.charts.load('current', {packages: ['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var options = {
title: 'Reparation par RSP',
width: '100%',
height: 500,
bar: { groupWidth: "75%" },
seriesType: 'bars',
series: { 5: { type: 'line' } },
colors: ['#dacfcf','#fee812','#ff7900','#ff0000','#ff0000'],
legend: 'right',
curveType: 'function',
isStacked: true,
hAxis: { format: '###' },
titleTextStyle: {
fontSize: 32,
},
};
var data = new google.visualization.DataTable({
"cols": [
{"label": "Month", "type": "string"},
{"label": "Y0", "type": "number"},
{"label": "Y1", "type": "number"},
{"label": "Y2", "type": "number"},
{"label": "Y3", "type": "number"},
{"label": "Y4", "type": "number"},
{"label": "Y5", "type": "number"}
],
"rows": [
{"c":[{"v":"FEB"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]},
{"c":[{"v":"JAN"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]},
{"c":[{"v":"DEC"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]},
{"c":[{"v":"NOV"},{"v":null},{"v":541438.55},{"v":442690.4},{"v":394919.81},{"v":497903.68},{"v":198755.42}]}
]
});
var view = new google.visualization.DataView(data);
view.setColumns([0,
1, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 1);}, type: "string", role: "annotation" ,sourceColumn: 1},
2, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 2);}, type: "string", role: "annotation" ,sourceColumn: 2},
3, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 3);}, type: "string", role: "annotation" ,sourceColumn: 3},
4, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 4);}, type: "string", role: "annotation" ,sourceColumn: 4},
5, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 5);}, type: "string", role: "annotation" ,sourceColumn: 5}
]);
function getAnnotation(dataTable, rowIndex, columnIndex) {
return dataTable.getFormattedValue(rowIndex, columnIndex) || null;
}
var chart = new google.visualization.BarChart($("#chart_div")[0]);
chart.draw(view, 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="chart_div"></div>

---

在您的情况下，它如下所示。
只需确保 JSON 中包含cols和rows的键，如上所示...

$.ajax({
type: "POST",
url: "ReparationParRSP.aspx/GetChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
// change this line
var data = new google.visualization.DataTable(r.d);
var view = new google.visualization.DataView(data);
view.setColumns([0,
1, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 1);}, type: "string", role: "annotation" ,sourceColumn: 1},
2, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 2);}, type: "string", role: "annotation" ,sourceColumn: 2},
3, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 3);}, type: "string", role: "annotation" ,sourceColumn: 3},
4, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 4);}, type: "string", role: "annotation" ,sourceColumn: 4},
5, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 5);}, type: "string", role: "annotation" ,sourceColumn: 5}
]);
function getAnnotation(dataTable, rowIndex, columnIndex) {
return dataTable.getFormattedValue(rowIndex, columnIndex) || null;
}
var chart = new google.visualization.BarChart($("#chart_div")[0]);
chart.draw(view, options);
},
});

---

编辑

我认为您获得的数据看起来是json...

[
["Nome","0_2","2_4","4_8","8_24","+24"],
["FEB",null,541438.55,442690.4,394919.81,497903.68],
["JAN",null,541438.55,442690.4,394919.81,497903.68],
["DEC",null,541438.55,442690.4,394919.81,497903.68],
["NOV",null,541438.55,442690.4,394919.81,497903.68]
]

因此，请从服务器代码中删除列标题，删除此行...

chartData.Add(new object[]
{
"Nome","0_2","2_4","4_8","8_24","+24"
});

然后在 JavaScript 中添加客户端上的列标题。
并使用addRows方法，如下所示...

google.charts.load('current', {packages: ['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var options = {
title: 'Reparation par RSP',
width: '100%',
height: 500,
bar: { groupWidth: "75%" },
seriesType: 'bars',
series: { 5: { type: 'line' } },
colors: ['#dacfcf','#fee812','#ff7900','#ff0000','#ff0000'],
legend: 'right',
curveType: 'function',
isStacked: true,
hAxis: { format: '###' },
titleTextStyle: {
fontSize: 32,
},
};
$.ajax({
type: "POST",
url: "ReparationParRSP.aspx/GetChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = new google.visualization.DataTable({
"cols": [
{"label": "Nome", "type": "string"},
{"label": "0_2", "type": "number"},
{"label": "2_4", "type": "number"},
{"label": "4_8", "type": "number"},
{"label": "8_24", "type": "number"},
{"label": "+24", "type": "number"},
{"label": "Y5", "type": "number"}
]
});
data.addRows(r.d);
var view = new google.visualization.DataView(data);
view.setColumns([0,
1, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 1);}, type: "string", role: "annotation" ,sourceColumn: 1},
2, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 2);}, type: "string", role: "annotation" ,sourceColumn: 2},
3, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 3);}, type: "string", role: "annotation" ,sourceColumn: 3},
4, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 4);}, type: "string", role: "annotation" ,sourceColumn: 4},
5, {calc: function (dataTable, rowIndex) {return getAnnotation(dataTable, rowIndex, 5);}, type: "string", role: "annotation" ,sourceColumn: 5}
]);
function getAnnotation(dataTable, rowIndex, columnIndex) {
return dataTable.getFormattedValue(rowIndex, columnIndex) || null;
}
var chart = new google.visualization.BarChart($("#chart_div")[0]);
chart.draw(view, options);
},
});
}

Answer 2

试试这个

#Panel1 {
position: fixed;
top: 0;
/* and any other style, such as `right`, `left`, `width` and etc. */
}