How to convert .json file to excel in c#

Question

I want to convert .json file to excel . iam not able to find the solution anywhere for these issue using c# language . Could any one help me out with these along with exact solution.

Answer 1

Here is another partial solution, that doesn't require making a json definition, as long as the json is in a table format.

DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

From here, you can use any of the numerous datatable -> excel solutions available on the internet.

Many are posted here: How to export DataTable to Excel

Answer 2

It depends on the data and what you want to achieve on the Excel side. I would tackle this with Json.NET and output to a CSV file, which may be opened in Excel.

Consider the following JSON... a simple array

[{
    "foo": "bar"
}]

With Json.NET we can do the following to get it into C#

var jsonData = "[{ \"foo\": \"bar\" }]";

var jsonDefinition = new object[]
{
    new {foo = ""}
};

var result = JsonConvert.DeserializeAnonymousType(jsonData, jsonDefinition);

We can then loop through the structure, outputting to a CSV file.

var sb = new StringBuilder();
foreach (dynamic o in result)
{
    sb.AppendLine(o.foo);
}
File.WriteAllText(@"c:\code\test.csv", sb.ToString());

Answer 3

I used ExcelMapper. This app is great because I just have to send it a C# list and ExcelMapper converts it to Excel. Wow!! It is available via Nuget Manager.

ExcelMapper em = new ExcelMapper();

        em.Save(stExportFileFullPath, items, "Totals", true); 

/* items is a list composed of the below class and fields.

public class CarrierRevenueLOBReport {

    public int Year { get; set; }
    public int Month { get; set; }       
    public string Carrier { get; set; }
    public string LOBName { get; set; }
    public string NewOrRenewal { get; set; }
    public double SumPremium { get; set; }
    public double SumCommision { get; set; }
 }
    

*/

Answer 4

It can be done with the help of EPPlus, check below code for complete answer.

1. Create And write Json Data to Excel

               using (StreamReader r = new StreamReader(file))
               {
                   string json = r.ReadToEnd();
                   var obj = JsonConvert.DeserializeObject<JObject>(json);
   
                   List<JsonData> dataList = GetDataList(obj);
   
                   var fileName = file.Split('\\').Last().Split('.')[0].Trim().ToString();
   
                   using (ExcelPackage excel = new ExcelPackage())
                   {
                       excel.Workbook.Worksheets.Add(fileName);
   
                       var headerRow = new List<string[]>()
                           {
                                   new string[] { "Key", "Value"}
                               };
   
                       string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";
   
                       var worksheet = excel.Workbook.Worksheets[fileName];
   
                       worksheet.Cells[headerRange].LoadFromArrays(headerRow);
   
                       worksheet.Cells[2, 1].LoadFromCollection(dataList);
   
                       FileInfo excelFile = new FileInfo($"D:\\JsonFilesToExcel\\{folder}\\{fileName}.xlsx");
                       excel.SaveAs(excelFile);
                   }
   
               }
   

2. Create C# Object from Reading Json Key Value pairs

   public static List<JsonData> GetDataList(JObject obj)
   {
       var listResult = new List<JsonData>();
   
       foreach (JProperty item in (JToken)obj)
       {
           listResult.Add(new JsonData { Key = item.Name, Value = item.Value.ToString() });
       }
       return listResult;
   }