SpringBladex + Avue实现Excel表格导入数据并上传至数据库

一、前端

1.在<template>中加入导入按钮、导入时的对话框

2.在data中引入参数 

3.在method中引入相关方法

4.从auth微服务中引入getToken方法

二、后台

 1.创建excel文件夹，创建

①excel表格对应的实体类OpenCgDetectorInfoExcel

②调用import所需的设置（工具）OpenCgDetectorInfoImportListener

 2.Excel对应的实体类及工具类内容如下（其中id为雪花id，在Excel模板中并没有出现该字段，由于使用雪花id必须使用Mybatis封装的insert，所以此处需要手动插入雪花id，方法见后面）

/**
 * Excel导入火焰探头模型实体类
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class FireDetectorInfoExcel implements Serializable {
	private static final long serialVersionUID = 1L;

	@ColumnWidth(15)
	@ExcelProperty("id")
	private Long id;

	@ColumnWidth(15)
	@ExcelProperty("项目编号")
	private String projectId;

	@ColumnWidth(15)
	@ExcelProperty("探头库类型")
	private Integer belongType;

	@ColumnWidth(15)
	@ExcelProperty("探头名称")
	private String probeName;

	@ColumnWidth(15)
	@ExcelProperty("探头描述")
	private String probeDescribe;

	@ColumnWidth(15)
	@ExcelProperty("品牌")
	private String probeBrand;

	@ColumnWidth(15)
	@ExcelProperty("型号")
	private String probModel;

	@ColumnWidth(15)
	@ExcelProperty("最大距离")
	private Double maxDistance;

	@ColumnWidth(15)
	@ExcelProperty("边缘效率")
	private Double edgeEffect;

	@ColumnWidth(15)
	@ExcelProperty("上仰角度")
	private Double upAngle;

	@ColumnWidth(15)
	@ExcelProperty("上仰满量程角度")
	private Double upFullAngle;

	@ColumnWidth(15)
	@ExcelProperty("俯视角度")
	private Double downAngle;

	@ColumnWidth(15)
	@ExcelProperty("俯视满量程角度")
	private Double downFullAngle;

	@ColumnWidth(15)
	@ExcelProperty("水平角度")
	private Double levelAngle;

	@ColumnWidth(15)
	@ExcelProperty("水平满量程角度")
	private Double levelFullAngle;
}

@Data
@RequiredArgsConstructor
@EqualsAndHashCode(callSuper = true)
public class OpenCgDetectorInfoImportListener extends AnalysisEventListener<OpenCgDetectorInfoExcel> {

	/**
	 * 默认每隔3000条存储数据库
	 */
	private int batchCount = 3000;
	/**
	 * 缓存的数据列表
	 */
	private List<OpenCgDetectorInfoExcel> list = new ArrayList<>();
	/**
	 * 用户service
	 */
	private final IOpenCgDetectorInfoService openCgDetectorInfoService;
	@Override
	public void invoke(OpenCgDetectorInfoExcel data, AnalysisContext context) {
		list.add(data);
		// 达到BATCH_COUNT，则调用importer方法入库，防止数据几万条数据在内存，容易OOM
		if (list.size() >= batchCount) {
			// 调用importer方法
			openCgDetectorInfoService.importOpenCgDetectorInfo(list);
			// 存储完成清理list
			list.clear();
		}
	}

	@Override
	public void doAfterAllAnalysed(AnalysisContext analysisContext) {
		// 调用importer方法
		openCgDetectorInfoService.importOpenCgDetectorInfo(list);
		// 存储完成清理list
		list.clear();
	}
}

3.controller层加入方法（url需要与前端页面中的action的值对应）

	/**
	 * excel导入开路式可燃气体探头模型
	 */
	@PostMapping("import-opencgdetectorinfo")
	@ApiOperationSupport(order = 12)
	@ApiOperation(value = "导入开路式可燃气体探头模型", notes = "传入excel")
	public R importOpenCgDetectorInfo(MultipartFile file, Integer isCovered) {
		String filename = file.getOriginalFilename();
		if (StringUtils.isEmpty(filename)) {
			throw new RuntimeException("请上传文件!");
		}
		if ((!StringUtils.endsWithIgnoreCase(filename, ".xls") && !StringUtils.endsWithIgnoreCase(filename, ".xlsx"))) {
			throw new RuntimeException("请上传正确的excel文件!");
		}
		InputStream inputStream;
		try {
			OpenCgDetectorInfoImportListener importListener = new OpenCgDetectorInfoImportListener(openCgDetectorInfoService);
			inputStream = new BufferedInputStream(file.getInputStream());
			ExcelReaderBuilder builder = EasyExcel.read(inputStream, OpenCgDetectorInfoExcel.class, importListener);
			builder.doReadAll();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return R.success("操作成功");
	}

4.service层

 5.Impl

	/**
	 * Excel导入开路式可燃气体探头模型
	 * @param data
	 */
	@Override
	public void importOpenCgDetectorInfo(List<OpenCgDetectorInfoExcel> data) {
        private OpenCgDetectorInfoMapper openCgDetectorInfoMapper;
		//遍历data，为每个对象手动插入雪花id
		for(OpenCgDetectorInfoExcel excel : data ){
			IdentifierGenerator identifierGenerator=new DefaultIdentifierGenerator();
			excel.setId((long)identifierGenerator.nextId(new Object()));
		}
		openCgDetectorInfoMapper.importOpenCgDetectorInfo(data);
	}

6.mapper

 7.xml文件

    <!--    Excel导入开路式可燃气体探头模型-->
    <insert id="importOpenCgDetectorInfo" parameterType="java.util.ArrayList">
        insert into `fgdm_open_cg_detector_info`(id,project_id,belong_type,probe_name,probe_num,probe_factory,com_medium,space_type,jz_diameter,real_coefficient,real_diameter,alarm_level_one,alarm_level_two,max_distance,probe_describe) values
        <foreach collection="list" index="index" separator="," item="item">
            (#{item.id},#{item.projectId},#{item.belongType},#{item.probeName},#{item.probeNum},#{item.probeFactory},#{item.comMedium},#{item.spaceType},#{item.jzDiameter},#{item.realCoefficient},#{item.realDiameter},#{item.alarmLevelOne},#{item.alarmLevelTwo},#{item.maxDistance},#{item.probeDescribe})
        </foreach>

    </insert>