POI解析excel文件读取文件（含合并单元格）

1.
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>


2. 

 @PostMapping(value = "/readCell",produces = {"application/json;charset=utf-8"})
    @ResponseBody
    public HashMap<String,Object>  readCell(@RequestParam("file") MultipartFile file){
        String fileName = file.getOriginalFilename();
        System.out.println(fileName);
        String filePath = "D:\\WorkPlace";
        File dir = new File(filePath);
        if(!dir.exists()){
            dir.mkdir();
        }
        filePath = filePath+File.separator+fileName;
        System.out.println(filePath);
        try {
            List readCells = ReadCellUtil.readCell(filePath);
            return new HashMap<String, Object>(){{put("readCell", readCells);}};
        } catch (Exception e) {
            return new HashMap<String,Object>(){{put("readCell","失败");}};
        }
    }


3.

@Getter
public enum ReadCellEnum {
    DAI_MA("代码","代码","setCode"),
    JB_LIST("疾病列表","疾病列表","setDiseaseList"),
    JB_NAME("疾病名称","疾病名称","setDiseaseName");

    private String code;
    private String desc;
    private String getWay;
    ReadCellEnum(String code ,String desc,String getWay){
        this.code = code;
        this.desc = desc;
        this.getWay = getWay;
    }
    public static ReadCellEnum getSetMethodByCode(String code) {
        for (ReadCellEnum readCellEnum : ReadCellEnum.values()) {
            if (readCellEnum.getCode().equals(code)) {
                return readCellEnum;
            }
        }
        return null;
    }
}

4.
@Slf4j
public class ReadCellUtil {

    /**
     *
     * @param filePath
     * @return readCellTest
     */
    public static List readCellTest(String filePath) {
        FileInputStream inputStream = null;
        ArrayList<ReadCell> readCells = new ArrayList<>();
        try {
            inputStream = new FileInputStream(new File(filePath));
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            log.info("workbook:{}", workbook);
            XSSFSheet sheet = workbook.getSheetAt(0);
            log.info("sheet:{}", sheet);
            int lastRowNum = sheet.getLastRowNum();
            log.info("lastRowNum:{}", lastRowNum);
            int lastCellNum = sheet.getRow(0).getLastCellNum() - 1;
            log.info("lastCellNum:{}", lastCellNum);
            for (int i = 1; i <= lastRowNum; i++) {
                ReadCell readCell = new ReadCell();
                for (int j = 0; j <= lastCellNum; j++) {
                    XSSFCell title = sheet.getRow(0).getCell(j);
                    String titleValue = title.getStringCellValue();
                    log.info("titleValue:{}", titleValue);
                    XSSFCell cell = sheet.getRow(i).getCell(j);
                    CellType cellType = cell.getCellType();
                    log.info("cellType:{}", cellType);
                    log.info("cellTypeClass:{}", cellType.getClass());
                    String value = cell.getStringCellValue();
                    log.info("value:{}", value);
                    ReadCellEnum readCellEnum = ReadCellEnum.getSetMethodByCode(titleValue.replace("\uFEFF", ""));
                    log.info("readCellEnum:{}", readCellEnum);
                    Method method = readCell.getClass().getMethod(readCellEnum.getGetWay(), String.class);
                    method.invoke(readCell, value);
                }
                readCells.add(readCell);
            }
            return readCells/*.stream().distinct().collect(Collectors.toList())*/;
        } catch (Exception e) {
            throw new RuntimeException("文件流解析失败");
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                throw new RuntimeException("输入流未关闭");
            }
        }
    }

    /**
    *  @Description 通过行列的遍历获取单元格的值，然后保存到list中,流一定要最后释放否则无法释放再次请求
     *  通过反射形式赋值
    *  @param   filePath
    *  @return  readCell
    *  @author  itw_lixd05
    */
    public static List readCell(String filePath) {
        FileInputStream inputStream = null;
        ArrayList<ReadCell> readCells = new ArrayList<>();
        try {
            inputStream = new FileInputStream(new File(filePath));
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            int lastCellNum = sheet.getRow(0).getLastCellNum() - 1;
            String value = "";
            for (int i = 1; i <= lastRowNum; i++) {
                ReadCell readCell = new ReadCell();
                for (int j = 0; j <= lastCellNum; j++) {
                    XSSFCell title = sheet.getRow(0).getCell(j);
                    String titleValue = title.getStringCellValue();
                    XSSFCell cell = sheet.getRow(i).getCell(j);
                    if (isMergedRegion(sheet, i, j)) {
                        value = getMergedRegionValue(sheet, i, j);
                    } else {
                        value = getCellValue(cell);
                    }
                    ReadCellEnum readCellEnum = ReadCellEnum.getSetMethodByCode(titleValue.replace("\uFEFF", ""));
                    Method method = readCell.getClass().getMethod(readCellEnum.getGetWay(), String.class);
                    method.invoke(readCell, value);
                }
                readCells.add(readCell);
            }
            return readCells.stream().distinct().collect(Collectors.toList());
        } catch (Exception e) {
            throw new RuntimeException("文件流解析失败");
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                throw new RuntimeException("输入流未关闭");
            }
        }
    }

   /**
   *  @Description   通过确定当前行号、列号所在区域块,获取当前区域块第一个单元格的值,赋值给整个区域块的值
    *  因为如果遍历每一个行列如果是合并单元格，只有第一个单元格有值，其他的值为空字符串
   *  @param   sheet  row  column
   *  @return  getMergedRegionValue
   *  @author  itw_lixd05
   */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetNumMergedRegions = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetNumMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row row1 = sheet.getRow(firstRow);
                    Cell cell = row1.getCell(firstColumn);
                    return getCellValue(cell);
                }
            }
        }
        return null;
    }

    /*
    *  @Description 根据行号、列号定位cell获取值
    *  @param  cell
    *  @return  getCellValue
    *  @author  itw_lixd05
    */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        switch (cell.getCellType()){
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case NUMERIC:
               cellValue = String.valueOf(cell.getNumericCellValue());
               break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                cellValue = "";
                break;
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;

        }
        return cellValue;
    }
    /**
    *  @Description 先获取合并的区域块,然后根据遍历的行、列判断是否在某个区域范围内，是否是合并单元格
    *  @param   sheet  row  column
    *  @return  isMergedRegion
    *  @author  itw_lixd05
    */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetNumMergedRegions = sheet.getNumMergedRegions();
        log.info("sheetNumMergedRegions:{}", sheetNumMergedRegions);
        for (int i = 0; i < sheetNumMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
}

5.
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ReadCell {
    private String code;
    private String diseaseList;
    private String diseaseName;
}

代码	疾病列表	疾病名称
S000	甲状腺、乳腺疾病	甲状腺结节（未手术）
			甲状腺结节（已手术）
				甲状腺功能亢进症
					甲状腺功能减退症
			乳腺结节
		乳腺纤维腺瘤
				S001		三高	高血压（收缩压>140mmHg，或舒张压>90mmHg）
			糖尿病
		糖耐量异常、血糖异常
			高脂血症
					S006		心脏、血管疾病	心肌炎
		房性早搏
			室性早搏
			窦性心动过缓
		心动过速（心率>100次/分）
		房颤
		冠心病
		心肌缺血
		心肌梗塞
		风湿性心脏病
		心脏瓣膜疾病（含缺损、狭窄、关闭不全）
		先天性心脏病
心功能不全
心脏手术
动脉瘤
S013	脑部、神经疾病	脑炎、脑膜炎
			脑卒中
			脑血管畸形
		脑梗死
		脑栓塞
		脑出血
		脑缺血
		蛛网膜下腔出血
		脑动静脉畸形
		脑垂体疾病
		帕金森氏症
		阿尔茨海默病（老年痴呆）
		癫痫
		不明原因头痛或眩晕