【Springboot】-读取Excel中的Json数据到List

excel读取json到List

Excel读出来的json，它是一个字符串，不是标准json，所以需要对字符串字段进行解析

复杂的excel如图

通过poi解析json，通过jackson完成对字段的解析

   public static List<Map<String, Object>> read(String filePath) throws IOException {
        File file = new File(filePath);   //creating a new file instance
        FileInputStream fis = new FileInputStream(file);   //obtaining bytes from the file
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object
        Iterator<Row> itr = sheet.iterator();    //iterating over excel file
        Row head = sheet.getRow(0);
        ObjectMapper mapper = new ObjectMapper();
        List<Map<String, Object>> mapList = new ArrayList<>();
        itr.next();//跳过第一行
        while (itr.hasNext()) {
            Row row = itr.next();

            Iterator<Cell> cellIterator = row.cellIterator();   //iterating over each column
            Map<String, Object> map = new HashMap<>();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String columnName = head.getCell(cell.getColumnIndex()).toString();
                switch (cell.getCellType()) {
                    case STRING:    //field that represents string cell type
                        try {
                            JsonNode node = mapper.readTree(cell.getStringCellValue());
                            if (node.isArray()) {
                                ArrayNode arrayNode = (ArrayNode) node;
                                try {
                                    List<Map> innerList = new ArrayList<>();
                                    for (JsonNode jsonNode : arrayNode) {
                                        innerList.add(mapper.convertValue(jsonNode, Map.class));
                                    }
                                    map.put(columnName, innerList);
                                } catch (Exception ex) {
                                    //简单类型的数组
                                    map.put(columnName, mapper.readValue(cell.getStringCellValue(), new TypeReference<List<String>>() {
                                    }));
                                }

                            } else {
                                map.put(columnName, mapper.convertValue(node, Map.class));
                            }
                        } catch (JsonParseException ex) {
                            map.put(columnName, cell.getStringCellValue());
                        }
                        break;
                    case NUMERIC:
                        map.put(columnName, cell.getStringCellValue());
                        break;
                    default:
                }
            }
            mapList.add(map);
        }

        return mapList;
    }

解析后的List如下