springboot-vue excel上传导出

数据库

device_manage表

字段，id，workshop,device_number,device_name,device_model,warn_time,expired_time

device_warn表

字段，id,warn_time,expired_time

后端

实体类格式

device_manage

@Data
@TableName("device_manage")/*
设备管理
*/
public class DeviceManageEntity {private static final long serialVersionUID = 1L;/*** 主键*/@TableIdprivate Integer id;/*** 车间名称*/private String workshop;/*** 设备编号*/private String deviceNumber;/*** 设备名称*/private String deviceName;/*** 设备型号*/private String deviceModel;/*** 维保预警时间*/@JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")private Date warnTime;/*** 维保到期时间*/@JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")private Date expiredTime;}

device_warn

@Data
@TableName("device_warn")/*保养预警*/
public class DeviceWarnEntity {private static final long serialVersionUID = 1L;/*** 编号*/@TableIdprivate Integer id;/*** 保养到期时间*/@JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")private Date expiredTime;/*** 预警时间*/@JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")private Date warnTime;}

选择导出的字段warnVo

@Data
@ColumnWidth(20)
public class WarnVo {//传输给前端展示//id@ExcelIgnoreprivate Long id;//车间@ExcelProperty("车间名称")private String workshop;//设备编号@ExcelProperty("设备编号")private String deviceNumber;//设备名称@ExcelProperty("设备名称")private String deviceName;//设备型号@ExcelProperty("设备型号")private String deviceModel;//维保到期时间@ExcelProperty("维保到期时间")@DateTimeFormat("yyyy-MM-dd HH:mm:ss")private Date expiredTime;//预警时间@ExcelProperty("预警时间")@DateTimeFormat("yyyy-MM-dd HH:mm:ss")private Date warnTime;
}

controller层

@RestController
@RequestMapping("/dev/warn")
public class exportController {@Autowiredprivate DeviceWarnService iTainWarnService;
//字典类，前端下拉框选项@Autowiredprivate SysDictService sysDictService;@Autowiredprivate DeviceManageService iDeviceService;//文件上传@PostMapping("/upload")@ResponseBodypublic R upload(MultipartFile file) throws IOException {if (file==null){System.out.println("文件为空");}WarnVoListener warnVoListener = new WarnVoListener(sysDictService, iTainWarnService, iDeviceService);
//初始化tipsList<ImportTips> tips = new ArrayList<>();for (WarnVo data : warnVoListener.getDatas()) {tips = warnVoListener.getTips();}if (tips.size() > 0) {return R.error();}EasyExcel.read(file.getInputStream(), WarnVo.class, warnVoListener).sheet().doRead();return R.ok();}//文件导出/*** 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）* 这种方法是将Excel文件的生成过程放在后端进行。前端发起一个请求到后端，后端处理数据并生成Excel文件，然后将文件返回给前端进行下载。* 这种方法的优点是可以将数据处理的压力放在后端，前端只需要处理请求和下载文件的逻辑。* @since 2.1.1，设置响应头*/private  void setExcelResponseProp(HttpServletResponse response,String rawFileName) throws UnsupportedEncodingException {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");}@GetMapping("/download")public void download(HttpServletResponse response) throws IOException {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmantry {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");this.setExcelResponseProp(response,"保养预警");List<WarnVo> warnVos = iTainWarnService.listAllWarn();
//得到字典类所有选项List<SysDictEntity> workShopList = sysDictService.maintenanceList(" workshop");for (WarnVo warnVo : warnVos) {for (SysDictEntity sysDictEntity :  workShopList) {if (sysDictEntity.getValue().compareTo(warnVo.getWorkshop())==0){warnVo.setWorkshop(sysDictEntity.getName());}}}List<SysDictEntity> deviceModelList = sysDictService.maintenanceList("deviceModel");for (WarnVo warnVo : warnVos) {for (SysDictEntity sysDictEntity :  deviceModelList) {if (sysDictEntity.getValue().compareTo(warnVo.getDeviceModel())==0){warnVo.setDeviceModel(sysDictEntity.getName());}}}// 这里需要设置不关闭流EasyExcel.write(response.getOutputStream(), WarnVo.class)// 导出Excel时在此处注册handler.registerWriteHandler(new CustomSheetWriteHandler(sysDictService)).autoCloseStream(Boolean.FALSE).sheet("保养预警").doWrite(warnVos);} catch (Exception e) {// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map<String, String> map = new HashMap<>();map.put("status", "failure");map.put("message", "下载文件失败" + e.getMessage());response.getWriter().println(JSON.toJSONString(map));}}}

listener

CustomSheetWriteHandler导出

@Service
public class CustomSheetWriteHandler implements SheetWriteHandler {@Autowiredprivate SysDictService sysDictService;public CustomSheetWriteHandler(SysDictService sysDictService) {this.sysDictService = sysDictService;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** 想实现Excel引用其他sheet页数据作为单元格下拉选项值，* 需要重写该方法** @param writeWorkbookHolder* @param writeSheetHolder*/@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 构造样例数据，该数据可根据实际需要，换成业务数据// 实际数据可通过构造方法，get、set方法等由外界传入List<String> selectworkshopList = new ArrayList<>();List<SysDictEntity> workshoplist = sysDictService.maintenanceList("workshop");for (SysDictEntity sysDictEntity : workshoplist) {if (sysDictEntity.getSort()!=null){selectworkshopList.add(sysDictEntity.getName());}}List<String> selectmodelList = new ArrayList<>();List<SysDictEntity> modellist = sysDictService.maintenanceList("deviceModel");for (SysDictEntity sysDictEntity : modellist) {if (sysDictEntity.getSort()!=null){selectmodelList.add(sysDictEntity.getName());}}// 构造下拉选项单元格列的位置，以及下拉选项可选参数值的map集合// key：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2// value：key对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项1..100Map<Integer, List<String>> selectParamMap = new HashMap<>();selectParamMap.put(0, selectworkshopList);selectParamMap.put(3, selectmodelList);// 获取第一个sheet页Sheet sheet = writeSheetHolder.getCachedSheet();// 获取sheet页的数据校验对象DataValidationHelper helper = sheet.getDataValidationHelper();// 获取工作簿对象，用于创建存放下拉数据的字典sheet数据页Workbook workbook = writeWorkbookHolder.getWorkbook();// 迭代索引，用于存放下拉数据的字典sheet数据页命名int index = 1;for (Map.Entry<Integer, List<String>> entry : selectParamMap.entrySet()) {// 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好String dictSheetName = "dict_hide_sheet" + index;Sheet dictSheet = workbook.createSheet(dictSheetName);// 隐藏字典sheet页workbook.setSheetHidden(index++, true);// 设置下拉列表覆盖的行数，从第一行开始到最后一行，这里注意，Excel行的// 索引是从0开始的，我这边第0行是标题行，第1行开始时数据化，可根据实// 际业务设置真正的数据开始行，如果要设置到最后一行，那么一定注意，// 最后一行的行索引是1048575，千万别写成1048576，不然会导致下拉列表// 失效，出不来CellRangeAddressList infoList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());int rowLen = entry.getValue().size();for (int i = 0; i < rowLen; i++) {// 向字典sheet写数据，从第一行开始写，此处可根据自己业务需要，自定// 义从第几行还是写，写的时候注意一下行索引是从0开始的即可dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue().get(i));}// 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().size();Name name = workbook.createName();name.setNameName(dictSheetName);// 将关联公式和sheet页做关联name.setRefersToFormula(refers);// 将上面设置好的下拉列表字典sheet页和目标sheet关联起来DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);DataValidation dataValidation = helper.createValidation(constraint, infoList);sheet.addValidationData(dataValidation);}}
}

WarnVoListener导入

@Slf4j
public class WarnVoListener extends AnalysisEventListener<WarnVo> {private static final Logger LOGGER = LoggerFactory.getLogger(WarnVoListener.class);/*** 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收*/private static final int BATCH_COUNT = 5;//    List<WarnVo> list = new ArrayList<>();List<DeviceManageEntity> deviceList = new ArrayList<>();List<DeviceWarnEntity> tainWarnList = new ArrayList<>();/*** 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。*/private SysDictService sysDictService;private DeviceWarnService iTainWarnService;private DeviceManageService iDeviceService;//    public WarnVoListener() {
//        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
//        demoDAO = new DemoDAO();
//    }/*** 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来**/public WarnVoListener(SysDictService sysDictService,DeviceWarnService iTainWarnService,DeviceManageService iDeviceService) {this.sysDictService = sysDictService;this.iTainWarnService = iTainWarnService;this.iDeviceService = iDeviceService;}/*** 返回提示语*/private List<ImportTips> tips = new ArrayList<>();/*** 自定义用于暂时存储data* 可以通过实例获取该值*/private List<WarnVo> datas = new ArrayList<>();/*** 这个每一条数据解析都会来调用** @param data*            one row value. Is is same as {@link AnalysisContext#readRowHolder()}* @param context*/@Overridepublic void invoke(WarnVo data, AnalysisContext context) {
//        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));// 该行数据是否有错误boolean checkRowError = false;LOGGER.info("数据导入，解析第{}行数据：{}" , context.readRowHolder().getRowIndex() , data);List<DeviceManageEntity> devList = iDeviceService.list();for (DeviceManageEntity devices : devList) {if (devices.getDeviceNumber().equals(data.getDeviceNumber())){saveTips(context.readRowHolder().getRowIndex(),"导入文件中设备编号有重复",tips);checkRowError = true;}}//当该行数据没有错误时，数据存储到集合，供批量处理。if(!checkRowError){//device表DeviceManageEntity device = new DeviceManageEntity();
//            device.setDeviceModel(data.getDeviceModel());device.setDeviceName(data.getDeviceName());device.setDeviceNumber(data.getDeviceNumber());device.setWarnTime(data.getWarnTime());device.setExpiredTime(data.getExpiredTime());List<SysDictEntity> list = sysDictService.maintenanceList("workshop");for (SysDictEntity sysDictEntity : list) {if (sysDictEntity.getName().compareTo(data.getWorkshop())!=0){device.setWorkshop(sysDictEntity.getValue());}}List<SysDictEntity> modellist = sysDictService.maintenanceList("deviceModel");for (SysDictEntity sysDictEntity : modellist) {if (sysDictEntity.getName().compareTo(data.getDeviceModel())!=0){device.setDeviceModel(sysDictEntity.getValue());}}this.deviceList.add(device);//tain_warn表/*  DeviceWarnEntity tainWarn = new DeviceWarnEntity();tainWarn.setExpiredTime(data.getExpiredTime());tainWarn.setWarnTime(data.getWarnTime());this.tainWarnList.add(tainWarn);*/datas.add(data);}// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (this.deviceList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listthis.deviceList.clear();}if (this.tainWarnList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listthis.tainWarnList.clear();}}/*** 所有数据解析完成了 都会来调用** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();LOGGER.info("所有数据解析完成！");}/*** 加上存储数据库*/private void saveData() {LOGGER.info("{}条数据，开始存储数据库！", deviceList.size());LOGGER.info("{}条数据，开始存储数据库！", tainWarnList.size());
//        demoDAO.save(list);
//        iTainWarnService.saveBatch(list);//存入数据库iDeviceService.saveBatch(deviceList);iTainWarnService.saveBatch(tainWarnList);LOGGER.info("存储数据库成功！");}/*** 保存提示信息到集合* @param rowIndex 行数* @param desc 提示信息* @param tips 存入集合*/private void saveTips(Integer rowIndex, String desc, List<ImportTips> tips) {ImportTips tip = new ImportTips();tip.setRowNum(rowIndex);tip.setTips(desc);tips.add(tip);}/*** 返回数据* @return 返回提示集合**/public List<ImportTips> getTips() {return tips;}/*** 返回数据* @return 返回读取的数据集合**/public List<WarnVo> getDatas() {return datas;}
}

service层

device_manage略

device_warn

public interface DeviceWarnService extends IService<DeviceWarnEntity> {List<WarnVo> listAllWarn();
}

service_impl层

@Service("DeviceWarnService")
public class DeviceWarnServiceImpl extends ServiceImpl<DeviceWarnDao, DeviceWarnEntity> implements DeviceWarnService {@Autowiredprivate DeviceWarnDao deviceWarnDao;@Overridepublic List<WarnVo> listAllWarn() {QueryWrapper<WarnVo> qw = new QueryWrapper<>();return this.deviceWarnDao.selectWarn(qw);}
}

Dao层，使用@Select注解写入sql语句获得所有信息

@Mapper
public interface DeviceWarnDao extends BaseMapper<DeviceWarnEntity> {@Select("select w.id,d.workshop,d.device_number,d.device_name,d.device_model,w.warn_time,w.expired_time,w.device_status from device_warn w left join device_manage d on d.id=w.device_id where w.is_deleted = 0")List<WarnVo> selectWarn(@Param(Constants.WRAPPER) QueryWrapper<WarnVo> wrapper);
}

前端

<template><div class="mod-config"><!-- 导入导出 --><el-form :inline="true"><el-form-item><el-button type="primary" icon="el-icon-share" @click="download()" >一键导出</el-button></el-form-item><el-form-item><el-uploadstyle="width: 400px"action="http://localhost:8080/wedu/dev/warn/upload":headers="tokenInfo":on-preview="handlePreview":on-remove="handleRemove":before-remove="beforeRemove"multiple:limit="3":on-exceed="handleExceed":file-list="fileList"><el-button type="primary">点击上传</el-button></el-upload></el-form-item></el-form><el-table:data="dataList"borderv-loading="dataListLoading"@selection-change="selectionChangeHandle"style="width: 100%;"><el-table-columnprop="workshop"header-align="center"align="center"label="车间名称"></el-table-column><el-table-columnprop="deviceNumber"header-align="center"align="center"label="设备编号"></el-table-column><el-table-columnprop="deviceName"header-align="center"align="center"label="设备名称"></el-table-column><el-table-columnprop="deviceModel"header-align="center"align="center"label="设备型号"></el-table-column><el-table-columnprop="expiredTime"header-align="center"align="center"label="维保到期时间"></el-table-column><el-table-columnprop="warnTime"header-align="center"align="center"label="维保预警时间"></el-table-column></template><script>export default {data () {return {tokenInfo: {token: this.$cookie.get("token"),},dataForm: {deviceNumber: ''},// 车间字典项allchejian: [],// 车间筛选workshops: [],bydicts: [],byMap: {},fileUploadBtnText: "点击上传", //上传文件提示文字fileList: [], // 上传文件列表fileUploadVisible:false}},activated () {this.getDataList();this.loadAllChejian("workshop");this.getBydicts("deviceModel");},methods: {fileUpload(){this.fileUploadVisible = true;this.$nextTick(() => {this.$refs.FileUpload.init();});},// 文件列表移除文件时的钩子handleRemove(file, fileList) {console.log(file, fileList);},// 点击文件列表中已上传的文件时的钩子handlePreview(file) {console.log(file);},// 限制上传文件的个数和定义超出限制时的行为handleExceed(files, fileList) {this.$message.warning(`当前限制选择 3 个文件，本次选择了 ${files.length} 个文件，共选择了 ${files.length + fileList.length} 个文件`);},// 文件列表移除文件时之前的钩子beforeRemove(file, fileList) {return this.$confirm(`确定移除 ${file.name}？`);},onUploadExcelError(response) {if (res.code === 500) this.$message.error(res.message);},//文件导出download() {this.$http({url: this.$http.adornUrl("/dev/warn/download"),method: "get",//设置响应类型（重要responseType: "blob",}).then((response) => {// 创建一个url，接收到的二进制数据const url = window.URL.createObjectURL(new Blob([response.data]));// 创建一个a标签,用于触发文件下载const link = document.createElement("a");// a元素的href属性为创建的urllink.href = url;link.setAttribute("download", "保养预警.xlsx");// 将a添加到文档document.body.appendChild(link);// 触发a的点击事件开始下载link.click();});},getWorkshopName(value) {const workshop = this.allchejian.find((item) => item.value === value);return workshop ? workshop.name : "";},getBydicts(code) {this.$http({url: this.$http.adornUrl("/sys/dict/maintenanceList"),method: "get",params: this.$http.adornParams({code: code,}),}).then(({ data }) => {if (data && data.code === 0) {this.bydicts = data.list;this.bydicts.forEach((dict) => {this.$set(this.byMap, dict.value, dict.name);});} else {this.bydicts = [];}});},//加载车间 所需的数据zybloadAllChejian(code) {this.allchejian = [];this.$http({url: this.$http.adornUrl("/sys/dict/maintenanceList"),method: "post",params: this.$http.adornParams({code: code,}),}).then(({ data }) => {if (data && data.code === 0) {this.allchejian = data.list.map((item) => ({name: item.name,value: item.value,}));this.workshop = data.list.map((item) => ({text: item.name,value: item.value,}));} else {}});},// 车间筛选方法 zybfilterHandler(value, row, column) {const property = column["property"];return row[property] === value;}}</script>

最终效果

点击导出

下载的excel表格中车间名称和设备型号有下拉框，对应前端配置的字典类

点击导入

将这个格式的excel导入

在数据库新增一条数据