根据特定规则将数据表转换为JSON(Converting datatable into JSON based on certain rules)
类:
public class DataCls { public int Year { get; set; } public string Month { get; set; } public int MonthOrder { get; set; } public string category { get; set; } public int Val { get; set; } }数据:
List<DataCls> dlist = new List<DataCls>(); DataCls dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 1; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "B"; dat.Val = 2; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "C"; dat.Val = 3; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 5; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 6; dlist.Add(dat); dat.Year = 2016; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 4; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 7; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 8; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "C"; dat.Val = 9; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Mar"; dat.MonthOrder = 3; dat.category = "A"; dat.Val = 10; dlist.Add(dat);输出我期待。
{ "Series": [{ "name": "A", "stack": 2015, "data": [1, 5, 10] }, { "name": "B", "stack": 2015, "data": [2, 6, null] }, { "name": "C", "stack": 2015, "data": [3, null, null] }, { "name": "A", "stack": 2016, "data": [4, 7, null] }, { "name": "B", "stack": 2016, "data": [null,8, null] }, { "name": "C", "stack": 2016, "data": [null,9, null] }] }要记住的要点:
Year属性将分配给json对象中的stack属性。 Category属性将分配给json对象中的name属性。 Category的数量不固定。 给定Category可能会或可能不会出现给定月份。 val属性将被分配给每个月的json对象中的data数组。 如果一个月缺少类别,我们需要将该值视为null。我真的不确定如何解决这个问题。 我该如何解决这个问题。 我很难解决这个问题。
Class:
public class DataCls { public int Year { get; set; } public string Month { get; set; } public int MonthOrder { get; set; } public string category { get; set; } public int Val { get; set; } }Data:
List<DataCls> dlist = new List<DataCls>(); DataCls dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 1; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "B"; dat.Val = 2; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "C"; dat.Val = 3; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 5; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 6; dlist.Add(dat); dat.Year = 2016; dat.Month = "Jan"; dat.MonthOrder = 1; dat.category = "A"; dat.Val = 4; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "A"; dat.Val = 7; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "B"; dat.Val = 8; dlist.Add(dat); dat = new DataCls(); dat.Year = 2016; dat.Month = "Feb"; dat.MonthOrder = 2; dat.category = "C"; dat.Val = 9; dlist.Add(dat); dat = new DataCls(); dat.Year = 2015; dat.Month = "Mar"; dat.MonthOrder = 3; dat.category = "A"; dat.Val = 10; dlist.Add(dat);output I am expecting.
{ "Series": [{ "name": "A", "stack": 2015, "data": [1, 5, 10] }, { "name": "B", "stack": 2015, "data": [2, 6, null] }, { "name": "C", "stack": 2015, "data": [3, null, null] }, { "name": "A", "stack": 2016, "data": [4, 7, null] }, { "name": "B", "stack": 2016, "data": [null,8, null] }, { "name": "C", "stack": 2016, "data": [null,9, null] }] }points to remember:
Year property will be assigned to stack property in json object. Category property will be assigned to name property in json object. The number of Category is not fixed. A given Category may or may not be present for a given month. val property will be assigned to data array in json object for each month. If the category is missing for a month, we need to consider the value as null.I am really not sure how to go about this problem. How can I proceed to solve this problem. I am having hard time fixing this.
最满意答案
您可以使用linq的组合来转换数据,然后使用Json.NET来创建json。
//here assuming monthMap is an int map // "Jan" => 1 // "Feb" => 2 // etc var lqOutput = dbOutput .OrderBy(a=>monthMap[a.Month]) .GroupBy(a=>new{a.Category,a.Year}) .Select(a=>new{ name=a.Key.Category, stack=a.Key.Year, data = a.Select(b=>b.Val).ToArray() }); var json = JsonConvert.SerializeObject(new{ Series = lqOutput});如果你想让没有出现的月份被跳过,你可以制作一个更小的辅助方法
var dictToArr = new Func<int,Dictionary<int,object>, object[]>((size,d)=>{ var retv = new object[size]; foreach(var key in d.Keys) { if(key>=0) { retv[key] = d[key]; } } return retv; }); var lqOutput = dbOutput .OrderBy(a=>monthMap[a.Month]) .GroupBy(a=>new{a.Category,a.Year}) .Select(a=>new{ name=a.Key.Category, stack=a.Key.Year, data = dictToArr ( 12, a.Select(b=>new{ val= b.Val, idx=monthMap[b.Month] }).ToDictionary( b=>b.idx, b=>b.val as object ) ) }); var json = JsonConvert.SerializeObject(new{ Series = lqOutput});如果你这样做,你需要将1月映射到0,从2月到1,等等
You can use a combination of linq to transform the data and then Json.NET to create the json.
//here assuming monthMap is an int map // "Jan" => 1 // "Feb" => 2 // etc var lqOutput = dbOutput .OrderBy(a=>monthMap[a.Month]) .GroupBy(a=>new{a.Category,a.Year}) .Select(a=>new{ name=a.Key.Category, stack=a.Key.Year, data = a.Select(b=>b.Val).ToArray() }); var json = JsonConvert.SerializeObject(new{ Series = lqOutput});If you wanted to have the months that do not show up be skipped you could make a smaller helper method
var dictToArr = new Func<int,Dictionary<int,object>, object[]>((size,d)=>{ var retv = new object[size]; foreach(var key in d.Keys) { if(key>=0) { retv[key] = d[key]; } } return retv; }); var lqOutput = dbOutput .OrderBy(a=>monthMap[a.Month]) .GroupBy(a=>new{a.Category,a.Year}) .Select(a=>new{ name=a.Key.Category, stack=a.Key.Year, data = dictToArr ( 12, a.Select(b=>new{ val= b.Val, idx=monthMap[b.Month] }).ToDictionary( b=>b.idx, b=>b.val as object ) ) }); var json = JsonConvert.SerializeObject(new{ Series = lqOutput});If you do this you need to map January to 0, Feb to 1, etc
更多推荐
发布评论