Converting JSON to XLS/CSV in Java

You could only convert a JSON array into a CSV file.

Lets say, you have a JSON like the following :

{"infile": [{"field1": 11,"field2": 12,"field3": 13},
            {"field1": 21,"field2": 22,"field3": 23},
            {"field1": 31,"field2": 32,"field3": 33}]}

Lets see the code for converting it to csv :

import org.apache.commons.io.FileUtils;

import org.json.CDL;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

public class JSON2CSV {
  public static void main(String myHelpers[]){
     String jsonString = "{\"infile\": [{\"field1\": 11,\"field2\": 12,\"field3\": 13},{\"field1\": 21,\"field2\": 22,\"field3\": 23},{\"field1\": 31,\"field2\": 32,\"field3\": 33}]}"

     JSONObject output = new JSONObject(jsonOut);
     JSONArray docs = response.getJSONArray("infile");

     File file=new File("yourpath/fromJSON.csv");
     String csv = CDL.toString(docs);
     FileUtils.writeStringToFile(file, csv);
  }
}

Now you got the CSV generated from JSON.

It should look like this:

field1,field2,field3
11,22,33
21,22,23
31,32,33

The maven dependency was like,

<dependency>
    <groupId>org.json</groupId>
    <artifactId>json</artifactId>
    <version>20090211</version>
</dependency>

you can use commons csv to convert into CSV format. or use POI to convert into xls. if you need helper to convert into xls, you can use jxls, it can convert java bean (or list) into excel with expression language.

Basically, the json doc maybe is a json array, right? so it will be same. the result will be list, and you just write the property that you want to display in excel format that will be read by jxls. See http://jxls.sourceforge.net/reference/collections.html

If the problem is the json can't be read in the jxls excel property, just serialize it into collection of java bean first.

A JSON document basically consists of lists and dictionaries. There is no obvious way to map such a datastructure on a two-dimensional table.