How to Convert Excel to JSON in Java

1. Introduction

When Java applications deal with data interchange between different systems, it’s common to convert Excel to JSON. Apache POI (Poor Obfuscation Implementation) is an open-source Java API library developed by the Apache Software Foundation for Microsoft Office formats. It enables Java applications to create, modify, and read MS Office documents programmatically. It supports both old binary formats (XLS, DOC, PPT) and newer XML-based formats (XLSX, DOCX, PPTX).

In this tutorial, I’ll demonstrate four ways to convert Excel to JSON in Java via the Apache POI library.

• Convert Excel data to JSON as JSON String.
• Convert Excel data to JSON as List<List<String>>.
• Convert Excel data to JSON as JSONArray of String.
• Convert Excel data to JSON as JSONArray of JSONObject.

2. Maven Project

Create a maven project which includes Apache POI, Jackson, and JSON library.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.zheng.demo</groupId>
	<artifactId>json-excel-conversion</artifactId>
	<name>json-excel-conversion</name>
	<version>1.0.0-SNAPSHOT</version>

	<dependencies>
		<dependency>
			<groupId>org.json</groupId>
			<artifactId>json</artifactId>
			<version>20240303</version>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.17.0</version>
		</dependency>
		
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.5</version>
		</dependency>

		<dependency>
			<groupId>org.junit.jupiter</groupId>
			<artifactId>junit-jupiter-api</artifactId>
			<version>5.8.1</version>
			<scope>test</scope>
		</dependency>
	</dependencies>


</project>

• line 14: gson library is added.
• line 20: Jackson-databind library is added.
• line 26: Apache poi-ooxml library is added.

3. Using Apache POI Library with JSON

3.1 Excel to Json Based Class

In this step, I will create an abstract base class: ConvertExcelToJsonBase. This class has three data members and two methods:

• inputStream: the inputStream is created from FileInputStream.
• workbook: the workbook is created from inputStream via XSSFWorkbook.
• sheet: the sheet is created from the workbook‘s first sheet.
• initialize(): initialize the data members.
• closeResource(): close the workbook and inputStream.

ConvertExcelToJsonBase.java

package com.zheng.demo;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public abstract class ConvertExcelToJsonBase {

	protected InputStream inputStream;
	protected Sheet sheet;
	protected Workbook workbook;

	public ConvertExcelToJsonBase() {
		super();
	}

	protected void closeResource() {
		try {
			workbook.close();
			inputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	protected void initialize(final String excelFileName) throws IOException {
		inputStream = new FileInputStream(excelFileName);
		workbook = new XSSFWorkbook(inputStream);
		sheet = workbook.getSheetAt(0);
	}

}

• line 32: assign the workbook variable from XSSFWorkbook(inputStream).
• line 33: assign the sheet variable from workbook.getSheetAt(0) as the book1.xlsx only has one worksheet.

3.2 Convert Excel To JSONArray

In this step, I will create a ConvertExcelToJSONArray class which converts an excel workbook named book1.xlsx to JSONArray. It extends the based class ConvertExcelToJsonBase created at step 3.1 and has two methods:

• convertExceltoJSONArray_withObject – convert the excel data as JSONArray of JSONObject.
• convertExceltoJSONArray_withValueOnly – convert the excel data as JSONArray of List<String>.

ConvertExcelToJSONArray.java

package com.zheng.demo;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.json.JSONArray;
import org.json.JSONObject;

public class ConvertExcelToJSONArray extends ConvertExcelToJsonBase {

	public JSONArray convertExceltoJSONArray_withObject(final String excelFileName) {
		JSONArray jsonArray = new JSONArray();
		try {
			initialize(excelFileName);

			String[] headers = new String[sheet.getRow(0).getLastCellNum()];

			Iterator<Row> iterator = sheet.iterator();

			while (iterator.hasNext()) {
				Row currentRow = iterator.next();
				if (currentRow.getRowNum() == 0) {
					for (int i = 0; i < currentRow.getLastCellNum(); i++) {
						headers[i] = currentRow.getCell(i).getStringCellValue();
					}
				} else {
					JSONObject obj = new JSONObject();
					for (int i = 0; i < currentRow.getLastCellNum(); i++) {
						Cell currentCell = currentRow.getCell(i);
						if (currentCell.getCellType() == CellType.STRING) {
							obj.put(headers[i], currentCell.getStringCellValue());
						} else if (currentCell.getCellType() == CellType.NUMERIC) {
							obj.put(headers[i], currentCell.getNumericCellValue());
						}
					}
					jsonArray.put(obj);
				}
			}

		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			closeResource();

		}

		return jsonArray;
	}

	public JSONArray convertExceltoJSONArray_withValueOnly(final String excelFileName) {
		JSONArray jsonArray = new JSONArray();
		try {
			initialize(excelFileName);

			Row headerRow = sheet.getRow(0);
			List<String> headers = new ArrayList<>();
			for (Cell cell : headerRow) {
				headers.add(cell.getStringCellValue());
			}
			jsonArray.put(headers);

			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				List<String> rowData = new ArrayList<>();
				for (Cell cell : row) {
					rowData.add(cell.toString());
				}
				jsonArray.put(rowData);
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			closeResource();
		}

		return jsonArray;
	}

}

• line 21: create a String array headers. The array size is based on the first row’s last cell number. The value of sheet.getRow(0).getLastCellNum() is 5 for book1.xlsx.
• line 23: create an iterator to loop the sheet‘s each row.
• line 27: treat the first row as the header row.
• line 29: save the first row data in headers.
• line 35: verify if the excel cell type is the STRING type.
• line 36: JSONObject key is the header, the value is the cell’s String value.
• line 37: verify if the excel cell type is the NUMERIC type.
• line 38: JSONObject key is the header, the value is the cell’s numeric value.
• line 63: use cell.getStringCellValue() to store headers as they are text.
• line 71: use cell.toString() for non-header data.

3.3 Demonstrate with Junit Tests

I will create a ConvertExcelToJSONArrayTest class which converts an excel file named book1.xlsx to Json. The book1.xlsx excel has three rows and five columns..

ConvertExcelToJSONArrayTest.java

package com.zheng.demo;

import static org.junit.jupiter.api.Assertions.assertEquals;

import java.util.Objects;

import org.json.JSONArray;
import org.json.JSONObject;
import org.junit.jupiter.api.Test;

class ConvertExcelToJSONArrayTest {

	public static String filePath = Objects
			.requireNonNull(ConvertExcelToJSONArrayTest.class.getClassLoader().getResource("book1.xlsx")).getFile();
	private ConvertExcelToJSONArray testClass = new ConvertExcelToJSONArray();

	@Test
	void test_convertExceltoJSONArray() {
		JSONArray convertedData = testClass.convertExceltoJSONArray_withValueOnly(filePath);
		assertEquals(4, convertedData.length());

		assertEquals("[\"header1\",\"header2\",\"header3\",\"stringHeader\",\"numberHeader\"]",
				convertedData.get(0).toString());
		assertEquals("[\"1.0\",\"2.0\",\"3.0\",\"test\",\"12.5\"]", convertedData.get(1).toString());
		assertEquals("[\"4.0\",\"5.0\",\"6.0\",\"mary\",\"99.99\"]", convertedData.get(2).toString());
		assertEquals("[\"7.0\",\"8.0\",\"9.0\",\"John\",\"100000.0\"]", convertedData.get(3).toString());
	}

	@Test
	void test_convertExceltoJSONArray_withObject() {
		JSONArray convertedData = testClass.convertExceltoJSONArray_withObject(filePath);
		assertEquals(3, convertedData.length());

		assertEquals(1.0, ((JSONObject) convertedData.get(0)).get("header1"));
		assertEquals(2.0, ((JSONObject) convertedData.get(0)).get("header2"));
		assertEquals(3.0, ((JSONObject) convertedData.get(0)).get("header3"));
		assertEquals("test", ((JSONObject) convertedData.get(0)).get("stringHeader"));
		assertEquals(12.5, ((JSONObject) convertedData.get(0)).get("numberHeader"));

		assertEquals(7.0, ((JSONObject) convertedData.get(2)).get("header1"));
		assertEquals(8.0, ((JSONObject) convertedData.get(2)).get("header2"));
		assertEquals(9.0, ((JSONObject) convertedData.get(2)).get("header3"));
		assertEquals("John", ((JSONObject) convertedData.get(2)).get("stringHeader"));
		assertEquals(100000.0, ((JSONObject) convertedData.get(2)).get("numberHeader"));

	}

}

• line 20, the converted JSONArray has 4 rows, the first row is the header.
• line 32, the converted JSONArray has 3 rows, the header becomes the JSONObject’s key.

4. Using Apache POI Library with Jackson

In this step, I will convert Excel data into Json String or Java POJO via Jackson library.

4.1 Add Jackson Dependency

update pom.xml to include Jackson dependency.

Dependency in pom.xml

<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.17.0</version>
		</dependency>

4.2 Convert Excel to Json String

In this step, I will create the ConvertExcelToJson class which converts Excel data to a Json string or POJO. It extends the based class ConvertExcelToJsonBase created at step 3.1 and has two methods:

• convertExceltoJsonString – this method invokes the convertExceltoListofString, and converts to Json via ObjectMapper.
• convertExceltoListofString – this method reads excel data and converts it to List<List<String>>

ConvertExcelToJson.java

package com.zheng.demo;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class ConvertExcelToJson extends ConvertExcelToJsonBase {

	public String convertExceltoJsonString(final String excelFileName) {
		ObjectMapper objectMapper = new ObjectMapper();
		List<List<String>> data = convertExceltoListofString(excelFileName);
		String json = null;
		try {
			json = objectMapper.writeValueAsString(data);
		} catch (JsonProcessingException e) {
			e.printStackTrace();
		}
		return json;
	}

	public List<List<String>> convertExceltoListofString(final String excelFileName) {
		List<List<String>> data = new ArrayList<>();

		try {
			initialize(excelFileName);
			Row headerRow = sheet.getRow(0);
			List<String> headers = new ArrayList<>();
			for (Cell cell : headerRow) {
				headers.add(cell.toString());
			}
			data.add(headers);

			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				List<String> rowData = new ArrayList<>();
				for (Cell cell : row) {
					rowData.add(cell.toString());
				}
				data.add(rowData);
			}

		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			closeResource();

		}

		return data;

	}

}

• line 16, create an ObjectMapper instance from Jackson library.
• line 20, convert the POJO to Json string via objectMapper.writeValueAsString(data).

4.3 Demonstrate with Junit Tests

Create a junit test class ConvertExcelToJsonTest which reads the data from book1.xlsx and converts it to Json. It has two test cases:

• test_convertExceltoJsonString, this test method reads the data from book1.xlsx and converts it to a Json formatted string.
• test_convertExceltoListofString, this test method reads the data from book1.xlsx and converts it to a List of List<String>.

ConvertExcelToJsonTest

package com.zheng.demo;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import java.util.List;
import java.util.Objects;

import org.junit.jupiter.api.Test;

class ConvertExcelToJsonTest {

	public static String filePath = Objects
			.requireNonNull(ConvertExcelToJsonTest.class.getClassLoader().getResource("book1.xlsx")).getFile();
	private ConvertExcelToJson testClass = new ConvertExcelToJson();

	@Test
	void test_convertExceltoJsonString() {

		String convertedData = testClass.convertExceltoJsonString(filePath);
		assertTrue(convertedData.contains("[\"header1\",\"header2\",\"header3\",\"stringHeader\",\"numberHeader\"]"));
	}

	@Test
	void test_convertExceltoListofString() {

		List<List> convertedData = testClass.convertExceltoListofString(filePath);
		assertEquals(4, convertedData.size());
		// header row
		assertEquals("header1", convertedData.get(0).get(0));
		assertEquals("header2", convertedData.get(0).get(1));
		assertEquals("header3", convertedData.get(0).get(2));
		assertEquals("stringHeader", convertedData.get(0).get(3));
		assertEquals("numberHeader", convertedData.get(0).get(4));

		// data row 1
		assertEquals("1.0", convertedData.get(1).get(0));
		assertEquals("2.0", convertedData.get(1).get(1));
		assertEquals("3.0", convertedData.get(1).get(2));
		assertEquals("test", convertedData.get(1).get(3));
		assertEquals("12.5", convertedData.get(1).get(4));

		// data row 2
		assertEquals("4.0", convertedData.get(2).get(0));
		assertEquals("5.0", convertedData.get(2).get(1));
		assertEquals("6.0", convertedData.get(2).get(2));
		assertEquals("mary", convertedData.get(2).get(3));
		assertEquals("99.99", convertedData.get(2).get(4));

		// data row 3
		assertEquals("7.0", convertedData.get(3).get(0));
		assertEquals("8.0", convertedData.get(3).get(1));
		assertEquals("9.0", convertedData.get(3).get(2));
		assertEquals("John", convertedData.get(3).get(3));
		assertEquals("100000.0", convertedData.get(3).get(4));

	}

}

As you seen in test_convertExceltoJsonString(), here is the converted Json string from the book1.xlsx.

converted Json from book1.xlsx

[
["header1","header2","header3","stringHeader","numberHeader"],
["1.0","2.0","3.0","test","12.5"],
["4.0","5.0","6.0","mary","99.99"],
["7.0","8.0","9.0","John","100000.0"]
]

Executed the Junit tests and all passed.

5. Conclusion

In this article, I demonstrated four ways of Java Excel JSON conversion. I created three java classes and two Junit test classes to read Excel files and convert data into JSON objects via Apache POI, GSON, and Jackson API libraries.

6. Download

You can download the source code for this maven project.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.