前提

使用poi实现，导入excel并且识别，返回查询结果

代码

java


 @PostMapping("/paperBorrowTempletImport")
    public Result<IPage<RecPaperBorrowRegisterResponse>>paperBorrowTempletImport(@RequestPart("file") MultipartFile file) {

        List<PaperBorrowTempletSearchRequest> domainList = new ArrayList<>();
        try (InputStream inpStream = file.getInputStream()){
            List<List<Object>> objlist = new ArrayList<>();
            // 创建Excel工作薄
            Workbook workbook = getWorkBook(inpStream);
            if (null == workbook) {
                throw new Exception("Excel工作薄为空！");
            }
            // 遍历Excel中的所有sheet
            int rowNull = 0;
            for (Sheet sheet : workbook) {
                // 取sheet
                if (sheet == null) {
                    continue;
                }
                // 遍历当前sheet中的所有行
                for (Row row : sheet) {
                    if (row == null) {
                        continue;
                    }
                    // 验证是否本业务数据模板
                    if (row.getRowNum() == 0) {
                        if (!"姓名".equals(getValue(row.getCell(0))) || !"工号".equals(getValue(row.getCell(1)))) {
                            throw new BusinessException(ResultCodeEnum.ERR_0x1000.getCode(),"非本业务数据！");
                        } else {
                            continue;
                        }
                    }
                    // 连续两行为空行，则跳出
                    if ("".equals(getValue(row.getCell(0)).toString())) {
                        rowNull++;
                        if (rowNull == 2) {
                            break;
                        } else {
                            continue;
                        }
                    }
                    rowNull = 0;
                    // 遍历所有列
                    List<Object> li = new ArrayList<>();
                    for (Cell cell : row) {
                        li.add(getValue(cell));
                    }
                    objlist.add(li);
                }
            }
            // 将字符数组转为对象
            for (List<Object> obj : objlist) {
                if (ObjectUtils.isEmpty(obj)) {
                    break;
                }
                PaperBorrowTempletSearchRequest domain = new PaperBorrowTempletSearchRequest();
                domain.setInpNo(obj.get(0).toString());
                domain.setVisitId(obj.get(1).toString());
                domainList.add(domain);
            }
            // 根据导入的住院号获取标注患者信息
            if (CollectionUtil.isNotEmpty(domainList)) {
                // 去除重复数据
                domainList.stream().distinct();
            }
        } catch (Exception e) {
            throw new BusinessException(ResultCodeEnum.ERR_0x1000.getCode(),e.getMessage());
        }
        RecPaperBorrowRegisterRequest request=new RecPaperBorrowRegisterRequest();
        request.setRows(-1);
        request.setPage(-1);
        request.setPaperBorrowTempletSearchRequestList(domainList);
        IPage<RecPaperBorrowRegisterResponse> iPage = iRecPaperBorrowService.getRecPaperBorrowRegisterList(request);
        return Result.success(iPage);
    }



    /**
     * 根据输入流自动检测文件类型并创建相应的工作簿对象
     * @param inpStream
     * @return
     * @throws Exception
     */
    private Workbook getWorkBook(InputStream inpStream) {
        Workbook work = null;
        try {
            work = WorkbookFactory.create(inpStream);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return work;
    }

    /**
     * 获取单元格的类型数据值
     * @param cell
     * @return
     */
    private String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String value;
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = sdf.format(date);
                } else {
                    value = String.valueOf(cell.getNumericCellValue());
                    if (value.endsWith(".0")) {
                        value = value.substring(0, value.length() - 2);
                    }
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case FORMULA:
                value = String.valueOf(cell.getNumericCellValue());
                if ("NaN".equals(value)) {
                    value = cell.getStringCellValue();
                }
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                value = cell.getStringCellValue();
        }
        return value;
    }

模板

java

    @PostMapping("/exportBorrowTemplateExcel")
   
    public void exportBorrowTemplateExcel(HttpServletResponse response){
        List<PaperBorrowTempletSearchRequest> list=new ArrayList<>();
        try {
            String fileName="登记模板";
            response.setHeader("content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" +  URLEncoder.encode(fileName + ".xlsx", "utf-8"));
            // 生成workbook 并导出
            ExportParams exportParams=new ExportParams();
            //设置sheet名称
            exportParams.setSheetName("Sheet1");
            Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams,PaperBorrowTempletSearchRequest.class, list);
            ExcelExportUtil.closeExportBigExcel();
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

PaperBorrowTempletSearchRequest

java

@Data
public class PaperBorrowTempletSearchRequest {
    @Excel(name = "姓名", width = 25)
    @ApiModelProperty(value = "姓名")
    private String inpNo;

    @Excel(name = "工号", width = 25)
    @ApiModelProperty(value = "工号")
    private String visitId;
}