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.