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?
Advertisement
Answer
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