Programmatically Get JSON in Excel XLSX Using C# .NET and Java Document APIs
Tags: webdev, tutorial, dotnet, java
Published: 2022-09-30 17:29:13

---
canonical_url: https://www.grapecity.com/blogs/programmatically-get-json-in-excel-xlsx-using-c-sharp-net-java-document-apis
description: Learn how to programmatically get JSON in Excel XLSX Using C# .NET, and Java Document APIs. See more from GrapeCity Documents today.
---

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 GcExcel API for [C# .NET](https://www.grapecity.com/documents-api-excel/) and [Java](https://www.grapecity.com/documents-api-excel-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:

![json](https://files.grapecity.com/gc-website-media/jgakyqt3/01-json.png?width=485.06571087216247&height=500)

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](#binding)
2.  [Filling data fields in a report (or Excel Template)](#fetching)

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

### <a id="binding"></a>Binding JSON Source to a Workbook

GcExcel offers support for [data-bound](https://www.grapecity.com/documents-api-excel/docs/online/data-binding.html) 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](https://www.grapecity.com/documents-api-excel/docs/online/data-binding.html) option; otherwise, use the [Sheet Binding](https://www.grapecity.com/documents-api-excel/docs/online/data-binding.html). This is because a Table in GcExcel 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:

![Product Inventory List](https://files.grapecity.com/gc-website-media/dffi312r/02-json.png?width=500&height=193.1937172774869)

Download the [.NET](https://files.grapecity.com/gc-website-media/qi5bqrty/json_datasource_net.zip) | [Java](https://files.grapecity.com/gc-website-media/nqtp51zm/json_datasource_java.zip) sample to see the complete code.

### <a id="fetching"></a>Fetching JSON Data in Excel Template

GcExcel provides [Templates](https://www.grapecity.com/documents-api-excel/docs/online/templates.html) 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:

![Inventory Status Dashboard](https://files.grapecity.com/gc-website-media/0hihalyc/03-json.png?width=500&height=197.94520547945206)

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:

![Excel](https://files.grapecity.com/gc-website-media/rjgo5yxy/04-json.png?width=500&height=457.3170731707317)


Check out the [.NET Help](https://www.grapecity.com/documents-api-excel/docs/online/data_source_binding.html) | [.NET Demo](https://www.grapecity.com/documents-api-excel/demos/datasource) | [Java Help](https://www.grapecity.com/documents-api-excel-java/docs/online/data_source_binding.html) | [Java Demo](https://www.grapecity.com/documents-api-excel-java/demos/datasource) for more information on Data Source binding in Excel Templates using GcExcel.

### Conclusion

With GcExcel, 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!