How to read excel data in the format column and its value and it should come in json structure

Question

In the attached excel, I want read the data and display in the below json format:

test id : value
desc :  value
execution status : value
execution browser : value

Any suggestions ???

i tried below code but it is populating only the first column and its value.

        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            String cell = sheet.getRow(i).getCell(0).toString();
    if (null != cell) {
            //String TC_ID = String.valueOf(row.getCell(0));
                String data = sheet.getRow(i + 1).getCell(0).toString();
                map.put(cell, data);

            } else {
                break;
            }
        }
        list.add(map);
        fis.close();
        FileOutputStream output_file = new FileOutputStream(excelPath);
        workSheet.write(output_file);
        output_file.close();
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }


    return list;
}

To read excel files with java refer to: https://stackoverflow.com/questions/1516144/how-to-read-and-write-excel-file — AlirezaAsadi, Nov 15 '19 at 11:00
Read about how to read Excel files: [apache-poi](https://poi.apache.org/), then implement the read and then read about the next step, which is putting the data read into JSON format. — deHaar, Nov 15 '19 at 11:00
possible duplicate of https://stackoverflow.com/a/44002379/7562674 — Himanshu Sharma, Nov 15 '19 at 11:01
And to make objects to Json format this is a very good tutorial: https://www.baeldung.com/java-org-json — AlirezaAsadi, Nov 15 '19 at 11:04
possible duplicate of https://stackoverflow.com/questions/44795100/how-can-i-get-excel-data-and-convert-it-to-json — Surender Khairwa, Nov 15 '19 at 11:15

score 0 · Answer 1 · answered Nov 15 '19 at 11:17

0

Use Apache POI (https://poi.apache.org). Look at this: https://github.com/nullpunkt/excel-to-json

answered Nov 15 '19 at 11:17

Surender Khairwa

601
4
17

score 0 · Answer 2 · answered Nov 15 '19 at 13:11

You could do this:

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;

import java.io.*;
import java.util.List;
import java.util.stream.Collectors;

public class CSVToJson {

    private static String pathname = "path-to-file.csv";

    public static void main(String[] args) throws Exception {
        File input = new File(pathname);
        FileReader reader = new FileReader(input);
        BufferedWriter writer = new BufferedWriter(new FileWriter(
                new File(input.getParent(), input.getName().replace(".csv", ".json"))));

        CSVParser parse = CSVParser.parse(reader, CSVFormat.DEFAULT
                .withFirstRecordAsHeader()
                .withTrim());
        ObjectMapper mapper = new ObjectMapper();

        List<String> headers = parse.getHeaderNames();

        System.out.println("Headers: " + headers);
        for (CSVRecord record : parse) {
            ObjectNode object = mapper.createObjectNode();
            for (String header : headers) {
                String value = record.get(header);
                object.put(header, value);
            }
            writer.write(mapper.writeValueAsString(object));
            writer.newLine();
        }
        writer.close();
    }
}

You would need the following dependencies:

    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-csv</artifactId>
        <version>1.7</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-core</artifactId>
        <version>2.9.8</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.9.8</version>
    </dependency>

thanks for ur support. I am writing a controller also and using type as json, so code is not required to convert it. The only concern is getting the column names with all the values in the json format. Let me know if it makes sense. — Sayeed, Nov 15 '19 at 13:51

How to read excel data in the format column and its value and it should come in json structure

2 Answers2