Java常用方法（三）Excel

原文地址

原文链接

一般使用EeayExcel

//data
@Data
@ColumnWidth(25)
@HeadRowHeight(30)
@HeadFontStyle(color = 0)
public class ExceptionDataExcelBO {

    private static final String TITLE =
            "注：导入关闭时，关闭理由需必填，且小于20个中文汉字；如遇导入关闭前已存在已关闭的房屋，则无法重复关闭。";

    @ExcelProperty({TITLE, "异常id"})
    @ApiModelProperty(value = "异常id")
    private String exceptionId;

    @ExcelProperty({TITLE, "产权编号"})
    @ApiModelProperty(value = "产权编号")
    private String id;

    @ExcelProperty({TITLE, "所属社区"})
    @ApiModelProperty(value = "所属社区")
    private String divisionAreaName;

    @ExcelProperty({TITLE, "房屋产权证地址"})
    @ApiModelProperty(value = "房屋产权证地址")
    private String houseCertificateAddr;

    @ExcelProperty({TITLE, "房屋性质"})
    @ApiModelProperty(value = "房屋性质：2-企业内部宿舍，3-政府保障性用房，4-村（社区）自建公寓， 5-个人出租房")
    private String housePropertyRightName;

    @ExcelProperty({TITLE, "异常原因"})
    @ApiModelProperty("异常原因")
    private String exceptionCause;

    @ExcelProperty({TITLE, "门锁申请状态"})
    @ApiModelProperty("门锁申请状态")
    private String processStatusName;

    @HeadFontStyle(color = 2)
    @ExcelProperty({TITLE, "*关闭理由"})
    @ApiModelProperty("关闭原因")
    private String closeCause;

    @ExcelProperty({TITLE, "录入方式"})
    @ApiModelProperty(value = "录入方式")
    private String resourceFromName;

    @ExcelProperty({TITLE, "房间数量"})
    @ApiModelProperty(value = "房间数量")
    private Integer roomCount;

    @ExcelProperty({TITLE, "房屋类型名称"})
    @ApiModelProperty(value = "房屋类型名称")
    private String houseCategoryName;

    @ExcelProperty({TITLE, "产权人"})
    @ApiModelProperty(value = "产权人")
    private String houseHolderName;

    @ExcelProperty({TITLE, "产权人手机"})
    @ApiModelProperty(value = "产权人手机")
    private String houseHolderPhone;

}

//read
List<CubeItemUpdateExcelBO> cubeItemUpdateExcelBoList =
            EasyExcel.read(file.getInputStream()).head(CubeItemUpdateExcelBO.class).sheet().doReadSync();
cubeItemUpdateExcelBoList.removeIf(item -> item.getSkuId() == null);

//read2
public ExceptionCloseImportResult importClose(MultipartFile file) {

        try {
            List<ExceptionDataExcelBO> closeList = new ArrayList<>();
            List<ExceptionDataExcelBO> focusList = new ArrayList<>();
            List<HrExceptionPr> focusPrList = new ArrayList<>();
            EasyExcel.read(file.getInputStream(), ExceptionDataExcelBO.class,
                    new PageReadListener<ExceptionDataExcelBO>(dataList -> {
                        for (ExceptionDataExcelBO exceptionDataExcelBo : dataList) {
                            if (StringUtils.isBlank(exceptionDataExcelBo.getId())) {
                                throw new ServiceException(
                                        new CommonServiceException(ResponseEnum.E_1004.getCode(), "存在产权id为空"));
                            }
                            if (StringUtils.isBlank(exceptionDataExcelBo.getExceptionId())) {
                                throw new ServiceException(
                                        new CommonServiceException(ResponseEnum.E_1004.getCode(), "存在异常id为空"));
                            }

                            closeList.add(exceptionDataExcelBo);
                            if(StringUtils.isNotBlank(exceptionDataExcelBo.getCloseCause())) {
                                focusList.add(exceptionDataExcelBo);

                                HrExceptionPr hrExceptionPr = new HrExceptionPr();
                                hrExceptionPr.setId(exceptionDataExcelBo.getExceptionId());
                                hrExceptionPr.setCloseCause(exceptionDataExcelBo.getCloseCause());
                                hrExceptionPr.setCloseTime(new Date());
                                hrExceptionPr.setCloseUser(LoginContextHolder.me().getSysLoginUserId());
                                hrExceptionPr.setCloseUserName(LoginContextHolder.me().getSysLoginUser().getName());
                                focusPrList.add(hrExceptionPr);
                            }
                        }
                    })).sheet().doRead();

            ExceptionCloseImportResult closeImportResult = new ExceptionCloseImportResult();
            closeImportResult.setAllNum(closeList.size());
            closeImportResult.setIgnoreNum(closeList.size() - focusList.size());
            closeImportResult.setSuccessNum(0);
            closeImportResult.setFailNum(0);
            if(!CollectionUtils.isEmpty(focusList)) {
                //正常流程，没想到什么情况下会更新失败

                LamdbaCondition<HrHousePr> condition = new LamdbaCondition<>(HrHousePr.class);
                condition.and().andIn(HrHousePr::getId,
                        focusList.stream().map(ExceptionDataExcelBO::getId).collect(Collectors.toList()));
                HrHousePr closeHousePr = new HrHousePr();
                closeHousePr.setProcessStatus(ProcessStatusEnum.CLOSE.getCode());
                hrHousePrMapper.updateByConditionSelective(closeHousePr, condition);

                //todo 有时间改成非循环
                for (HrExceptionPr hrExceptionPr : focusPrList) {
                    exceptionPrMapper.updateByPrimaryKeySelective(hrExceptionPr);
                }
                closeImportResult.setSuccessNum(focusPrList.size());

            }
            return closeImportResult;
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new ServiceException(
                    new CommonServiceException(ResponseEnum.E_1014.getCode(), ex.getMessage()));
        }

    }

//write
public Boolean exportItem(Long actId, HttpServletResponse response)
{
    //获取基本数据
    List<CubeItemBO> cubeItemBOList = ItemCubeConverter.cubeItemListPoToBo(cubeItemDao.queryItemByActIdAndStatus(actId, null));
    if(CollectionUtils.isEmpty(cubeItemBOList))
    {
        throw new ParamException("提报ID错误或者提报ID下没有商品，未查询到提报信息");
    }
    //获取拓展数据类型
    List<CubeProm> cubePromList = cubePromDao.selectByActId(actId);
    if(CollectionUtils.isEmpty(cubePromList))
    {
        log.error("[op:queryItem] db sync error");
        throw new DbException("数据库同步错误");
    }
    List<CubeItemDetailBO> cubeItemDetailBoList = cubeItemPropertyHandler.getDetail(cubeItemBOList, cubePromList.get(0).getCouponShare(), cubePromList.get(0).getCouponDiscount().doubleValue(), cubeDao.selectById(actId));
    //数据转化
    List < CubeItemExcelBO > cubeItemExcelBoList = ItemCubeConverter.cubeItemDetailBoToExcelBo(cubeItemDetailBoList);
    //数据写入
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    StringBuilder stringBuilder = new StringBuilder();
    String exportName = null;
    try
    {
        exportName = URLEncoder.encode("提报商品", "UTF-8").replaceAll("\\+", "%20");
    }
    catch(UnsupportedEncodingException e)
    {
        log.error("[op:exportItem] file name encode error, e=", e);
    }
    stringBuilder.append("attachment;filename=").append(exportName).append(".xlsx");
    String responseHeader = stringBuilder.toString();
    response.setHeader("Content-Disposition", responseHeader);
    try
    {
        EasyExcel.write(response.getOutputStream(), CubeItemExcelBO.class).sheet().doWrite(cubeItemExcelBoList);
    }
    catch(Exception e)
    {
        log.info("[op:exportItem] write excel fail e=", e);
        return false;
    }
    return true;
}
//CubeItemExcelBO
@Data
@ColumnWidth(25)
public class CubeItemExcelBO {

    /**
     * 提报活动id
     */
    @ExcelProperty(value = "提报活动Id")
    private Long actId;

    /**
     * SpuId
     */
    @ExcelProperty(value = "商品Id")
    private Long spuId;

    /**
     * SkuId
     */
    @ExcelProperty(value = "SkuId")
    private Long skuId;
    
    //...
}

原文地址

原文链接