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