javaEasExcel读写数据

1.添加pom依赖：

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

Excel表的模版 

 

2.读取数据

public static void main(String[] args) {
    //读取文件
    ExcelReaderBuilder readerBuilder= EasyExcel.read();
    //获取文件对象
    readerBuilder.file("E:\\自学\\Easy Excel\\EasyExcelDemo.xlsx");
    //指定sheet
    readerBuilder.sheet("Sheet1");
    //自动关闭输入流
    readerBuilder.autoCloseStream(true);
    //设置Excel文件格式
    readerBuilder.excelType(ExcelTypeEnum.XLSX);
    //注册监听器进行数据的解析
    readerBuilder.registerReadListener(new AnalysisEventListener() {
        @Override
        public void invoke(Object o, AnalysisContext analysisContext) {
            //一行数据读取完成之后的回调
            System.out.println(o);

        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            //通知文件读取完毕
            System.out.println("数据读取完毕");
        }
    });
    //构建读取器
    ExcelReader reader=readerBuilder.build();
    //读取数据
    reader.readAll();
    //读取完毕
    reader.finish();

}

可以通过泛型指定数据类型，map集合

       

public static void main(String[] args) {
    //读取文件
    ExcelReaderBuilder readerBuilder= EasyExcel.read();
    //获取文件对象
    readerBuilder.file("E:\\自学\\Easy Excel\\EasyExcelDemo.xlsx");
    //指定sheet
    readerBuilder.sheet("Sheet1");
    //自动关闭输入流
    readerBuilder.autoCloseStream(true);
    //设置Excel文件格式
    readerBuilder.excelType(ExcelTypeEnum.XLSX);
    //注册监听器进行数据的解析
    readerBuilder.registerReadListener(new AnalysisEventListener<Map<Integer,String>>() {

        @Override
        public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
            Set<Integer> keySet=integerStringMap.keySet();
            Iterator<Integer> iterator=keySet.iterator();
            while (iterator.hasNext()){
                Integer key=iterator.next();
                System.out.print(key+":"+integerStringMap.get(key)+", ");
            }
            System.out.println("");
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            //通知文件读取完毕
            System.out.println("数据读取完毕");
        }
    });
    //构建读取器
    ExcelReader reader=readerBuilder.build();
    //读取数据
    reader.readAll();
    //读取完毕
    reader.finish();

}

实际开发中，我们可以对上述代码进行简化

public static void main(String[] args) {
    List<Map<Integer,String>> list=new LinkedList<>();
    EasyExcel.read("E:\\自学\\Easy Excel\\EasyExcelDemo.xlsx")
            .sheet()
            .registerReadListener( new AnalysisEventListener<Map<Integer,String>>(){

                @Override
                public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                    list.add(integerStringMap);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                    System.out.println("数据读取完毕");
                }
            }).doRead();

    for(Map<Integer,String> integerStringMap:list){
        Set<Integer> keySet=integerStringMap.keySet();
        Iterator<Integer> iterator=keySet.iterator();
        while (iterator.hasNext()){
            Integer key=iterator.next();
            System.out.println(key+":"+integerStringMap.get(key)+", ");
        }
        System.out.println();
    }
}

映射成指定对象，需要创建实体类，使用 @ExcelProperty注解完成实体类成员变量和Excel字段映射

@Data
public class ExcelData {
    @ExcelProperty("ID")
    private String id;
    @ExcelProperty("用户名")
    private String name;
    @ExcelProperty("性别")
    private String gender;
    @ExcelProperty("积分")
    private Integer score;

}

解析Excel文件的时候直接指定实体类即可

public static void main(String[] args) {
    List<ExcelData> list=new LinkedList<>();
    EasyExcel.read("E:\\自学\\Easy Excel\\EasyExcelDemo.xlsx")
            .head(ExcelData.class)
            .sheet()
            .registerReadListener( new AnalysisEventListener<ExcelData>(){

                @Override
                public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
                    list.add(excelData);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                    System.out.println("数据读取完毕");
                }
            }).doRead();

    for(ExcelData excelData:list){
        System.out.println(excelData);
    }
}

2.写数据

package com.example.demoexcel.com.southwind;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.example.demoexcel.entity.ExcelData;
import org.hibernate.tool.schema.spi.ScriptTargetOutput;

import java.awt.*;
import java.util.*;
import java.util.List;

public class ExcelText {
    public static void main(String[] args) {
        List<ExcelData> list=parseDate();
        //list写入Excel文件
        EasyExcel.write("E:\\自学\\Easy Excel\\EasyExcelDemo_副本.xlsx")
                .head(ExcelData.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("用户信息表")
                .doWrite(list);

    }

    public static List<ExcelData> parseDate(){
        List<ExcelData> list=new LinkedList<>();
        EasyExcel.read("E:\\自学\\Easy Excel\\EasyExcelDemo.xlsx")
                .head(ExcelData.class)
                .sheet()
                .registerReadListener( new AnalysisEventListener<ExcelData>(){

                    @Override
                    public void invoke(ExcelData excelData, AnalysisContext analysisContext) {
                        list.add(excelData);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("数据读取完毕");
                    }
                }).doRead();
        return list;
    }
}