Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- I'm trying to display the simple element of the array or value, below is my JSON string(an API from python pandas)
- ```JSON
- {
- "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"
- ]
- }
- }
- }
- ```
- Below code of `C#` is the query that I'm using, `TestDataset.cs`:
- ```C#
- 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")
- {
- domain = Constant.setURL_ROOT_COMMON(domain);
- 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["fnd_cde"];
- for (int iCol = 0; iCol < colNum; iCol++)
- {
- if (columns[iCol]["type"].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";
- }
- }
- }
- ```
- And below is the code of `request.cs`
- ```C#
- 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));
- }
- }
- }
- ```
- And I got the following error on `JArray columns = (JArray)jsonFeed["data"]["schema"]["fields"];`
- ```error
- System.NullReferenceException HResult=0x80004003 message=Object reference not set to an instance of an object.
- ```
- I tried to debug and I got below results,
- ```Name: historicalData, Value:"{}"; Name: jsonFeed,Vaule:Null; Name:B,Vaule:{{}}, Name:data, Value:"null"```
- 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.
Advertisement
Add Comment
Please, Sign In to add comment