Convert Excel to Json in Java – Apache Poi + Jackson

Java Convert Excel to Json

In the tutorial, I shows how to convert Excel File to JSON String or JSON File and vice versa with Java language by examples using Apache poi & Jackson.

* Technologies:
– Java
– Apache poi
– Jackson

What will we do?
1. Convert Excel File to JSON String
2. Convert Excel File to JSON File
3. Convert JSON String to Excel File
4. Convert JSON File to Excel File

Java Dependencies – Convert Excel to Json in Java

– Apache POI is the master project for developing pure Java ports of file formats based on Office Open XML (ooxml). OOXML is part of an ECMA / ISO standardisation effort.
– Jackson is a very popular and efficient java based library to serialize or map java objects to JSON and vice versa.


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

Excel File to JSON String

We do 2 steps:
– Step 1: Read Excel File into Java List Objects
– Step 2: Convert Java List Objects to JSON String

-> Excel File: customers

convert-excel-file-to-from-json-file-string---excel-file

Customer.java:

package com.loizenai.convertexcel2json;

public class Customer {
	private String id;
	private String name;
	private String address;
	private int age;
 
	public Customer() {
	}
 
	public Customer(String id, String name, String address, int age) {
		this.id = id;
		this.name = name;
		this.address = address;
		this.age = age;
	}
 
	public String getId() {
		return id;
	}
 
	public void setId(String id) {
		this.id = id;
	}
 
	public String getName() {
		return name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
 
	public String getAddress() {
		return address;
	}
 
	public void setAddress(String address) {
		this.address = address;
	}
 
	public int getAge() {
		return age;
	}
 
	public void setAge(int age) {
		this.age = age;
	}
 
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]";
	}
 
}

– Converting Implemetation:


package com.loizenai.convertexcel2json;

import java.io.File;
import java.io.FileInputStream;
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.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.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;


public class ConvertExcel2Json {
	
	public static void main(String[] args) {
		// Step 1: Read Excel File into Java List Objects
		List<Customer> customers = readExcelFile("customers.xlsx");
		
		// Step 2: Convert Java Objects to JSON String
		String jsonString = convertObjects2JsonString(customers);
		
		System.out.println(jsonString);
	}
	
	/**
	 * Read Excel File into Java List Objects
	 * 
	 * @param filePath
	 * @return
	 */
	private static List<Customer> readExcelFile(String filePath){
		try {
			FileInputStream excelFile = new FileInputStream(new File(filePath));
    		Workbook workbook = new XSSFWorkbook(excelFile);
     
    		Sheet sheet = workbook.getSheet("Customers");
    		Iterator<Row> rows = sheet.iterator();
    		
    		List<Customer> lstCustomers = new ArrayList<Customer>();
    		
    		int rowNumber = 0;
    		while (rows.hasNext()) {
    			Row currentRow = rows.next();
    			
    			// skip header
    			if(rowNumber == 0) {
    				rowNumber++;
    				continue;
    			}
    			
    			Iterator<Cell> cellsInRow = currentRow.iterator();
 
    			Customer cust = new Customer();
    			
    			int cellIndex = 0;
    			while (cellsInRow.hasNext()) {
    				Cell currentCell = cellsInRow.next();
    				
    				if(cellIndex==0) { // ID
    					cust.setId(String.valueOf(currentCell.getNumericCellValue()));
    				} else if(cellIndex==1) { // Name
    					cust.setName(currentCell.getStringCellValue());
    				} else if(cellIndex==2) { // Address
    					cust.setAddress(currentCell.getStringCellValue());
    				} else if(cellIndex==3) { // Age
    					cust.setAge((int) currentCell.getNumericCellValue());
    				}
    				
    				cellIndex++;
    			}
    			
    			lstCustomers.add(cust);
    		}
    		
    		// Close WorkBook
    		workbook.close();
    		
    		return lstCustomers;
        } catch (IOException e) {
        	throw new RuntimeException("FAIL! -> message = " + e.getMessage());
        }
	}
	
	/**
	 * Convert Java Objects to JSON String
	 * 
	 * @param customers
	 * @param fileName
	 */
	private static String convertObjects2JsonString(List<Customer> customers) {
    	ObjectMapper mapper = new ObjectMapper();
    	String jsonString = "";
    	
    	try {
    		jsonString = mapper.writeValueAsString(customers);
    	} catch (JsonProcessingException e) {
    		e.printStackTrace();
    	}
    	
    	return jsonString; 
	}
}

– Output:

[{"id":"1.0","name":"Jack Smith","address":"Massachusetts","age":23},{"id":"2.0","name":"Adam Johnson","address":"New York","age":27},{"id":"3.0","name":"Katherin Carter","address":"Washington DC","age":26},{"id":"4.0","name":"Jack London","address":"Nevada","age":33},{"id":"5.0","name":"Jason Bourne","address":"California","age":36}]

-> Pretty-Printed:

[
  {
    "id": "1.0",
    "name": "Jack Smith",
    "address": "Massachusetts",
    "age": 23
  },
  {
    "id": "2.0",
    "name": "Adam Johnson",
    "address": "New York",
    "age": 27
  },
  {
    "id": "3.0",
    "name": "Katherin Carter",
    "address": "Washington DC",
    "age": 26
  },
  {
    "id": "4.0",
    "name": "Jack London",
    "address": "Nevada",
    "age": 33
  },
  {
    "id": "5.0",
    "name": "Jason Bourne",
    "address": "California",
    "age": 36
  }
]

Excel File to JSON File

We do 2 steps:
– Step 1: Read Excel File into Java List Objects
– Step 2: Write Java List Objects to JSON File

– Implementation:


package com.loizenai.convertexcel2json;

import java.io.File;
import java.io.FileInputStream;
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.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.ObjectMapper;

public class ConvertExcel2Json {
	
	public static void main(String[] args) {
		// Step 1: Read Excel File into Java List Objects
		List<Customer> customers = readExcelFile("customers.xlsx");
		
    	// Step 2: Write Java List Objects to JSON File
    	writeObjects2JsonFile(customers, "customers.json");
    	
    	System.out.println("Done");
	}
	
	/**
	 * Read Excel File into Java List Objects
	 * 
	 * @param filePath
	 * @return
	 */
	private static List<Customer> readExcelFile(String filePath){
		try {
			FileInputStream excelFile = new FileInputStream(new File(filePath));
    		Workbook workbook = new XSSFWorkbook(excelFile);
     
    		Sheet sheet = workbook.getSheet("Customers");
    		Iterator<Row> rows = sheet.iterator();
    		
    		List<Customer> lstCustomers = new ArrayList<Customer>();
    		
    		int rowNumber = 0;
    		while (rows.hasNext()) {
    			Row currentRow = rows.next();
    			
    			// skip header
    			if(rowNumber == 0) {
    				rowNumber++;
    				continue;
    			}
    			
    			Iterator<Cell> cellsInRow = currentRow.iterator();
 
    			Customer cust = new Customer();
    			
    			int cellIndex = 0;
    			while (cellsInRow.hasNext()) {
    				Cell currentCell = cellsInRow.next();
    				
    				if(cellIndex==0) { // ID
    					cust.setId(String.valueOf(currentCell.getNumericCellValue()));
    				} else if(cellIndex==1) { // Name
    					cust.setName(currentCell.getStringCellValue());
    				} else if(cellIndex==2) { // Address
    					cust.setAddress(currentCell.getStringCellValue());
    				} else if(cellIndex==3) { // Age
    					cust.setAge((int) currentCell.getNumericCellValue());
    				}
    				
    				cellIndex++;
    			}
    			
    			lstCustomers.add(cust);
    		}
    		
    		// Close WorkBook
    		workbook.close();
    		
    		return lstCustomers;
        } catch (IOException e) {
        	throw new RuntimeException("FAIL! -> message = " + e.getMessage());
        }
	}
	
    /**
     * 
     * Convert Java Objects to JSON File
     * 
     * @param customers
     * @param pathFile
     */
    private static void writeObjects2JsonFile(List<Customer> customers, String pathFile) {
        ObjectMapper mapper = new ObjectMapper();

        File file = new File(pathFile);
        try {
            // Serialize Java object info JSON file.
            mapper.writeValue(file, customers);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

JSON String to Excel File

We do 2 steps:
– Convert JSON String to Java List Objects
– Write Java List Objects to Excel File

– Implementation:


package com.loizenai.convertexcel2json;

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.core.JsonParseException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class ConvertJson2Excel {
	public static void main(String[] args) throws IOException {
		// Step 1: Read JSON File to List Objects
		String jsonStr = "[{\"id\":\"1\",\"name\":\"Jack Smith\",\"address\":\"Massachusetts\",\"age\":23},{\"id\":\"2\",\"name\":\"Adam Johnson\",\"address\":\"New York\",\"age\":27},{\"id\":\"3\",\"name\":\"Katherin Carter\",\"address\":\"Washington DC\",\"age\":26},{\"id\":\"4\",\"name\":\"Jack London\",\"address\":\"Nevada\",\"age\":33},{\"id\":\"5\",\"name\":\"Jason Bourne\",\"address\":\"California\",\"age\":36}]";
		
		List<Customer> customers = convertJsonString2Objects(jsonStr);
		
    	// Step 2: Convert Java List Objects to JSON File
    	writeObjects2ExcelFile(customers, "customers.xlsx");
	}
	
	/**
	 * 
	 * Convert JSON String to Java List Objects
	 * 
	 * @param pathFile
	 * @return
	 */
	private static List<Customer> convertJsonString2Objects(String jsonString){
		List<Customer> customers = null;
		
		try {
			customers = new ObjectMapper().readValue(jsonString, new TypeReference<List<Customer>>(){});
		} catch (JsonParseException e) {
			e.printStackTrace();
		} catch (JsonMappingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return customers;
	}
	
	/**
	 * 
	 * Write Java Object Lists to Excel File
	 * 
	 * @param customers
	 * @param filePath
	 * @throws IOException 
	 */
	private static void writeObjects2ExcelFile(List<Customer> customers, String filePath) throws IOException {
		String[] COLUMNs = {"Id", "Name", "Address", "Age"};
		
		Workbook workbook = new XSSFWorkbook();
		 
		CreationHelper createHelper = workbook.getCreationHelper();
 
		Sheet sheet = workbook.createSheet("Customers");
 
		Font headerFont = workbook.createFont();
		headerFont.setBold(true);
		headerFont.setColor(IndexedColors.BLUE.getIndex());
 
		CellStyle headerCellStyle = workbook.createCellStyle();
		headerCellStyle.setFont(headerFont);
 
		// Row for Header
		Row headerRow = sheet.createRow(0);
 
		// Header
		for (int col = 0; col < COLUMNs.length; col++) {
			Cell cell = headerRow.createCell(col);
			cell.setCellValue(COLUMNs[col]);
			cell.setCellStyle(headerCellStyle);
		}
 
		// CellStyle for Age
		CellStyle ageCellStyle = workbook.createCellStyle();
		ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
 
		int rowIdx = 1;
		for (Customer customer : customers) {
			Row row = sheet.createRow(rowIdx++);
 
			row.createCell(0).setCellValue(customer.getId());
			row.createCell(1).setCellValue(customer.getName());
			row.createCell(2).setCellValue(customer.getAddress());
 
			Cell ageCell = row.createCell(3);
			ageCell.setCellValue(customer.getAge());
			ageCell.setCellStyle(ageCellStyle);
		}
 
		FileOutputStream fileOut = new FileOutputStream(filePath);
		workbook.write(fileOut);
		fileOut.close();
		workbook.close();
	}
}

JSON File to Excel File

– Step 1: Read JSON File into Java List Objects
– Step 2: Convert Java List Objects to Excel File

– Implementation:


package com.loizenai.convertexcel2json;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.core.JsonParseException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class ConvertJson2Excel {
	public static void main(String[] args) throws IOException {
		// Step 1: Read JSON File to List Objects
		List<Customer> customers = readJsonFile2Objects("customers.json");
		
    	// Step 2: Convert Java List Objects to JSON File
    	writeObjects2ExcelFile(customers, "customers.xlsx");
	}
	
	/**
	 * 
	 * Convert JSON String to Java List Objects
	 * 
	 * @param pathFile
	 * @return
	 */
	private static List<Customer> readJsonFile2Objects(String pathFile){
		InputStream inJson = Customer.class.getResourceAsStream(pathFile);
		List<Customer> customers = null;
		
		try {
			customers = new ObjectMapper().readValue(inJson, new TypeReference<List<Customer>>(){});
		} catch (JsonParseException e) {
			e.printStackTrace();
		} catch (JsonMappingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return customers;
	}
	
	/**
	 * 
	 * Write Java Object Lists to Excel File
	 * 
	 * @param customers
	 * @param filePath
	 * @throws IOException 
	 */
	private static void writeObjects2ExcelFile(List<Customer> customers, String filePath) throws IOException {
		String[] COLUMNs = {"Id", "Name", "Address", "Age"};
		
		Workbook workbook = new XSSFWorkbook();
		 
		CreationHelper createHelper = workbook.getCreationHelper();
 
		Sheet sheet = workbook.createSheet("Customers");
 
		Font headerFont = workbook.createFont();
		headerFont.setBold(true);
		headerFont.setColor(IndexedColors.BLUE.getIndex());
 
		CellStyle headerCellStyle = workbook.createCellStyle();
		headerCellStyle.setFont(headerFont);
 
		// Row for Header
		Row headerRow = sheet.createRow(0);
 
		// Header
		for (int col = 0; col < COLUMNs.length; col++) {
			Cell cell = headerRow.createCell(col);
			cell.setCellValue(COLUMNs[col]);
			cell.setCellStyle(headerCellStyle);
		}
 
		// CellStyle for Age
		CellStyle ageCellStyle = workbook.createCellStyle();
		ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
 
		int rowIdx = 1;
		for (Customer customer : customers) {
			Row row = sheet.createRow(rowIdx++);
 
			row.createCell(0).setCellValue(customer.getId());
			row.createCell(1).setCellValue(customer.getName());
			row.createCell(2).setCellValue(customer.getAddress());
 
			Cell ageCell = row.createCell(3);
			ageCell.setCellValue(customer.getAge());
			ageCell.setCellStyle(ageCellStyle);
		}
 
		FileOutputStream fileOut = new FileOutputStream(filePath);
		workbook.write(fileOut);
		fileOut.close();
		workbook.close();
	}
}

Read More – Convert Excel to Json in Java

Related posts:


Leave a Reply

Your email address will not be published. Required fields are marked *