当前位置： 首页 > news >正文

EasyExcel分页上传数据

news 来源：原创 2024/5/20 12:16:43

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*/
@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分页上传相应文档

相关文章：

  • json、jsonlines格式化显示
  • Android:国际化弹出框
  • Linux下centos操作系统安装Mysql8.0过程及踩坑填补
  • 鸿蒙（HarmonyOS）项目方舟框架（ArkUI）之Menu组件
  • 通过Spring @Validated 更优雅的实现参数校验
  • Vue3中路由配置Catch all routes (“*“) must .....问题
  • vscode的vetur文档格式化失效
  • 零基础学Python之面向对象
  • 计算机网络相关题目及答案（第五章）
  • WINDOWS搭建NFS服务器
  • NC6X单点登录设计文档说明
  • 【深入浅出Java性能调优】「底层技术原理体系」详细分析探索Java服务器性能监控Metrics框架的实现原理分析（Dropwizard度量基础案例指南)
  • MinMaxScaler, StandardScaler数据预处理中常用的两种缩放方法，用于将数据标准化或归一化到特定的范围或分布
  • 瑞芯微推理RKNN使用
  • stable-diffusion | v1-5-pruned.ckpt和v1-5-pruned-emaonly.ckpt的区别
  • ES6指北【2】—— 箭头函数
  • 《Javascript高级程序设计 (第三版)》第五章 引用类型
  • Bootstrap JS插件Alert源码分析
  • CSS3 变换
  • Fabric架构演变之路
  • JAVA 学习IO流
  • python 装饰器（一）
  • Python进阶细节
  • Spark学习笔记之相关记录
  • Yii源码解读－服务定位器（Service Locator）
  • 分布式熔断降级平台aegis
  • 猫头鹰的深夜翻译：JDK9 NotNullOrElse方法
  • 模型微调
  • 让你的分享飞起来——极光推出社会化分享组件
  • 如何优雅地使用 Sublime Text
  • 腾讯优测优分享 | Android碎片化问题小结——关于闪光灯的那些事儿
  • 协程
  • LIGO、Virgo第三轮探测告捷，同时探测到一对黑洞合并产生的引力波事件 ...
  • raise 与 raise ... from 的区别
  • 测评：对于写作的人来说，Markdown是你最好的朋友 ...
  • ​软考-高级-系统架构设计师教程（清华第2版）【第12章 信息系统架构设计理论与实践（P420~465）-思维导图】​
  • #QT项目实战（天气预报）
  • #在 README.md 中生成项目目录结构
  • (13)Latex：基于ΤΕΧ的自动排版系统——写论文必备
  • （初研） Sentence-embedding fine-tune notebook
  • (实战)静默dbca安装创建数据库 --参数说明+举例
  • (续)使用Django搭建一个完整的项目(Centos7+Nginx)
  • (一)认识微服务
  • （转）JVM内存分配 -Xms128m -Xmx512m -XX:PermSize=128m -XX:MaxPermSize=512m
  • (转载)深入super，看Python如何解决钻石继承难题
  • ***php进行支付宝开发中return_url和notify_url的区别分析
  • .gitignore文件—git忽略文件
  • .NET 6 Mysql Canal (CDC 增量同步,捕获变更数据) 案例版
  • .net core 6 集成和使用 mongodb
  • .Net Remoting(分离服务程序实现) - Part.3
  • .NET 中让 Task 支持带超时的异步等待
  • .Net通用分页类(存储过程分页版,可以选择页码的显示样式,且有中英选择)
  • .net知识和学习方法系列（二十一）CLR-枚举
  • .ui文件相关
  • @TableLogic注解说明，以及对增删改查的影响