springboot~寫一個從excel讀取json到List<Map>的方法

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