Of course! Converting Excel files to JSON is a common task in Java. The best way to do this is by using a dedicated library to handle the complexities of reading the Excel format.

Here’s a complete guide covering:

1. The Recommended Approach: Using the popular and easy-to-use Apache POI library.
2. A Step-by-Step Guide with a complete, runnable example.
3. Handling Complex Scenarios like sheets with multiple data structures.
4. Alternative Libraries (like EasyExcel) for different use cases.

Recommended Approach: Apache POI

Apache POI is the most well-known Java library for manipulating various file formats based upon Microsoft's OLE 2 Compound Document Format (OLE2), including Excel (.xls and .xlsx).

Step 1: Add Dependencies

You need to add the Apache POI and a JSON library (like Jackson or Gson) to your project.

Using Maven (pom.xml):

<!-- For reading .xlsx files -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version> <!-- Use the latest version -->
</dependency>
<!-- For reading .xls files (the legacy format) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version> <!-- Use the latest version -->
</dependency>
<!-- For converting Java objects to JSON (Jackson is very common) -->
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.15.2</version> <!-- Use the latest version -->
</dependency>

Using Gradle (build.gradle):

// For reading .xlsx files
implementation 'org.apache.poi:poi-ooxml:5.2.5' // Use the latest version
// For reading .xls files (the legacy format)
implementation 'org.apache.poi:poi:5.2.5' // Use the latest version
// For converting Java objects to JSON (Jackson is very common)
implementation 'com.fasterxml.jackson.core:jackson-databind:2.15.2' // Use the latest version

Step 2: Prepare Your Excel File

Let's assume you have an Excel file named data.xlsx with a single sheet named "Users". The first row contains the headers, and subsequent rows contain the data.

data.xlsx Content:

Step 3: Write the Java Code

The logic is as follows:

1. Load the Excel file.
2. Get the first sheet.
3. Read the first row to get the JSON keys (headers).
4. Iterate through the remaining rows.
5. For each row, create a Map where the key is the header and the value is the cell's content.
6. Add each Map (representing one row) to a List.
7. Use Jackson to convert the List of Maps into a JSON string.

Here is the complete Java code:

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.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) {
        // Path to the Excel file
        String excelFilePath = "path/to/your/data.xlsx";
        // Path to the output JSON file
        String jsonFilePath = "output.json";
        try {
            // 1. Load the Excel file
            FileInputStream fis = new FileInputStream(new File(excelFilePath));
            Workbook workbook = new XSSFWorkbook(fis); // Use XSSFWorkbook for .xlsx, HSSFWorkbook for .xls
            // 2. Get the first sheet
            Sheet sheet = workbook.getSheetAt(0); // or getSheet("Users")
            // 3. Get the first row to use as headers (JSON keys)
            Row headerRow = sheet.getRow(0);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }
            // 4. Iterate through 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; // Skip empty rows
                }
                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. Close the workbook and input stream
            workbook.close();
            fis.close();
            // 6. Convert the List of Maps to JSON using Jackson
            ObjectMapper objectMapper = new ObjectMapper();
            String jsonString = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(jsonData);
            // 7. Write the JSON string to a file (or print it to console)
            System.out.println(jsonString);
            // Files.write(Paths.get(jsonFilePath), jsonString.getBytes());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * Helper method to get the cell value as a String, handling different cell types.
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // Handle numbers, including integers and doubles
                    double numericValue = cell.getNumericCellValue();
                    // Check if it's an integer (e.g., 30.0) or a double (e.g., 30.5)
                    if (numericValue == (long) numericValue) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                // Evaluate the formula and get the resulting value
                return getCellValueAsString(cell.getCachedFormulaResultType() == CellType.STRING ? cell.getRichStringCellValue() : cell.getNumericCellValue());
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}

Expected JSON Output

Running the code above with the sample data.xlsx will produce the following JSON:

[
  {
    "Name" : "Alice",
    "Email" : "alice@example.com",
    "Age" : "30",
    "Is Active" : "true"
  },
  {
    "Name" : "Bob",
    "Email" : "bob@example.com",
    "Age" : "24",
    "Is Active" : "false"
  },
  {
    "Name" : "Charlie",
    "Email" : "charlie@example.com",
    "Age" : "35",
    "Is Active" : "true"
  }
]

Handling Complex Scenarios

Converting Data to Proper Types (e.g., int, boolean)

The simple example above converts everything to a String. You often want to preserve the original data types. Here's how you can modify the getCellValueAsString method to return Objects of the correct type.

// In your main loop, instead of getCellValueAsString(cell)
Object cellValue = getCellValueAsObject(cell);
rowMap.put(header, cellValue);
// New helper method
private static Object getCellValueAsObject(Cell cell) {
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
        case STRING:
            return cell.getStringCellValue();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue(); // Return a Date object
            } else {
                // Return Double, which can be cast to int later if needed
                return cell.getNumericCellValue();
            }
        case BOOLEAN:
            return cell.getBooleanCellValue();
        case BLANK:
            return null;
        case FORMULA:
            // Evaluate the formula
            return getCellValueAsObject(cell.getCachedFormulaResultType() == CellType.STRING ? cell.getRichStringCellValue() : cell.getNumericCellValue());
        default:
            return null;
    }
}

Now, the JSON output will have correct types:

[
  {
    "Name" : "Alice",
    "Email" : "alice@example.com",
    "Age" : 30.0, // or 30 if you cast to int
    "Is Active" : true
  },
  // ...
]

Mapping to a POJO (Plain Old Java Object)

For a more structured and type-safe application, it's better to map the Excel rows to custom Java objects (POJOs).

1. Create a POJO:

   public class User {
       private String name;
       private String email;
       private int age;
       private boolean active;
       // Getters and Setters (and optionally a constructor)
       public String getName() { return name; }
       public void setName(String name) { this.name = name; }
       public String getEmail() { return email; }
       public void setEmail(String email) { this.email = email; }
       public int getAge() { return age; }
       public void setAge(int age) { this.age = age; }
       public boolean isActive() { return active; }
       public void setActive(boolean active) { this.active = active; }
       @Override
       public String toString() {
           return "User{" +
                   "name='" + name + '\'' +
                   ", email='" + email + '\'' +
                   ", age=" + age +
                   ", active=" + active +
                   '}';
       }
   }

2. Modify the conversion logic:

   // Inside the main loop
   List<User> users = new ArrayList<>();
   for (int i = 1; i <= sheet.getLastRowNum(); i++) {
       Row currentRow = sheet.getRow(i);
       if (currentRow == null) continue;
       User user = new User();
       // Set properties by column index or name
       user.setName(getCellValueAsString(currentRow.getCell(0)));
       user.setEmail(getCellValueAsString(currentRow.getCell(1)));
       user.setAge((int) currentRow.getCell(2).getNumericCellValue()); // Cast to int
       user.setActive(currentRow.getCell(3).getBooleanCellValue());
       users.add(user);
   }
   // Convert List<User> to JSON
   ObjectMapper objectMapper = new ObjectMapper();
   String jsonString = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(users);
   System.out.println(jsonString);

Alternative: EasyExcel (by Alibaba)

If you are dealing with very large Excel files (hundreds of thousands or millions of rows), Apache POI can consume a lot of memory because it loads the entire file into memory.

EasyExcel is a powerful library from Alibaba that uses the SAX (Simple API for XML) mode to read files row by row, making it extremely memory-efficient.

Step 1: Add EasyExcel Dependency

Maven:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version> <!-- Use the latest version -->
</dependency>

Step 2: Write the Code (Event Model)

EasyExcel works by reading a file and calling a "listener" for each row.

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class EasyExcelConverter {
    public static void main(String[] args) {
        String excelFilePath = "path/to/your/data.xlsx";
        // 1. Create a listener to handle data as it's read
        ExcelDataListener listener = new ExcelDataListener();
        // 2. Read the Excel file. The listener will be called for each row.
        EasyExcel.read(excelFilePath, Map.class, listener).sheet().doRead();
        // 3. Get the data from the listener
        List<Map<Integer, String>> data = listener.getData();
        // 4. Convert to JSON (You'll need to map column indices to headers)
        // This part requires extra logic to convert index-based map to key-based map
        // For simplicity, let's assume the first row is the header.
        // A more robust implementation would read the header first.
        ObjectMapper objectMapper = new ObjectMapper();
        try {
            String jsonString = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(data);
            System.out.println(jsonString);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // Custom listener to handle each row
    public static class ExcelDataListener extends AnalysisEventListener<Map<Integer, String>> {
        private List<Map<Integer, String>> data = new ArrayList<>();
        @Override
        public void invoke(Map<Integer, String> row, AnalysisContext context) {
            // This method is called for each row
            data.add(new HashMap<>(row));
        }
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // This method is called after all rows are read
            System.out.println("Excel file read completely!");
        }
        public List<Map<Integer, String>> getData() {
            return data;
        }
    }
}

Pros of EasyExcel:

• Low Memory Usage: Ideal for huge files.
• High Performance: Faster than POI for large datasets.

Cons of EasyExcel:

• Steeper Learning Curve: The event-driven model is different from POI's object model.
• Less Flexible for Complex Structures: It's best suited for simple, row-by-row data conversion.

Summary: Which One to Choose?

For most projects, start with Apache POI. It's the standard, well-documented, and easy to use. Only switch to EasyExcel if you encounter performance or memory issues with large files.