Convert Excel to JSON in Java [Source Code]

This article shows you how can you convert an Excel file to JSON using Apache POI. The Apache POI is a pure java library to work with various Microsoft documents. Today we will show you the Excel to JSON Converter in java by using the POI and the Jackson JSON library. If you want to do this conversion without using Apache poi, read this article: Convert Excel file data to JSON in Javascript.

How to convert excel to json in java?

To convert excel files to JSON, we will use the below two Java libraries here:

  1. Apache POI – to read the excel file data.
  2. Jackson JSON – to parse the excel file data into JSON 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>

Excel to json converter example in Java

Here we will see an example to convert an excel file to JSON object in java using the 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.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.JsonNode;
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 ExcelToJSONConverter {

	private ObjectMapper mapper = new ObjectMapper();

	/**
	 * Method to convert excel sheet data to JSON format
	 * 
	 * @param excel
	 * @return
	 */
	public JsonNode excelToJson(File excel) {
		// hold the excel data sheet wise
		ObjectNode excelData = mapper.createObjectNode();
		FileInputStream fis = null;
		Workbook workbook = null;
		try {
			// Creating file input stream
			fis = new FileInputStream(excel);

			String filename = excel.getName().toLowerCase();
			if (filename.endsWith(".xls") || filename.endsWith(".xlsx")) {
				// creating workbook object based on excel file format
				if (filename.endsWith(".xls")) {
					workbook = new HSSFWorkbook(fis);
				} else {
					workbook = new XSSFWorkbook(fis);
				}

				// Reading each sheet one by one
				for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
					Sheet sheet = workbook.getSheetAt(i);
					String sheetName = sheet.getSheetName();

					List<String> headers = new ArrayList<String>();
					ArrayNode sheetData = mapper.createArrayNode();
					// Reading each row of the sheet
					for (int j = 0; j <= sheet.getLastRowNum(); j++) {
						Row row = sheet.getRow(j);
						if (j == 0) {
							// reading sheet header's name
							for (int k = 0; k < row.getLastCellNum(); k++) {
								headers.add(row.getCell(k).getStringCellValue());
							}
						} else {
							// reading work sheet data
							ObjectNode rowData = mapper.createObjectNode();
							for (int k = 0; k < headers.size(); k++) {
								Cell cell = row.getCell(k);
								String headerName = headers.get(k);
								if (cell != null) {
									switch (cell.getCellType()) {
									case FORMULA:
										rowData.put(headerName, cell.getCellFormula());
										break;
									case BOOLEAN:
										rowData.put(headerName, cell.getBooleanCellValue());
										break;
									case NUMERIC:
										rowData.put(headerName, cell.getNumericCellValue());
										break;
									case BLANK:
										rowData.put(headerName, "");
										break;
									default:
										rowData.put(headerName, cell.getStringCellValue());
										break;
									}
								} else {
									rowData.put(headerName, "");
								}
							}
							sheetData.add(rowData);
						}
					}
					excelData.set(sheetName, sheetData);
				}
				return excelData;
			} else {
				throw new IllegalArgumentException("File format not supported.");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (fis != null) {
				try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

		}
		return null;
	}

	/**
	 * Main method to test this converter
	 * 
	 * @param args
	 */
	public static void main(String[] args) {

		// Creating a file object with specific file path
		File excel = new File("E:/Excel/employee.xlsx");
		ExcelToJSONConverter converter = new ExcelToJSONConverter();
		JsonNode data = converter.excelToJson(excel);
		System.out.println("Excel file contains the Data:\n" + data);

	}
}

How to write JSON data into text file in java?

Now, if you want to write the converted JSON object into a file, then you can use the below method:

/**
	 * Method to write the json data into file
	 * 
	 * @param jsonFile
	 * @param jsonData
	 */
	public boolean writeJsonToFile(File jsonFile, JsonNode jsonData) {
		try {
			if (jsonFile.getName().endsWith(".json")) {
				if (!jsonFile.exists()) {
					jsonFile.createNewFile();
				}
				FileWriter fw = new FileWriter(jsonFile);
				fw.write(jsonData.toPrettyString());
				fw.close();
				return true;
			} else {
				throw new IllegalArgumentException("File should be .json file only");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

Conclusion

In this article, you have seen how do you Convert Excel files to JSON in java using the Apache POI and Jackson JSON libraries. If you want to do this in javascript, visit another article here: Convert Excel file data to JSON in Javascript.

You can download the above example (maven application zip) from the below download link.

FAQ

How to convert xls to JSON?

You can use the above example program to convert xls to JSON. In this case, you can use only one maven dependency which is poi-4.0.1

How to convert xlsx to JSON?

For this converter also you can use the above example program, but make sure that you use both maven dependencies (poi-4.0.1 and poi-ooxml-4.0.1).

Related Articles:

Share with friends

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

  1. Hello i need to combine the 2 codes so when i read from excel and covert it to json object, after that i want to generate json file from this object.
    Can you help me?

Leave a Comment

Your email address will not be published.