How to convert an excel sheet into Json

Question

I have a task to read a file (specifically excel sheet), and to convert it into JSON to make an API call.

Excel Data :

MAIN_ID  SUB_ID    VALUE
1000     1000-A1    10
1000     1000-A2    15
1000     1000-A3    20 // MAX Value against MAIN_ID
1001     1001-A1    12
1001     1001-A2    14
1001     1001-A3    25 // MAX Value against MAIN_ID

My Requirement :

Edit :

I forgot to add one required logic here, as I am creating JSON, I need to set Max VALUE as VALUE against the MAIN_ID.

i.e I need to create a JSON file like :

{
   "data": [
      {
         "MAIN_ID": "1000",
         "SUB_ID": "1000-A1",
         "VALUE": "20"
      },
      {
         "MAIN_ID": "1000",
         "SUB_ID": "1000-A2",
         "VALUE": "20"
      },
      {
         "MAIN_ID": "1000",
         "SUB_ID": "1000-A3",
         "VALUE": "20"
      },
      {
         "MAIN_ID": "1001",
         "SUB_ID": "1001-A1",
         "VALUE": "25"
      },
      {
         "MAIN_ID": "1001",
         "SUB_ID": "1001-A2",
         "VALUE": "25"
      },
      {
         "MAIN_ID": "1001",
         "SUB_ID": "1001-A3",
         "VALUE": "25"
      }
   ]
}

Answer 1

There is a very powerful and FREE data cleansing tool named OpenRefine (formerly Google Refine) that you can use for this purpose. It is not advertised for this purpose, but it does it simply, and very well. There is one caveat -- you must have 64 bit Java installed.

I recommend you go through their 3, brief, video tutorials before working with your data. OpenRefine will not corrupt your data, as it works with an in-memory copy of your data on your local computer; the tutorial helps you to better understand and use the tool.

When you are ready, create a new project. While your project is being created, the JSON is being generated. You can view the JSON that will be output by selecting Export then Templating. The Templating Export window will open with the JSON to be exported displayed in a panel on the right. If it is already formatted to your liking, just click on the Export button, and you're done. If not, you can modify the template by adjusting the settings in one or more of the panels on the left.

If your data requires tweaking, return to your project, make any necessary adjustments, then come back to the Templating Export window.

There is a lot you can do to shape your date with OpenRefine. It's a great tool to have if you frequently work with data. You can find it at openrefine.org.

Answer 2

I assume that your excel sheet is not complicated so that it can be stored as CSV. From there, you can parse the CSV file then write it as a JSON file, both tasks are already supported by a lot of libraries. Obviously, you can implement any required logic during the process.

Here is one simple code doing that:

public class Model {
    @JsonProperty("MAIN_ID")
    private String mainId;
    @JsonProperty("SUB_ID")
    private String subId;
    @JsonProperty("VALUE")
    private String value;

    public String getMainId() { ... }
    public String setMainId(String mainId) { ... }
    // other getters and settters
}

public class JacksonProgram {

    public static void main(String[] args) throws Exception {
        File input = new File("/x/data.csv");
        File output = new File("/x/data.json");

        List<Model> data = readObjectsFromCsv(input);
        applyRequiredLogic(data);
        writeAsJson(data, output);
    }

    public static void applyRequiredLogic(List<Model> data) {
        //... your logic here
    }

    public static List<Model> readObjectsFromCsv(File file) throws IOException {
        CsvMapper csvMapper = new CsvMapper();
        CsvSchema bootstrap = csvMapper.schemaFor(Model.class).withHeader();
        CsvMapper csvMapper = new CsvMapper();
        MappingIterator<Model> mappingIterator = csvMapper.reader(Model.class).with(bootstrap).readValues(file);

        return mappingIterator.readAll();
    }

    public static void writeAsJson(List<Model> data, File file) throws IOException {
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(file, data);
    }
}

The above code use com.fasterxml.jackson.dataformat:jackson-dataformat-csv:2.+ and com.fasterxml.jackson.core:jackson-databind:2.+ as dependencies, so please add declare those two as your maven or gradle dependencies before using the code.

Answer 3

You can simply convert from objects to toJson like this;

public String toJson(ArrayList<Object> objects){
    String result="[";
    for(int i=0;i<objects.getSize();i++){
     result+="{";
     result+="MAIN_ID:"+ objects.get(i).mainId+",";
     result+="SUB_ID:"+ objects.get(i).subId+",";
     result+="VALUE:"+ objects.get(i).value+",";
     result+="}";
     if(i<object.getSize()-1){
      result+=",";
    }
   }
result+=']';
return result;
}

Try the Apache POI HSSF. Here's an example on how to read an excel file:

try {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    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;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    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
                }
            }
        }
    }
} catch(Exception ioe) {
    ioe.printStackTrace();
}

**Edit:**You can update the list value field according to MAIN_ID You can iterate list and create new list mainIdAndValueList(MainId-Value) after that update objects list