easyexcel的简单使用

easyexcel使用

1.依赖

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

2.注解

• @ExcelIgnore：忽略掉该字段；
• @ExcelProperty(“用户名”)：设置该列的名称为”用户名“；
• @ColumnWidth(20)：设置表格列的宽度为20；
• @DateTimeFormat(“yyyy年MM月dd日HH时mm分ss秒”)：按照指定的格式对日期进行格式化；
• @NumberFormat(“#.##%”)：接收百分比的数字
• @ContentLoopMerge(eachRow = 2) ：当前列 每隔2行 合并单元格
• @ContentRowHeight(10)：设置表格行内高 （注解在类上将应用于该类中的所有字段）
• @HeadRowHeight(20)：设置表头行高（注解在类上将应用于该类中的所有字段）
• @ColumnWidth(25)：设置列宽度（注解在类上将应用于该类中的所有字段）

3.简单的读写

3.1根据excel内容封装实体

@Data
public class EasyExcelReport {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("年龄")
    private Integer age;
    @ExcelProperty("性别")
    private String sex;
}

3.2 简单的数据读取

监听器

@Slf4j
public class EasyExcelListener implements ReadListener<EasyExcelReport> {

    private ArrayList<EasyExcelReport> reports=new ArrayList<>();

  	/**
     * 每解析到一条数据都会进入invoke
     * @return void
     * @date 2023-12-13 15:24
     **/
    @Override
    public void invoke(EasyExcelReport easyExcelReport, AnalysisContext analysisContext) {
        reports.add(easyExcelReport);
        log.info("解析到一条数据");
    }

    /**
     * 所有数据解析完毕会进入 doAfterAllAnalysed
     * @return void
     * @date 2023-12-13 15:24
     **/
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println(reports);
        log.info("全部数据解析结束");

    }
}

3.2.1 写法1（使用新建监听器）

 public void readExcel(){
     //读取第一个sheet中的数据
        EasyExcel.read("D:\\simpleWrite1702366731651.xlsx",EasyExcelReport.class,new EasyExcelListener())
                .sheet().doRead();
    }

3.2.2 写法2（匿名内部类）

 public void readExcel(){

        EasyExcel.read("D:\\simpleWrite1702366731651.xlsx",EasyExcelReport.class,new EasyExcelListener())
                .sheet().doRead();

        EasyExcel.read("D:\\simpleWrite1702366731651.xlsx", EasyExcelReport.class, new ReadListener<EasyExcelReport>() {
            /**
             * 声明单次存储数据量
             */
            public static final int BATCH_COUNT = 10;
            
            private List<EasyExcelReport> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            @Override
            public void invoke(EasyExcelReport data, AnalysisContext context) {
                log.info("解析到一条数据");
                cachedDataList.add(data);
                //存储数据量达到声明的数据量后清理list 方便内存回收
                if (cachedDataList.size() >= BATCH_COUNT) {
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("数据读取结束");
            }

        }).sheet().doRead();
    }

3.2.3 写法3（使用ExcelReader 读取特定 sheet数据）

 public void readExcel(){
        ExcelReader excelReader = EasyExcel.read("D:\\simpleWrite1702366731651.xlsx", EasyExcelReport.class, new EasyExcelListener()).build();
     	//readSheet() 有两种参数 sheetName：工作表的名称 sheetIndex：工作表的索引，从0开始。
     	//读取名称为sheet1的工作表
        ReadSheet build = EasyExcel.readSheet("sheet1").build();
     	//读取第一个工作表
     	ReadSheet build = EasyExcel.readSheet(0).build();
        excelReader.read(build);
    }

3.2.4 读取多个sheet（写法1）

public void repeatedRead() {
        // 读取全部sheet
        EasyExcel.read("D:\\simpleWrite1702366731651.xlsx", EasyExcelReport.class, new EasyExcelListener()).doReadAll();
    }

3.2.5 读取多个sheet（写法2）

   public void repeatedRead() {

        ExcelReader excelReader = EasyExcel.read("D:\\simpleWrite1702366731651.xlsx").build();
        ReadSheet readSheet1 =
                EasyExcel.readSheet(0).head(EasyExcelReport.class).registerReadListener(new EasyExcelListener()).build();
        ReadSheet readSheet2 =
                EasyExcel.readSheet(1).head(EasyExcelReport.class).registerReadListener(new EasyExcelListener()).build();
        excelReader.read(readSheet1, readSheet2);
    }

3.3 简单的数据写入

3.3.1 依据特有模板写入（表头不可变）

public static void main(String[] args) {
        String fileName= "D:\\simpleWrite" + System.currentTimeMillis() + ".xlsx";
        ArrayList<EasyExcelReport> reports = new ArrayList<>();
        for (int i=0;i<10;i++){
            EasyExcelReport easyExcelReport = new EasyExcelReport();
            easyExcelReport.setName("xxx"+i);
            easyExcelReport.setAge(25);
            easyExcelReport.setSex("男");
            reports.add(easyExcelReport);
        }
    	//将reports集合中的数据写入excel中 写入第一个sheet中 名字为sheet1
        EasyExcel.write(fileName,EasyExcelReport.class).sheet("sheet1").doWrite(reports);
    }

3.3.2 动态表头写入

	/**
     * 声明表头 可依据需求自定义表头
     * @param 
     * @return List<List<String>>
     * @date 2023-12-13 16:15
     **/
    private static List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("姓名" );
        List<String> head1 = new ArrayList<String>();
        head1.add("班级" );
        List<String> head2 = new ArrayList<String>();
        head2.add("语文" );
        List<String> head3 = new ArrayList<String>();
        head3.add("数学" );
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        return list;
    }

	/**
     * 动态表头
     * @param
     * @date 2023-12-12 15:30
     **/
    private static void trendsTable(){
        String fileName =  "D:\\动态表头测试.xlsx";;

        List<List<String>> head = head();
        LinkedList<List<Integer>> linkedLists = new LinkedList<>();

        for (int i = 0; i < 10; i++) {
            //列
            List<Integer> list = ListUtils.newArrayList();
            int j=0;
            for (List<String> str:head){
                //行
                list.add(j++);
            }
            linkedLists.add(list);
        }
        EasyExcel.write(fileName)
                // 这里放入动态头
                .head(head).sheet("模板")
            	//使用List<List<String>> 传入数据
                .doWrite(linkedLists);
    }

结果

3.3.3 复杂表头

@Data
public class EasyExcelReport {
    //使用@ExcelProperty()注解指定复杂表头 然后直接写入
    @ExcelProperty({"主标题", "姓名"})
    private String name;
    @ExcelProperty({"主标题","年龄"})
    private Integer age;
    @ExcelProperty({"主标题","性别"})
    private String sex;
}

结果

3.3.4 动态表头的复杂表头

    
private static List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("主标题" );
        head0.add("设备" );
        List<String> head1 = new ArrayList<String>();
        head1.add("主标题" );
        head1.add("1月电量" );
        List<String> head2 = new ArrayList<String>();
        head2.add("主标题" );
        head2.add("2月电量" );
        List<String> head3 = new ArrayList<String>();
        head3.add("主标题" );
        head3.add("3月电量" );
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        return list;
    }

结果

4.web下载

public void exportMembers1(HttpServletResponse response) throws IOException {
    	//文件名
        String fileName= "D:\\simpleWrite" + System.currentTimeMillis() + ".xlsx";
        ArrayList<EasyExcelReport> reports = new ArrayList<>();
        for (int i=0;i<10;i++){
            EasyExcelReport easyExcelReport = new EasyExcelReport();
            easyExcelReport.setName("xxx"+i);
            easyExcelReport.setAge(25);
            easyExcelReport.setSex("男");
            reports.add(easyExcelReport);
        }
        // 设置文本内省
        response.setContentType("application/vnd.ms-excel");
        // 设置字符编码
        response.setCharacterEncoding("utf-8");
        // 设置响应头
        response.setHeader("Content-disposition", "attachment;filename="+fileName);

        EasyExcel.write(response.getOutputStream(), EasyExcelReport.class).sheet("成员列表").doWrite(reports);
    }

直接用浏览器调用结果