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

准备工作创建数据库

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;

准备Excel

maven 包

 <dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-spring-boot-starter</artifactId>
     <version>4.4.0</version>
 </dependency>

实体类

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/**").addResourceLocations("file:"+testStudent);
 }
}

Controller

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.sobuy.supplysingle.common.utils.StringUtils;
import cn.sobuy.supplysingle.common.utils.UUIDUtil;
import cn.sobuy.supplysingle.pojo.Student;
import cn.sobuy.supplysingle.pojo.common.Result;
import cn.sobuy.supplysingle.service.sobuy.StudentService;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


@RestController
@RequestMapping("student")
public class StudentController {
    @Resource
    HttpServletRequest request;

    @Resource
    HttpServletResponse response;

    @Autowired
    private StudentService studentService;

    public final static String STUDENT_UPLOAD_PATH = "D:\images\student\";

    /**
     * 导入方法
     * @param file
     * @return
     */
    @PostMapping("upload")
    public Result upload(MultipartFile file) {
        try {
            //params有很多参数可以自己调
            ImportParams params = new ImportParams();
            List<Student> 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<Student> 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();
        }
    }

    /**
     * 文件转存
     * @param students
     */
    private void saveImage(List<Student> 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();
                }
            }
        }
    }
}

vue前端代码

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

<el-upload
          action=""
          :http-request="uploadFile"
        >
          <el-button size="small" type="primary">点击上传el-button>

el-upload>
<el-button type="success" @click="exportFile" style="margin-top:20px">导出el-button>

/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})
   });
 },