Excel的导入和导出以及动态模板的生成

动态模板的生成

动态模板访问链接

模板导入示例

Controller层

    /**
     * 导入数据
     * @return 导入数据
     */

    @PostResource(name = "导入报价",path = "/excelPrice" ,code ="freightDeductSeaPriceItem:excelPrice")
    public ResponseData<List<FreightDeductSeaPriceItem>> excelPrice(@RequestPart MultipartFile file) {
        return new SuccessResponseData<>(this.freightDeductSeaPriceItemService.excelPrice(file));
    }

ServiceImpl

 
 	@Override
    public List<FreightDeductSeaPriceItem> excelPrice(MultipartFile excelFile) {
        //对Excel代码进行读取
        SimpleExcelDataListener<FreightDeductSeaPriceItemExcelDto> readListener = new SimpleExcelDataListener<>(true);
        try {
            EasyExcel.read(excelFile.getInputStream(), FreightDeductSeaPriceItemExcelDto.class, readListener)
                    .sheet()
                    .doRead();
        } catch (IOException e) {
            throw new BusinessException(
                    OfficeExceptionEnum.OFFICE_ERROR,
                    StrUtil.format(OfficeExceptionEnum.OFFICE_ERROR.getUserTip(), e.getMessage())
            );
        }
        //对格式进行校验
        if (readListener.hasError()) {
            throw new BusinessException(FreightExceptionEnum.IMPORT_DATA_ERROR);//抛出异常
        }
        List<FreightDeductSeaPriceItemExcelDto> dataList = readListener.getDataList();
        if (dataList.isEmpty()) {
            throw new BusinessException(FreightExceptionEnum.EMPTY_ORDER_DATA);//抛出异常
        }
		//对数据进行组装保存
        List<FreightDeductSeaPriceItem> priceItems = dataList.stream().map(item -> {
            FreightDeductSeaPriceItem priceItem = new FreightDeductSeaPriceItem();
            //对导入数据进行封装
            BeanUtils.copyProperties(item, priceItem);
            if (ObjectUtils.isNotEmpty(item.getPortName())) {
                CustomsPort customsPort = customsPortService.lambdaQuery().eq(CustomsPort::getPortName, item.getPortName()).one();
                priceItem.setPortId(customsPort.getId());
            }
            priceItem.setIsClearance(IsClearanceEnum.getCodeByName(item.getIsClearance()));
            priceItem.setIsInspection(IsInspectionEnum.getCodeByName(item.getIsInspection()));
            priceItem.setBillingMethod(BillMethodType.getByName(item.getBillingMethod()));
            priceItem.setClearanceType(ClearanceTypeEnum.valueOfName(item.getClearanceType()));
            return priceItem;
        }).collect(Collectors.toList());
        return priceItems;
    }

模板导出示例

Controller层

    /**
     * 导出数据
     * @return 导出数据
     */
    @GetResource(name = "导出报价",path = "/exportPrice" ,code ="freightDeductSeaPriceItem:exportPrice")
    public void exportPrice(HttpServletResponse response, @RequestParam (required = true) Long deductSeaPriceId) {
        this.freightDeductSeaPriceItemService.exportPrice(response, deductSeaPriceId);
    }

ServiceImpl

	@Override
    public void exportPrice(HttpServletResponse response, Long deductSeaPriceId) {
	String fileName = "Excel文件名称" + System.currentTimeMillis();
        ExcelWriter excelWriter = null;//创建文件
        try {
            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            /*创建sheet页
             * writerSheet：第几个sheet页，sheet页名
             * head：继承了BaseRowModel的实体类 .class
             * */
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "第一个sheet的名字").head(FreightDeductSeaPriceItemExcelDto.class).build();
            //需要填充的数据
            List<FreightDeductSeaPriceItem> priceItems = this.lambdaQuery()
                    .eq(FreightDeductSeaPriceItem::getDeductSeaPriceId, deductSeaPriceId)
                    .eq(FreightDeductSeaPriceItem::getDelFlag, YesOrNoEnum.N.name())
                    .list();
            List<FreightDeductSeaPriceItemExcelDto> dtoList = priceItems.stream().map(item -> {
                FreightDeductSeaPriceItemExcelDto dto = new FreightDeductSeaPriceItemExcelDto();
                BeanUtils.copyProperties(item, dto);
                dto.setIsClearance(IsClearanceEnum.getNameByCode(item.getIsClearance()));
                dto.setIsInspection(IsInspectionEnum.getNameByCode(item.getIsInspection()));
                dto.setBillingMethod(item.getBillingMethod().getName());
                if (ObjectUtils.isNotEmpty(item.getClearanceType())){
                    dto.setClearanceType(item.getClearanceType().name());
                }
                return dto;
            }).collect(Collectors.toList());
            
            //把查到的数据写进sheet页
            excelWriter.write(dtoList, writeSheet);
			// .........

			//多个sheet页面重复上述操作即可

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
   	}