Converting JSON to XLS/CSV in Java

BachLover2342 picture BachLover2342 · Aug 24, 2011 · Viewed 131.9k times · Source

Does anyone have any sample Java code to convert a JSON document to XLS/CSV file? I have tried to search on Google but to no avail.

Answer

Sridhar picture Sridhar · Mar 14, 2013

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 java.io.File;
import java.io.IOException;

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;
        try {
            output = new JSONObject(jsonString);


            JSONArray docs = output.getJSONArray("infile");

            File file=new File("/tmp2/fromJSON.csv");
            String csv = CDL.toString(docs);
            FileUtils.writeStringToFile(file, csv);
        } catch (JSONException e) {
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }        
    }

}

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>

Update Dec 13, 2019:

Updating the answer, since now we can support complex JSON Arrays as well.

import java.nio.file.Files;
import java.nio.file.Paths;

import com.github.opendevl.JFlat;

public class FlattenJson {

    public static void main(String[] args) throws Exception {
        String str = new String(Files.readAllBytes(Paths.get("path_to_imput.json")));

        JFlat flatMe = new JFlat(str);

        //get the 2D representation of JSON document
        flatMe.json2Sheet().headerSeparator("_").getJsonAsSheet();

        //write the 2D representation in csv format
        flatMe.write2csv("path_to_output.csv");
    }

}

dependency and docs details are in link