阿里EasyExcel让你彻底告别easypoi
为什么说EasyExcel可以让你告别easypoi呢？在说这个问题之前我们先来了解下easypoi
easypoi
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法
这是easypoi官方给出的定义，使用这个工具后发现在进行excel的导入导出时，的确很方便。特别是一些简单的excel
比如这种简单的excel，easypoi的确是不二选择，只需要引入mavn依赖，添加一个pojo,加一个注解，然后就可以导出。
但是在遇到一些比较复杂的excel，比如下面这种：
类似与这种比较复杂的表头，一个sheet多张表，多个sheet,合并单元格各种复杂的情况下，easypoi处理起来就比较复杂了,反观easyExcel就比较拿手。
easyExcel处理简单的excel和easypoi一样简单，处理复杂的excel也完全可以通过注解的方式一步到位。开发者只需要编写很少的style代码就能直接达到自己想要的效果，下面就让我们一起来看看easyExcel的强大之处
引入maven依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency>复制代码
新建实体
@Data
@Accessors(chain = true)
@FieldNameConstants
@HeadRowHeight(value = 25)
@ContentRowHeight(value = 18)
@ColumnWidth(value = 20)
@HeadStyle(fillBackgroundColor = 64)
@HeadFontStyle(bold = false)
@ContentStyle(borderTop= BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN,borderBottom = BorderStyle.THIN)
public class ComplexSubjectEasyExcel {
@ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","科目编码","科目编码"},index = 0)
private String subjectId;
@ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","科目名称","科目名称"},index = 1)
private String subjectName;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","期初余额","借方"},index = 2)
private BigDecimal firstBorrowMoney;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","期初余额","贷方"},index = 3)
private BigDecimal firstCreditMoney;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本期发生额","借方"},index = 4)
private BigDecimal nowBorrowMoney;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本期发生额","贷方"},index = 5)
private BigDecimal nowCreditMoney;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本年累计发生额","借方"},index = 6)
private BigDecimal yearBorrowMoney;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本年累计发生额","贷方"},index = 7)
private BigDecimal yearCreditMoney;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","单位：元","期末余额","借方"},index = 8)
private BigDecimal endBorrowMoney;
@HeadFontStyle(bold = true)
@ExcelProperty(value = {"科目余额表","单位：元","期末余额","贷方"},index = 9)
private BigDecimal endCreditMoney;
}复制代码@ExcelProperty 注解的value是个数组，按照index从上到下，相同的值头部会进行合并。这种合并头部的方式相比easypoi的实体嵌套显得直观多了，更加方便。我们对页面列表的数据查询后，也不用进行数组对象嵌套组装，省了很多的工作量，如果希望头部的标题是动态的也可以设置成#{title}的方式(当然这是我自己封装的)
导出工具
下面我封装的easyExcel导出工具，使用的话可以直接复制，并自己做些适当的调整
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.builder.ExcelWriterTableBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import org.springframework.util.Assert;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.util.*;
/**
* easyExcel工具
*/
public class EasyExcelUtilsV1 {
public static final String FILE_PATH = "/home/easy/excel/";
public static final Map<String,List<ExcelAnnotationValue>> annotationValues = new HashMap<>();
private static String outputStream(String fileName){
try {
String path = FILE_PATH+new Date().getTime() +"/";
String filePath = path+fileName+".xls";
File dir = new File(path);
if(!dir.exists()){
dir.mkdirs();
}
File file = new File(filePath);
if(file.exists()){
file.deleteOnExit();
}
file.createNewFile();
return filePath;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 默认导出方式 单个sheet
*/
public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, String fileName, String sheetName, Map<String,String> vars){
resetCLassAnnotationValue(pojoClass);
setExportClassAnnotation(pojoClass,vars);
String filePath = outputStream(fileName);
EasyExcel.write(filePath,pojoClass)
.sheet(sheetName)
.registerWriteHandler(new CustomCellWriteHandler())
.doWrite(list);
return getExcelOssUrl(filePath,fileName);
}
/**
* 默认导出方式 单个sheet
*/
public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, CellWriteHandler handler, String fileName, String sheetName, Map<String,String> vars){
resetCLassAnnotationValue(pojoClass);
setExportClassAnnotation(pojoClass,vars);
String filePath = outputStream(fileName);
EasyExcel.write(filePath,pojoClass)
.sheet(sheetName)
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(handler)
.doWrite(list);
return getExcelOssUrl(filePath,fileName);
}
/**
* 默认导出excel 单个sheet
*/
public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, List<WriteHandler> handlers, String fileName, String sheetName, Map<String,String> vars) {
resetCLassAnnotationValue(pojoClass);
setExportClassAnnotation(pojoClass,vars);
String filePath = outputStream(fileName);
ExcelWriterSheetBuilder builder = EasyExcel.write(filePath,pojoClass)
.sheet(sheetName);
if(!ObjectUtils.isEmpty(handlers)){
for(WriteHandler handler : handlers){
builder.registerWriteHandler(handler);
}
}
builder.doWrite(list);
return getExcelOssUrl(filePath,fileName);
}
/**
* 默认导出excel 单个sheet 多个table
*/
public static String defaultExportOssUrl(EasyExcelMoreSheetMoreTableEntity entity, String fileName, Map<String,String> vars) {
String filePath = outputStream(fileName);
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
String sheetName = entity.getSheetName();
List<WriteHandler> handlers = entity.getHandlers();
List<EasyExcelMoreSheetEntity> list = entity.getList();
try {
WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).build();
for (int i = 0; i < list.size(); i++) {
EasyExcelMoreSheetEntity sheetEntity = list.get(i);
List date = sheetEntity.getList();
Class clazz = sheetEntity.getClazz();
resetCLassAnnotationValue(clazz);
setExportClassAnnotation(clazz,vars);
ExcelWriterTableBuilder tableBuilder = EasyExcel.writerTable(i);
if (!ObjectUtils.isEmpty(handlers)) {
for (WriteHandler handler : handlers) {
tableBuilder.registerWriteHandler(handler);
}
}
WriteTable table = tableBuilder.head(clazz).needHead(true).build();
excelWriter.write(date, writeSheet, table);
}
}catch (Exception e){
e.printStackTrace();
}finally {
excelWriter.finish();
}
return getExcelOssUrl(filePath,fileName);
}
/**
* 多个sheet页导出
*/
public static String moreSheetExportOssUrl(List<EasyExcelMoreSheetEntity> entities,String fileName){
String filePath = outputStream(fileName);
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
try {
for (int i = 0; i < entities.size(); i++) {
EasyExcelMoreSheetEntity entity = entities.get(i);
Class clazz = entity.getClazz();
List list = entity.getList();
Map<String,String> vars = entity.getVars();
resetCLassAnnotationValue(clazz);
setExportClassAnnotation(clazz,vars);
String sheetName = entity.getSheetName();
List<WriteHandler> handlers = entity.getHandlers();
ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(i, sheetName);
if(!ObjectUtils.isEmpty(handlers)){
for(WriteHandler handler :handlers){
builder.registerWriteHandler(handler);
}
}
WriteSheet writeSheet = builder.head(clazz).build();
excelWriter.write(list, writeSheet);
}
}catch (Exception e){
e.printStackTrace();
}finally {
excelWriter.finish();
}
return getExcelOssUrl(filePath,fileName);
}
@SuppressWarnings("unchecked")
public static String moreSheetMoreTableExportOssUrl(List<EasyExcelMoreSheetMoreTableEntity> entities,String fileName){
String filePath = outputStream(fileName);
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
try {
for (int i = 0; i < entities.size(); i++) {
EasyExcelMoreSheetMoreTableEntity entity = entities.get(i);
List<EasyExcelMoreSheetEntity> list = entity.getList();
String sheetName = entity.getSheetName();
List<WriteHandler> handlers = entity.getHandlers();
ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet(i, sheetName);
if(!ObjectUtils.isEmpty(handlers)){
for(WriteHandler handler :handlers){
sheetBuilder.registerWriteHandler(handler);
}
}
//创建sheet
WriteSheet writeSheet = sheetBuilder.build();
//创建table
Assert.isTrue(!ObjectUtils.isEmpty(list),"缺少table数据");
for(int j = 0 ; j < list.size() ; j++){
EasyExcelMoreSheetEntity tableEntity = list.get(j);
Map<String,String> vars = tableEntity.getVars();
List<?> date = tableEntity.getList();
Class<?> clazz = tableEntity.getClazz();
resetCLassAnnotationValue(clazz);
setExportClassAnnotation(clazz, vars);
ExcelWriterTableBuilder tableBuilder = EasyExcel.writerTable(j);
if(j > 0){
tableBuilder.relativeHeadRowIndex(2);
}
WriteTable table = tableBuilder.head(clazz).needHead(true).build();
excelWriter.write(date,writeSheet,table);
}
}
}catch (Exception e){
e.printStackTrace();
}finally {
excelWriter.finish();
}
return getExcelOssUrl(filePath,fileName);
}
public static void defaultExport(List<?> list, Class<?> pojoClass, String filePath, String sheetName) {
EasyExcel.write(filePath,pojoClass)
.sheet(sheetName)
.registerWriteHandler(new CustomCellWriteHandler())
.doWrite(list);
}
private static String getExcelOssUrl(String filePath,String fileName) {
InputStream in = null;
try{
//临时缓冲区
in = new FileInputStream(filePath);
} catch (Exception e){
e.printStackTrace();
}
// 此处可以调用腾讯云的cos 或者阿里云的oss todo
String url = "";
return url;
}
public static void setExportClassAnnotation(Class<?> clazz,Map<String,String> map){
Field[] fields = clazz.getDeclaredFields();
for(Field field : fields){
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if(property != null){
List<String> newValues = new ArrayList<>();
String[] values = property.value();
for(String value : values){
value = replace(value,map);
newValues.add(value);
}
InvocationHandler h = Proxy.getInvocationHandler(property);
try {
Field annotationField = h.getClass().getDeclaredField("memberValues");
annotationField.setAccessible(true);
Map memberValues = (Map) annotationField.get(h);
memberValues.put("value",newValues.toArray(new String[]{}));
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
private static void resetCLassAnnotationValue(Class<?> clazz){
String className = clazz.getSimpleName();
List<ExcelAnnotationValue> values = annotationValues.get(className);
if(ObjectUtils.isEmpty(values)){
//如果静态资源是空的，保存
Field[] fields = clazz.getDeclaredFields();
values = new ArrayList<>();
for(Field field : fields){
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if(!ObjectUtils.isEmpty(excelProperty)) {
String[] vs = excelProperty.value();
ExcelAnnotationValue value = new ExcelAnnotationValue()
.setFieldName(field.getName())
.setValues(vs);
values.add(value);
}
}
annotationValues.put(className,values);
return;
}
Field[] fields = clazz.getDeclaredFields();
for(Field field : fields){
String fieldName = field.getName();
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if(!ObjectUtils.isEmpty(excelProperty)){
ExcelAnnotationValue value = values.stream().filter(v->v.getFieldName().equals(fieldName)).findFirst().orElse(null);
if(!ObjectUtils.isEmpty(value)){
String[] oldValues = value.getValues();
InvocationHandler handler = Proxy.getInvocationHandler(excelProperty);
try {
Field annotationField = handler.getClass().getDeclaredField("memberValues");
annotationField.setAccessible(true);
Map memberValues = (Map) annotationField.get(handler);
memberValues.put("value",oldValues);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
public static String replace(String el,Map<String,String> map){
if(map == null){
return el;
}
String evl = el;
for(Map.Entry<String,String> m : map.entrySet()){
String key = m.getKey();
String value = m.getValue();
el = el.replaceAll("#\{"+key+"\}",value);
if(!evl.equals(el)) {
return el;
}
}
return el;
}
}复制代码表格合并配置
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* 合并单元格handler
*
* @author zl
*/
public class CustomCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public CustomCellMergeStrategy() {
}
public CustomCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
int curRowIndex = cell.getRowIndex();
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//此处需要注意：因为我是按照序号确定是否需要合并的，所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
if (dataBool && bool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}复制代码表格的高度默认设置
public class CustomCellWriteHandler extends AbstractRowHeightStyleStrategy {
@Override
protected void setHeadColumnHeight(Row row, int i) {
if(i == 0){
row.setHeight((short) (1000));
}else if(i == 1){
row.setHeight((short) 300);
}else{
row.setHeight((short) 500);
}
}
@Override
protected void setContentColumnHeight(Row row, int i) {
row.setHeight((short) 500);
}
}复制代码@Data
@Accessors(chain = true)
public class ExcelAnnotationValue {
/**
* 字段名称
*/
private String fieldName;
/**
* ExcelProperty注解 属性value数组
*/
private String[] values;
}复制代码多sheet导出对象参数
@Data
@Accessors(chain = true)
public class EasyExcelMoreSheetEntity {
/**
* 实体类
*/
private Class<?> clazz;
/**
* 数据
*/
private List<?> list;
/**
* sheet名称
*/
private String sheetName;
/**
* 样式
*/
private List<WriteHandler> handlers;
/**
* head 参数
*/
private Map<String,String> vars;
}复制代码多表多sheet导出对象参数
@Data
@Accessors(chain = true)
public class EasyExcelMoreSheetMoreTableEntity {
/**
* 数据
*/
private List<EasyExcelMoreSheetEntity> list;
/**
* sheet名称
*/
private String sheetName;
/**
* 样式
*/
private List<WriteHandler> handlers;
/**
* head 参数
*/
private Map<String,String> vars;
}复制代码下面的是最简单的导出
ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel()
.setSubjectId("1001")
.setSubjectName("库存现金")
.setFirstBorrowMoney(BigDecimal.valueOf(100))
.setNowBorrowMoney(BigDecimal.valueOf(105))
.setNowCreditMoney(BigDecimal.valueOf(100))
.setYearBorrowMoney(BigDecimal.valueOf(200))
.setYearCreditMoney(BigDecimal.valueOf(205))
.setEndBorrowMoney(BigDecimal.valueOf(240));
List<ComplexSubjectEasyExcel> excels = new ArrayList<>();
excels.add(excel);
String url = EasyExcelUtils.defaultExportOssUrl(excels,ComplexSubjectEasyExcel.class,"科目余额表","科目余额表",new HashMap<>());
System.out.println(url);复制代码多sheet导出
public void moreSheetTest(){
ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel()
.setSubjectId("1001")
.setSubjectName("库存现金")
.setFirstBorrowMoney(BigDecimal.valueOf(100))
.setNowBorrowMoney(BigDecimal.valueOf(105))
.setNowCreditMoney(BigDecimal.valueOf(100))
.setYearBorrowMoney(BigDecimal.valueOf(200))
.setYearCreditMoney(BigDecimal.valueOf(205))
.setEndBorrowMoney(BigDecimal.valueOf(240));
List<ComplexSubjectEasyExcel> excels = new ArrayList<>();
excels.add(excel);
List<EasyExcelMoreSheetEntity> entities = new ArrayList<>();
for(int i=0 ; i< 2; i++){
EasyExcelMoreSheetEntity entity = new EasyExcelMoreSheetEntity()
.setClazz(ComplexSubjectEasyExcel.class)
.setList(excels)
.setSheetName("科目余额表"+i);
entities.add(entity);
}
String url = EasyExcelUtils.moreSheetExportOssUrl(entities,"科目余额表");
System.out.println(url);
}复制代码多sheet，多表的导出
public void moreSheetMoreTableTest(){
ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel()
.setSubjectId("1001")
.setSubjectName("库存现金")
.setFirstBorrowMoney(BigDecimal.valueOf(100))
.setNowBorrowMoney(BigDecimal.valueOf(105))
.setNowCreditMoney(BigDecimal.valueOf(100))
.setYearBorrowMoney(BigDecimal.valueOf(200))
.setYearCreditMoney(BigDecimal.valueOf(205))
.setEndBorrowMoney(BigDecimal.valueOf(240));
List<ComplexSubjectEasyExcel> excels = new ArrayList<>();
excels.add(excel);
List<EasyExcelMoreSheetMoreTableEntity> entities = new ArrayList<>();
for(int i=0 ; i< 2; i++){
EasyExcelMoreSheetMoreTableEntity tableEntity = new EasyExcelMoreSheetMoreTableEntity()
.setSheetName("科目余额表"+i)
.setHandlers(Arrays.asList(new CustomCellWriteHandler()));
List<EasyExcelMoreSheetEntity> tables = new ArrayList<>();
EasyExcelMoreSheetEntity table = new EasyExcelMoreSheetEntity()
.setClazz(ComplexSubjectEasyExcel.class)
.setList(excels);
if(i== 1){
tables.add(table);
}
tables.add(table);
tableEntity.setList(tables);
entities.add(tableEntity);
}
String url = EasyExcelUtils.moreSheetMoreTableExportOssUrl(entities,"科目余额表");
System.out.println(url);
}复制代码到此为止！ 使用之后你就会发现easyExcel的便捷和强大
作者：码眼
链接：https://juejin.cn/post/7023649189196201992