EasyPoi 导入并上传文件

  @ApiOperation(value = "导入对象Excel")
    @PostMapping(value = "/importSubsidyObjectExcel")
    public R<Boolean> importSubsidyObjectExcel(@RequestParam(value = "file") MultipartFile simpleFile, HttpServletRequest request,
                                   HttpServletResponse response) throws Exception {
        ImportParams params = new ImportParams();
        params.setTitleRows(StrUtil.isEmpty(request.getParameter("titleRows")) ? 1 : Convert.toInt(request.getParameter("titleRows")));
        params.setHeadRows(StrUtil.isEmpty(request.getParameter("headRows")) ? 1 : Convert.toInt(request.getParameter("headRows")));
        List<Map<String, String>> list = ExcelImportUtil.importExcel(simpleFile.getInputStream(), Map.class, params);

        if (list != null && !list.isEmpty()) {
            return R.success(baseService.addSubsidyObjectInfo(list));
        }
        return validFail("导入Excel无有效数据！");
    }


@Transactional(rollbackFor = Exception.class)
    @Override
    public Boolean addSubsidyObjectInfo(List<Map<String, String>> list) throws Exception {

        List<Map<String, String>> dataList = new ArrayList<>();
        List<SubsidyObjectExcelVO> allInfoList = new ArrayList<>();
        List<String> idCardList = new ArrayList<>();
        int i = 1;
        for (Map<String, String> map : list) {

            String difficultSituation = map.get("困难情况");
            String userName = map.get("人员姓名");
            String idCardNo = map.get("身份证");
            String mobile = map.get("联系电话");
            String registerAddress = map.get("户籍地址");
            String liveAddress = map.get("居住地址");

            SubsidyObjectExcelVO subsidyObjectExcelVO = new SubsidyObjectExcelVO();
            subsidyObjectExcelVO.setSerialRowNo(String.valueOf(i));
            subsidyObjectExcelVO.setDifficultLevel(difficultSituation);
            subsidyObjectExcelVO.setName(userName);
            subsidyObjectExcelVO.setIdCard(idCardNo);
            subsidyObjectExcelVO.setPhone(mobile);
            subsidyObjectExcelVO.setRegisteredAddress(registerAddress);
            subsidyObjectExcelVO.setDomicileAddress(liveAddress);
            i++;

            if(StringUtils.isAnyBlank(difficultSituation,userName,idCardNo,mobile,registerAddress,liveAddress)){
                subsidyObjectExcelVO.setRemark("必填项内容为空");
                allInfoList.add(subsidyObjectExcelVO);
                continue;
            }
            if(idCardList.contains(idCardNo)){
                subsidyObjectExcelVO.setRemark("证件号已存在");
                allInfoList.add(subsidyObjectExcelVO);
                continue;
            }
            //校验户籍地址和常驻地址是否匹配成功
            if(!ObjectUtils.allNotNull(getAreaIdByAddress(liveAddress),getAreaIdByAddress(registerAddress))){
                subsidyObjectExcelVO.setRemark("常驻地址或户籍地址为空");
                allInfoList.add(subsidyObjectExcelVO);
                continue;
            }
            idCardList.add(idCardNo);
            allInfoList.add(subsidyObjectExcelVO);
            dataList.add(map);
        }
        List<SubsidyObjectInfo> subsidyObjectInfoList = buildSubsidyObjectList(dataList);
        Boolean flag = true;
        if(CollectionUtils.isNotEmpty(subsidyObjectInfoList)){
            List<String> idCardUserList = subsidyObjectInfoList.stream().map(SubsidyObjectInfo::getIdCard).collect(Collectors.toList());
            //校验数据库是否存在
            LbqWrapper<SubsidyObjectInfo> subsidyObjectInfoLbqWrapper = Wraps.<SubsidyObjectInfo>lbQ()
                    .eq(SubsidyObjectInfo::getDataStatus,DataStatus.NORMAL)
                    .in(SubsidyObjectInfo::getIdCard,idCardUserList);
            List<SubsidyObjectInfo> oldSubsidyUserList = baseMapper.selectList(subsidyObjectInfoLbqWrapper);
            if(CollectionUtils.isEmpty(oldSubsidyUserList)){
                //不存在新增
                flag = super.saveBatch(subsidyObjectInfoList);
            }else{
                //部分存在处理,设置id
                Map<String,Long> oldSubsidyUserMap =
                        oldSubsidyUserList.stream().collect(Collectors.toMap(SubsidyObjectInfo::getIdCard,SubsidyObjectInfo::getId));
                subsidyObjectInfoList.stream().forEach(s->{
                    if(ObjectUtils.isNotEmpty(oldSubsidyUserMap) && ObjectUtils.isNotEmpty(oldSubsidyUserMap.get(s.getIdCard()))){
                        s.setId(oldSubsidyUserMap.get(s.getIdCard()));
                    }
                });
                flag = super.saveOrUpdateBatch(subsidyObjectInfoList);
            }
        }
        String headerName = "                     补贴申请在册人员列表                    注：红色字体为必填内容";
        uploadSubsidyObjectExcel(allInfoList,1,headerName);
        return flag;
    }

/**
     * @description: 设置单元格样式
     * @date 2022/12/9 10:41
     * @version 1.0.0
     */
    public void uploadSubsidyObjectExcel(List<SubsidyObjectExcelVO> dataList,int titleStyleIndex,String headerName) throws Exception {

        List<SubsidyObjectExcelVO> subsidyList = new ArrayList<>();
        subsidyList.addAll(dataList);
       
        String title = "补贴申请人员";
        //获取导出参数
        ExportParams exportParams = new ExportParams(headerName, title, ExcelType.XSSF);
        exportParams.setStyle(ExcelStyleUtil.class);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,SubsidyObjectExcelVO.class, dataList);

        Font font = workbook.createFont();
        font.setColor(IndexedColors.RED.getIndex());
        font.setBold(true);
        Sheet sheetAt = workbook.getSheetAt(0);
        CellStyle style = workbook.createCellStyle();
        setExcelCellStyle(style);
        style.setFont(font);

        // 获取列数
        int physicalNumberOfCells = sheetAt.getRow(0).getPhysicalNumberOfCells();
        int lastCellNum = sheetAt.getRow(0).getLastCellNum();
        //设置标题头样式
        for (int k = 0; k < physicalNumberOfCells; k++) {
            if(k == lastCellNum-titleStyleIndex){
                continue;
            }
            sheetAt.getRow(1).getCell(k).setCellStyle(style);;
        }

        CellStyle style2 = workbook.createCellStyle();
        setExcelCellStyle(style2);
        style2.setFillForegroundColor(IndexedColors.RED.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //设置行样式
        for (int i = 2; i <= sheetAt.getLastRowNum(); i++){
            if(StringUtils.isBlank(sheetAt.getRow(i).getCell(lastCellNum-1).getStringCellValue())){
                continue;
            }
            for (int k = 0; k < physicalNumberOfCells; k++) {
                sheetAt.getRow(i).getCell(k).setCellStyle(style2);
            }
        }
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.write(byteArrayOutputStream);
        byteArrayOutputStream.close();
        //将字节数组转换成输入流
        InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
        MultipartFile multipartFile = new MockMultipartFile(title,title+".xlsx",ContentType.TEXT_PLAIN.toString(), inputStream);
        FileUploadVO attachmentVO = new FileUploadVO();
        attachmentVO.setBizType("SUBSIDY_OBJECT_TYPE");
        attachmentVO.setBucket("SUBSIDY_OBJECT");
        attachmentVO.setStorageType(FileStorageType.LOCAL);
        FileResultVO fileResultVO = fileService.upload(multipartFile,attachmentVO);
        log.info("fileResultVO", JSON.toJSONString(fileResultVO));
    }

    /**
     * @description: 设置表格样式
     * @author  hmy
     * @date 2022/12/9 10:39
     * @version 1.0.0
     */
    private void setExcelCellStyle(CellStyle style){
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
    }

package com.yunweng.ec.common.utils; 
 
/**
 * @Description: 
 * @author: haomingyang
 * @date: 2022/12/08 14:49
 * @Version: v1.0.0
 **/
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

public class ExcelStyleUtil implements IExcelExportStyler {
    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 9;
    private static final short FONT_SIZE_ELEVEN = 10;
    private static final short FONT_SIZE_TWELVE = 10;
    /**
     * 大标题样式
     */
    private CellStyle headerStyle;
    /**
     * 每列标题样式
     */
    private CellStyle titleStyle;
    /**
     * 数据行样式
     */
    private CellStyle styles;

    public ExcelStyleUtil(Workbook workbook) {
        this.init(workbook);
    }

    /**
     * 初始化样式
     *
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
        this.styles = initStyles(workbook);
    }

    /**
     * 大标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
    }

    /**
     * 每列标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
    }

    /**
     * 数据行样式
     *
     * @param parity 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
     */
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
    }

    /**
     * 获取样式方法
     *
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
     */
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
    }

    /**
     * 模板使用的样式设置
     */
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
    }

    /**
     * 初始化--大标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        return style;
    }

    /**
     * 初始化--每列标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        //背景色
//        style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 初始化--数据行样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initStyles(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 基础样式
     *
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("宋体");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
}

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
    </dependency>
</dependencies>