How to create excel file with multiple sheet using json data using c#?

You can also use free spire.xls api to create excel file from json data.

First, search and install free spire.xls via nuget:

https://www.nuget.org/packages/FreeSpire.XLS/

Second, use the following code:

static void Main(string[] args)
{
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
DataTable dt = JsonToDataTable("your json string");
sheet.InsertDataTable(dt, true, 1, 1);
book.SaveToFile("JsonToExcel.xlsx",ExcelVersion.Version2013);
}
//Convert jason to datatable
public static DataTable JsonToDataTable(string json)
{
DataTable table = new DataTable();
JArray array = JsonConvert.DeserializeObject(json) as JArray;
if (array.Count > 0)
{
StringBuilder columns = new StringBuilder();
JObject objColumns = array[0] as JObject;
foreach (JToken jkon in objColumns.AsEnumerable())
{
string name = ((JProperty)(jkon)).Name;
columns.Append(name + ",");
table.Columns.Add(name);
}
for (int i = 0; i < array.Count; i++)
{
DataRow row = table.NewRow();
JObject obj = array[i] as JObject;
foreach (JToken jkon in obj.AsEnumerable())
{
string name = ((JProperty)(jkon)).Name;
string value = ((JProperty)(jkon)).Value.ToString();
row[name] = value;
}
table.Rows.Add(row);
}
}
return table;
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D6y ago
You can also use free spire.xls api to create excel file from json data.

First, search and install free spire.xls via nuget:

https://www.nuget.org/packages/FreeSpire.XLS/

Second, use the following code:
static void Main(string[] args)
{
    //export datatable to excel
    Workbook book = new Workbook();
    Worksheet sheet = book.Worksheets[0];
    DataTable dt = JsonToDataTable("your json string");
    sheet.InsertDataTable(dt, true, 1, 1);
    book.SaveToFile("JsonToExcel.xlsx",ExcelVersion.Version2013);
}

//Convert jason to datatable
public static DataTable JsonToDataTable(string json)
{
    DataTable table = new DataTable();
    JArray array = JsonConvert.DeserializeObject(json) as JArray;
    if (array.Count > 0)
    {
        StringBuilder columns = new StringBuilder();

        JObject objColumns = array[0] as JObject;
        foreach (JToken jkon in objColumns.AsEnumerable())
        {
            string name = ((JProperty)(jkon)).Name;
            columns.Append(name + ",");
            table.Columns.Add(name);
        }
        for (int i = 0; i < array.Count; i++)
        {
            DataRow row = table.NewRow();
            JObject obj = array[i] as JObject;
            foreach (JToken jkon in obj.AsEnumerable())
            {

                string name = ((JProperty)(jkon)).Name;
                string value = ((JProperty)(jkon)).Value.ToString();
                row[name] = value;
            }
            table.Rows.Add(row);
        }
    }
    return table;
}
0
Uttam Chaturvedi6y ago
Here you go.. Hope it will help you

https://www.grapecity.com/blogs/how-to-generate-excel-from-xml-and-json-data-using-c
https://blog.aspose.com/2020/04/03/import-data-from-json-to-excel-in-csharp-asp.net/
0