Having difficulty creating an array of Json objects for use in a Kendo grid -
i need convert server response array array of json object format.
the rows of data coming fron server needs converted in order kendo ui grid widget work properly.
i have converted schema , column definitions json format, having difficulty actual data rows.
here's raw server response
1) "data" array contains data schema
2) "rows" array contains actual values :
{"status":"success", "messages":[], "data":[{"attributes":[{"name":"bookinglocation","type":"string"},{"name":"sum(cdsstress a:usd 10y x -1.25)","type":"double"},{"name":"sum(cdsstress a:usd 10y x 1.25)","type":"double"},{"name":"sum(cdsstress a:usd 8y x 1.25)","type":"double"}], "rows":[{"id":0,"values":["london",0,0,0]},{"id":1,"values":["paris",0,0,0]},{"id":2,"values":["dubai",1564.92711931719,-171.934775655824,-54539.9112355565]},{"id":3,"values":["new york",0,0,0]},{"id":4,"values":["stockholm",0,0,0]},{"id":5,"values":["world",0,0,0]}]}]} essentially need pull "attributes" array, , match them "rows" array - , dynamically server response.
now pulling schema information "attributes" array (within "data"), can build schema json object dynamically, plug kendo grid - :
var mymodel = { // sample schema object; built dynamically in javascript "id": "id", "fields": { "bookingcountry": { "type": "string" }, "sum(cdsstress a:usd 10y x -1.25)": { "type": "number" }, "sum(cdsstress a:usd 10y x 1.25)": { "type": "number" }, "sum(cdsstress a:usd 8y x 1.25)": { "type": "number" } } } var mycolumns = $rootscope.reptwizard.coldefs; // build columns def var mydata = $rootscope.reptwizard.aggrresults; // convert data rows json object var ds = { schema: { model: mymodel }, vm.mainhiergridoptions = { datasource: ds, columns: mycolumns }; pagesize: 10, data: mydata }; { however, 'mydata' object needs :
[ { "id": 0, "bookingcountry": "london", "sum(cdsstress a:usd 10y x -1.25)": 1564.92711, "sum(cdsstress a:usd 10y x 1.25)": -171.9347756, "sum(cdsstress a:usd 8y x 1.25)": -54539.911235 }, { "id": 0, "bookingcountry": "dubai", "sum(cdsstress a:usd 10y x -1.25)": 1564.92711, "sum(cdsstress a:usd 10y x 1.25)": -171.9347756, "sum(cdsstress a:usd 8y x 1.25)": -54539.911235 } ] as can see, need iterate "values" array , create nicely-formatted json object correct field:value - i.e. "bookingcountry": "london".
here's function created format mycols var :
function builddynamiccolumndefs(fieldattribs) { // creates array of json objects; used input grid's 'columns' property var numattribs = fieldattribs.length; var mytype = ''; var mycols = []; if (numattribs > 0) { _.each(fieldattribs, function (field) { mycols.push({field: field, title: field}); }); } return mycols; } and function create mymodel var :
function builddynamicdatasourcemodel(fieldattribs) { // creates valid json object {name:value} used in kendo grid datasource var jsonexample = { "bookinglocation": { "type": "string" }, "sum(cdsstress a:usd 10y x -1.25)": { "type": "number" }, "sum(cdsstress a:usd 10y x 1.25)": { "type": "number" }, "sum(cdsstress a:usd 8y x 1.25)": { "type": "number" } }; var numattribs = fieldattribs.length; var mytype = ''; var myfields = {}; if (numattribs > 0) { _.each(fieldattribs, function (field) { mytype = (field.type == "double" ? "number" : field.type); // 'number' valid type kendo grid schema myfields[field.name] = { type: mytype }; }); } var mymodel = { id: "id", fields: myfields /*fields: { dimensions: { type: "string" }, values: { editable: false, type: "number" } }*/ }; return mymodel; } but need create buildjsongriddata(), , appreciated.
regards,
bob
here function used in exporting kendo ui grid pdf , reporting services . function takes client blob values (title,columns,data) , build collection ofg types objects. probally same on client side. notice if(dataobject!=null) located data json objects deserialzed. hope helps.
//------------------------------------------------------------------------------------------------------------------------------------ private customreportdata createreportfromkendodata(string title, string columns, string data) { list<int> _ignoredcolumns=new list<int>(); var columnobject = (columns == null) ? null : jsonconvert.deserializeobject<dynamic>(columns); var dataobject = (data == null) ? null : jsonconvert.deserializeobject<dynamic>(data); customreportdata reportdata = new customreportdata(); reportdata.reporttitle = title; if (columnobject != null) (int columncount = 0; columncount < columnobject.count; columncount++) { string fieldname = columnobject[columncount].title == null ? columnobject[columncount].field.tostring() : columnobject[columncount].title.tostring(); if (shoulddisplay(columnobject[columncount].tostring())) { //if width null columns evnly spread bomb reportdata.columns.add(new customreportcolumn { fieldname = fieldname, datatype = "", width = (columnobject[columncount].width!=null)?this.getfirstnumeric(columnobject[columncount].width.tostring(), 250) :250 }); } else _ignoredcolumns.add(columncount); } if (dataobject != null) { bool hasgrouping = dataobject[0].value != null; (int rowcount = 0; rowcount < dataobject.count; rowcount++) { if (hasgrouping) { customreportgroup group = new customreportgroup(); group.groupname = dataobject[rowcount].field.tostring(); group.groupvalue = applyformat(dataobject[rowcount].value.tostring(),null); this.recursiveprocessgroups(group, dataobject[rowcount].items.tostring(), columnobject.tostring(),_ignoredcolumns); reportdata.groups.add(group); } else { customreportdatarow row = new customreportdatarow(); (int columncount = 0; columncount < columnobject.count; columncount++) { if (!_ignoredcolumns.contains(columncount)) { string format = (columnobject[columncount].format == null ? "" : columnobject[columncount].format.tostring()); string value = dataobject[rowcount][columnobject[columncount].field.tostring()].tostring(); value = applyformat(value, format); row.fields.add(value); } } reportdata.datarows.add(row); } } reportdata.groupdepth = this.getgroupdepth(reportdata.groups); } return reportdata; } recursive function group nesting
//------------------------------------------------------------------------------------------------------------------------------------ private void recursiveprocessgroups(customreportgroup parentgroup, string items, string columns, list<int> ignoredcolumns) { var groupitems = (items == null) ? null : jsonconvert.deserializeobject<dynamic>(items); var columnobject = (columns == null) ? null : jsonconvert.deserializeobject<dynamic>(columns); bool hasgrouping = groupitems[0]["value"] != null; if (hasgrouping) { (int rowcount = 0; rowcount < groupitems.count; rowcount++) { customreportgroup group = new customreportgroup(); group.groupname = groupitems[rowcount].field.tostring(); group.groupvalue = applyformat(groupitems[rowcount].value.tostring(),null); parentgroup.groups.add(group); recursiveprocessgroups(group, groupitems[rowcount].items.tostring(), columns.tostring(), ignoredcolumns); } } else { (int groupitemcount = 0; groupitemcount < groupitems.count; groupitemcount++) { customreportdatarow row = new customreportdatarow(); (int columncount = 0; columncount < columnobject.count; columncount++) if (!ignoredcolumns.contains(columncount)) { string value = groupitems[groupitemcount][columnobject[columncount].field.tostring()].tostring(); string format = (columnobject[columncount].format == null ? "" : columnobject[columncount].format.tostring()); value = applyformat(value, format); row.fields.add(value); } parentgroup.datarows.add(row); } } } domain objects
public class customreportcolumn { public string fieldname { get; set; } public string datatype { get; set; } public float width { get; set; } } public class customreportcolumns : list<customreportcolumn> { } public class customreportdatarow { public customreportdatarow() { fields = new list<string>(); } public list<string> fields { get; set; } } public class customreportdatarows : list<customreportdatarow> { } public class customreportgroup { public customreportgroup() { datarows = new customreportdatarows(); groups = new customreportgroups(); } public string groupname { get; set; } public string groupvalue { get; set; } public customreportgroups groups { get; set; } public customreportdatarows datarows { get; set; } } public class customreportgroups : list<customreportgroup> { } public class customreportdata { public customreportdata() { groups = new customreportgroups(); columns = new customreportcolumns(); datarows = new customreportdatarows(); } public int groupdepth { get; set; } public customreportcolumns columns { get; set; } public customreportgroups groups { get; set; } public customreportdatarows datarows { get; set; } public string reporttitle { get; set; } } pdf conversion
using itextsharp.text; using itextsharp.text.pdf; using svg;
public class pdfserviceitextsharp:ipdfservice { private int _groupdepth=0; private int _columncount = 0; //----------------------------------------------------------------------------------------------------------------------------------------- public byte[] rendercustomreport(customreportdata data) { try { using (memorymappedfile mmf = memorymappedfile.createnew("inmemorypdf.pdf", 1000000)) { pdfpagemargins margins = new pdfpagemargins(10, 10, 10, 10); _groupdepth = data.groupdepth; _columncount = data.columns.count; float _pagewidth = this.getpagewidth(data.columns, margins); var document=new document((_pagewidth>540)?pagesize.a4.rotate(): pagesize.a4, margins.left,margins.right, margins.top, margins.bottom); using (memorymappedviewstream stream = mmf.createviewstream()) { pdfwriter.getinstance(document, stream); document.open(); var pdftable = new pdfptable(_columncount + _groupdepth); int[] columnwidths = new int[_columncount + _groupdepth]; for(int x=0;x<_groupdepth;x++) columnwidths[x]=3; for(int y=0;y<data.columns.count;y++) columnwidths[_groupdepth + y] = convert.toint32(data.columns[y].width); pdftable.setwidths(columnwidths); pdftable.defaultcell.padding = 3; pdftable.defaultcell.borderwidth = 1; pdftable.defaultcell.horizontalalignment = element.align_top; this.createheadercolumns(pdftable, data.columns); pdftable.headerrows = 1; pdftable.defaultcell.borderwidth = 1; if (data.groups.count > 0) this.creategroupdata(pdftable, data.groups, 0); else (int x = 0; x < data.datarows.count; x++) this.createdetailcolumns(pdftable, data.datarows[x]); document.add(pdftable); document.close(); } byte[] content; using (memorymappedviewstream stream = mmf.createviewstream()) { binaryreader rdr = new binaryreader(stream); content = new byte[mmf.createviewstream().length]; rdr.read(content, 0, (int)mmf.createviewstream().length); } return content; } } catch { throw; } } //----------------------------------------------------------------------------------------------------------------------------------------- public float getpagewidth(customreportcolumns columns, pdfpagemargins margins) { float width=0; foreach (customreportcolumn c in columns) width += c.width; return width + margins.left + margins.right + (_groupdepth * 3); } //----------------------------------------------------------------------------------------------------------------------------------------- private void createheadercolumns(pdfptable table,customreportcolumns columns) { font headerfont = this.getheaderfont(); for(int gc=0;gc<_groupdepth;gc++) { pdfpcell cell = new pdfpcell(new phrase("", headerfont)); cell.borderwidth=0; table.addcell(cell); } (int x = 0; x < columns.count; x++) { pdfpcell cell = new pdfpcell(new phrase(columns[x].fieldname, headerfont)); cell.bordercolor = new basecolor(100, 149, 237); cell.horizontalalignment = element.align_center; cell.borderwidth=0.75f; cell.border = rectangle.bottom_border | rectangle.top_border | rectangle.right_border | rectangle.left_border; cell.paddingbottom = 10; cell.paddingleft = 3; cell.paddingright = 3; cell.paddingtop = 10; table.addcell(cell); } } //----------------------------------------------------------------------------------------------------------------------------------------- private void creategroupdata(pdfptable table, customreportgroups parentgroups, int groupdepth) { if ((parentgroups == null)||(parentgroups.count==0)) return; groupdepth++; foreach (customreportgroup group in parentgroups) { this.creategroupheader(table, group, groupdepth); (int x = 0; x < group.datarows.count; x++) this.createdetailcolumns(table, group.datarows[x]); creategroupdata(table, group.groups, groupdepth); } } //----------------------------------------------------------------------------------------------------------------------------------------- private void createdetailcolumns(pdfptable table, customreportdatarow row) { font detailfont = this.getdetailfont(); (int gc = 1; gc <= _groupdepth; gc++) { basecolor groupcolor = this.getgroupheadercolor(_groupdepth); pdfpcell cell = new pdfpcell(new phrase("", detailfont)); cell.backgroundcolor = groupcolor; cell.bordercolor = groupcolor; cell.borderwidth = 0.75f; table.addcell(cell); } (int x = 0; x < row.fields.count; x++) { pdfpcell cell = new pdfpcell(new phrase(row.fields[x], detailfont)); cell.bordercolor = new basecolor(211, 211, 211); cell.horizontalalignment = element.align_center; cell.borderwidth = 0.75f; cell.border = rectangle.bottom_border | rectangle.top_border | rectangle.right_border | rectangle.left_border; cell.paddingbottom = 1; cell.paddingleft = 3; cell.paddingright = 3; cell.paddingtop = 1; table.addcell(cell); } } //----------------------------------------------------------------------------------------------------------------------------------------- private void creategroupheader(pdfptable table, customreportgroup group, int depth) { font detailfont = this.getdetailfont(); (int gc = 1; gc <= _groupdepth; gc++) { basecolor depthcolor = this.getgroupheadercolor(depth); pdfpcell ecell = new pdfpcell(new phrase("", detailfont)); ecell.backgroundcolor = depthcolor; ecell.bordercolor = depthcolor; ecell.borderwidth = 0.75f; table.addcell(ecell); } basecolor groupcolor = this.getgroupheadercolor(depth); pdfpcell cell = new pdfpcell(new phrase( group.groupvalue, detailfont)); cell.colspan = _columncount + ((_groupdepth - depth)); cell.bordercolor = groupcolor; cell.backgroundcolor = groupcolor; cell.horizontalalignment = element.align_left; cell.borderwidth = 0.75f; cell.border = rectangle.bottom_border | rectangle.top_border | rectangle.right_border | rectangle.left_border; cell.paddingbottom = 3; cell.paddingleft = 3; cell.paddingright = 3; cell.paddingtop = 3; table.addcell(cell); } //----------------------------------------------------------------------------------------------------------------------------------------- private font getheaderfont() { return fontfactory.getfont("arial", 10, font.bold, basecolor.black); } //----------------------------------------------------------------------------------------------------------------------------------------- private font getdetailfont() { return fontfactory.getfont("arial", 8, font.normal, basecolor.black); } //----------------------------------------------------------------------------------------------------------------------------------------- private basecolor getgroupheadercolor(int groupdepth) { switch (groupdepth) { case 1: return new basecolor(211,211,211); case 2: return new basecolor(100, 149, 237); case 3: return new basecolor(255,165,0); case 4: return new basecolor(169,169,169); case 5: return new basecolor(211, 211, 211); default: return new basecolor(211, 211, 211); } } //----------------------------------------------------------------------------------------------------------------------------------------- public void mergefiles(system.collections.generic.list<string> sourcefiles, string destinationfile) { document document=null; if (system.io.file.exists(destinationfile)) system.io.file.delete(destinationfile); try { pdfcopy writer = null; int numberofpages=0; foreach(string sourcefile in sourcefiles) { pdfreader reader = new pdfreader(sourcefile); reader.consolidatenameddestinations(); numberofpages = reader.numberofpages; if(document==null) { document = new document(reader.getpagesizewithrotation(1)); writer = new pdfcopy(document, new filestream(destinationfile, filemode.create)); document.open(); } (int x = 1;x <= numberofpages;x++ ) { if (writer != null) { pdfimportedpage page = writer.getimportedpage(reader, x); writer.addpage(page); } } pracroform form = reader.acroform; if (form != null && writer != null) { writer.copyacroform(reader); } } } catch { throw; } { if (document != null && document.isopen()) document.close(); } } //----------------------------------------------------------------------------------------------------------------------------------------- public byte[] renderreportfromsvg(string svgfilecontents) { system.drawing.bitmap _bitmap=null; byte[] bytearray = encoding.ascii.getbytes(svgfilecontents); using (var stream = new memorystream(bytearray)) { xmldocument xdoc=new xmldocument(); xdoc.loadxml(svgfilecontents); var svgdocument = svgdocument.open(xdoc); _bitmap = svgdocument.draw(); } using (memorymappedfile mmf = memorymappedfile.createnew("inmemorypdf.pdf", 1000000)) { pdfpagemargins margins = new pdfpagemargins(10, 10, 10, 10); float _pagewidth = margins.left + margins.right + _bitmap.width; var document = new document((_pagewidth > 540) ? pagesize.a4.rotate() : pagesize.a4, margins.left, margins.right, margins.top, margins.bottom); using (memorymappedviewstream stream = mmf.createviewstream()) { pdfwriter.getinstance(document, stream); document.open(); itextsharp.text.image pdfimage = itextsharp.text.image.getinstance(_bitmap, system.drawing.imaging.imageformat.bmp); var pdftable = new pdfptable(1); pdftable.setwidths(new int[]{_bitmap.width}); pdftable.defaultcell.horizontalalignment = element.align_top; pdfpcell cell = new pdfpcell(pdfimage); //cell.fixedheight = fixedheight; cell.horizontalalignment = element.align_center; cell.verticalalignment = element.align_middle; //img.scaleabsolute(200, fixedheight); pdftable.addcell(cell); document.add(pdftable); document.close(); } byte[] content; using (memorymappedviewstream stream = mmf.createviewstream()) { binaryreader rdr = new binaryreader(stream); content = new byte[mmf.createviewstream().length]; rdr.read(content, 0, (int)mmf.createviewstream().length); } return content; } } //----------------------------------------------------------------------------------------------------------------------------------------- }
finally excelservice render grid excel column groupings
using npoi.hssf.usermodel; using npoi.ss.usermodel; public class excelservicenpoi:iexcelservice { //----------------------------------------------------------------------------------------------------------------------------------------- public byte[] rendercustomreport(customreportdata data) { try { if (data == null) throw new argumentnullexception("report data cannot null"); hssfworkbook workbook = new hssfworkbook(); isheet sheet = workbook.createsheet(data.reporttitle); if (data.groups.count > 0) { int maxdepth = data.groupdepth;// this.groupdepth(data.groups, 0); this.createcolumnheader(sheet,maxdepth, data.columns ); this.creategroupdata(sheet, data.groups, 0, maxdepth); } else { this.createcolumnheader(sheet, data.columns); this.createrowdata(sheet, data.datarows); } this.setcolumnwidth(sheet, data.columns); sheet.createfreezepane(0, 1); memorystream ms = new memorystream(); workbook.write(ms); return ms.toarray(); } catch { throw; } } //----------------------------------------------------------------------------------------------------------------------------------------- private void createcolumnheader(isheet sheet,customreportcolumns columns) { this.createcolumnheader(sheet, 0, columns); } //----------------------------------------------------------------------------------------------------------------------------------------- private void createcolumnheader(isheet sheet, int columnoffset, customreportcolumns columns ) { if (columns == null || columns.count == 0) return; irow headerrow = sheet.createrow(0); int colcount = columnoffset; foreach (customreportcolumn column in columns) { headerrow.createcell(colcount).setcellvalue(column.fieldname); colcount++; } } //----------------------------------------------------------------------------------------------------------------------------------------- private void creategroupdata(isheet sheet, customreportgroups parentgroups,int groupdepth, int maxdepth) { if (parentgroups == null) return; groupdepth++; foreach (customreportgroup group in parentgroups) { customreportdatarow groupheader = new customreportdatarow(); groupheader.fields.add(group.groupvalue); this.createrowdata(sheet,groupdepth-1, groupheader); int groupbeginrownum = sheet.lastrownum+1; this.createrowdata(sheet,maxdepth, group.datarows); creategroupdata(sheet,group.groups, groupdepth, maxdepth); sheet.grouprow(groupbeginrownum, sheet.lastrownum); } } //----------------------------------------------------------------------------------------------------------------------------------------- private void createrowdata(isheet sheet, int startcolumnindex, customreportdatarow row) { int colnum = startcolumnindex; irow newrow = sheet.createrow(sheet.lastrownum + 1); foreach (string field in row.fields) newrow.createcell(colnum++).setcellvalue(field); } //----------------------------------------------------------------------------------------------------------------------------------------- private void createrowdata(isheet sheet, customreportdatarows rows) { createrowdata(sheet, 0, rows); } //----------------------------------------------------------------------------------------------------------------------------------------- private void createrowdata(isheet sheet, int startcolumnindex, customreportdatarows rows) { foreach (customreportdatarow row in rows) { int colnum = startcolumnindex; irow newrow = sheet.createrow(sheet.lastrownum + 1); foreach (string field in row.fields) newrow.createcell(colnum++).setcellvalue(field); } } //----------------------------------------------------------------------------------------------------------------------------------------- private void setcolumnwidth(isheet sheet, customreportcolumns columns) { if (columns == null || columns.count == 0) return; (int x = 0; x < columns.count;x++ ) sheet.autosizecolumn(x); } //----------------------------------------------------------------------------------------------------------------------------------------- } feel free use it. works latest version of kendo. have added function renderreportfromsvg() print kendo chart pdf using chart.svgcontents
Comments
Post a Comment