springboot+easypoi 导入导出带图片excel实例

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
  `photo` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '照片',
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `tel` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电话',
  `sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
     cn.afterturn
     easypoi-spring-boot-starter
     4.4.0
 

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;

@Data
public class Student implements Serializable {
    @Excel(name="编号")
    private Integer id;
    //上传的图片会直接存到 savePath指定的目录下
    @Excel(name = "头像", width = 20 , height = 40,type = 2,savePath = "D:\student")
    private String photo;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "电话",width = 15)
    private String tel;
    @Excel(name = "性别")
    private String sex;
    @Excel(name = "地址")
    private String address;
    @Excel(name = "出生日期",format = "yyyy-MM-dd",width = 15.0)
    private Date birthday;
}

@Configuration
public class ResourcesConfig implements WebMvcConfigurer{

 @Override
 public void addResourceHandlers(ResourceHandlerRegistry registry) {
     //获取文件的真实路径
     String testStudent = "D:/images/student/";
     registry.addResourceHandler("/student
    @PostMapping("upload")
    public Result upload(MultipartFile file) {
        try {
            //params有很多参数可以自己调
            ImportParams params = new ImportParams();
            List students = ExcelImportUtil.importExcel(file.getInputStream(), Student.class, params);
            //执行完上面这条，excel已经解析完了，图片已经暂存了，我这里做了转存
            saveImage(students);
            studentService.saveBatch(students);
            return Result.success().message("上传成功");
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error().message("上传失败");
        }
    }
    
    @GetMapping("export")
    public void export() {
        try {
            List students = studentService.list();
            ExportParams exportParams = new ExportParams();
            exportParams.setSheetName("学生信息");
            exportParams.setType(ExcelType.HSSF);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams,Student.class, students);
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户数据表", "UTF-8") + ".xls");
            response.setCharacterEncoding("UTF-8");
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    
    private void saveImage(List students) {
        //遍历每一条数据
        for (Student student : students) {
            if (StringUtils.isNotEmpty(student.getPhoto())) {
                try {
                    //获取到暂存的文件
                    File tmpFile = new File(student.getPhoto());
                    FileInputStream fileInputStream = new FileInputStream(tmpFile);
                    //转换为 multipartFile 类
                    MultipartFile multipartFile = new MockMultipartFile("file", tmpFile.getName(), "text/plain", IOUtils.toByteArray(fileInputStream));
                    //获取当前的日期，按日期归档
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd/");
                    String format = sdf.format(new Date());
                    //获取到本地磁盘的路径，先建立路径
                    File file = new File(STUDENT_UPLOAD_PATH + format);
                    if (!file.isDirectory()) {
                        file.mkdirs();
                    }
                    //初始文件名
                    String originName = tmpFile.getName();
                    //后缀名
                    String suffix = originName.substring(originName.lastIndexOf("."));
                    //存加密后的uuid+后缀作为存到path里的文件名
                    String fileName = UUIDUtil.getUUID() + suffix;
                    File dest = new File(file.getAbsoluteFile() + File.separator + fileName);
                    String filePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "/student/" + format + fileName;
                    multipartFile.transferTo(dest);
                    student.setPhoto(filePath);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

是个能传文件的方法都可以，页面不细究了，能用就行
我这里直接拿了官网的 element-ui 的上传

          点击上传


导出

/api 是 vue.config.js里的代理转发，用于识别请求是前端还是后端的, 请求方法也是封装的 axios

 //我尝试的时候 导出时候用了 axios 不能触发下载，于是改为如下
 exportFile(){
   window.location.href="/api/student/export"
 },
 //上传文件
 uploadFile(file) {
   let formDatas = new FormData();
   formDatas.append("file", file.file);
   this.postRequest("/api/student/upload", formDatas, {
     "Content-Type": "multipart/form-data",
   }).then((res) => {
      this.$message.success({message:res.message})
   });
 },

因为是一个demo，所以我全放controller里了，不要学哦 （ ：

参考文章: https://blog.51cto.com/u_9177933/2984871