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

You pick one of the dozens of Java JSON parsers/formatters (Jackson, GSon, Json Simple, whatever), read the documentation, and do what it tells. — JB Nizet, Aug 27 '17 at 09:55
I'm pretty sure I saw this question a few days back. EDIT: it was a similar one: https://stackoverflow.com/questions/45866022/copying-excel-data-to-text-file-java-poi — , Aug 27 '17 at 09:55
**(1)** https://github.com/nullpunkt/excel-to-json/blob/master/src/main/java/net/nullpunkt/exceljson/convert/ExcelToJsonConverter.java **(2)** https://stackoverflow.com/questions/35794684/excel-to-json-converter-in-java — AsfK, Aug 27 '17 at 09:55

juzraai · Answer 1 · 2017-08-28T07:39:16.363

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

can u please explain the list part here,, hoe it works,, a simple sample will work :) — Srishti, Aug 28 '17 at 03:43

score 1 · Answer 2 · answered Aug 27 '17 at 10:04

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.

I m getting error: SerializedName cannot be resolved to a type,, in eclipse. How to resolve :( — Srishti, Aug 27 '17 at 11:28
Had you added gson dependency to your project? Which dependecy manager do you use, e.g. gradle / maven? — Клаус Шварц, Aug 27 '17 at 11:30
try version `2.8.1` — this one ` com.google.code.gson gson 2.8.1 ` — Клаус Шварц, Aug 27 '17 at 11:42
great,, 2.8.1 worked, no errors :) I have created further 3 getters — Srishti, Aug 27 '17 at 11:50
@BleedCode Glad for you :) I can recommend you to switch to `kotlin`. You won't need to bother with getters :) — Клаус Шварц, Aug 27 '17 at 12:03

How can i convert fetched data to JSON using java

2 Answers2