i need produce same output produce in xml using json
for tabular data in excel worksheet, data in format:
column1 column2 column3 aaa bbb ccc xxx yyy zzz kkk jjj nnn i need write json file using json writer because can't create class produce data, have use created json deserializing class in different application. able deserialize class in consumer application need have class can name myclass containing collection of items, each item representing row, headers, column1, column2, column3 names of properties.
i've been able produce this:
{ "item": { "column1": "aaa", "column2": "bbb", "column3": "ccc", }, "item": { "column1": "xxx", "column2": "yyy", "column3": "zzz", }, } unfortunately not object containing collection of item not deserialize.
this code use manual serialization excel file, not able find how script start , end of collection:
stringbuilder sb = new stringbuilder(); stringwriter sw = new stringwriter(sb); jsonwriter jsonwriter = null; jsonwriter = new jsontextwriter(sw); jsonwriter.formatting = newtonsoft.json.formatting.indented; jsonwriter.writestartobject(); int countawait = 0; // here miss write open collection (int row = firstrow; row <= end.row; row++) { count++; countawait++; if (countawait >= 10) { resulttext = "reading record " + count; countawait = 0; } jsonwriter.writepropertyname(rowelement); jsonwriter.writestartobject(); (int col = start.column; col <= end.column; col++) { jsonwriter.writepropertyname(fieldnames[col]); jsonwriter.writevalue(getcellstringvalue(ws, row, col)); } jsonwriter.writeendobject(); } // here need write closing of collection jsonwriter.writeendobject(); jsonwriter.close(); edited add how json serializer serializes sample of destination class:
{ "items": [ { "column1": "xxx", "column2": "yyy", "column3": "zzz", }, { "column1": "aaa", "column2": "bbb", "column3": "ccc", } ] } the class myclass , contains collection items of classes of type item.
you should write property name "items" once, value use jsonwriter.writestartarray() , jsonwriter.writeendarray() start , end json array, write each row object nested in array:
var sb = new stringbuilder(); using (var sw = new stringwriter(sb)) using (var jsonwriter = new jsontextwriter(sw)) { var countawait = 0; jsonwriter.formatting = newtonsoft.json.formatting.indented; jsonwriter.writestartobject(); // write opening of root object jsonwriter.writepropertyname(rowelement); // write "items" property name jsonwriter.writestartarray(); // write opening of "items" array (int row = firstrow; row <= end.row; row++) { count++; countawait++; if (countawait >= 10) { resulttext = "reading record " + count; countawait = 0; } jsonwriter.writestartobject(); // write beginning of entry in "items" array (int col = start.column; col <= end.column; col++) { jsonwriter.writepropertyname(fieldnames[col]); jsonwriter.writevalue(getcellstringvalue(ws, row, col)); } jsonwriter.writeendobject(); // write ending of entry in "items" array } jsonwriter.writeendarray(); // write closing of "items" array. jsonwriter.writeendobject(); // write closing of root object // no need close explicitly when inside using statement } (here assuming rowelement corresponds "items" string.)
