Convert JSON to Excel in Java [Source Code]

This article shows you how can you convert JSON files to Excel files in java. Today you will learn to create a JSON to Excel Converter Example using Apache POI and Jackson JSON library. If you want to do Excel to JSON converter, then visit our other article Convert Excel to JSON in Java with Source Code.

How to convert json to excel in java?

To convert JSON to excel, we use below two java libraries here:

  1. Apache POI â€“ to write the JSON data to an excel file.
  2. Jackson JSON â€“ to read the JSON file and parse it to JSON object format.

Apache POI

Apache POI is an API provided by Apache Software Foundation for manipulating various file formats based upon Microsoft’s OLE2 Compound Document Format (OLE2) and Office Open XML standards (OOXML)Click here to know more about Apache POI. In order to work with the Apache POI library, you can use either maven dependency or poi jars.

Apache POI Maven Dependency

If you want to create a maven application, then you can add the below maven dependency into the pom.xml file.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

NOTE:

If you want to work with only XLS file, then the first dependency is enough but if you want to work with both XLS or XLSX then you have to use both dependencies.

Apache POI Jars

If you are going to create a non-maven application, then you can use the below jar files directly in classpath:

  1. poi-4.0.1.jar
  2. poi-ooxml-4.0.1.jar
  3. poi-ooxml-schemas-4.0.1.jar
  4. xmlbeans-3.0.2.jar
  5. curvesapi-1.05.jar
  6. commons-codec-1.11.jar
  7. commons-collections4-4.2.jar
  8. commons-compress-1.18.jar
  9. commons-math3-3.6.1.jar

You can easily download all the above jars in one place: download Jars here.

download apache poi jars

If you want to know the complete Apache POI setups process in Eclipse IDE, visit another article Apache POI – Getting Started.

Jackson JSON

Jackson is a very popular, lightweight, and high-performance library in java to serialize or map java objects to JSON and vice versa. In order to use this library in the maven application, you can add the below dependency into the pom.xml file.

<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.13.2</version>
		</dependency>

JSON to Excel converter example in Java

Here we will see an example to convert a JSON file to an Excel file in java using the Apache POI and Jackson JSON library. This example program will work for both Excel file formats XLS and XLSX.

package com.javacodepoint.example;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;

/**
 * 
 * @author javacodepoint.com
 *
 */
public class JsonToExcelConverter {

	private ObjectMapper mapper = new ObjectMapper();

	/**
	 * Method to convert json file to excel file
	 * @param srcFile
	 * @param targetFileExtension
	 * @return file
	 */
	public File jsonFileToExcelFile(File srcFile, String targetFileExtension) {
		try {
			if (!srcFile.getName().endsWith(".json")) {
				throw new IllegalArgumentException("The source file should be .json file only");
			} else {
				Workbook workbook = null;
				
				//Creating workbook object based on target file format
				if (targetFileExtension.equals(".xls")) {
					workbook = new HSSFWorkbook();
				} else if (targetFileExtension.equals(".xlsx")) {
					workbook = new XSSFWorkbook();
				} else {
					throw new IllegalArgumentException("The target file extension should be .xls or .xlsx only");
				}

				//Reading the json file
				ObjectNode jsonData = (ObjectNode) mapper.readTree(srcFile);
				
				//Iterating over the each sheets
				Iterator<String> sheetItr = jsonData.fieldNames();
				while (sheetItr.hasNext()) {
					
					// create the workbook sheet
					String sheetName = sheetItr.next();
					Sheet sheet = workbook.createSheet(sheetName);

					ArrayNode sheetData = (ArrayNode) jsonData.get(sheetName);
					ArrayList<String> headers = new ArrayList<String>();	
					
					//Creating cell style for header to make it bold
					CellStyle headerStyle = workbook.createCellStyle();
					Font font = workbook.createFont();
					font.setBold(true);
					headerStyle.setFont(font);
					
					//creating the header into the sheet
					Row header = sheet.createRow(0);	
					Iterator<String> it = sheetData.get(0).fieldNames();
					int headerIdx = 0;
					while (it.hasNext()) {
						String headerName = it.next();
						headers.add(headerName);
						Cell cell=header.createCell(headerIdx++);
						cell.setCellValue(headerName);
						//apply the bold style to headers
						cell.setCellStyle(headerStyle);
					}
					
					//Iterating over the each row data and writing into the sheet
					for (int i = 0; i < sheetData.size(); i++) {
						ObjectNode rowData = (ObjectNode) sheetData.get(i);
						Row row = sheet.createRow(i + 1);
						for (int j = 0; j < headers.size(); j++) {
							String value = rowData.get(headers.get(j)).asText();
							row.createCell(j).setCellValue(value);
						}
					}

					/*
					 * automatic adjust data in column using autoSizeColumn, autoSizeColumn should
					 * be made after populating the data into the excel. Calling before populating
					 * data will not have any effect.
					 */
					for (int i = 0; i < headers.size(); i++) {
						sheet.autoSizeColumn(i);
					}

				}
				
				//creating a target file
				String filename = srcFile.getName();
				filename = filename.substring(0, filename.lastIndexOf(".json")) + targetFileExtension;
				File targetFile = new File(srcFile.getParent(), filename);

				// write the workbook into target file
				FileOutputStream fos = new FileOutputStream(targetFile);
				workbook.write(fos);
				
				//close the workbook and fos
				workbook.close();
				fos.close();
				return targetFile;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Main method to test this converter
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		
		JsonToExcelConverter converter = new JsonToExcelConverter();
		File srcFile = new File("E:/employee_info.json");		
		File xlsxFile = converter.jsonFileToExcelFile(srcFile, ".xlsx");
		System.out.println("Sucessfully converted JSON to Excel file at =" + xlsxFile.getAbsolutePath());

	}

}

NOTE: Find the complete maven+eclipse application for the above JSON to Excel converter from the below download link.

Test the JSON to Excel Converter using sample files

In order to test the above application, you need to have a sample JSON file with you. Here just look at the below sample JSON file and the Excel file looks after the JSON to XLSX file conversion.

Below is a JSON file that contains the employee’s information (Employee Name, Address, Email ID, and Age).

JSON to Excel - employee_info.json file

After converting JSON to Excel file, it will look as follow:

JSON to Excel - employee_info.xlsx file

JSON to excel converter online

There are multiple websites available for JSON to excel online conversion. Here we are listing a few websites to convert JSON files to Excel files online.

Conclusion

In this article, you have seen how do you Convert JSON files to Excel files in java using the Apache POI and Jackson JSON libraries. Now if you want to convert an Excel file to a JSON file, visit our other article here Convert Excel to JSON in Java [Source Code].

You can download this application from the below download link.

FAQ

How to convert JSON to xls?

You can use the above example program to convert JSON to xls. The XLS is one of the excel document formats (2003 or earlier). In this case, you can use only one maven dependency which is poi-4.0.1

How to convert JSON to xlsx?

The XLSX is another Excel file format (2007 or later). For this converter also you can use the above example, but make sure that you use both maven dependencies (poi-4.0.1 and poi-ooxml-4.0.1).

How to write JSON string to excel in java?

To write the JSON string to excel follow the below steps:
Step-1: Read JSON string and parse it to a JSON object. For example, JsonNode data = new ObjectMapper().readValue(“{\”id\”:\”101\”,\”name\”:\”ABC\”}”, JsonNode.class);

Step-2: Write the JSON object into an excel file using POI. Refer to the above example program for this.

Related Articles:

Share with friends

2 thoughts on “Convert JSON to Excel in Java [Source Code]”

  1. I am unable to convert JSON file to excel file by using this code, can please help me out.

    This is the error i am getting.
    **************************************
    java.lang.ClassCastException: class com.fasterxml.jackson.databind.node.ObjectNode cannot be cast to class com.fasterxml.jackson.databind.node.ArrayNode (com.fasterxml.jackson.databind.node.ObjectNode and com.fasterxml.jackson.databind.node.ArrayNode are in unnamed module of loader ‘app’)
    at API_Package.JsonToExcelConverter.jsonFileToExcelFile(JsonToExcelConverter.java:57)
    at API_Package.JsonToExcelConverter.main(JsonToExcelConverter.java:124)
    Exception in thread “main” java.lang.NullPointerException: Cannot invoke “java.io.File.getAbsolutePath()” because “xlsxFile” is null at API_Package.JsonToExcelConverter.main(JsonToExcelConverter.java:125)
    **********************************************************

    1. java.lang.ClassCastException: class com.fasterxml.jackson.databind.node.ObjectNode cannot be cast to class com.fasterxml.jackson.databind.node.ArrayNode (com.fasterxml.jackson.databind.node.ObjectNode and com.fasterxml.jackson.databind.node.ArrayNode are in unnamed module of loader ‘app’)
      at cnsResources.JsonToExcelConverter.jsonFileToExcelFile(JsonToExcelConverter.java:63)
      at cnsResources.JsonToExcelConverter.main(JsonToExcelConverter.java:135)
      Exception in thread “main” java.lang.NullPointerException: Cannot invoke “java.io.File.getAbsolutePath()” because “xlsxFile” is null
      at cnsResources.JsonToExcelConverter.main(JsonToExcelConverter.java:136)

Leave a Comment

Your email address will not be published. Required fields are marked *