Converting JSON to Xls/CSV in Java

How to convert json file to excel file in java

Do not generate Excel file until you really have to. In case, you want to generate data without any specific formatting, charts, macros, etc. just generate CSV file with pure data. To read JSON and generate CSV you can use Jackson library which supports these two data formats. Just assume your JSON looks like below:

{
  "rows": [
    {
      "id": 1,
      "name": "Vika",
      "age": 27
    },
    {
      "id": 2,
      "name": "Mike",
      "age": 28
    }
  ]
}

You, need to create POJO model which fits to that structure, deserialise JSON to objects and serialise objects to CSV format. Example solution, could look like below:

import com.fasterxml.jackson.annotation.JsonPropertyOrder;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SequenceWriter;
import com.fasterxml.jackson.dataformat.csv.CsvMapper;
import com.fasterxml.jackson.dataformat.csv.CsvSchema;

import java.io.File;
import java.util.List;

public class JsonApp {

    public static void main(String[] args) throws Exception {
        File jsonFile = new File("./resource/test.json").getAbsoluteFile();

        ObjectMapper jsonMapper = new ObjectMapper();
        Response response = jsonMapper.readValue(jsonFile, Response.class);

        CsvMapper csvMapper = new CsvMapper();
        CsvSchema schema = csvMapper.schemaFor(Item.class).withHeader();
        SequenceWriter sequenceWriter = csvMapper.writer(schema).writeValues(System.out);
        sequenceWriter.writeAll(response.getRows());
    }
}

class Response {
    private List<Item> rows;

    // getters, setters
}

@JsonPropertyOrder({"id", "name", "age"})
class Item {

    private int id;
    private String name;
    private int age;

    // getters, setters
}

Above code prints:

id,name,age
1,Vika,27
2,Mike,28

See also:

• jackson-dataformats-text
• Intro to the Jackson ObjectMapper

How export json data to excel sheet in java

Questions on exporting JSON to CSV have been previously asked here and a response was provided here: Converting JSON to XLS/CSV in Java

However since you asked for a tutorial this page shows an example with a different approach.

Hope that helps

How to convert simple json object to csv using java?

JSON isn't really a (CSV) table format. So I guess you'll have to come up with something yourself. (If you don't already have,) you can gain Object-access to your JSON. You'll also need a CSVWriter. You can take the OpenCSV library or write a simple one yourself (that just concats the strings with commas inbetween).

Here is some logic

csvWriter = new CSVWriter(...)
json = parseJson(...)

//header line
for each key in json
    csvWriter.write(key)

csvWriter.nextLine()

//value line
for each key in json
    value = json[key]
    csvWriter.write(value)

csvString = csvWriter.asString()

A converter from JSON to CSV in Java doesn't work properly

working as you expect using the below code. I don't see any major difference between your and my code....

public static void main(String[] args) throws IOException {
        String inputJson = "{\"Code\":2,\"Description\":\"OK\",\"Status\":0,\"Items\":[{\"City\":\"nameOfCity\",\"Country\":\"nameOfCountry\",\"CountryCode\":\"US\",\"Latitude\":\"11.11111\",\"Longitude\":\"-11.11111\",\"Name\":\"name of Company\",\"Region\":\"nameofRegion\",\"ServisID\":\"111AAA111AA\",\"SiteAddress\":\"number and street 2301\",\"ZipCode\":\"1111\"},{\"City\":\"nameOfCity2\",\"Country\":\"nameOfCountry\",\"CountryCode\":\"US\",\"Latitude\":\"22.22222\",\"Longitude\":\"22.2222222222\",\"Name\":\"name of Company2\",\"Region\":\"nameofRegion\",\"ServisID\":\"111BBB111BB\",\"SiteAddress\":"
                + null + ",\"ZipCode\":" + null + "}]}";
        JSONObject objJsonObject = new JSONObject(inputJson);
        JSONArray objJsonArray = objJsonObject.getJSONArray("Items");
        String csv = CDL.toString(objJsonArray);
        FileUtils.writeStringToFile(new File(System.getenv("HOME")+ "/temp.csv"), csv);
}

i opened the csv file using libreoffice v5.1 with UTF-8 encoding

---

Related Topics