开发笔记 | Springboot整合easyexcel实现简单导入导出

目录

开发准备

导出

常用注解

导出excel到指定位置

导出excel到指定web

导入

将指定位置Excel导入并显示至web

---

开发准备

1.导入依赖

    com.alibaba
    easyexcel
    2.0.5

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

    //生成表格时，次字段不生成
    @ExcelIgnore
    private String id;

    //定义表头名称及位置，value表示列名，0表示第一列
    @ExcelProperty(value = "姓名",index = 0)
    private String name;

    @ExcelProperty(value = "年龄",index = 1)
    private String age;

    @ColumnWidth(20)
    @ExcelProperty(value = "生日",index = 2)
    private String birthday;

}

数据源

@Component
public class StudentMapper {
    public List getStudents(){
        List studentList = new ArrayList<>();
        studentList.add(new Student("1","小明","16","1997-03-02"));
        studentList.add(new Student("2","小红","17","1993-03-02"));
        studentList.add(new Student("3","小东","18","1994-03-02"));
        return studentList;
    }
}

导出 常用注解

@ExcelProperty 列名，通过index属性指定位置

@ExcelIgnore 忽略该字段不进行导出

@DateTimeFormat 日期格式转换，String接收excel日期使用

@NumberFormat  数字格式转换 String接收excel数字格式使用

导出excel到指定位置

public static void export2File(String path, String excelName, String sheetName, Class clazz, List data){
    String fileName = path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue());
    EasyExcel.write(fileName,clazz).sheet(sheetName).doWrite(data);
}

参数说明：

export2File(path,"学生表","学生信息", Student.class,studentMapper.getStudents());

path：导出位置 如："D:\\test11\\"

excelName：导出表格名字

sheetName：第一个sheet名字

clazz：导出数据对应的实体类

List data：导出数据源

导出excel到指定web

public static void export2Web(HttpServletResponse response,String excelName, String sheetName,Class clazz,List data) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //URLEncoder.encoder防止中文乱码
        excelName = URLEncoder.encode(excelName,"utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
    }

导入 将指定位置Excel导入并显示至web

   public static String export2Web4File(HttpServletResponse response, String path, String excelName) throws UnsupportedEncodingException {
        File file = new File(path.concat(excelName).concat(ExcelTypeEnum.XLSX.getValue()));
        if (!file.exists()) {
            return "文件不存在！";
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());

        try (
                FileInputStream in = new FileInputStream(file);
                ServletOutputStream out = response.getOutputStream();
        ) {
            IOUtils.copy(in, out);
            return "导出成功！";
        } catch (Exception e) {
            log.error("导出文件异常：", e);
        }

        return "导出失败！";
    }