apache poi convert excel to json

user_15169462 0
Viewed 150

ExcelToJson converter

public JsonNode excelToJson(File excel) throws IOException {
        ObjectMapper mapper = new ObjectMapper();
            // hold the excel data sheet wise
            ObjectNode excelData = mapper.createObjectNode();
            FileInputStream fis = null;
            Workbook workbook = null;
            try {
                // Creating file input stream
                fis = new FileInputStream(excel);

                String filename = excel.getName().toLowerCase();
                if (filename.endsWith(".xls") || filename.endsWith(".xlsx")) {
                    // creating workbook object based on excel file format
                    if (filename.endsWith(".xls")) {
                        workbook = new HSSFWorkbook(fis);
                    } else {
                        workbook = new XSSFWorkbook(fis);
                    }

                    // Reading each sheet one by one
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sheet = workbook.getSheetAt(i);
                        String sheetName = sheet.getSheetName();

                        List<String> headers = new ArrayList<String>();
                        ArrayNode sheetData = mapper.createArrayNode();
                        // Reading each row of the sheet
                        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                            Row row = sheet.getRow(j);
                            if (j == 0) {
                                // reading sheet header's name
                                for (int k = 0; k < row.getLastCellNum(); k++) {
                                    headers.add(row.getCell(k).getStringCellValue());
                                }
                            } else {
                                // reading work sheet data
                                ObjectNode rowData = mapper.createObjectNode();
                                for (int k = 0; k < headers.size(); k++) {
                                    Cell cell = row.getCell(k);
                                    String headerName = headers.get(k);
                                    if (cell != null) {
                                        switch (cell.getCellType()) {
                                            case FORMULA:
                                                rowData.put(headerName, cell.getCellFormula());
                                                break;
                                            case BOOLEAN:
                                                rowData.put(headerName, cell.getBooleanCellValue());
                                                break;
                                            case NUMERIC:
                                                rowData.put(headerName, cell.getNumericCellValue());
                                                break;
                                            case BLANK:
                                                rowData.put(headerName, "");
                                                break;
                                            default:
                                                rowData.put(headerName, cell.getStringCellValue());
                                                break;
                                        }
                                    } else {
                                        rowData.put(headerName, "");
                                    }
                                }
                                sheetData.add(rowData);
                            }
                        }
                        excelData.set(sheetName, sheetData);
                    }
                    return excelData;
                } else {
                    throw new IllegalArgumentException("File format not supported.");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (workbook != null) {
                    try {
                        workbook.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }

            }
            return null;
    }

The method works correctly, but at the output it produces an array, which I then need to write to the database. This array forms one object and the data cannot be written to the database. Is it possible that the data would come not as an array, but as a simple list?

spring-boot excel spring json java
2 Answers

I think you can follow the below code snippet and convert the JSON array into a Java array list that you need.

Add these classes in your import,

import java.util.ArrayList;  
import org.json.JSONArray;  
import org.json.JSONObject;

You can create a separate method using the below code and call it in a place where you are going to pass the JSON array to get the List. While using it remove the sample JSON data and the JSON object creation part as you will be already having the JSON array with you. Pass the JSON array as a parameter and start adding the JSON data into your list.

public static void main(String[] args){  
        //Creating string of JSON data   
        String jsonData = "{\"languages\" : [{\"name\": \"Java\", \"description\":"  
                + " \" Java is a class-based high-level programming language that"  
                + " follows the OOPs concepts.\"},{\"name\": \"Javascript\","  
                + "\"description\": \"JavaScript is also a high-level, often "  
                + "just-in-time compiled, and multi-paradigm programming language."  
                + "\"},{\"name\": \"Python\",\"description\": \"Python is another "  
                + "high-level, interpreted and general-purpose programming language."  
                + "\"}]}";  
          
        //Converting jsonData string into JSON object  
        JSONObject jsnobject = new JSONObject(jsonData);  
        //Printing JSON object  
        System.out.println("JSON Object");  
        System.out.println(jsnobject);  
        //Getting languages JSON array from the JSON object  
        JSONArray jsonArray = jsnobject.getJSONArray("languages");  
        //Printing JSON array  
        System.out.println("JSON Array");  
        System.out.println(jsonArray);  
        //Creating an empty ArrayList of type Object  
        ArrayList<Object> listdata = new ArrayList<Object>();  
          
        //Checking whether the JSON array has some value or not  
        if (jsonArray != null) {   
              
            //Iterating JSON array  
            for (int i=0;i<jsonArray.length();i++){   
                  
                //Adding each element of JSON array into ArrayList  
                listdata.add(jsonArray.get(i));  
            }   
        }  
        //Iterating ArrayList to print each element  
  
        System.out.println("Each element of ArrayList");  
        for(int i=0; i<listdata.size(); i++) {  
            //Printing each element of ArrayList  
            System.out.println(listdata.get(i));  
        }  
    }

Output:

JSON Object
{"languages":[{"name":"Java","description":"Java is a class-based high-level programming language that follows the OOPs concepts."},{"name":"Javascript","description":"JavaScript is also a high-level, often just-in-time compiled, and multi-paradigm programming language."},{"name":"Python","description":"Python is another high-level, interpreted and general-purpose programming language."}]}

JSON Array
[{"name":"Java","description":"Java is a class-based high-level programming language that follows the OOPs concepts."},{"name":"Javascript","description":"JavaScript is also a high-level, often just-in-time compiled, and multi-paradigm programming language."},{"name":"Python","description":"Python is another high-level, interpreted and general-purpose programming language."}]

Each element of ArrayList
{"name":"Java","description":"Java is a class-based high-level programming language that follows the OOPs concepts."}
{"name":"Javascript","description":"JavaScript is also a high-level, often just-in-time compiled, and multi-paradigm programming language."}
{"name":"Python","description":"Python is another high-level, interpreted and general-purpose programming language."}

If you have any further doubts refer to here

user_111435250 
import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;

public class Test {
    
    public static void main(String[] args) {
        try {
            File file = new File("FILE_PATH");
            if(file.exists()) {
                JsonNode excelData = excelToJson(file); //your excelToJson method
                System.out.println(excelData);
                
                JsonNode sheetData = excelData.withArray("SHEET_NAME"); //Get your sheet data
                System.out.println(sheetData);
                
                boolean isArray = sheetData.isArray();
                if(isArray) {
                    ArrayNode arrayNode = (ArrayNode) sheetData;
                    ObjectMapper objectMapper = new ObjectMapper();
                    List<Map<String, Object>> list = objectMapper.convertValue(arrayNode, new TypeReference<List<Map<String, Object>>>(){});    //Convert to list
                    System.out.println(list);
                }               
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
user_193689260
Related