Create Excel with JSON

Question

I have an API who takes any JSON and I want to make a Excel with it. I'm using

Json.Decode(json)

for convert that JSON in a dynamic object, but I don't know how to access to any key and value created by the decoder.

How can I reference each key and value created?

My code:

Request model

/// 
/// Request de servicio Excel
/// 
public class GenerarExcelRequest
{
    /// 
    /// Lista de elementos
    /// 
    public string NombreArchivo { get; set; }

    /// 
    /// JSON a convertir en Excel
    /// 
    public object Modelo { get; set; }
}

Service

public GenerarExcelResponse GenerarExcel(GenerarExcelRequest request)
{
    using (ExcelPackage exc = new ExcelPackage())
    {
        ExcelWorksheet ex = exc.Workbook.Worksheets.Add("Reporte Excel");

        // If the dynamic object != null, convert it

        var modeloDecode = request.Modelo != null ? request.Modelo = Json.Decode(request.Modelo.ToString()) : null;

        // I get every value and key created and make the key header of the Excel
        if (modeloDecode == null)
            return new GenerarExcelResponse() { RutaArchivoExcel = ""};

        //var listaEncabezados = 

        // Load every value in the Excel

        // Return the file path of the new Excel

        string filePath = "C:\" + request.NombreArchivo;

        byte[] bin = exc.GetAsByteArray();
        File.WriteAllBytes(filePath, bin);
        return new GenerarExcelResponse()
        {
            RutaArchivoExcel = filePath
        };
     }
 }

JSON Input Example:

{
    "NombreArchivo": "Prueba",
    "Modelo": [
        {
            "id": 24135,
            "nombre": "Ignacio"          
        },
        {
            "id": 28733,
            "nombre": "Francisco"           
        }
    ]
}

Excel Output I want

Id ---------- Nombre

24135 ------- Ignacio

28733 ------- Francisco

But the next time that someone use this API may send this input:

JSON Input Example 2:

{
    "NombreArchivo": "Prueba2",
    "Modelo": [
        {
            "id": 25,
            "product": "XXAA2121",          
            "stock": 21
        },
        {
            "id": 23,
            "product": "XXFJJ212"           
            "stock": 4
        }
    ]
}

And want to make and Excel like this:

Excel Output I want

Id ---------- Product --------- Stock

25 ---------- XXAA2121 -------- 21

23 ---------- XXFJJ212 -------- 4

user_1073742 · Answer

I'm not exactly sure what you want, if this should work regardless of the structure of the doc, but something like this might do what you need to get it into a flat list. From there, its a trivial matter to put it into excel. This is a working example from Linqpad once you add Json.NET as a nuget package.

void Main()
{
    var jsonFoo = @"{
    ""NombreArchivo"": ""Prueba"",

    ""Modelo"": [
        {
            ""id"": 24135,
            ""nombre"": ""Ignacio""          
        },
        {
    ""id"": 28733,
            ""nombre"": ""Francisco""

        }
    ]
}";
    
var foo = (JObject)JsonConvert.DeserializeObject(jsonFoo);
foo.Dump();
var dict = new List>();
ConvertJsonToDictionary(foo, dict);
dict.Dump();
}
// Define other methods and classes here

public void ConvertJsonToDictionary(JToken foo, List> dict)
{
    switch(foo.Type)
    {
        case JTokenType.Object:
            foreach (var item in (JObject)foo)
            {
                dict.Add(new Tuple(item.Key, item.Value.ToString()));
                if (item.Value.GetType() == typeof(JArray))
                {
                    ConvertJsonToDictionary(item.Value, dict);
                }
            }
            break;
        case JTokenType.Array:
            foreach(var item in (JArray)foo)
            {
                ConvertJsonToDictionary(item, dict);
            }
            break;
    }
}

Create Excel with JSON

1 Answers