Edit 8 Sept I'm having trouble parsing a configuration spreadsheet into JSON (generated from a multitude of other spreadsheets) Initially I'm loading a spreadsheet from Excel using ClosedXML into a Datatable
After much rewriting, I'm pretty close. The issue I've got is that I need to exclude any options under pages where there's no But_Type_P1 [aka type] value - the button_num serves no useful purpose if it doesn't have a button type associated with it - it just indicates it's position on an X-Y grid on a hardware device.
I need to work out how I filter out any children if specific ones are null. If the alias name is empty (or null) we can generate a placeholder for that downstream. ... I haven't added those to the export yet as they are just cosmetic.
[
{
"type": "function",
"label": "VM",
"Location": "lhs_front_pc",
"options": [
{
"type": "page",
"label": "VM|PCs",
"options": [
{
"type": "Source",
"index": "1",
"value": "45401"
},
{
"type": "Source",
"index": "2",
"value": "45402"
},
...continues...
{
"type": "page",
"label": "HW|PCs",
"options": [
{
"type": "Source",
"index": "1",
"value": "45751"
},
{
"type": "Source",
"index": "2",
"value": "45752"
},
{
"type": "Source",
"index": "3",
"value": "45753"
},
{
"type": "Source",
"index": "4",
"value": "45754"
},
{
"type": "",
"index": "5",
"value": ""
},
{
"type": "",
"index": "6",
"value": ""
},
I'm at the point now where I've got a filtered datatable for the function level, and another one filtered for the pages for each function (up to 5 pages per function)
Code Snippet
public void dtToFunction(DataTable dt)
{
var functionTable = from instance in dt.AsEnumerable()
group instance by instance.Field<string>("Panel_Instance") into panel_instance
select new
{
Instance = panel_instance.Key,
List = panel_instance.ToList(),
};
foreach (var row in functionTable)
{
logger.Info($"{row.Instance.ToString()}");
var obj = new JArray();
var pageTable = from page in row.List.AsEnumerable()
group page by page.Field<string>("Page_P1") into page
select new
{
Page = page.Key,
List = page.ToList(),
};
var pageObj = new JArray();
try {
pageObj = new JArray(
row.List
.GroupBy(r => new { Page = r["Page_P1"] })
.Select(g => new JObject(
new JProperty("type", "page"),
new JProperty("label", g.Key.Page.ToString()),
new JProperty("options",g.Select(r =>
new JObject(
new JProperty("type", r["But_Type_P1"].ToString()),
new JProperty("index", r["Button_Num"]),
new JProperty("value", r["But_Idx_P1"])
)
)
)
)
)
);
}
catch (Exception exp)
{ logger.Error($"Exception: {exp.Message}"); }
try
{
obj = new JArray(
row.List
.GroupBy(r => new { Function = r["Function_Name"], Location = r["Location"] })
.Select(g => new JObject(
new JProperty("type", "function"),
new JProperty("label", g.Key.Function.ToString()),
new JProperty("Location", g.Key.Location.ToString()),
new JProperty("options",
pageObj
)
)
)
);
logger.Info($"{obj.ToString()}");
}
catch (Exception exp)
{
logger.Error($"Exception: {exp.Message}");
}
I've been through
