[Java知识库]spring boot项目实现excel的导入导出

一、添加依赖

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

二、导出excel

1、编写实体类，添加注解

/**
 * @description 学生档案记录实体
 * @author zzy
 * @date 2021年07月18日 15:36
 */
@Data
public class StudentRecord {
    private Long id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "性别",replace = {"男_0","女_1"})
    private Integer sex;
    @Excel(name = "学校")
    private String college;
    @Excel(name = "年级")
    private String grade;
    @Excel(name = "班级")
    private String className;
    @Excel(name = "手机号")
    private Long phoneNumber;
    @Excel(name = "创建时间",format = "yyyy-mm-dd hh:mm:ss")
    private String createTime;
    private String image;
}

注意：添加了@Excel注解的会被导出或导入；replace = {“男_0”,“女_1”}在导出时会把0替换成男，1替换成女，导入则相反；format = "yyyy-mm-dd hh:mm:ss"导出时对时间进行格式化。具体的注解属性请看文章末尾

2、编写Controller

@RequestMapping("exportStudentRecord")
    public void exportStudentRecord(HttpServletResponse response) {
    	// 查询学生信息，这是从数据库中查出来的学生信息
        List<StudentRecord> recordList = studentRecordService.list();
        try {
            // 设置响应输出的头类型及下载文件的默认名称
            String fileName = new String("demo信息表.xls".getBytes("utf-8"), "ISO-8859-1");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/vnd.ms-excel;charset=gb2312");

            //导出
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), StudentRecord.class, recordList);
            workbook.write(response.getOutputStream());
            log.info("请求 exportExcel end ......");
        } catch (IOException e) {
            log.info("请求 exportExcel 异常：{}", e.getMessage());
        }

    }

浏览器请求http://localhost:8080/demo/exportStudentRecord会自动下载excel，打开如下

三、导入excel

编写Controller

@RequestMapping("importStudentRecord")
    public void importStudentRecord() {

        try{
        	// 将导入的数据赋值给学生信息的recordList集合，test.xls是桌面上的一个excel表
            List<StudentRecord> recordList = ExcelImportUtil.importExcel(new File("C:\\Users\\zzy\\Desktop\\test.xls"),StudentRecord.class,new ImportParams());
            // 打印导入的recordList
            log.info("importStudentRecord:{}", recordList);
            // 调用mybatis plus的批量插入方法，插入到数据库
            studentRecordService.saveBatch(recordList);
        } catch (Exception e){
            log.info(" Excel 导入异常：{}", e.getMessage());
        }
    }

excel文件

日志打印结果

importStudentRecord:
	[
		StudentRecord(id=null, name=张三, sex=0, college=清华大学, grade=2017级, className=一班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null), 
		StudentRecord(id=null, name=李四, sex=1, college=北京大学, grade=2017级, className=一班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null), 
		StudentRecord(id=null, name=王五, sex=1, college=北京大学, grade=2017级, className=一班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null), 
		StudentRecord(id=null, name=王柳, sex=0, college=北京大学, grade=2017级, className=一班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null), 
		StudentRecord(id=null, name=刘1, sex=0, college=北京大学, grade=2017级, className=二班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null), 
		StudentRecord(id=null, name=刘2, sex=1, college=清华大学, grade=2017级, className=二班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null), 
		StudentRecord(id=null, name=刘3, sex=0, college=清华大学, grade=2017级, className=二班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null), 
		StudentRecord(id=null, name=刘4, sex=0, college=清华大学, grade=2017级, className=二班, phoneNumber=15230641011, createTime=2020-06-06 10:06:15, image=null)
	]

四、@Excel注解的属性