轻松运用EasyExcel，告别模板，*定制Excel数据导出
最编程
2024-07-25 15:51:14
...
package com.kazusa.excel.demo;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.beans.PropertyDescriptor;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
/**
* @Description EasyExcel导出工具类
* @Author Kazusa
*/
public class EasyExcelUtil {
//在这里新增了一个ThreadLocal类变量，用以存储一个自适应列宽的策略
private static final ThreadLocal<LongestMatchColumnWidthStyleStrategy> matchStrategy = new ThreadLocal();
public void export(OutputStream os, ExportCustomCommon params, List<String> fields) {
//获取标题部分数据
List<String> headerTable = params.getHeaderTable();
//获取公共部分数据
ExportCustomCommon.ExcelCommonData commonTable = params.getCommonTable();
//获取列表部分数据
List<? extends ExportCustomCommon.ExcelListData> listTable = params.getListTable();
//获取列表部分数据的类对象
Class<? extends ExportCustomCommon.ExcelListData> listDataClass = listTable.get(0).getClass();
//每次构建一个新的Excel文件时，新建一个自适应列宽策略对象，并存入ThreadLocal中
LongestMatchColumnWidthStyleStrategy matchWidthStrategy = new LongestMatchColumnWidthStyleStrategy();
matchStrategy.set(matchWidthStrategy);
//构建EasyExcel Writer对象
ExcelWriter writer = null;
try {
writer = EasyExcel.write(os, listDataClass)//指定写入的流，以及需要EasyExcel自带动态生成的类的类对象
.excelType(ExcelTypeEnum.XLSX).build();
WriteSheet sheet = EasyExcel
.writerSheet("sheet1")//指定写入的sheet
.needHead(false)//是否需要head，也就是每一个字段对应的字段名，这里为不需要，我们需要EasyExcel去生成字段名的地方只有列表数据部分
.build();
//使用一个计数器记录当前已经写了几个表格
AtomicInteger tableNoCounting = new AtomicInteger(1);
//需要知道列数的最大值是多少
int maxColumn = fields.size();
this.buildHeader(maxColumn, headerTable, sheet, writer, tableNoCounting);
this.buildCommon(maxColumn, commonTable, sheet, writer, tableNoCounting);
this.buildList(listTable, sheet, writer, tableNoCounting, fields);
} finally {
assert writer != null;
// 关闭流
writer.finish();
matchStrategy.remove();
}
}
/**
* 构建标题
*/
private void buildHeader(int maxColumn, List<String> headerList, WriteSheet sheet, ExcelWriter writer, AtomicInteger tableNoCounting) {
//自定义到处样式
WriteCellStyle cellStyle = new WriteCellStyle();
//水平居中
cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont writeFont = new WriteFont();
//加粗
writeFont.setBold(Boolean.TRUE);
//字体大小
writeFont.setFontHeightInPoints((short) 15);
cellStyle.setWriteFont(writeFont);
//遍历标题部分的List
for (String header : headerList) {
WriteTable table = EasyExcel
.writerTable(tableNoCounting.get())//指定写入表格的序号，EasyExcel会将多个表格按照序号从小到大、由上到下的排列
.needHead(Boolean.FALSE)//也不需要标题
.registerWriteHandler(
//合并标题的单元格
new OnceAbsoluteMergeStrategy(tableNoCounting.get() - 1, tableNoCounting.getAndIncrement() - 1, 0, maxColumn - 1)
)
//将自定义样式应用与该表
.registerWriteHandler(new HorizontalCellStyleStrategy(cellStyle, cellStyle))
.build();
//在这里，由于EasyExcel使用List<List<String>>这样的数据来构建一个表，里面的List<String>表示一行数据。
//所以我们这里一次只构建一行数据，一行数据中只有一个单元格的数据，一行数据就作为一个表格写入
//故有几个标题就需要构建几次表格
List<String> cellList = new ArrayList<>();
cellList.add(header);
//因为需要合并单元格到列数最大的单元格处，这里如果不添加空字符串，EasyExcel不会构建单元格，在合并单元格的时候就会报错
for (int i = 0; i < maxColumn - 1; ++i) {
cellList.add("");
}
List<List<String>> rowList = new ArrayList<>();
rowList.add(cellList);
//写入表格
writer.write(rowList, sheet, table);
}
}
/**
* 属性实体类
*/
private static class ExcelField {
//属性名
private String fieldName;
//Excel中显示名
private String showName;
//排序
private int order;
//属性值
private Object value;
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public String getShowName() {
return showName;
}
public void setShowName(String showName) {
this.showName = showName;
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
}
/**
* 构建公共部分
*/
private void buildCommon(int maxColumn, ExportCustomCommon.ExcelCommonData commonTable, WriteSheet sheet, ExcelWriter writer, AtomicInteger tableNoCounting) {
if (ObjectUtil.isNotEmpty(commonTable)) {
//获取公共数据的类对象
Class<?> commonDataClass = commonTable.getClass();
//通过类对象获取该类中的所有属性
List<Field> fields = this.getAllField(commonDataClass);
List<ExcelField> fieldList = new ArrayList<>();
try {
for (Field field : fields) {
//如果在Maven打包时报错，Spring项目中可以替换为Spring中的BeanUtils.getPropertyDescriptor（）
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), commonDataClass);
Assert.notNull(pd, Exception::new);
//反射获取读方法
Method readMethod = pd.getReadMethod();
//读到属性值
Object fieldValue = readMethod.invoke(commonTable);
//获取属性注解
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
//获取Excel显示名称
String excelFieldName = property.value()[0];
//获取Excel中排序
int excelFieldOrder = property.order();
//构建对象
ExcelField excelField = new ExcelField();
excelField.setFieldName(field.getName());
excelField.setShowName(excelFieldName);
excelField.setOrder(excelFieldOrder);
excelField.setValue(fieldValue);
fieldList.add(excelField);
}
} catch (Exception e) {
e.printStackTrace();
}
//根据Order排序
fieldList.sort(Comparator.comparingInt(ExcelField::getOrder));
int count = fieldList.size();
//计算一行显示属性的个数，除以3是因为一个属性需要属性名--属性值--空字符串三个单元格
int lineCount = maxColumn / 3;
//计算行数
int rows = (count + lineCount - 1) / lineCount;
List<Object> cellList = new ArrayList<>();
List<List<Object>> rowList = new ArrayList<>();
//自定义样式
WriteCellStyle cellStyle = new WriteCellStyle();
//水平靠左
cellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
//遍历所有行，一行作为一个表
for (int row = 0; row < rows; ++row) {
WriteTable table = EasyExcel.writerTable(tableNoCounting.getAndIncrement())
.needHead(Boolean.FALSE)
.registerWriteHandler(
new HorizontalCellStyleStrategy(cellStyle, cellStyle)
)
//添加自适应列宽策略
.registerWriteHandler(matchStrategy.get())
.build();
//构建List<List<String>>类型的数据给EasyExcel导出
for (int i = 0; i < lineCount && row * lineCount + i < count; ++i) {
ExcelField field = fieldList.get(row * lineCount + i);
cellList.add(field.getShowName() + ":");
cellList.add(field.getValue());
cellList.add("");
}
rowList.add(cellList);
//指定写入的sheet和table
writer.write(rowList, sheet, table);
cellList.clear();
rowList.clear();
}
}
}
/**
* 构建列表部分
*/
private void buildList(List<? extends ExportCustomCommon.ExcelListData> listTable, WriteSheet sheet, ExcelWriter writer, AtomicInteger tableNoCounting, List<String> fields) {
//自定义样式
WriteCellStyle headStyle = new WriteCellStyle();
//设置header背景颜色为透明
headStyle.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
//水平居中
headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//上下左右四个边框
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
WriteFont writeFont = new WriteFont();
//字体加粗
writeFont.setBold(Boolean.TRUE);
//字号
writeFont.setFontHeightInPoints((short) 12);
headStyle.setWriteFont(writeFont);
WriteCellStyle contentStyle = new WriteCellStyle();
//内容上下左右四个边框
contentStyle.setBorderBottom(BorderStyle.THIN);
contentStyle.setBorderTop(BorderStyle.THIN);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
//水平居中
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteTable table = EasyExcel.writerTable(tableNoCounting.getAndIncrement())
.needHead(Boolean.TRUE)//需要Header
//添加自适应列宽策略
.registerWriteHandler(matchStrategy.get())
.registerWriteHandler(new HorizontalCellStyleStrategy(headStyle, contentStyle))//传入自定义样式
.includeColumnFiledNames(fields)//选择需要哪些属性
.build();
writer.write(listTable, sheet, table);
}
//获取该类的所有属性，包括父类中不重名的属性
private List<Field> getAllField(Class
上一篇： 如何让Word中的组织架构图文字垂直排列