EasyExcel 读写&多sheet操作

EasyExcel是一款Java操作Excel的开源库，使用它可以方便地读取、写入和操作Excel文件。下面是使用EasyExcel的基本步骤：

引入EasyExcel依赖

在Maven项目中，可以在pom.xml文件中添加以下依赖：

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

一. 写入Excel文件

使用EasyExcel写入Excel文件的步骤如下：

（1）定义Excel表头

public class DemoData {
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    // 省略getter/setter方法
}

（2）创建ExcelWriter对象

String fileName = "demo.xlsx";
// 可以自定义表格样式
ExcelWriter excelWriter = EasyExcel.write(fileName).registerWriteHandler(ExcelStyleUtil.defaultStyle()).build();

（3）写入数据

List<DemoData> dataList = new ArrayList<>();
// 多sheet操作
excelWriter.write(dataList, EasyExcel.writerSheet(0, "Sheet1").head(DemoData.class).build());
excelWriter.write(dataList, EasyExcel.writerSheet(1, "Sheet2").head(DemoData.class).build());

（4）关闭ExcelWriter对象

excelWriter.finish();

（5）Excel样式定义

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

public class ExcelStyleUtil {
    private ExcelStyleUtil(){}

    public static HorizontalCellStyleStrategy defaultStyle() {
        // 头的策略
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)10);
        headWriteFont.setFontName("Arial");
        headWriteFont.setBold(true);

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setWrapped(false);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 内容的策略
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short)10);
        contentWriteFont.setFontName("Arial");
        contentWriteFont.setBold(false);

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
}

二. 读取Excel文件

使用EasyExcel读取Excel文件的步骤如下：

（1）定义Excel表头

public class DemoData {
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    // 省略getter/setter方法
}

（2）创建ExcelReader对象

String fileName = "demo.xlsx";
ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();

（3）注册数据监听器

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    private List<DemoData> dataList = new ArrayList<>();

    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        dataList.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 处理完所有数据后的操作
    }

    public List<DemoData> getDataList() {
        return dataList;
    }
}

（4）读取数据

DemoDataListener listener = new DemoDataListener();
excelReader.read(EasyExcel.readSheet(0).build(), listener);
List<DemoData> dataList = listener.getDataList();

（5）关闭ExcelReader对象

excelReader.finish();