使用EasyPoi快速实现excel导入导出功能

飞熊 • 2023年5月14日下午10:23 • 技术随笔 • 阅读 249

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法EasyPoi快速实现excel导入导出功能

添加依赖

<!--easypoi依赖-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>

导入功能实现

创建一个excel，填充数据

新建一个学生实体类

public class StudentDo {
    /**
     * @Excel 作用在一个filed上面，对列的描述
     * name 列名
     * orderNum 下标，从0开始
     */
    @Excel(name = "姓名", orderNum = "0")
    private String name;
    @Excel(name = "性别", orderNum = "1")
    private String gender;
    @Excel(name = "成绩", orderNum = "2")
    private String score;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getScore() {
        return score;
    }

    public void setScore(String score) {
        this.score = score;
    }
}

在每列增加@Excel注解，name对应列名，
控制层：

@RestController
@RequestMapping("student")
public class StudentController {
    /**
     * excel导入数据
     * @param file
     * @return
     */
    @RequestMapping("import")
    public List<StudentDo> importData(@RequestParam(value = "file") MultipartFile file) {
        ImportParams importParams = new ImportParams();
        importParams.setHeadRows(1);
        importParams.setTitleRows(1);
        try {
            List<StudentDo> list = ExcelImportUtil.importExcel(file.getInputStream(), StudentDo.class, importParams);
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

经过上面的配置，就可以完成excel的数据导入了。
测试：

导出功能实现

控制层

/**
 * 数据导出excel
 * @return
 */
@RequestMapping("export")
public void exportData(HttpServletResponse response) {
    try {
        List<StudentDo> list = new ArrayList<>();
        StudentDo studentDo1 = new StudentDo();
        studentDo1.setName("孙常胜");
        studentDo1.setGender("男");
        studentDo1.setScore("100");
        list.add(studentDo1);
        String fileName = "学生信息表.xls";
        String sheetName = "学生";
        response.setHeader("content-Type", "application/vnd.ms-excel");
        fileName = new String(fileName.getBytes(), "ISO-8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        ServletOutputStream out = response.getOutputStream();
        ExportParams params = new ExportParams(); //sheet
        params.setSheetName(sheetName);
        params.setTitle("学生信息表，标题");
        Workbook workbook = ExcelExportUtil.exportExcel(params, StudentDo.class, list);
        workbook.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    }
}