How to Convert Excel to JSON in Java

1. Introduction

Converting Excel data to JSON format is common in many Java applications, especially when dealing with data interchange between different systems.

In this tutorial, we’ll explore two ways to convert Excel files to JSON in Java.

2. Using Apache POI Library with JSON

Apache POI is a popular Java library for reading and writing Microsoft Office file formats, including Excel. Therefore, we can use POI to read Excel files and convert the data to JSON format.

2.1. Adding Apache POI and JSON Dependencies

First, we need to add the Apache POI and JSON dependencies to our project. If we’re using Maven, include the following dependencies in our pom.xml:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>

<dependency>
    <groupId>org.json</groupId>
    <artifactId>json</artifactId>
    <version>20230227</version>
</dependency>

2.2. Reading Excel Data and Converting to JSON

Here’s a sample Java code demonstrating how to read an Excel file using Apache POI and convert its data to JSON:

JSONArray jsonArray = new JSONArray();
public String expectedJson = "[[\"C1\",\"C2\",\"C3\",\"C4\",\"C5\"]," +
  "[\"1.0\",\"2.0\",\"3.0\",\"4.0\",\"5.0\"]," +
  "[\"1.0\",\"2.0\",\"3.0\",\"4.0\",\"5.0\"]," +
  "[\"1.0\",\"2.0\",\"3.0\",\"4.0\",\"5.0\"]," +
  "[\"1.0\",\"2.0\",\"3.0\",\"4.0\",\"5.0\"]]";
private Workbook workbook;
private Sheet sheet;
private InputStream inputStream;

public ExcelToJsonUnitTest() throws IOException {
    inputStream = new FileInputStream(filePath);
    workbook = new XSSFWorkbook(inputStream);
    sheet = workbook.getSheetAt(0);
}

Converting Excel to JSON begins with initializing an InputStream that takes the filePath as a parameter to read the Excel file.

Then, we load this file into a Workbook object, explicitly utilizing the XSSFWorkbook implementation for .xlsx files. With the workbook variable in hand, the desired sheet can be accessed via the getSheetAt(0) method, assuming it’s the first sheet.

Now, let’s process the Excel data by iterating through each row and cell within the sheet using Apache POI functionalities:

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

Initially, we retrieve the headerRow of the Excel sheet using sheet.getRow(0) and iterate through each cell in the header row with the headerRow.cellIterator() method. For each cell, we extract its content as a string using the cell.toString() method and store it in the jsonArray list. This process ensures that we capture all the header values accurately.

Subsequently, we’ll iterate through each row of the Excel sheet (excluding the header row) using a for loop:

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);
}

Here, we retrieve each row with sheet.getRow(i). Moreover, we iterate through each cell in the current row and add its content to the rowData. This list, representing rows in the Excel file, is then appended to a JSONArray using jsonArray.put().

assertEquals(expectedJson, jsonArray.toString());

Finally, we assert its equality with the expected JSON string using assertEquals().

3. Using Apache POI Library with Jackson

Jackson is a popular Java library for JSON processing. It provides powerful data-binding features for converting Java objects to JSON and vice versa.

3.1. Adding Jackson Dependencies

We first start by adding the following dependency to our pom.xml:

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

3.2. Reading Excel Data and Converting to JSON

The conversion process here differs because it focuses on structuring the Excel data into Java objects before serializing to JSON. Jackson’s ObjectMapper class is pivotal here, as it handles the conversion of Java objects to JSON strings effortlessly:

@Test
public void givenExcelFile_whenUsingJacksonConversion_thenConvertToJson() throws JsonProcessingException {
    List<List<String>> data = new ArrayList<>();

    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);
    }

    ObjectMapper objectMapper = new ObjectMapper();
    String json = objectMapper.writeValueAsString(data);

    assertEquals(expectedJson, json);
}

Here, we initialize an empty list of data named data to hold the Excel data in a structured manner. It then iterates through each row of the Excel sheet, converting the cell values to strings and storing them in the data list. After collecting all the data, we utilize Jackson’s ObjectMapper to convert the structured list into a JSON string using the writeValueAsString() method.

Jackson’s strength lies in its robust data-binding capabilities, making it ideal for handling complex object structures and providing a high level of abstraction.

4. Conclusion

In this article, we discussed two methods for converting Excel files to JSON format in Java: reading and processing Excel data with Apache POI and then converting it to JSON using the JSON and Jackson libraries.

Both libraries provide convenient ways to read Excel files and manipulate their data, allowing us to seamlessly convert Excel data into JSON objects for further processing in our Java applications.