Here I am explaining how to export a complex JSON to excel file.
You need required jar file to perform this operation.
1. gson-2.2.4.jar
2. jakarta-poi-2.5.jar
You can download the jar file and add it in you application.
Step 1: I have a json what I am manipulating, I call it JSONInfo.txt
"records": [
{
"properties": {
"RegisteredForActualService": 11632,
"EmsCreationTime": 1431415606381,
"RequestsOffering": 11370
},
"record_type": "Request",
"related_properties": {
}
},
{
"properties": {
"RegisteredForActualService": 11632,
"EmsCreationTime": 1431420542792,
"RequestsOffering": 11370
},
"record_type": "Request",
"related_properties": {
}
},
{
"properties": {
"RegisteredForActualService": 11632,
"EmsCreationTime": 1431421910769,
"RequestsOffering": 11370
},
"record_type": "Request",
"related_properties": {
}
},
{
"properties": {
"RegisteredForActualService": 11632,
"EmsCreationTime": 1431425328296,
"RequestsOffering": 11370
},
"record_type": "Request",
"related_properties": {
}
},
{
"properties": {
"RegisteredForActualService": 11632,
"EmsCreationTime": 1431425508307,
"RequestsOffering": 11370
},
"record_type": "Request",
"related_properties": {
}
}
],
"meta": {
"completion_status": "OK",
"total_count": 9755
}
Please Note :- Add curly braces start and end of this json, when you put in json/text file or json viewer.
Step 2: Creating correspondent classes to json
public class Properties {
private Long RegisteredForActualService;
private Long EmsCreationTime;
private Long RequestsOffering;
public Long getRegisteredForActualService() {
return RegisteredForActualService;
}
public void setRegisteredForActualService(Long registeredForActualService) {
RegisteredForActualService = registeredForActualService;
}
public Long getEmsCreationTime() {
return EmsCreationTime;
}
public void setEmsCreationTime(Long emsCreationTime) {
EmsCreationTime = emsCreationTime;
}
public Long getRequestsOffering() {
return RequestsOffering;
}
public void setRequestsOffering(Long requestsOffering) {
RequestsOffering = requestsOffering;
}
}
Create another class, this is blank class representing "related_properties" in json
public class RelatedObject {
}
Creating a java class which contain Properties and RelatedObject
public class ProgramInfo {
private Properties properties;
private String record_type;
private RelatedObject related_properties;
public Properties getProperties() {
return properties;
}
public void setProperties(Properties properties) {
this.properties = properties;
}
public String getRecord_type() {
return record_type;
}
public void setRecord_type(String record_type) {
this.record_type = record_type;
}
public RelatedObject getRelated_properties() {
return related_properties;
}
public void setRelated_properties(RelatedObject related_properties) {
this.related_properties = related_properties;
}
}
Creating a java class which contain meta info of json
public class MetaInfo {
private String completion_status;
private Long total_count;
public String getCompletion_status() {
return completion_status;
}
public void setCompletion_status(String completion_status) {
this.completion_status = completion_status;
}
public Long getTotal_count() {
return total_count;
}
public void setTotal_count(Long total_count) {
this.total_count = total_count;
}
}
Now create a java class which contain all json info, I call it JSONInfo
public class JSONInfo {
private List<ProgramInfo> records;
private MetaInfo meta;
public List<ProgramInfo> getRecords() {
return records;
}
public void setRecords(List<ProgramInfo> records) {
this.records = records;
}
public MetaInfo getMeta() {
return meta;
}
public void setMeta(MetaInfo meta) {
this.meta = meta;
}
}
Finally I have a java class which is responsible to convert json to java object and exporting same in excel, I call it ProgramJSONExample
public class ProgramJSONExample {
public static void main(String[] args) throws IOException {
Gson gson = new GsonBuilder().setPrettyPrinting().create();
String fileData = new String(Files.readAllBytes(Paths
.get("C:\\JSON\\JSONInfo.txt")));
JSONInfo jsonInfo = gson.fromJson(fileData, JSONInfo.class);
if (jsonInfo != null) {
writeExcel(jsonInfo); // Method to write data in excel
} else {
System.out.println("No data to write in excel, json is null or empty.");
}
}
/**
* Contract od this method is to write data into excel file
*
* @param jsonInfo
*/
private static void writeExcel(JSONInfo jsonInfo) {
HSSFWorkbook hssfWorkbook = null;
HSSFRow row = null;
HSSFSheet hssfSheet = null;
FileOutputStream fileOutputStream = null;
Properties properties = null;
try {
String filename = "c:/JSON/JSONInfo.xls";
hssfWorkbook = new HSSFWorkbook();
hssfSheet = hssfWorkbook.createSheet("new sheet");
HSSFRow rowhead = hssfSheet.createRow((short) 0); // Header
rowhead.createCell((short) 0).setCellValue("SNo");
rowhead.createCell((short) 1).setCellValue(
"RegisteredForActualService");
rowhead.createCell((short) 2).setCellValue("EmsCreationTime");
rowhead.createCell((short) 3).setCellValue("RequestsOffering");
rowhead.createCell((short) 4).setCellValue("Record Type");
rowhead.createCell((short) 5).setCellValue("Related Properties");
int counter = 1;
for (ProgramInfo programInfo : jsonInfo.getRecords()) {
properties = programInfo.getProperties();
row = hssfSheet.createRow((short) counter);
row.createCell((short) 0).setCellValue(counter);
row.createCell((short) 1).setCellValue(
properties.getRegisteredForActualService());
row.createCell((short) 2).setCellValue(
properties.getEmsCreationTime());
row.createCell((short) 3).setCellValue(
properties.getRequestsOffering());
row.createCell((short) 4).setCellValue(
programInfo.getRecord_type());
row.createCell((short) 5).setCellValue("");// This is blank
// object without
// any property
counter++;
}
counter += 5;
row = hssfSheet.createRow((short) counter);
row.createCell((short) 0).setCellValue("Completion Status : "+
jsonInfo.getMeta().getCompletion_status());
row.createCell((short) 1).setCellValue("Total Count : "+
jsonInfo.getMeta().getTotal_count());
fileOutputStream = new FileOutputStream(filename);
hssfWorkbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("JSON data successfully exported to excel!");
} catch (Throwable throwable) {
System.out.println("Exception in writting data to excel : "
+ throwable);
}
}
Here is my excel file
Please let me know if you face any issue.
