C# Interop add JSON values to worksheet

Question

How can I get all values from a JSON object and then display them to the Worksheet at once?

This is how my JSON object looks:

{"report_suites": [
  {
    "rsid": "op-api-docs",
    "site_title": "API Docs Portal"
  },
  {
    "rsid": "op-bigideas",
    "site_title": "Big Ideas"
  },
  {
    "rsid": "op-education",
    "site_title": "Education"
  },
  {
    "rsid": "op-education-dev",
    "site_title": "Education - Dev"
  },
  {
    "rsid": "op-global",
    "site_title": "Global"
  },
  {
    "rsid": "op-global-dev",
    "site_title": "Global - Dev"
  }
]}

Expected result:

This is how I tried to get this done, but it returns the following error:

The best overloaded method match for 'System.Collections.Generic.List.AddRange(System.Collections.Generic.IEnumerable)' has some invalid arguments

dynamic response = JsonConvert.DeserializeObject(responseXml);
var just_rs = response.report_suites;
var report_title = new List<string>();
var report_id = new List<int>();
foreach(var item in just_rs) {
    report_title.AddRange(item.site_title);
    report_id.AddRange(item.rsid);
}
rng = (Excel.Range)ws.Range[ws.Cells[2, 1], ws.Cells[report_id.Length, 1]];
rng.Value = report_id;
rng = (Excel.Range)ws.Range[ws.Cells[2, 2], ws.Cells[report_title.Length, 2]];
rng.Value = report_title;

Where response is my JSON object mentioned above.

If I use Add instead of AddRange I get the following error:

The best overloaded method match for 'System.Collections.Generic.List.Add(string)' has some invalid arguments

score 1 · Accepted Answer · answered Mar 10 '17 at 15:20

1

item.site_title is not a string, yet you are trying to add it to a collection of string

If I recall correctly JsonConvert to a dynamic results in JArray/JObject/JToken, so you might want to look at determining what type item.site_title is and looking at its appropriate method to extract the value to a string.

As for using the AddRange() method, are you sure this is what you want to use? You are inside of the loop trying to add a range of a single element. I think you were looking to use Add() instead. After determining the type and converting to string use the Add() method.

answered Mar 10 '17 at 15:20

maccettura

10,514
3
28
35

Managed to add the data to the list as follows: `report_title.AddRange(item.site_title.ToString());`, but now it fails when trying to set the data in the worksheet, returning `Exception from HRESULT: 0x800A03EC` on `rng.Value = report_id;` ... any ideas why? – Valip Mar 10 '17 at 15:34
Try using a [non-zero based index](http://stackoverflow.com/questions/12714626/exception-from-hresult-0x800a03ec-error) – maccettura Mar 10 '17 at 15:35
Fixed it with this: `rng.Value = ws.Parent.Parent.Transpose(report_title.AsEnumerable().ToArray());` – Valip Mar 10 '17 at 15:40
As a side note, if these are values you wish to manipulate in code you may want to create a class that represents the data so you can easily serialize/deserialize – maccettura Mar 10 '17 at 15:43

C# Interop add JSON values to worksheet

1 Answers1