从API请求数据并在C#中将数据集显示到Excel时如何检查NewtonJson Jarray空/空错误 [英] How to check NewtonJson Jarray null/empty error when request data from an API and display DataSet to Excel in C#
问题描述
我正在尝试显示数组或值的简单元素，下面是我的JSON字符串(来自python pandas的API)
I'm trying to display the simple element of the array or value, below is my JSON string(an API from python pandas)
{
"msg": "success",
"state": 10000,
"data": {
"data": [
{
"index": 0,
"test_1": 110,
"test_2": "000001",
"test_3": "CN",
"test_4": "Bank",
"test_5": 893,
"test_6": 229
}
],
"schema": {
"fields": [
{
"type": "integer",
"name": "index"
},
{
"type": "string",
"name": "test_1"
},
{
"type": "string",
"name": "test_2"
},
{
"type": "number",
"name": "test_3"
},
{
"type": "number",
"name": "test_4"
},
{
"type": "number",
"name": "test_5"
},
{
"type": "string",
"name": "test_6"
}
],
"pandas_version": "0.20.0",
"primaryKey": [
"index"
]
}
}
}
C#的下面的代码是我正在使用的查询，TestDataset.cs:
Below code of C# is the query that I'm using, TestDataset.cs:
using System;
using System.IO;
using System.Net;
using Newtonsoft.Json.Linq;
using ExcelDna.Integration;
using Excel = Microsoft.Office.Interop.Excel;
namespace Test_Atune
{
public class Request
{
Excel.Application ExcelApp = (Excel.Application)ExcelDnaUtil.Application;
public object GetDatasetFromUrl(string root, string path, string headval, Excel.Range excelAddress)
{
string historicalData= "{}";
var webConnection = new WebClient();
webConnection.Headers.Add("Host", headval);
try
{
historicalData = webConnection.DownloadString(root+path);
}
catch (WebException ex)
{
string error_str = ex.Message;
if (ex.Status.ToString() == "ConnectFailure" || ex.Status.ToString() == "Timeout")
{
root = Constant.setURL_ROOT_COMMON(root);
try
{
historicalData= webConnection.DownloadString(root + path);
}
catch (WebException ex2)
{
return "";
}
}
}
finally
{
webConnection.Dispose();
}
JObject jsonFeed = JObject.Parse(historicalData);
Excel.Range range = excelAddress;
JObject B = new JObject();
JArray data = (JArray)jsonFeed["data"]["data"];
JArray columns = (JArray)jsonFeed["data"]["schema"]["fields"];
int rowNum = data.Count;
int colNum = columns.Count;
Excel.Range range_head = ExcelApp.Cells[range.Row + 1, range.Column];
range_head = range_head.get_Resize(1, colNum);
Excel.Range range_data = ExcelApp.Cells[range.Row + 2, range.Column];
range_data = range_data.get_Resize(rowNum, colNum);
// write header
object[,] headerData = new object[1, colNum];
for (int iCol = 0; iCol < colNum; iCol++)
{
headerData[0, iCol] = columns[iCol]["name"];
}
range_head.Value2 = headerData;
// write data
object[,] cellData = new object[rowNum, colNum];
int iRow = 0;
foreach (JObject jo in data)
{
var a = jo["test_code"];
for (int iCol = 0; iCol < colNum; iCol++)
{
if (columns[iCol]["test_1"].ToString() == "string")
{
cellData[iRow, iCol] = "'" + jo[columns[iCol]["name"].ToString()];
}
else
{
cellData[iRow, iCol] = jo[columns[iCol]["name"].ToString()];
}
}
iRow += 1;
}
range_data.Value2 = cellData;
return "Total" + rowNum.ToString() + "cells";
}
}
}
下面是request.cs
using ExcelDna.Integration;
using Excel = Microsoft.Office.Interop.Excel;
namespace Test_Atune
{
public class Dataset
{
public static string baseurl = Constant.URL_ROOT_COMMON;
public static string headval = Constant.HEADVAL_COMMON;
public static Request request = new Request();
[ExcelFunction(Category = "test", IsMacroType = true, Description = "test dataset")]
public static object TEST_DATASET(
[ExcelArgument(Description = "test_code")] string test_1,
[ExcelArgument(Description = "YYYYMMDD")] string test_2,
[ExcelArgument(Description = "YYYYMMDD")] string test_3
)
{
string parstr = @"/test_dataset/?" +
@"test_1=" + test_1 +
@"&test_2=" + test_2 +
@"&test_3=" + test_3;
ExcelReference caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
Excel.Range rg = caller.ToPiaRange();
return ExcelAsyncUtil.Run("TEST_DATASET",
new object[] { parstr },
() => request.GetDatasetFromUrl(Constant.URL_ROOT_COMMON, parstr, headval, rg));
}
}
}
我在JArray columns = (JArray)jsonFeed["data"]["schema"]["fields"];
System.NullReferenceException HResult=0x80004003 message=Object reference not set to an instance of an object.
我尝试调试，但得到了以下结果，
Name: historicalData, Value:"{}"; Name: jsonFeed,Vaule:Null; Name:B,Vaule:{{}}, Name:data, Value:"null"
I tried to debug and I got below results,
Name: historicalData, Value:"{}"; Name: jsonFeed,Vaule:Null; Name:B,Vaule:{{}}, Name:data, Value:"null"
我对C#很陌生，是数组，域或url问题，还是其他问题?我怎样才能做到这一点?非常感谢您的任何建议.
I am very new to C#, is that an array,domain or url problem, or anything else? How can I do that? thank you so much for any advice.
推荐答案
错误:对象引用未设置为对象的实例.这意味着["data"]或["data"]["schema"]或["data"]["schema"]["fields"]下的一个或所有数据不存在.
Error: Object reference not set to an instance of an object. This means that the one or all of the data under ["data"] or ["data"]["schema"] or ["data"]["schema"]["fields"] does not exist.
我刚刚测试了您的代码，以下代码根据给定的json正常工作.
I just tested your code and the following code works just fine based on the given json.
JArray columns = (JArray)jsonFeed["data"]["schema"]["fields"];
建议:使用调试器，查看jsonFeed的值是否正确，因为那是我唯一无法复制的内容.如果您无法使用调试器，请在解析对象上使用.ToString()记录解析对象. (jsonFeed.ToString())
Suggestion: Use the debugger and see if the value of your jsonFeed is correct because thats the only thing that I cannot reproduce. If you are not able to use the debugger, log the parsed object by using .ToString() on the parsed object. (jsonFeed.ToString())
注意:您的json出现问题.假定"test_3": CN,为"test_3": "CN",，并在CN上加上引号.
NOTE: There is an issue with your json. "test_3": CN, is suposed to be "test_3": "CN", with quotes on CN.
这篇关于从API请求数据并在C#中将数据集显示到Excel时如何检查NewtonJson Jarray空/空错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
