Programmatically Get JSON in Excel XLSX Using C#, .NET and Java Document APIs

In the programming world, data is everywhere, and one of the biggest challenges is storing, transferring, and managing it across applications. For a long time, programmers, developers, and IT professionals have used XML and CSV for exchanging data between applications.

However, with the rise in API code programming and Web Services, the use of JSON has gained popularity among them because of its easy-to-use process. JSON is text-based and lightweight, has an easy-to-parse data format that requires no additional parsing code, processes faster, can handle lots of data, and is machine-independent.

Once developed to transfer data and build communication between applications, JSON now finds its use in several data scenarios, including storing data, such as the number of registered users on a website, storing application configuration, data validation, simplifying complex data models to predictable and human-readable text, and many more.

With this growing use and data being exposed to users in native object format, it is often required to present JSON into structured (tabular) data such as Excel. Manually copying each data object and its value from JSON into Excel is not a good solution (at least in situations when the JSON file is too large). Thus, we bring the programmatic approach to fetch JSON data into an Excel file using the Document Solutions for Excel (DsExcel, previously GcExcel) API for C# .NET and Java.

Use-Case

Most businesses automate their core processes using software solutions such as ERP, CRM, SCM, etc. These systems store tons of business information, including product details, sales statistics, registered users, customers, employees, and many other pieces of data.

Suppose you are the developer for a company that has embedded one such solution in its existing system to track production, inventory, and sales of different products. The solution provides an API to fetch the stored information, but the data it returns is in JSON format. Thus, you are looking for a solution that can be integrated into your system to generate Excel files for such JSON data.

For this blog, we will generate an Excel file for the product inventory data available in JSON format as shown below:

Getting this JSON data into an Excel file is possible in the following ways:

1. Binding the data to a workbook and showing it in a Table or Range of Cells
2. Filling data fields in a report (or Excel Template)

Let’s see how we can do this using a simple API that DsExcel offers for C# .NET and Java.

Binding JSON Source to a Workbook

DsExcel offers support for data-bound reports using the DataSource property of the IWorksheet interface. To get the data from the JSON source, bind this property to an object of JsonDataSource class that accepts the JSON string parameter. If the JSON contains a named list or array, use the Table Binding option; otherwise, use the Sheet Binding. This is because a Table in DsExcel accepts a binding path corresponding to the list's name or array in the JSON.

As the JSON data referenced for this blog contains a “data” array, we’re binding a Table in the Excel file as shown in the code snippet below:

C# .NET

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();		
			
//Read the JSON data and store in a string
string jsonText = string.Empty;
using (Stream stream = GetResourceStream("json\\inventory.json"))
using (StreamReader reader = new StreamReader(stream))
{
	jsonText = reader.ReadToEnd();
}

// Create a JsonDataSource from the JSON string
var datasource = new JsonDataSource(jsonText);

//Add JSON datasource to the Workbook
worksheet.DataSource = datasource;

//Create a table
ITable table = worksheet.Tables.Add(worksheet.Range["A1:E10"], false);
table.AutoGenerateColumns = true;

//Bind the array from the JSON to the Table using the Binding path property
table.BindingPath = "data";

. . .
//Add formtting etc.
. . .

// Save to an excel file
workbook.Save("InventoryList.xlsx");

Java

//create a new workbook
var workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Table Binding");

//Read json data into a stream
String jsonText = "";
try {
    	    
    String fileName = "src/jsonDataSource/resources/inventory.json";

    try (InputStream stream = new FileInputStream(fileName)) {
		ByteArrayOutputStream result = new ByteArrayOutputStream();
		byte[] buffer = new byte[1024];
		int length;
		while ((length = stream.read(buffer)) != -1) {
		    result.write(buffer, 0, length);
		}

		jsonText = result.toString("UTF-8");
	}
} catch (Exception e) {
	e.printStackTrace();
}

// Create a JsonDataSource from the JSON string
var datasource = new JsonDataSource(jsonText);

//Add JSON datasource to the Workbook
worksheet.setDataSource(datasource);

//Create a table
ITable table = worksheet.getTables().add(worksheet.getRange("A1:E10"), false); 
table.setAutoGenerateColumns(true);

//Bind the array from the JSON to the Table using the Binding path property
table.setBindingPath("data");

. . .
//Add formtting etc.
. . . 

//Save the workbook
workbook.save("InventoryList.xlsx");

The data from the JSON file appears in the Excel file after executing the above code, as shown below:

Download the .NET | Java sample to see the complete code.

Check out the .NET Help | .NET Demo | Java Help | Java Demo for more information on Data binding options in DsExcel.

Fetching JSON Data in Excel Template

DsExcel provides Templates to create highly effective and well-designed Excel reports. These reports are meaningful when the Template layout is filled with the data from a data source. To fill data from a JSON data source in an Excel Template, use the object of JsonDataSource class as an argument to the AddDataSource method of the IWorkbook interface.

To fill the data from the JSON file referenced for this blog, we have created an Excel template that appears as shown below:

And used the code below to get the data into the template file:

C# .NET

//Create workbook and load the template file
var workbookTemplate= new GrapeCity.Documents.Excel.Workbook();
var template = GetResourceStream("template\\Inventorydashboard.xlsx");
workbookTemplate.Open(template);

//Read the JSON data and store in a string
jsonText = string.Empty;
using (Stream stream = GetResourceStream("json\\inventory.json"))
using (StreamReader reader = new StreamReader(stream))
{
	jsonText = reader.ReadToEnd();
}

// Create a JsonDataSource from the JSON string
var datasource = new JsonDataSource(jsonText);

//Add JSON data source to the Workbook
workbookTemplate.AddDataSource("ds", datasource);

//Invoke to process the template
workbookTemplate.ProcessTemplate();

// Save to an excel file
workbookTemplate.Save("Inventory Status Dashboard.xlsx");

Java

//Create workbook and load the template file
Workbook workbookTemplate = new Workbook();
workbookTemplate.open("Inventorydashboard.xlsx");

//Read the JSON data and store in a string
String jsonText = "";
try {
    	    
    String fileName = "src/jsonDataSource/resources/inventory.json";

    try (InputStream stream = new FileInputStream(fileName)) {
		ByteArrayOutputStream result = new ByteArrayOutputStream();
		byte[] buffer = new byte[1024];
		int length;
		while ((length = stream.read(buffer)) != -1) {
		    result.write(buffer, 0, length);
		}

		jsonText = result.toString("UTF-8");
	}
} catch (Exception e) {
		e.printStackTrace();
}

// Create a JsonDataSource from the JSON string
JsonDataSource datasource = new JsonDataSource(jsonText);

//Add JSON data source to the Workbook
workbookTemplate.addDataSource("ds", datasource);	


//Invoke to process the template
workbookTemplate.processTemplate();

// Save to an excel file
workbookTemplate.save("Inventory Status Dashboard.xlsx");

The Excel file generated after executing the above code appears as shown below:

Download the .NET | Java sample to see the complete code.

Check out the .NET Help | .NET Demo | Java Help | Java Demo for more information on Data Source binding in Excel Templates using DsExcel.

Conclusion

With Document Solutions for Excel (both the .NET and Java editions), apart from just populating JSON data into Excel files, you can also convert it to other formats such as PDF, HTML, etc. We’ll see the details in the coming articles. Stay tuned!