springboot整合easyexcel 导入导出excel案例及文件无法打开

前端请求

vue3+axios+typescript

downloadByUrlStream({url:process.env.VUE_APP_API_URL1+"/MeshDevice/upExcel"})

 export function downloadByUrlStream({url, target = '_self', fileName, fileType}: {
    url: string;
    target?: '_self' | '_blank';
    fileName?: string;
    fileType?: string;
}): Promise<boolean> {
    return new Promise<boolean>((resolve, reject) => {
            const link = document.createElement('a');
            link.href = url;
            link.target = target;

            if (link.download !== undefined) {
                link.download = fileName || getFileName(url)
            }

            if (document.createEvent) {
                const e = document.createEvent('MouseEvents');
                e.initEvent('click', true, true);
                link.dispatchEvent(e);
                return resolve(true);
            }

            if (url.indexOf('?') === -1) {
                url += '?download';
            }

            window.open(url, target);
            return resolve(true);
    })
}

Springboot 后端部分
先在pom文件中引用依赖
 

        <!-- 引入easyexcel-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.1.1</version>
		</dependency>
		<!--使用POI读取文件-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.1.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
		</dependency>

注意：如果你引入的easyexcel 是3.x版本 请也将poi对应升至4.x版本

导出数据

controller or service 层
 

    @GetMapping("/upExcel")
    public void downloadDeviceExcel(HttpServletResponse response,HttpServletRequest request) throws ParseException {
        try {
            List<MeshDeviceVos> list = meshDeviceMapper.deviceByAll();
            Set<String> includeColumnFieldNames = new HashSet<String>();
            includeColumnFieldNames.add("mac");
            includeColumnFieldNames.add("ver");
            includeColumnFieldNames.add("innermodel");
            includeColumnFieldNames.add("serialNumber");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
            String fileName = URLEncoder.encode("设备信息表", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 如果不用模板的方式导出的话，是doWrite
            EasyExcel.write(response.getOutputStream(), MeshDeviceData.class).includeColumnFieldNames(includeColumnFieldNames).sheet("设备信息表数据").doWrite(list);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

// 根据用户传入字段 假设我们要忽略 字段
Set<String> excludeColumnFiledNames = new HashSet<String>();

// 根据用户传入字段 假设我们只要导出 字段
Set<String> includeColumnFiledNames = new HashSet<String>();

实体类

@Data
public class MeshDeviceData {
    @ExcelProperty(index = 0,value = "Mac")
    @ColumnWidth(20)
    private String mac;
    @ExcelProperty(index = 1,value = "版本")
    private String ver;
    @ExcelProperty(index = 2,value = "型号")
    private String innermodel;
    @ExcelProperty(index = 3,value = "序列号")
    @ColumnWidth(20)
    private String serialNumber;
}

导入数据

 前端代码

    <p>
      <a-upload
        v-model:file-list="fileList"
        name="file"
        accept=".xlsx,.xls"
        :multiple="true"
        :action="importUrl"
        :headers="headers"
        @change="handleChange"
      >
        <a-button>
          <upload-outlined></upload-outlined>
          点击上传
        </a-button>
      </a-upload>
    </p>

handleChange 方法 

    const fileList = ref<FileItem[]>([])
    const importUrl =ref<string>(process.env.VUE_APP_API_URL1 + "/shop/importExcel"); 
    const handleChange = (info: FileInfo) => {
      if (info.file.status !== 'uploading') {
        console.log(info.file, info.fileList)
      }
      fileList.value = [...info.fileList].splice(-1)
      if (info.file.status === 'done') {
        debugger
        if (info.file.response.code === 0) {
          message.success('导入成功')
           ctx.emit('handle', false)
        } else {
          message.error('导入失败 ' + info.file.response.message)
        }
      } else if (info.file.status === 'error') {
        message.error(`${info.file.name} 上传失败`)
      }
    }

后端controller or service 

    @PostMapping("/importExcel")
    public RS insertCategoriesExcel(@RequestParam("file") MultipartFile file){
        if(StringUtils.isEmpty(file)){
            throw  new MyException(1,"文件不能为空");
        }
        careCategoriesService.importCategories(file,careCategoriesService);
        return new RS().ok();
    }

    @Override
    public void importCategories(MultipartFile file, CareCategoriesService careCategoriesService) {
        try {
            InputStream inputStream = file.getInputStream();
            EasyExcel.read(inputStream, CategoriesVo.class,new CategoriesExcleListener(careCategoriesService)).sheet().doRead();
        }catch (Exception e){
            e.printStackTrace();
            //throw  new MyException(1,"上传出错了");
        }
    }

 新增一个listener 类 继承AnalysisEventListener<CategoriesVo> CategoriesVo 是实体类

public class CategoriesExcleListener extends AnalysisEventListener<CategoriesVo> {
    public CareCategoriesService careCategoriesService;

    public CategoriesExcleListener(CareCategoriesService careCategoriesService) {
        this.careCategoriesService = careCategoriesService;
    }

    @Override
    public void invoke(CategoriesVo careCategoriesVo, AnalysisContext analysisContext) {
        if(careCategoriesVo ==null){
            throw  new MyException(1,"添加失败");
        }
        CareCategories careCategories = new CareCategories();
        BeanUtils.copyProperties(careCategoriesVo,careCategories);
        //插入数据库
        String title = careCategories.getTitle();
        QueryWrapper<CareCategories> careCategoriesQueryWrapper = new QueryWrapper<>();
        careCategoriesQueryWrapper.eq("title",title);
        List<CareCategories> categoriesList = careCategoriesService.list(careCategoriesQueryWrapper);
        if(categoriesList.size()!=0){
            throw  new MyException(1,"该类别已经存在库中，请勿重复导入");
        }
        careCategoriesService.save(careCategories);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

 遇到下载无法打开，后端报

java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellType()

需要将poi版本如

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>

更改为

 

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.2</version>
</dependency>

更多easyExcel 用法 可以前往官方API查看EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel

gitHub地址：GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具