How to read JSON and convert list of results to an Excel file using C#
Introduction
This C# recipe shows how you can query a JSON url and write the results to Excel using C#
Ingredients
The recipe uses the EPPlus library version 4.5.3.3. This is the last version of EPPlus under the LGPL License (aka free for commercial uses). You can install it using Nuget Package manager:
Install-Package EPPlus -Version 4.5.3.3
Also The recipe uses the ServiceStack.Text Nuget package. You can install it using Nuget Package manager:
Install-Package ServiceStack.Text
From v4.0.62+ ServiceStack.Text is free!
C# Code Snippet
// retrieve json
var json = $"https://jsonplaceholder.typicode.com/posts"
.GetJsonFromUrl();
var objects = json.FromJson<List<PostDto>>();
// populate excel spreadsheet
string Path = @"C:\Temp\ExcelRecipes\";
var fileName = "json_to_xlsx.xlsx";
var fileInfo = new FileInfo(Path + fileName);
using (var excelFile = new ExcelPackage(fileInfo))
{
// add worksheet if not exists
if (!excelFile.Workbook.Worksheets.Any(ar => ar.Name == "Posts"))
excelFile.Workbook.Worksheets.Add("Posts");
var ws = excelFile.Workbook.Worksheets["Posts"];
// headers
ws.Cells[1, 1].Value = "UserId";
ws.Cells[1, 2].Value = "Id";
ws.Cells[1, 3].Value = "Title";
ws.Cells[1, 4].Value = "Body";
// content
int row = 2;
foreach (var o in objects)
{
ws.Cells[row, 1].Value = o.UserId;
ws.Cells[row, 2].Value = o.Id;
ws.Cells[row, 3].Value = o.Title;
ws.Cells[row, 4].Value = o.Body;
row++;
}
excelFile.Save();
}
public class PostDto
{
public int UserId { get; set; }
public int Id { get; set; }
public string Title { get; set; }
public string Body { get; set; }
}