C#•2y ago

❔ JSON To Excel

I have a console app that takes a folder of JSON objects, and deserializes them into Classes From here, I want to map these classes into excel sheets (I will link the models in a sec via hastebin) What I want is to map these json to excel tables by making a worksheet based on the "skill" property The thing is that these things are nested, and so far it results in my excel giving me a single sheet, with System.Collection[...] values in the cells, instead of recursively getting all nested properties, and mapping them internally.. So basically here's the schema I use for the JSONs https://pastebin.com/CbgdUpt0 And these are the models I made: https://pastebin.com/N4y3ciRy Most of my program works fine, but what I can't seem to figure is the excel part. Here's the method I use to generate a DataTable, which is not looking into nested properties, and I don't know how

public static DataTable ConvertToDataTable<T>(this IEnumerable<T> models)
{
    DataTable dataTable = new DataTable(typeof(T).Name);
    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in props)
    {
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in models)
    {
        object[] values = new object[props.Length];
        for (int i = 0; i < props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    return dataTable;
}

public static DataTable ConvertToDataTable<T>(this IEnumerable<T> models)
{
    DataTable dataTable = new DataTable(typeof(T).Name);
    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in props)
    {
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in models)
    {
        object[] values = new object[props.Length];
        for (int i = 0; i < props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    return dataTable;
}

2 Replies

malkavOP•2y ago

Expected Excel with the following example: https://pastebin.com/wByE0SVB (uses d. as the root of the JSON)

(SheetName: Hot Food Cooking)
| Name   | Known   | Trigger   | Cookers   | Containers       | Ingredients   | Result      | Description          |
| d.name | d.known | d.trigger | d.cookers | [Insert table_2] | d.ingredients | d.result.id | d.result.description |

TABLE_2
| Mandatory         | One Or More       | Zero Or One       | Any               | One Of            | Optional
| [insert TABLE_3A] | [insert TABLE_3B] | [insert TABLE_3C] | [insert TABLE_3D] | [insert TABLE_3E] | [insert TABLE_3F]

TABLE_3
  | Name | ratio | loss | difficulty |
A | d.ingredients.mandatory.Select(x => x.id) | d.ingredients.mandatory.select(x => x.ratio) | d.ingredients.mandatory.select(x => x.loss) | d.ingredients.mandatory.select(x => x.difficulty)
B | d.ingredients.oneormore.Select(x => x.id)
C | d.ingredients.zeroorone.Select(x => x.id)
D | d.ingredients.any.Select(x => x.id)
E | d.ingredients.oneof.Select(x => x.id)
F | d.ingredients.optional.Select(x => x.id)

(SheetName: Hot Food Cooking)
| Name   | Known   | Trigger   | Cookers   | Containers       | Ingredients   | Result      | Description          |
| d.name | d.known | d.trigger | d.cookers | [Insert table_2] | d.ingredients | d.result.id | d.result.description |

TABLE_2
| Mandatory         | One Or More       | Zero Or One       | Any               | One Of            | Optional
| [insert TABLE_3A] | [insert TABLE_3B] | [insert TABLE_3C] | [insert TABLE_3D] | [insert TABLE_3E] | [insert TABLE_3F]

TABLE_3
  | Name | ratio | loss | difficulty |
A | d.ingredients.mandatory.Select(x => x.id) | d.ingredients.mandatory.select(x => x.ratio) | d.ingredients.mandatory.select(x => x.loss) | d.ingredients.mandatory.select(x => x.difficulty)
B | d.ingredients.oneormore.Select(x => x.id)
C | d.ingredients.zeroorone.Select(x => x.id)
D | d.ingredients.any.Select(x => x.id)
E | d.ingredients.oneof.Select(x => x.id)
F | d.ingredients.optional.Select(x => x.id)

Note that B - F in table 3 do not have additional data in them, these should be optional, and can be added if they are not null, but most will be null.

Accord•2y ago

Looks like nothing has happened here. I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ JSON To Excel