EasyExcel分页上传数据
一、实例
- 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;
}
- 分页上传逻辑
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
@Transactional
public void invoke(TWorkSheetReadVO readVO, AnalysisContext analysisContext) {
list.add(readVO);
if (list.size() >= BATCH_COUNT) {
workSheetInnerService.batchInsertWorkSheet(list);
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param analysisContext
*/
@Override
@Transactional
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//这里也要保存数据，确保最后遗留的数据也存储到数据库
workSheetInnerService.batchInsertWorkSheet(list);
}
}
- 插入数据库
@Override
public void batchInsertWorkSheet(List<TWorkSheetReadVO> list) {
workSheetService.batchInsert(list);
}
- 实体对象
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
*/
@Data
public 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分页上传相应文档
