SpringBoot笔记 | EasyExcel导入导出及基于模板导出

目录

开发准备

导出

常用注解

导出excel到指定位置

导出excel到指定web

导入

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

 使用ExcelWriter基于模板导出

---

开发准备

1.导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.1</version>
</dependency>

实体类

@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<Student> getStudents(){
        List<Student> 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 "导出失败！";
    }

 使用ExcelWriter基于模板导出

private void export(List<DataVO> dataList) {
        ExcelWriter excelWriter = null;
        //获取项目resources/template目录下模板的数据流
        InputStream templateInputStream = null;

        //构建第一个sheet页的数据，根据模板填充
        Map<String, Object> sheetMap = new HashMap<>();
        //与模板对应字段-值设置
        sheetMap.put("data1", "测试数据1");
        sheetMap.put("data2", "测试数据2");
        sheetMap.put("data3", "测试数据3");
        String filePath = null;
        try {
            //获取项目resources/template目录下模板的数据流
            templateInputStream = new 
                ClassPathResource("template/data_template.xlsx").getInputStream();
            if (templateInputStream.available() == 0) {
                log.error("获取模板失败");
            }
            //设置文件名，filePath导出路径: 暂存java临时目录 最后再删除 "java.io.tmpdir"
            String fileName = "测试报告" + System.currentTimeMillis() + ".xlsx";
            String path = System.getProperty("java.io.tmpdir") + File.separator;
            String filePath = path + fileName;
            //ExcelWriter通过POI将值写入Excel
            excelWriter = EasyExcel.write(filePath).withTemplate(templateInputStream).build();
        // sheet-概览
        WriteSheet firstSheet = EasyExcel.writerSheet(0).build();
        //用数据去填充模板 取对应的值显示在模板对应的位置
        excelWriter.fill(sheetMap, firstSheet);

        //如果有第二页sheet 非模板 直接生成excel
        WriteSheet secondSheet = EasyExcel.writerSheet(1).head(DataVO.class).build();
        excelWriter.write(dataList, secondSheet );

        } catch (IOException e) {
            log.error("获取模板：examreport 失败。");
            e.printStackTrace();
        } finally {
            //最后记得关闭流
            excelWriter.finish();
            IOUtils.closeQuietly(templateInputStream);
            //删除临时文件
            if (null != filePath) {
                ExcelUtils.deleteFile(filePath);
            }
        }
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class DataVO{

    @ExcelProperty(value = "姓名")
    @ColumnWidth(value = 15)
    private String name;

    @ExcelProperty(value = "年龄")
    @ColumnWidth(value = 15)
    private String age;
}

模板文件data_template.xlsx 

用{}表示占位，数据根据map键值填充