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
Customer.java: we create an Java Object class with 4 attributes: id, name, address, age
package com.ozenero.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.ozenero.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.ozenero.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.ozenero.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.ozenero.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:
