Java – Convert Excel File to/from JSON (String/File) – using Apache Poi + Jackson

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

In the tutorial, Grokonez shows how to convert Excel File to JSON String or JSON File and vice versa with Java language by examples.

– Excel Files: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA). It has been a very widely applied spreadsheet for these platforms
– JSON stands for JavaScript Object Notation. JSON is a lightweight format for storing and transporting data. JSON is often used when data is sent from a server to a web page.

Dependencies

org.apache.poi: The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.
jackson-databind: General data-binding package for Jackson (2.x): works on streaming API (core) implementation(s)

<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: we create an Java Object class with 4 attributes: id, name, address, age


package com.grokonez.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 + "]";
	}
 
}

* Convert Implemetation: we do 2 steps:
– Step 1: Read Excel File into Java List Objects. We use org.apache.poi lib to do the task.
+ Use FileInputStream to get a Excel file.
+ Create a Excel Workbook from FileInputStream.
+ Get an Excel Sheet from above Workbook.
+ Iterate over the Sheet. With each excel row, We get and map the cell-value with each field of Customer object and add to a Customer Object List.
+ Finally, returns all the customer list object.

– Step 2: Convert Java Objects to JSON String
+ Use ObjectMapper to convert List Customer Objects to Json String value.

*** Note: – What is Workbook? -> In Microsoft Excel, a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.

– Details coding:


package com.grokonez.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 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 readExcelFile(String filePath){
		try {
			FileInputStream excelFile = new FileInputStream(new File(filePath));
    		Workbook workbook = new XSSFWorkbook(excelFile);
     
    		Sheet sheet = workbook.getSheet("Customers");
    		Iterator rows = sheet.iterator();
    		
    		List lstCustomers = new ArrayList();
    		
    		int rowNumber = 0;
    		while (rows.hasNext()) {
    			Row currentRow = rows.next();
    			
    			// skip header
    			if(rowNumber == 0) {
    				rowNumber++;
    				continue;
    			}
    			
    			Iterator 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 customers) {
    	ObjectMapper mapper = new ObjectMapper();
    	String jsonString = "";
    	
    	try {
    		jsonString = mapper.writeValueAsString(customers);
    	} catch (JsonProcessingException e) {
    		e.printStackTrace();
    	}
    	
    	return jsonString; 
	}
}

-> Output, we get a list of customer with Json String format:


[{"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, we can see more beautiful-printed with below format:


[
  {
    "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, we do the same above step.
– Step 2: Write Java List Objects to JSON File. We also use the ObjectMapper, but at the case, we write to a Json file not just return a Json String.

-> Detail Coding:


package com.grokonez.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 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 readExcelFile(String filePath){
		try {
			FileInputStream excelFile = new FileInputStream(new File(filePath));
    		Workbook workbook = new XSSFWorkbook(excelFile);
     
    		Sheet sheet = workbook.getSheet("Customers");
    		Iterator rows = sheet.iterator();
    		
    		List lstCustomers = new ArrayList();
    		
    		int rowNumber = 0;
    		while (rows.hasNext()) {
    			Row currentRow = rows.next();
    			
    			// skip header
    			if(rowNumber == 0) {
    				rowNumber++;
    				continue;
    			}
    			
    			Iterator 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 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:
+ We use ObjectMapper() and method readValue of it to convert json string to object list.

– Write Java List Objects to Excel File, we use org.apache.poi library:
+ Create a new Excel WorkBook.
+ Create a new Excel Sheet from WorkBook
+ Create Header Row for WorkBook
+ Iterate over Customers Object list, We map each value of customer-object with a corresponding cell of Excel row.

-> Implementation:


package com.grokonez.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 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 convertJsonString2Objects(String jsonString){
		List customers = null;
		
		try {
			customers = new ObjectMapper().readValue(jsonString, new TypeReference>(){});
		} 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 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.grokonez.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 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 readJsonFile2Objects(String pathFile){
		InputStream inJson = Customer.class.getResourceAsStream(pathFile);
		List customers = null;
		
		try {
			customers = new ObjectMapper().readValue(inJson, new TypeReference>(){});
		} 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 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();
	}
}

- Project Structure:

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



By grokonez | February 23, 2019.

Last updated on June 10, 2021.



Related Posts


58 thoughts on “Java – Convert Excel File to/from JSON (String/File) – using Apache Poi + Jackson”

  1. Amazing tutorial. Used it via Kotlin when I had to convert from JSON to Excel. Good approach. Keep up! Quick question, any idea of how to recheck existence of the same name of excel file on the PC?

  2. 306041 377642Hello there. I required to inquire some thingis this a wordpress internet site as we are thinking about transferring across to WP. Moreover did you make this theme all by yourself? Cheers. 657957

  3. 41378 452093This web-site can be a walk-through rather than the details you wished about it and didnt know who should. Glimpse here, and you will definitely discover it. 940652

  4. 776011 166900Thank you for writing this tremendous top quality write-up. The data in this material confirms my point of view and you truly laid it out properly. I could never have written an article this good. 754161

  5. 537261 622622This design is steller! You most undoubtedly know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own weblog (properly, almostHaHa!) Fantastic job. I really loved what you had to say, and a lot more than that, how you presented it. Too cool! 65771

  6. 82045 536086Great beat ! I wish to apprentice although you amend your internet website, how can i subscribe for a weblog site? The account aided me a appropriate deal. I had been a bit bit acquainted of this your broadcast provided bright clear thought 216792

  7. 743349 520246Hello there, just became alert to your weblog by means of Google, and found that its truly informative. Im gonna watch out for brussels. I will appreciate if you continue this in future. Plenty of individuals will probably be benefited from your writing. Cheers! xrumer 719777

  8. 810527 45395Spot up for this write-up, I seriously believe this internet site needs a whole lot more consideration. Ill apt to be once a lot more to learn additional, appreciate your that info. 192866

  9. 496514 672559Empathetic for your monstrous inspect, in addition Im just seriously great as an alternative to Zune, and consequently optimism them, together with the quite very good critical reviews some other players have documented, will let you determine whether it does not take appropriate choice for you. 182065

  10. 317567 238225Your talent is truly appreciated!! Thank you. You saved me lots of frustration. I switched from Joomla to Drupal towards the WordPress platform and Ive fully embraced WordPress. Its so much easier and easier to tweak. Anyway, thanks once more. Awesome domain! 960630

  11. 481658 38056The vacation trades offered are evaluated a variety of within the chosen and basically good value all about the world. Those hostels are normally based towards households which you will discover accented by way of charming shores promoting crystal-clear fishing holes, concurrent of ones Ocean. Hotels Discounts 735247

  12. 780215 596349This kind of publish appears to get yourself lots of visitors. How will you acquire traffic to that? It provides a great unique twist upon issues. I guess having something traditional or perhaps substantial to give information on will be the central aspect. 68082

  13. 596226 742321Your talent is really appreciated!! Thank you. You saved me plenty of frustration. I switched from Joomla to Drupal to the WordPress platform and Ive fully embraced WordPress. Its so considerably easier and easier to tweak. Anyway, thanks once more. Awesome domain! 757126

  14. 168102 869235An fascinating discussion is worth comment. I do feel that you ought to write read much more about this topic, it will not be considered a taboo topic but generally everyone is too couple of to communicate in on such topics. To yet another. Cheers 704859

  15. 436558 403538Very efficiently written story. It will likely be helpful to anybody who employess it, including me. Keep up the excellent work – canr wait to read a lot more posts. 352335

  16. 93480 501219Oh my goodness! an outstanding post dude. Many thanks However We are experiencing issue with ur rss . Dont know why Not able to sign up to it. Could there be anybody getting identical rss dilemma? Anyone who knows kindly respond. Thnkx 785918

  17. Positive site, where did u come up with the information on this posting?I
    have read a few of the articles on your website now, and I really like your
    style.

Got Something To Say:

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

*