Reputation: 355
I have successfully fetched data from excel sheet and now i want to convert the fetched data into JSON format. In specific format. How can i do further conversion part ?
The JSON format should be:
{ Machine: M/C1, Time: 12:00 PM, Status: Working,}, { Machine: M/C2, Time: 12:00PM, Status: Working}, } ......}
Code which i have written to fetch data from excel sheet:
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("F:\\software\\list.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = 0; // No of columns
int tmp = 0;
for(int i = 0; i < 10 || i < rows; i++) {
row = sheet.getRow(i);
if(row != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
if(tmp > cols) cols = tmp;
}
}
for(int r = 0; r < rows; r++) {
row = sheet.getRow(r);
if(row != null) {
for(int c = 0; c < cols; c++) {
cell = row.getCell((short)c);
if(cell != null) {
// Your code here
System.out.println(cell);
}
}
}
}
} catch(Exception ioe) {
ioe.printStackTrace();
}
Upvotes: 3
Views: 902
Reputation: 5953
I suggest you to create a POJO class for the JSON entries, e.g.:
public class MachineData {
private String machine;
private String time;
private String status;
public MachineData(String m, String t, String s) {
this.machine = m;
this.time = t;
this.status = s;
}
//+ getters, setters
}
Then create MachineData
objects from the data you extracted from Excel. Put them in a list, then you can use Jackson or GSON to convert the list into JSON.
// create a list of MachineData objects:
List<MachineData> list = new LinkedList<>();
// then when you go through Excel rows:
String machine = ... // parse from Excel
String time = ... // parse from Excel
String status = ... // parse from Excel
// build a MachineData object
MachineData md = new MachineData(machine, time, status);
// and add it to the list:
list.add(md);
// after you finished with the Excel part
Gson gson = new Gson();
String json = gson.toJson(list); // here you have the JSON in a string
Upvotes: 1
Reputation: 3268
You can use Gson
(if you don't need high performance) or Jackson
(if you care about performance).
Add gson as dependecy to your project. If you use Gradle just add this line to build.gradle dependecies
section:
compile group: 'com.google.code.gson', name: 'gson', version: '2.8.1'
Or check other build systems examples here
With Gson everything is quite simple. Declare a class for your object:
public class MachineStatus {
@SerializedName("Machine")
String machine;
@SerializedName("Time")
String time;
@SerializedName("Status")
String status;
}
Then prepare a list of such objects
ArrayList<MachineStatus> statuses = new ArrayList<>();
for(int r = 0; r < rows; r++) {
row = sheet.getRow(r);
if(row != null) {
for(int c = 0; c < cols; c++) {
cell = row.getCell((short)c);
if(cell != null) {
// Your code here
System.out.println(cell);
MachineStatus status = new MachineStatus();
status.machine = cell.getMachine(); // change this part to use correct method that will fetch data from cell
status.time = cell.getTime(); // same here
status.status = cell.getStatus(); // same here
statuses.add(status);
}
}
}
}
Gson gson = new GsonBuilder()
.setPrettyPrinting() // comment this out if you need to save bandwidth;
.create();
// Prints Json array string
System.out.println(gson.toJson(statuses));
More on gson and collections of objects.
Upvotes: 1