Writing to csv/excel based on header in Java

Question

I have a JSON which has entries that look like

[{ "key": { "keyLabel": "Label1" }, "specs": [{ "specKey": "spec1", "specValue": ["s11"] }, { "specKey": "spec2", "specValue": ["s12"] }] },

{ "key": { "keyLabel": "Label2" }, "specs": [{ "specKey": "spec1", "specValue": ["s21"] }, { "specKey": "spec3", "specValue": ["s22"] }] }]

Spec Keys present changes on the basis of KeyLabel value. As you can see above if if KeyLabel = Label1, spec1 and spec2 are present. If KeyLabel = Label2, spec1 and spec3 are present

I want to create a CSV/Excel using this such that header/top row as following columns

KeyLabel, spec1, spec2, spec3 (basically union of all specKeys)

Label1, s11,s12

Label2, s21, ,s22

So, the challenging part is that at the time of writing to file, I need to write under in the appropriate column.

Any thoughts on if there are any csv/excel libraries which make this easier. Naive way does seem very elegant - which is to store the ordered list of headers and basic on key write commas and values so that values are in write column

Answer 1

I think you don't need specific library for doing that. You should only need some common library jackson poi-ooxml

1. First, you should read json string to POJO object (ref: http://www.jsonschema2pojo.org/)

   List examples = new ObjectMapper().readValue(json, new TypeReference>() {});

2. Seconds, collect header

   List header = examples .stream() .map(Example::getSpecs) .flatMap(Collection::stream) .map(Spec::getSpecKey) .distinct() .collect(Collectors.toList()); header.add(0, "Key");

3. Next, use POI library write excel file

final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("sheetname"));

    // Headers
    Row row = sheet.createRow(0);
    for (int index = 0; index < headers.size(); index++) {
        row.createCell(index).setCellValue(headers.get(index));
    }

    // Content
    int startRow = 1;
    for (Example object : objects) {
        row = sheet.createRow(startRow++);
        // key label

        row.createCell(0).setCellValue(object.getKey().getKeyLabel());
        // spec
        for (int index = 1; index < headers.size(); index++) {
            String speckey = headers.get(index);
            String value = object.getSpecs().stream().filter(e -> e.getSpecKey().equals(speckey)).findAny().map(Spec::getSpecValue).orElse(Collections.emptyList()).toString();
            row.createCell(index).setCellValue(value);
        }
    }

    FileOutputStream fileOut = new FileOutputStream("D:\\poi-generated-file.xlsx");
    try {
        workbook.write(fileOut);
    } finally {
        fileOut.close();
        workbook.close();
    }

It only example code. You should modify and update to better.