0

I was wondering if anyone knows how to parse JSON. I have an EJ2 Spreadsheet control. When "update" is clicked, the data from the Spreadsheet control is passed into the Controller via JSON. I need to traverse the JSON in order to get to the "rows", then get to the values in each "Cell". I attached the JSON I am trying to traverse.

{"jsonObject":{"Workbook":{"allowOpen":false,"allowSave":true,"saveUrl":"https://ej2services.syncfusion.com/development/web-services/api/spreadsheet/save","enableRtl":false,"locale":"en-US","enablePersistence":false,"cellStyle":{"fontFamily":"Calibri","verticalAlign":"bottom","textIndent":"0pt","backgroundColor":"#ffffff","color":"#000000","textAlign":"left","fontSize":"11pt","fontWeight":"normal","fontStyle":"normal","textDecoration":"none","border":"","borderLeft":"","borderTop":"","borderRight":"","borderBottom":""},"showRibbon":true,"showFormulaBar":true,"showSheetTabs":true,"allowEditing":true,"enableContextMenu":true,"allowAutoFill":true,"selectionSettings":{"mode":"Multiple"},"enableKeyboardNavigation":true,"allowNumberFormatting":true,"enableKeyboardShortcut":true,"enableClipboard":true,"allowCellFormatting":true,"allowSorting":true,"allowResizing":true,"allowHyperlink":true,"allowUndoRedo":true,"allowFiltering":true,"allowWrap":true,"allowInsert":true,"allowDelete":true,"allowDataValidation":true,"allowFindAndReplace":true,"allowMerge":true,"allowConditionalFormat":true,"allowImage":true,"allowChart":true,"activeSheetIndex":0,"cssClass":"","height":"100%","width":"100%","allowScrolling":true,"scrollSettings":{"enableVirtualization":true,"isFinite":false},"definedNames":[],"isProtected":false,"password":"","autoFillSettings":{"showFillOptions":true},"showAggregate":true,"sheets":[{"rows":[{"cells":[{"value":"Id","style":{"fontWeight":"bold"}},{"value":"Vote Center Name","style":{"fontWeight":"bold"}},{"value":"Precinct","style":{"fontWeight":"bold"}},{"value":"Ballot Style","style":{"fontWeight":"bold"}},{"value":"Ballot Quantity","style":{"fontWeight":"bold"}},{"value":"Absentee Quantity","style":{"fontWeight":"bold"}},{"value":"Registered Voters","style":{"fontWeight":"bold"}}]},{"cells":[{"value":"45503"},{"value":"St Rose 1 & 2"},{"value":"St Rose 1"},{"value":"001","style":{"textAlign":"right"}},{"value":"25"},{"value":"484"},{"value":"484"}]},{"cells":[{"value":"45504"},{"value":"St Rose 1 & 2"},{"value":"St Rose 1"},{"value":"002","style":{"textAlign":"right"}},{"value":"175"},{"value":"25"},{"value":"263"}]},{"cells":[{"value":"45505"},{"value":"St Rose 1 & 2"},{"value":"St Rose 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45506"},{"value":"St Rose 1 & 2"},{"value":"St Rose 2"},{"value":"001","style":{"textAlign":"right"}},{"value":"200"},{"value":"25"},{"value":"300"}]},{"cells":[{"value":"45507"},{"value":"St Rose 1 & 2"},{"value":"St Rose 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45508"},{"value":"Wheatfield"},{"value":"Wheatfield"},{"value":"001","style":{"textAlign":"right"}},{"value":"225"},{"value":"25"},{"value":"348"}]},{"cells":[{"value":"45509"},{"value":"Wheatfield"},{"value":"Wheatfield"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45510"},{"value":"Irishtown 1 & 2"},{"value":"Irishtown 1"},{"value":"003","style":{"textAlign":"right"}},{"value":"350"},{"value":"50"},{"value":"514"}]},{"cells":[{"value":"45511"},{"value":"Irishtown 1 & 2"},{"value":"Irishtown 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45512"},{"value":"Irishtown 1 & 2"},{"value":"Irishtown 2"},{"value":"003","style":{"textAlign":"right"}},{"value":"175"},{"value":"25"},{"value":"271"}]},{"cells":[{"value":"45513"},{"value":"Irishtown 1 & 2"},{"value":"Irishtown 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45514"},{"value":"East Fork"},{"value":"East Fork"},{"value":"004","style":{"textAlign":"right"}},{"value":"200"},{"value":"25"},{"value":"286"}]},{"cells":[{"value":"45515"},{"value":"East Fork"},{"value":"East Fork"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45516"},{"value":"Sugar Creek 1 & 5"},{"value":"Sugar Creek 1"},{"value":"002","style":{"textAlign":"right"}},{"value":"675"},{"value":"75"},{"value":"975"}]},{"cells":[{"value":"45517"},{"value":"Sugar Creek 1 & 5"},{"value":"Sugar Creek 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45524"},{"value":"Sugar Creek 1 & 5"},{"value":"Sugar Creek 5"},{"value":"002","style":{"textAlign":"right"}},{"value":"625"},{"value":"75"},{"value":"922"}]},{"cells":[{"value":"45525"},{"value":"Sugar Creek 1 & 5"},{"value":"Sugar Creek 5"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45518"},{"value":"Sugar Creek 2"},{"value":"Sugar Creek 2"},{"value":"002","style":{"textAlign":"right"}},{"value":"800"},{"value":"100"},{"value":"1171"}]},{"cells":[{"value":"45519"},{"value":"Sugar Creek 2"},{"value":"Sugar Creek 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45520"},{"value":"Sugar Creek 3 & 4"},{"value":"Sugar Creek 3"},{"value":"005","style":{"textAlign":"right"}},{"value":"575"},{"value":"75"},{"value":"832"}]},{"cells":[{"value":"45521"},{"value":"Sugar Creek 3 & 4"},{"value":"Sugar Creek 3"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45522"},{"value":"Sugar Creek 3 & 4"},{"value":"Sugar Creek 4"},{"value":"002","style":{"textAlign":"right"}},{"value":"600"},{"value":"75"},{"value":"888"}]},{"cells":[{"value":"45523"},{"value":"Sugar Creek 3 & 4"},{"value":"Sugar Creek 4"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45526"},{"value":"Breese 1 & 2"},{"value":"Breese 1"},{"value":"006","style":{"textAlign":"right"}},{"value":"625"},{"value":"75"},{"value":"915"}]},{"cells":[{"value":"45527"},{"value":"Breese 1 & 2"},{"value":"Breese 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45528"},{"value":"Breese 1 & 2"},{"value":"Breese 2"},{"value":"006","style":{"textAlign":"right"}},{"value":"600"},{"value":"75"},{"value":"859"}]},{"cells":[{"value":"45529"},{"value":"Breese 1 & 2"},{"value":"Breese 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45530"},{"value":"Breese 3 & 4"},{"value":"Breese 3"},{"value":"006","style":{"textAlign":"right"}},{"value":"925"},{"value":"125"},{"value":"1345"}]},{"cells":[{"value":"45531"},{"value":"Breese 3 & 4"},{"value":"Breese 3"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45532"},{"value":"Breese 3 & 4"},{"value":"Breese 4"},{"value":"006","style":{"textAlign":"right"}},{"value":"750"},{"value":"100"},{"value":"1094"}]},{"cells":[{"value":"45533"},{"value":"Breese 3 & 4"},{"value":"Breese 4"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45534"},{"value":"Wade 1 & 2"},{"value":"Wade 1"},{"value":"002","style":{"textAlign":"right"}},{"value":"325"},{"value":"50"},{"value":"499"}]},{"cells":[{"value":"45535"},{"value":"Wade 1 & 2"},{"value":"Wade 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45536"},{"value":"Wade 1 & 2"},{"value":"Wade 2"},{"value":"007","style":{"textAlign":"right"}},{"value":"475"},{"value":"50"},{"value":"706"}]},{"cells":[{"value":"45537"},{"value":"Wade 1 & 2"},{"value":"Wade 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45538"},{"value":"Carlyle 1 & 2"},{"value":"Carlyle 1"},{"value":"007","style":{"textAlign":"right"}},{"value":"525"},{"value":"75"},{"value":"755"}]},{"cells":[{"value":"45539"},{"value":"Carlyle 1 & 2"},{"value":"Carlyle 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45540"},{"value":"Carlyle 1 & 2"},{"value":"Carlyle 2"},{"value":"007","style":{"textAlign":"right"}},{"value":"575"},{"value":"75"},{"value":"839"}]},{"cells":[{"value":"45541"},{"value":"Carlyle 1 & 2"},{"value":"Carlyle 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45542"},{"value":"Carlyle 3 & 4"},{"value":"Carlyle 3"},{"value":"007","style":{"textAlign":"right"}},{"value":"400"},{"value":"50"},{"value":"600"}]},{"cells":[{"value":"45543"},{"value":"Carlyle 3 & 4"},{"value":"Carlyle 3"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45544"},{"value":"Carlyle 3 & 4"},{"value":"Carlyle 4"},{"value":"007","style":{"textAlign":"right"}},{"value":"375"},{"value":"50"},{"value":"561"}]},{"cells":[{"value":"45545"},{"value":"Carlyle 3 & 4"},{"value":"Carlyle 4"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45546"},{"value":"Clement"},{"value":"Clement"},{"value":"003","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"3"}]},{"cells":[{"value":"45547"},{"value":"Clement"},{"value":"Clement"},{"value":"007","style":{"textAlign":"right"}},{"value":"225"},{"value":"25"},{"value":"341"}]},{"cells":[{"value":"45548"},{"value":"Clement"},{"value":"Clement"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45549"},{"value":"Meridian"},{"value":"Meridian"},{"value":"004","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"19"}]},{"cells":[{"value":"45550"},{"value":"Meridian"},{"value":"Meridian"},{"value":"008","style":{"textAlign":"right"}},{"value":"250"},{"value":"25"},{"value":"380"}]},{"cells":[{"value":"45551"},{"value":"Meridian"},{"value":"Meridian"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45552"},{"value":"Lookingglass 1 & 2"},{"value":"Lookingglass 1"},{"value":"005","style":{"textAlign":"right"}},{"value":"700"},{"value":"100"},{"value":"1034"}]},{"cells":[{"value":"45553"},{"value":"Lookingglass 1 & 2"},{"value":"Lookingglass 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45554"},{"value":"Lookingglass 1 & 2"},{"value":"Lookingglass 2"},{"value":"005","style":{"textAlign":"right"}},{"value":"325"},{"value":"25"},{"value":"482"}]},{"cells":[{"value":"45555"},{"value":"Lookingglass 1 & 2"},{"value":"Lookingglass 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45556"},{"value":"Lookingglass 3"},{"value":"Lookingglass 3"},{"value":"005","style":{"textAlign":"right"}},{"value":"150"},{"value":"0"},{"value":"235"}]},{"cells":[{"value":"45557"},{"value":"Lookingglass 3"},{"value":"Lookingglass 3"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45558"},{"value":"Lookingglass 4"},{"value":"Lookingglass 4"},{"value":"005","style":{"textAlign":"right"}},{"value":"675"},{"value":"75"},{"value":"977"}]},{"cells":[{"value":"45559"},{"value":"Lookingglass 4"},{"value":"Lookingglass 4"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45560"},{"value":"Lookingglass 5"},{"value":"Lookingglass 5"},{"value":"005","style":{"textAlign":"right"}},{"value":"375"},{"value":"50"},{"value":"540"}]},{"cells":[{"value":"45561"},{"value":"Lookingglass 5"},{"value":"Lookingglass 5"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45562"},{"value":"Lookingglass 6"},{"value":"Lookingglass 6"},{"value":"005","style":{"textAlign":"right"}},{"value":"750"},{"value":"100"},{"value":"1104"}]},{"cells":[{"value":"45563"},{"value":"Lookingglass 6"},{"value":"Lookingglass 6"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45564"},{"value":"Germantown 1 & 2"},{"value":"Germantown 1"},{"value":"006","style":{"textAlign":"right"}},{"value":"650"},{"value":"75"},{"value":"951"}]},{"cells":[{"value":"45565"},{"value":"Germantown 1 & 2"},{"value":"Germantown 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45566"},{"value":"Germantown 1 & 2"},{"value":"Germantown 2"},{"value":"006","style":{"textAlign":"right"}},{"value":"400"},{"value":"50"},{"value":"604"}]},{"cells":[{"value":"45567"},{"value":"Germantown 1 & 2"},{"value":"Germantown 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45568"},{"value":"Santa Fe"},{"value":"Santa Fe"},{"value":"007","style":{"textAlign":"right"}},{"value":"600"},{"value":"75"},{"value":"877"}]},{"cells":[{"value":"45569"},{"value":"Santa Fe"},{"value":"Santa Fe"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45570"},{"value":"Lake 1 & 2"},{"value":"Lake 1"},{"value":"008","style":{"textAlign":"right"}},{"value":"350"},{"value":"50"},{"value":"535"}]},{"cells":[{"value":"45571"},{"value":"Lake 1 & 2"},{"value":"Lake 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45572"},{"value":"Lake 1 & 2"},{"value":"Lake 2"},{"value":"008","style":{"textAlign":"right"}},{"value":"75"},{"value":"0"},{"value":"135"}]},{"cells":[{"value":"45573"},{"value":"Lake 1 & 2"},{"value":"Lake 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45574"},{"value":"Brookside 1"},{"value":"Brookside 1"},{"value":"008","style":{"textAlign":"right"}},{"value":"200"},{"value":"25"},{"value":"297"}]},{"cells":[{"value":"45575"},{"value":"Brookside 1"},{"value":"Brookside 1"},{"value":"009","style":{"textAlign":"right"}},{"value":"50"},{"value":"0"},{"value":"97"}]},{"cells":[{"value":"45576"},{"value":"Brookside 1"},{"value":"Brookside 1"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45577"},{"value":"Brookside 2 & 5"},{"value":"Brookside 2"},{"value":"008","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"33"}]},{"cells":[{"value":"45578"},{"value":"Brookside 2 & 5"},{"value":"Brookside 2"},{"value":"009","style":{"textAlign":"right"}},{"value":"175"},{"value":"25"},{"value":"283"}]},{"cells":[{"value":"45579"},{"value":"Brookside 2 & 5"},{"value":"Brookside 2"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45586"},{"value":"Brookside 2 & 5"},{"value":"Brookside 5"},{"value":"008","style":{"textAlign":"right"}},{"value":"25"},{"value":"0"},{"value":"64"}]},{"cells":[{"value":"45587"},{"value":"Brookside 2 & 5"},{"value":"Brookside 5"},{"value":"009","style":{"textAlign":"right"}},{"value":"300"},{"value":"25"},{"value":"459"}]},{"cells":[{"value":"45588"},{"value":"Brookside 2 & 5"},{"value":"Brookside 5"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45580"},{"value":"Brookside 3 & 4"},{"value":"Brookside 3"},{"value":"008","style":{"textAlign":"right"}},{"value":"275"},{"value":"25"},{"value":"399"}]},{"cells":[{"value":"45581"},{"value":"Brookside 3 & 4"},{"value":"Brookside 3"},{"value":"009","style":{"textAlign":"right"}},{"value":"75"},{"value":"0"},{"value":"137"}]},{"cells":[{"value":"45582"},{"value":"Brookside 3 & 4"},{"value":"Brookside 3"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]},{"cells":[{"value":"45583"},{"value":"Brookside 3 & 4"},{"value":"Brookside 4"},{"value":"008","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"14"}]},{"cells":[{"value":"45584"},{"value":"Brookside 3 & 4"},{"value":"Brookside 4"},{"value":"009","style":{"textAlign":"right"}},{"value":"300"},{"value":"25"},{"value":"429"}]},{"cells":[{"value":"45585"},{"value":"Brookside 3 & 4"},{"value":"Brookside 4"},{"value":"F15","style":{"textAlign":"right"}},{"value":"0"},{"value":"0"},{"value":"0"}]}],"columns":[{"width":80,"hidden":true},{"width":80},{"width":82},{"width":160},{"width":110},{"width":130},{"width":130}],"name":"Sheet1","rowCount":100,"colCount":100,"topLeftCell":"B1","activeCell":"A1","selectedRange":"A1:A1","usedRange":{"rowIndex":86,"colIndex":6},"showHeaders":true,"showGridLines":true,"state":"Visible","protectSettings":{"selectCells":false},"isProtected":false,"paneTopLeftCell":"B1","frozenRows":0,"frozenColumns":0,"index":0,"id":1}]}}}
Mark
  • 43
  • 1
  • 6

1 Answers1

1

you need to deserialize the json first:

using Newtonsoft.Json;

To get only the values you need those classes and properties otherwise you can generate the classes and properties in this link Convert Json To C# Class

  public class Root1
    {
        public JsonObject jsonObject { get; set; }
    }
    public class JsonObject
    {
        public Workbook Workbook { get; set; }
    }
    public class Workbook
    {
        public List<Sheet> sheets { get; set; }
    }
    public class Sheet
    {
        public List<Row> rows { get; set; }
    }

    public class Row
    {
        public List<Cell> cells { get; set; }
    }

    public class Cell
    {
        public string value { get; set; }
    }

To make the deserialize

  private void btnConvert_Click(object sender, EventArgs e)
    {

        string fileName = "YOUR_PATH\\test.json";
        string jsonString = File.ReadAllText(fileName);
        Root1 l = JsonConvert.DeserializeObject<Root1>(jsonString);
        tbResult.Text = "l.jsonObject.Workbook.sheets[0].rows[0].cells[1].value - \n" + l.jsonObject.Workbook.sheets[0].rows[0].cells[1].value;
    }

and the result:

enter image description here

Aviv Halevy
  • 161
  • 1
  • 10