Of course! The combination of Java, JSON, and Excel is extremely common in enterprise applications, data processing, and reporting. Here’s a comprehensive guide covering the most popular and effective ways to work with these three technologies.

We'll break it down into three main scenarios:

1. Reading from Excel and converting to JSON
2. Reading from JSON and writing to Excel
3. Advanced: Working with JSON data inside Excel cells

For these tasks, we'll use two excellent, battle-tested libraries:

• Apache POI: The de-facto standard for reading and writing Microsoft Office formats (like .xls and .xlsx) in Java.
• Jackson: The most popular library for processing JSON in Java. It's used by the Spring Framework and many other projects.

Step 1: Add Dependencies

First, you need to add the required libraries to your project. Here are the Maven dependencies.

For Maven (pom.xml):

<!-- For Excel (XLSX and XLS) support -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>
<!-- For JSON support -->
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.15.2</version>
</dependency>

For Gradle (build.gradle):

// For Excel (XLSX and XLS) support
implementation 'org.apache.poi:poi:5.2.5'
implementation 'org.apache.poi:poi-ooxml:5.2.5'
// For JSON support
implementation 'com.fasterxml.jackson.core:jackson-databind:2.15.2'

Scenario 1: Reading from Excel and Converting to JSON

Let's say you have an Excel file named data.xlsx with the following content:

Our goal is to convert this into a JSON array of objects.

The Java Code:

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelToJsonConverter {
    public static void main(String[] args) {
        String excelFilePath = "data.xlsx";
        String jsonFilePath = "output.json";
        try {
            // 1. Setup Jackson ObjectMapper
            ObjectMapper objectMapper = new ObjectMapper();
            // 2. Read the Excel file
            FileInputStream fis = new FileInputStream(excelFilePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0); // Get the first sheet
            // 3. Get the header row (assumes first row is the header)
            Row headerRow = sheet.getRow(0);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }
            // 4. Iterate over the rest of the rows
            List<Map<String, Object>> jsonData = new ArrayList<>();
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row currentRow = sheet.getRow(i);
                if (currentRow == null) continue;
                Map<String, Object> rowMap = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = currentRow.getCell(j);
                    String header = headers.get(j);
                    // Handle different cell types
                    Object cellValue = getCellValueAsString(cell);
                    rowMap.put(header, cellValue);
                }
                jsonData.add(rowMap);
            }
            // 5. Write the JSON data to a file
            objectMapper.writerWithDefaultPrettyPrinter().writeValue(new java.io.File(jsonFilePath), jsonData);
            System.out.println("Successfully converted Excel to JSON at: " + jsonFilePath);
            workbook.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * Helper method to convert any Cell type to a String.
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                // Check if it's an integer or a double
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    double num = cell.getNumericCellValue();
                    if (num == (long) num) {
                        return String.valueOf((long) num);
                    } else {
                        return String.valueOf(num);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}

The Generated JSON (output.json):

[
  {
    "id" : "1",
    "name" : "Alice",
    "email" : "alice@example.com",
    "role" : "Developer"
  },
  {
    "id" : "2",
    "name" : "Bob",
    "email" : "bob@example.com",
    "role" : "Designer"
  },
  {
    "id" : "3",
    "name" : "Charlie",
    "email" : "charlie@example.com",
    "role" : "Manager"
  }
]

Scenario 2: Reading from JSON and Writing to Excel

Now, let's do the reverse. We'll read the output.json file we just created and generate an Excel file named generated_data.xlsx.

The Java Code:

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
public class JsonToExcelConverter {
    public static void main(String[] args) {
        String jsonFilePath = "output.json";
        String excelFilePath = "generated_data.xlsx";
        try {
            // 1. Setup Jackson ObjectMapper
            ObjectMapper objectMapper = new ObjectMapper();
            // 2. Read JSON file into a List of Maps
            List<Map<String, Object>> jsonData = objectMapper.readValue(
                new File(jsonFilePath),
                new TypeReference<List<Map<String, Object>>>() {}
            );
            if (jsonData == null || jsonData.isEmpty()) {
                System.out.println("No data to write to Excel.");
                return;
            }
            // 3. Create a new Excel workbook and sheet
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Data");
            // 4. Create the header row from the keys of the first map
            Row headerRow = sheet.createRow(0);
            Map<String, Object> firstRow = jsonData.get(0);
            int headerColNum = 0;
            for (String key : firstRow.keySet()) {
                Cell cell = headerRow.createCell(headerColNum++);
                cell.setCellValue(key);
            }
            // 5. Create data rows
            int rowNum = 1;
            for (Map<String, Object> rowData : jsonData) {
                Row currentRow = sheet.createRow(rowNum++);
                int colNum = 0;
                for (Object value : rowData.values()) {
                    Cell cell = currentRow.createCell(colNum++);
                    if (value instanceof String) {
                        cell.setCellValue((String) value);
                    } else if (value instanceof Number) {
                        cell.setCellValue(((Number) value).doubleValue());
                    } else if (value instanceof Boolean) {
                        cell.setCellValue((Boolean) value);
                    } else {
                        cell.setCellValue(value != null ? value.toString() : "");
                    }
                }
            }
            // 6. Auto-size columns for better readability
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                sheet.autoSizeColumn(i);
            }
            // 7. Write the workbook to a file
            try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
                workbook.write(fileOut);
            }
            System.out.println("Successfully converted JSON to Excel at: " + excelFilePath);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

This code will create a generated_data.xlsx file that is identical in structure to the original data.xlsx.

Scenario 3: Advanced - Embedding JSON in a Single Excel Cell

Sometimes, you don't want to convert the whole sheet. You might just want to store a complex JSON object inside a single cell of your Excel file.

The Java Code:

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
public class JsonInExcelCell {
    public static void main(String[] args) {
        String excelFilePath = "json_in_cell.xlsx";
        // 1. Create a complex JSON object
        Map<String, Object> complexData = new HashMap<>();
        complexData.put("userId", 123);
        complexData.put("username", "johndoe");
        complexData.put("isActive", true);
        complexData.put("profile", Map.of(
            "firstName", "John",
            "lastName", "Doe",
            "age", 30
        ));
        // 2. Convert the Java Map to a JSON String
        ObjectMapper objectMapper = new ObjectMapper();
        String jsonString = "";
        try {
            jsonString = objectMapper.writeValueAsString(complexData);
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 3. Create a new Excel workbook and sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Complex Data");
        // 4. Create a row and a cell
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        // 5. Set the JSON string as the cell's value
        cell.setCellValue(jsonString);
        // 6. Write the workbook to a file
        try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("Excel file with JSON in a cell created: " + excelFilePath);
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

The Result (json_in_cell.xlsx):

If you open json_in_cell.xlsx, cell A1 will contain the entire JSON string as plain text:

{"userId":123,"username":"johndoe","isActive":true,"profile":{"firstName":"John","lastName":"Doe","age":30}}

Summary and Best Practices

Key Takeaways:

• POI for Excel: Use Apache POI for any low-level, cell-by-cell manipulation of Excel files. It's powerful but requires you to manage details like rows, columns, and cell types.
• Jackson for JSON: Use Jackson for all JSON serialization (Java object to JSON) and deserialization (JSON to Java object). Its ObjectMapper is the central component.
• Choose the Right Approach:
  • For simple table-like data, Scenario 1 & 2 are perfect.
  • For storing complex, nested, or configuration-like data, Scenario 3 is the way to go.
  • For very large Excel files, consider using the SXSSFWorkbook from POI, which is an event-based model (like SAX for XML) that is much more memory-efficient.