EasyExcel分页上传数据

一、实例

  1. controller上传入口
 @PostMapping("/upload")@ResponseBody@Log(title = "导入工单", businessType = BusinessType.IMPORT)public AjaxResult uploadFile(HttpServletRequest request, MultipartFile files) throws Exception {AjaxResult ajaxResult = this.checkFile(files);if (HttpStatus.SUCCESS != (int) ajaxResult.get(AjaxResult.CODE_TAG)) {return ajaxResult;}try {EasyExcel.read(files.getInputStream(), TWorkSheetReadVO.class, new WorkSheetListener(workSheetInnerService, new ArrayList<>())).sheet().doRead();} finally {//}return AjaxResult.success(String.format("上传成功"));}private AjaxResult checkFile(MultipartFile file) {String name = file.getOriginalFilename();String subName = name.substring(name.lastIndexOf('.'), name.length());if (!Lists.newArrayList(".xlsx", ".xls").contains(subName)) {return AjaxResult.error("只支持excel文件上传格式");}boolean checkSize = checkFileSize(file.getSize(), 10, "M");if(checkSize) return AjaxResult.success();return AjaxResult.error("上传的文件大小超过限制");}/** * 判断文件大小 * * @param len文件长度 * @param size 限制大小 * @param unit 限制单位（B,K,M,G） * @author youlu * @return */public static boolean checkFileSize(Long len, int size, String unit) {// long len = file.length();double fileSize = 0;if ("B".equals(unit.toUpperCase())) {fileSize = (double) len;} else if ("K".equals(unit.toUpperCase())) {fileSize = (double) len / 1024;} else if ("M".equals(unit.toUpperCase())) {fileSize = (double) len / 1048576;} else if ("G".equals(unit.toUpperCase())) {fileSize = (double) len / 1073741824;}if (fileSize > size) {return false;}return true;}
  1. 分页上传逻辑
package com.smy.ows.project.worksheet.listener;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.smy.ows.project.worksheet.domain.vo.TWorkSheetReadVO;import com.smy.ows.project.worksheet.service.inner.WorkSheetInnerService;import org.springframework.transaction.annotation.Transactional;import java.util.List;/** * @Description * @ClassName ExcelModelListener * @Author youlu * @date 2023.02.02 15:01 */public class WorkSheetListener extends AnalysisEventListener<TWorkSheetReadVO> {private WorkSheetInnerService workSheetInnerService;private List<TWorkSheetReadVO> list;private static final int BATCH_COUNT = 1000;public WorkSheetListener(WorkSheetInnerService workSheetInnerService, List<TWorkSheetReadVO> list) {this.workSheetInnerService = workSheetInnerService;this.list = list;}@Override@Transactionalpublic void invoke(TWorkSheetReadVO readVO, AnalysisContext analysisContext) {list.add(readVO);if (list.size() >= BATCH_COUNT) {workSheetInnerService.batchInsertWorkSheet(list);list.clear();}}/** * 所有数据解析完成了 都会来调用 * * @param analysisContext */@Override@Transactionalpublic void doAfterAllAnalysed(AnalysisContext analysisContext) {//这里也要保存数据，确保最后遗留的数据也存储到数据库workSheetInnerService.batchInsertWorkSheet(list);}}
  1. 插入数据库
@Overridepublic void batchInsertWorkSheet(List<TWorkSheetReadVO> list) {workSheetService.batchInsert(list);}
  1. 实体对象
package com.smy.ows.project.worksheet.domain.vo;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.HeadStyle;import com.alibaba.excel.converters.date.DateStringConverter;import com.alibaba.excel.enums.poi.FillPatternTypeEnum;import com.fasterxml.jackson.annotation.JsonFormat;import com.smy.framework.base.DesensitizationAnnotation;import com.smy.ows.project.worksheet.enums.SheetLevelEnums;import com.smy.ows.project.worksheet.enums.WorkSheetStatus;import com.smy.ows.util.*;import lombok.Data;import java.io.Serializable;import java.util.Date;/** * 客诉工单对象 t_work_sheet * * @author smy * @date 2023-01-11 */@Datapublic class TWorkSheetReadVO implements Serializable {private static final long serialVersionUID = 5924360788178861972L;/** * 客诉标题 */@ExcelProperty(value = "客诉标题", index = 0)@ColumnWidth(20)private String complaintHeadline;/** * @see SheetLevelEnums */@ExcelProperty(value = "优先级", index = 1, converter = PriorityIntegerStringConverter.class)@ColumnWidth(10)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private Integer priority;@ExcelProperty(value = "客户姓名", index = 2)@ColumnWidth(20)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String custName;/** * 客户号 */@ExcelProperty(value = "客户号", index = 3)@ColumnWidth(20)private String custNo;@DesensitizationAnnotation@ExcelProperty(value = "客户手机号", index = 4)@ColumnWidth(20)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String custMobile;@DesensitizationAnnotation@ExcelProperty(value = "客户身份证", index = 5)@ColumnWidth(30)private String custIdNo;/** * 投诉时间 */@ExcelProperty(value = "投诉时间(yyyy-MM-dd HH:mm:ss)", index = 6, converter = DateStringConverter.class)@ColumnWidth(40)@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")private Date complaintTime;//反馈渠道@ExcelProperty(value = "反馈渠道", index = 7, converter = ChannelStringStringConverter.class)@ColumnWidth(15)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String feedbackChannel;@ExcelProperty(value = "工单类型", index = 8, converter = TypeIntegerStringConverter.class)@ColumnWidth(15)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private Integer type;@ExcelProperty(value = "业务类型", index = 9, converter = BizTypeIntegerStringConverter.class)@ColumnWidth(15)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private Integer bizType;@DesensitizationAnnotation@ExcelProperty(value = "客户联系方式", index = 10)@ColumnWidth(15)private String custContactMobile;/** * 所属资方 */@ExcelProperty(value = "所属资方", index = 11)@ColumnWidth(15)private String capital;@ExcelProperty(value = "投诉内容", index = 12)@ColumnWidth(30)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String content;/** * @see WorkSheetStatus */@ExcelProperty(value = "工单状态", index = 13, converter = StatusIntegerStringConverter.class)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)@ColumnWidth(15)private Integer status;@ExcelProperty(value = "处理结果", index = 14, converter = ResultIntegerStringConverter.class)@ColumnWidth(15)private Integer result;/** * 处理情况 */@ExcelProperty(value = "处理情况", index = 15)@ColumnWidth(15)private String handingInfo;}

其中要注意的是converter格式转换使用 。也可以自定义转换，将上传的数据转换成真正要接收的数据，例如：

@ExcelProperty(value = "优先级", index = 1, converter = PriorityIntegerStringConverter.class)@ColumnWidth(10)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private Integer priority;

这个属性是Integer 类型的。上传的数据如下是字符串类型的（一般，紧急，特急）。因此需要将其映射成数据库对应的integer数据。此时就需要自定义转换，在convertToJavaData方法中将字符串转换成相应的integer值。

package com.smy.ows.util;import com.alibaba.excel.converters.Converter;import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.GlobalConfiguration;import com.alibaba.excel.metadata.data.ReadCellData;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.metadata.property.ExcelContentProperty;import com.google.common.collect.Lists;import com.smy.ows.common.core.domain.entity.SysDictData;import com.smy.ows.common.utils.ParamThreadLocal;import com.smy.ows.project.worksheet.constant.WorksheetDictTypeConstant;import java.text.ParseException;import java.util.List;import java.util.Map;import java.util.Optional;import java.util.stream.Collectors;public class PriorityIntegerStringConverter implements Converter<Integer> {public PriorityIntegerStringConverter() {}public Class<" />> supportJavaTypeKey() {return Integer.class;}public CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/** * 读 * @author youlu * @date 2023/8/18 10:46 * @param cellData * @param contentProperty * @param globalConfiguration * @return java.lang.Integer */public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws ParseException {String priorityDesc = cellData.getStringValue();Map<String, List<SysDictData>> dictDataMap = (Map<String, List<SysDictData>>) ParamThreadLocal.getParam();Map<String, SysDictData> dataMap = Optional.ofNullable(dictDataMap.get(WorksheetDictTypeConstant.WORKSHEET_PRIORITY)).orElse(Lists.newArrayList()).stream().collect(Collectors.toMap(k -> k.getDictLabel(), m -> m, (m1, m2) -> m2));SysDictData sysDictData = dataMap.get(priorityDesc);if (sysDictData == null) {return -1;}return Integer.valueOf(sysDictData.getDictValue());}/** * 写 * @author youlu * @date 2023/8/18 10:46 * @param value * @param contentProperty * @param globalConfiguration * @return com.alibaba.excel.metadata.data.WriteCellData */public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {Map<String, List<SysDictData>> dictDataMap = (Map<String, List<SysDictData>>) ParamThreadLocal.getParam();Map<String, SysDictData> dataMap = Optional.ofNullable(dictDataMap.get(WorksheetDictTypeConstant.WORKSHEET_PRIORITY)).orElse(Lists.newArrayList()).stream().collect(Collectors.toMap(k -> k.getDictValue(), m -> m, (m1, m2) -> m2));SysDictData sysDictData = dataMap.get(String.valueOf(value));if (sysDictData == null) {return new WriteCellData<String>("" + value);}return new WriteCellData<String>(sysDictData.getDictLabel());}}

二、参考文档

easyExcel分页上传相应文档