英文:
How to iterate over a xlsx file with multiple comma separated values in cell in to a JSONArray
问题
我正在使用Java Apache POI工具读取一个xlsx文件。在这个xlsx文件中，有三列分别是Teacher，class&Section，Sunbect。最后两列在单元格中有多个逗号分隔的值。我需要迭代并构建一个JSONArray。
我已经尝试了一些代码：
try {
FileInputStream inputStream = new FileInputStream(new File("C:/Users/HP/Downloads/school.xlsx"));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet AddCatalogSheet = workbook.getSheetAt(0);
int rowcount = AddCatalogSheet.getLastRowNum() - AddCatalogSheet.getFirstRowNum();
System.out.println("Total row number: " + rowcount);
for (int i = 1; i < rowcount + 1; i++) {
Row row = AddCatalogSheet.getRow(i);
List<String> arrName = new ArrayList<String>();
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
arrName.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
arrName.add(cell.getStringCellValue());
break;
}
}
System.out.println(arrName);
System.out.println("Size of the arrayList: " + arrName.size());
JSONObject teacher = new JSONObject();
JSONArray jsonArray = new JSONArray();
for (int counter = 0; counter < arrName.size(); counter++) {
System.out.println(arrName.get(counter));
jsonArray.put(arrName.get(counter));
}
System.out.println(jsonArray.toString());
}
} catch (IOException e) {
e.printStackTrace();
}
我的期望结果如下：
[
{
"Teacher_code": "23424234",
"class": [
{
"class": "6",
"section": "A"
},
{
"class": "7",
"section": "B"
},
{
"class": "8",
"section": "A"
}
],
"subject_name": [
{
"subject": "Tamil"
},
{
"subject": "English"
},
{
"subject": "Maths"
}
]
}
]
请帮助我解决这个问题。
英文:
I am reading a xlsx file using java Apache poi util. In which the xlsx file has 3 columns as Teacher, class&Section, Sunbect. The last two columns have multiple comma separated values in the cell. I need to iterate over and construct a JSONArray.
I have tried some code
try {
FileInputStream inputStream = new FileInputStream(new File("C:/Users/HP/Downloads/school.xlsx"));
//FileInputStream inputStream = new FileInputStream(new File("TestExecution.xlsx"));
//HashMap<Integer, Data> mp= new HashMap<Integer, Data>();
HashMap<String, List<String>> mp= new HashMap<>();
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet AddCatalogSheet = workbook.getSheetAt(0);
//Find number of rows in excel file
int rowcount = AddCatalogSheet.getLastRowNum()- AddCatalogSheet.getFirstRowNum();
System.out.println("Total row number: "+rowcount);
for(int i=1; i<rowcount+1; i++){
//Create a loop to get the cell values of a row for one iteration
Row row = AddCatalogSheet.getRow(i);
List<String> arrName = new ArrayList<String>();
for(int j=0; j<row.getLastCellNum(); j++){
// Create an object reference of 'Cell' class
Cell cell = row.getCell(j);
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
//System.out.print(cell.getNumericCellValue() + "\t");
arrName.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
//System.out.print(cell.getStringCellValue() + "\t");
arrName.add(cell.getStringCellValue());
break;
}
// Add all the cell values of a particular row
}
System.out.println(arrName);
System.out.println("Size of the arrayList: "+arrName.size());
// Create an iterator to iterate through the arrayList- 'arrName'
JSONObject teacher = new JSONObject();
JSONArray jsonArray = new JSONArray();
for (int counter = 0; counter < arrName.size(); counter++) {
System.out.println(arrName.get(counter));
jsonArray.put(arrName.get(counter));
}
System.out.println(jsonArray.toString());
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
My Expected Result like :
[
{
"Teacher_code": "23424234",
"class": [
{
"class": "6",
"section": "A"
},
{
"class": "7",
"section": "B"
},
{
"class": "8",
"section": "A"
}
],
"subject_name": [
{
"subject": "Tamil"
},
{
"subject": "English"
},
{
"subject": "Maths"
}
]
}
]
Please help me to figure out this
答案1
得分: 0
我认为不应该在你的代码中使用JsonObject，这会使你的代码变得冗长且难以理解。正确的做法是将其封装在一个POJO中。
public static void main(String[] args) throws Exception {
try (InputStream in = Files.newInputStream(Paths.get("E:\\test\\res\\school.xlsx"), READ);
Workbook workbook = new XSSFWorkbook(in)) {
Sheet sheet = workbook.getSheetAt(0);
Row headRow = sheet.getRow(sheet.getFirstRowNum());
//获取第一行
List<String> head = IntStream.range(0, headRow.getLastCellNum())
.mapToObj(i -> headRow.getCell(i).getStringCellValue())
.collect(Collectors.toList());
List<JSONObject> jsonObjects = new ArrayList<>();
//跳过第一行
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
JSONObject jsonObject = new JSONObject();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
switch (cell.getCellType()) {
case STRING:
String strV = cell.getStringCellValue();
List<JSONObject> collect = Arrays.stream(strV.split(","))
.map(s -> {
JSONObject classObj = new JSONObject();
if (!s.contains("-")) {
classObj.put("subject", s);
return classObj;
}
String[] classAndSection = s.split("-");
classObj.put("class", classAndSection[0]);
classObj.put("section", classAndSection[1]);
return classObj;
})
.collect(Collectors.toList());
jsonObject.put(head.get(j), collect);
break;
case NUMERIC:
//修复 2.34566E
NumberFormat nf = NumberFormat.getInstance();
nf.setGroupingUsed(false);
String format = nf.format(cell.getNumericCellValue());
//教师
if (j == row.getFirstCellNum()) {
jsonObject.put(head.get(j), format);
break;
}
JSONObject classObj = new JSONObject();
classObj.put("class", format);
jsonObject.put(head.get(j), classObj);
break;
}
}
jsonObjects.add(jsonObject);
}
System.out.println(JSON.toJSONString(jsonObjects));
}
}
代码运行结果
[
{
"teacher": "23424234",
"classAndSection": [
{
"section": "A",
"class": "8"
},
{
"section": "B",
"class": "9"
}
],
"subject_name": [
{
"subject": "English"
},
{
"subject": "Math"
}
]
},
{
"teacher": "23424234",
"classAndSection": {
"class": "8"
},
"subject_name": [
{
"subject": "English"
}
]
}
]
英文:
i think don't use JsonObject inside your code,this will make your code very bloated and difficult to understand. The correct way to do it is to wrap it in a pojo.
public static void main(String[] args) throws Exception {
try (InputStream in = Files.newInputStream(Paths.get("E:\\test\\res\\school.xlsx"), READ);
Workbook workbook = new XSSFWorkbook(in)) {
Sheet sheet = workbook.getSheetAt(0);
Row headRow = sheet.getRow(sheet.getFirstRowNum());
//get first row
List<String> head = IntStream.range(0, headRow.getLastCellNum())
.mapToObj(i -> headRow.getCell(i).getStringCellValue())
.collect(Collectors.toList());
List<JSONObject> jsonObjects = new ArrayList<>();
//skip fist row
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
JSONObject jsonObject = new JSONObject();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
switch (cell.getCellType()) {
case STRING:
String strV = cell.getStringCellValue();
List<JSONObject> collect = Arrays.stream(strV.split(","))
.map(s -> {
JSONObject classObj = new JSONObject();
if (!s.contains("-")) {
classObj.put("subject", s);
return classObj;
}
String[] classAndSection = s.split("-");
classObj.put("class", classAndSection[0]);
classObj.put("section", classAndSection[1]);
return classObj;
})
.collect(Collectors.toList());
jsonObject.put(head.get(j), collect);
break;
case NUMERIC:
//fix 2.34566E
NumberFormat nf = NumberFormat.getInstance();
nf.setGroupingUsed(false);
String format = nf.format(cell.getNumericCellValue());
//teacher
if (j == row.getFirstCellNum()) {
jsonObject.put(head.get(j), format);
break;
}
JSONObject classObj = new JSONObject();
classObj.put("class", format);
jsonObject.put(head.get(j), classObj);
break;
}
}
jsonObjects.add(jsonObject);
}
System.out.println(JSON.toJSONString(jsonObjects));
}
}
code running result
[
{
"teacher": "23424234",
"classAndSection": [
{
"section": "A",
"class": "8"
},
{
"section": "B",
"class": "9"
}
],
"subject_name": [
{
"subject": "English"
},
{
"subject": "Math"
}
]
},
{
"teacher": "23424234",
"classAndSection": {
"class": "8"
},
"subject_name": [
{
"subject": "English"
}
]
}
]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论