easyexcel工具

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

excel读数据

public class UserDto  {
 
    private String name;
    private Integer age;

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
}

模板的读取类

public class UserDtoExcelListener extends AnalysisEventListener<UserDto> {
    private int maxSize = 1000;
    private List<UserDto> userDtoList = new ArrayList<>(maxSize);

    private static final Logger LOGGER = LoggerFactory.getLogger(UserDtoExcelListener.class);
    /**
     * 每隔1000条存储数据库，然后清理list ，方便内存回收
     */
    @Override
    public void invoke(UserDto userDto, AnalysisContext analysisContext) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(userDto));
        // 达到maxSize了，需要去存储一次数据库，防止数据几万条数据在内存，容易OO
        if (userDtoList.size() == maxSize) {
            saveDate();
            userDtoList.clear();
        }
    }

    private void saveDate() {
        //.......
        LOGGER.info("{}条数据，开始存储数据库！", userDtoList.size());
        LOGGER.info("存储数据库成功！");
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        if (userDtoList.size() > 0) {
            saveDate();
            userDtoList.clear();
            LOGGER.info("所有数据解析完成！");
        }
    }
}

读取单sheet

 	 // 写法1：
        String fileName = "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, UserDto.class, new UserDtoExcelListener()).sheet().doRead();

        // 写法2：
        fileName = "demo.xlsx";
        ExcelReader excelReader = EasyExcel.read(fileName, UserDto.class, new UserDtoExcelListener()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();

        //多行头
         fileName =  "demo.xlsx";
        EasyExcel.read(fileName, UserDto.class, new UserDtoExcelListener()).sheet()
                // 这里可以设置1，因为头就是一行。如果多行头，可以设置其他值。不传入也可以，因为默认会根据DemoData 来解析，他没有指定头，也就是默认1行
                .headRowNumber(1).doRead();

同步的返回，数据量大会把数据放到内存里面

        String fileName = "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        List<Object> list = EasyExcel.read(fileName).head(UserDto.class).sheet().doReadSync();
        for (Object obj : list) {
            UserDto data = (UserDto)obj;
        }

        // 这里 也可以不指定class，返回一个list，然后读取第一个sheet 同步读取会自动finish
        list = EasyExcel.read(fileName).sheet().doReadSync();
        for (Object obj : list) {
            // 返回每条数据的键值对 表示所在的列 和所在列的值
            Map<Integer, String> data = (Map<Integer, String>)obj;
        }

读多个sheet

// 读多个sheet ,sheet1 sheet2 数据不一致
        String fileName = "user表.xls";
        ExcelReader excelReader = EasyExcel.read(fileName).build();
        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(UserDto.class).registerReadListener(new UserDtoExcelListener()).build();
        readSheet1.setSheetName("user1");
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(UserDto.class).registerReadListener(new UserDtoExcelListener()).build();
        readSheet2.setSheetName("user2");
        excelReader.read(readSheet1);
        excelReader.read(readSheet2);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();

**

excel写数据

**

public class UserDto  {
    @ExcelProperty(index = 0,value = "姓名")
    private String name;
    @ExcelProperty(index = 1,value = "年龄")
    private Integer age;
	//set get...
}

自定义表头

 		 // 写法1
        String fileName = "demo.xlsx";
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
        
        // 写法2
        fileName =  "demo.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        excelWriter.write(data(), writeSheet);
        excelWriter.finish();
        
        //复杂表头
 		fileName =  "demo.xlsx";
        EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data());

import lombok.Data;
/**
 * 复杂头数据.这里最终效果是第一行就一个主标题，第二行分类
 **/
@Data
public class ComplexHeadData {
    @ExcelProperty({"主标题", "字符串标题"})
    private String string;
    @ExcelProperty({"主标题", "日期标题"})
    private Date date;
    @ExcelProperty({"主标题", "数字标题"})
    private Double doubleData;
}

  //自定义表头 多sheet
        String fileName = "user表.xls";
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        ExcelWriter build = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet sheet = EasyExcel.writerSheet(1, "user1").build();
        WriteSheet sheet2 = EasyExcel.writerSheet(2, "user2").build();
        WriteSheet sheet3 = EasyExcel.writerSheet(3, "user3").build();
        WriteTable writeTable = new WriteTable();
        writeTable.setHead(getHeadList(1));
        WriteTable writeTable2 = new WriteTable();
        writeTable2.setHead(getHeadList(2));
        WriteTable writeTable3 = new WriteTable();
        writeTable3.setHead(getHeadList(3));
        build.write(data(), sheet, writeTable).write(data(), sheet2, writeTable2).write(data(), sheet3, writeTable3).finish();

		// 方法2 写到不同的sheet 不同的对象
        fileName = "demo.xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        for (int i = 0; i < 5; i++) {
            // 每次都要创建writeSheet 这里注意必须指定sheetNo
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板").head(UserDto.class).build();
            List<UserDto> data = data();
            excelWriter.write(data, writeSheet);
        }
        excelWriter.finish();

合并单元格

 String fileName = "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        excelWriter.merge(1,2,0,0).write(data(), new WriteSheet()).finish();

    private List<UserDto> data() {
        List<UserDto> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserDto user = new UserDto();
            user.setName("a" + i);
            user.setAge(i);
            list.add(user);
        }
        return list;
    }

    private List<List<String>> getHeadList(int i) {
        List<List<String>> listList = new ArrayList<>();
        List<String> nameList = new ArrayList<>();
        nameList.add("name" + i);
        listList.add(nameList);
        List<String> ageList = new ArrayList<>();
        ageList.add("age" + i);
        listList.add(ageList);
        return listList;
    }