使用poi解析excel表格数据

使用poi解析excel中有合并单元格的表格数据

控制层编写

 /**
     * 解析excel
     * @param file
     */
    @RequestMapping("/importExcel")
    public void importExcel(MultipartFile file) {
        try {
            fileUploadService.importExcel(file);
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

逻辑层编写

最终返回List<Map<String,Object>>的对象

 @Override
    public void importExcel(MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            Workbook workbook = null;
            if (file.getOriginalFilename().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
            ArrayList<LjtcTime> arrayList = new ArrayList<>();
            List<Map<String,Object>> ljtcTimesList = oljParse(workbook);
            System.out.println(ljtcTimesList);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

oljParse方法

        public List<Map<String, Object>> oljParse(Workbook workbook) {
        ArrayList<Map<String, Object>> arrayList = new ArrayList<>();
        //根据不同路局 解析
        int numberOfSheets = workbook.getNumberOfSheets();
//        String sheetName = sheet.getSheetName();
        Sheet sheet1 = workbook.getSheetAt(0);
        int rowNum = sheet1.getPhysicalNumberOfRows(); //178
        for (int i = 4; i < rowNum - 1; i++) {
//            LjtcTime ljtcTime = new LjtcTime();
            Map<String, Object> mapList = new HashMap<>();
            Row row = sheet1.getRow(i);
            for (Cell cell : row) {  //这行中的列进行循环
                String returnStr = "";
                boolean isMerge = isMergedRegion(sheet1, i, cell.getColumnIndex());
                if (isMerge) {
                    returnStr = getMergedRegionValue(sheet1, i, cell.getColumnIndex());
                } else {
                      returnStr = getCellValue(cell);
                    //getRichStringCellValue将包含任何格式信息
//                    returnStr = cell.getRichStringCellValue().getString();
                }
                if (cell.getColumnIndex() == 0) {
                    mapList.put("route", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("quJian", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("xiaSgtc", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("xiaSgtcJs", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("xiaWxtc", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("xiaWxtcJs", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("shSgtc", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("shSgtcJs", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("shWxtc", returnStr);
                } else if (cell.getColumnIndex() == 1) {
                    mapList.put("shWxtcJs", returnStr);
                }
                arrayList.add(mapList);
            }
            return arrayList;
        }
    }

getMergedRegionValue方法

 /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
    //得到合并的区域数量
        int sheetMergeCount = sheet.getNumMergedRegions(); 

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }```

###  getCellValue方法

```clike
 /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) {
        if (cell == null) return "";
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }