原文地址
一般使用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;
//...
}