How can i convert fetched data to JSON using java

Question

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();
    }

Answer 1

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

Answer 2

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.