1. 实列相关依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
2.EasyExcel导出常见的两种方式：
1.根据路径保存到磁盘:
2.响应Response，用户直接网页下载:
正常分为三个步骤：
(1)构建一个ExcelWriter对象。
(2)再次通过write方法写入数据。
(3)调用EasyExcel的finish。
根据路径保存示例:
private ExcelWriter excelWriter;
public EasyExcelHelper(String path) {
excelWriter = EasyExcel.write(path).build();
//固定的excel标题：
//Class类的属性必须使用`ExcelProperty`注解修饰。
//EasyExcel.write(path, Class).build()
}
//保存到本地磁盘
excelWriter.finish();
响应Respones示例:
private ExcelWriter excelWriter;
public EasyExcelHelper() {
excelWriter = new ExcelWriterBuilder()
.excelType(ExcelTypeEnum.XLSX)
.needHead(true)
.build();
}
public void export(HttpServletResponse response，String fileName ) {
final String exportName = fileName + ExcelTypeEnum.XLSX.getValue();
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
try {
excelWriter.finish();
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(exportName, StandardCharsets.UTF_8.name()));
response.setHeader(HttpHeaders.CONTENT_LENGTH, String.valueOf(dataBytes.length));
} catch (IOException e) {
log.error(e.getMessage(), e);
throw new ApiException(ErrorEnum.DOWNLOAD_ERROR);
}
}
3.EaxyExcel动态标题
/**
* @param sheetStartNum 默认为0即可
* @param sheetName sheet名称
* @param headList 动态标题，格式：List<List<String>>
* @param list 数据，格式List<List<Object>>;
*/
public void createSheet(Integer sheetStartNum, String sheetName, List headList, List list) {
WriteSheet sheet= EasyExcel.writerSheet(sheetStartNum, sheetName).build();
WriteTable table = new WriteTable();
table.setTableNo(0);
table.setHead(headList);
excelWriter.write(list, sheet, table);
}
4.EaxyExcel标题格式 策略设置
这里我使用两个策略,分别是defaultStyle、CustomerColumnWidthStyleStrategy。同时启用两个策略时，如果我们创建的excel文件有标题defaultStyle将会生效。不存在标题时，直接存入数据时，CustomerColumnWidthStyleStrategy将会适当调整excel宽度。在上面构建WriteSheet的时候我们可以添加这些策略：
EasyExcel.writerSheet(sheetStartNum, sheetName).
registerWriteHandler(defaultStyle()).
registerWriteHandler(new CustomerColumnWidthStyleStrategy()).build();
/**
* 设置头部样式
*
* @return
*/
private HorizontalCellStyleStrategy defaultStyle() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//头部样式
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteFont.setFontName("Arial");
headWriteFont.setBold(Boolean.FALSE);
headWriteFont.setColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteCellStyle.setWrapped(Boolean.FALSE);
headWriteCellStyle.setBorderRight(BorderStyle.NONE);
headWriteCellStyle.setBorderLeft(BorderStyle.NONE);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 列宽设置
* memo:当数据不存在标题时，defaultStyle头部样式将失效，该设置将会生效
*/
private class CustomerColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 7000;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead) {
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), MAX_COLUMN_WIDTH);
}
}
5.EaxyExcel 同值合并策略
上面我们添加了excel标题策略，现在我们再添加一个同值合并策略。笔者当前遇到一个需求，需要根据数据中一个字段的集合拆分成多条数据，拆分出来的数据相同值需要合并，所以添加了下面的策略。策略的添加方式与上面一致。
参数说明：mergeRowIndex:合并开始的位置，第一行为0，正常是标题行，一般我们从1开始。eachRow:多少行合并一次，假设我们现在根据某个属性将一条数据拆分成3条，如果我们不希望这3条以外的数据发生相同值合并，那么可以设置为3。mergeColumnIndex:合并列的下标数据，第一列为0，假设等于[0,1,3]，那么就意味着只有这3列会发生合并。
这三个参数配合使用，可以达到这样的效果：从x行开始，每y条数据发生合并，合并z[z1,z2,z3]的列。
/**
* 相同值合并策略
*/
public class ExcelMergeStrategy implements CellWriteHandler {
/**
* 合并起始行
*/
private int mergeRowIndex;
/**
* 多少行合并一次
*/
private int eachRow;
/**
* 合并字段的下标
*/
private int[] mergeColumnIndex;
public ExcelMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, int eachRow) {
if (mergeRowIndex < 0) {
throw new IllegalArgumentException("mergeRowIndex must be greater than 0");
}
if (eachRow < 0) {
throw new IllegalArgumentException("eachRow must be greater than 0");
}
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.eachRow = eachRow;
}
@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) {
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
//合并条件：
//1.当前行>合并起始行，默认标题行（0）不参加合并
//2.间隔行（eachRow）的上下两条不参加合并
//2.1间隔行（eachRow）==0时，不设置间隔
if (isMerge(curRowIndex)) {
IntStream.range(0, mergeColumnIndex.length).anyMatch(i -> {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cellData, cell, curRowIndex, curColIndex);
return true;
}
return false;
});
}
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
/**
* 判断是否合并
* 1.当前位置必须大于开始位置：curRowIndex > mergeRowIndex
* 2.根据eachRow 判断数据分割的间隔
* 2.1如果根据eachRow=0，默认不合并
* 2.2如果1如果根据eachRow>0,分割后的第一条数据不会与之前的合并：（curRowIndex-mergeRowIndex）%eachRow==0
*
* @return
*/
private boolean isMerge(Integer curRowIndex) {
if ((curRowIndex > mergeRowIndex) && eachRow > 0) {
if ((curRowIndex - mergeRowIndex) % eachRow == 0) {
return false;
}
return true;
}
return false;
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, CellData cellData, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cellData.getType() == CellDataTypeEnum.STRING ? cellData.getStringValue() : cellData.getNumberValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (Objects.equals(curData, preData)) {
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);
}
}
}
}
6.动态标题拓展
使用场景：当我们需要动态设置excel标题时，但存在部分标题是固定的情况时，我们可以创建一个DemoVO对象使用DynamicColumn修饰这些属性。通过调用conver方法将DemoVO转换成一个ColumnData对象的集合。
然后通过commons-beanutils包的BeanUtils.describe()方法将查询的集合数据映射成Map。
List<DemoVO> data = query();
List<ColumnData> columnList = conver(DemoVO.class);
//excel数据
List<List<Object>> excelData = Lists.newArrayList();
for (DemoVO o : data) {
//对象转map
Map<String,String> oMap = BeanUtils.describe(o);
List<Object> baseList = Lists.newArrayList();
for (ColumnData columnData : columnList) {
//按照标题顺序设置值
baseList.add(oMap.get(columnData.getFieldName()));
}
excelData.add(baseList);
}
1.自定义动态excel注解
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DynamicColumn {
String name();//excel标题
int index(); //标题顺序
}
@Data
public class DemoVO {
@DynamicColumn(name = "库存", index = 0)
private String stockState;
@DynamicColumn(name = "编码", index = 1)
private String skuCode;
@DynamicColumn(name = "描述", index = 2)
private String skuDesc;
//动态拆分成多个标题
private List<String> address;
}
2.注解对应对象
@Builder
@Data
public class ColumnData {
/**
* excel 标题
*/
private String columnName;
/**
* 字段 属性
*/
private String fieldName;
/**
* 排序
*/
private int index;
}
3.注解转对象工具类
public static <R> List<ColumnData> conver(final Class<R> clazz) {
List<ColumnData> list = Lists.newArrayList();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
DynamicColumn annotation = field.getAnnotation(DynamicColumn.class);
if (!Objects.isNull(annotation)) {
ColumnData columnData = ColumnData.builder().
columnName(annotation.name()).
index(annotation.index()).
fieldName(field.getName()).
build();
list.add(columnData);
}
}
list.sort(Comparator.comparingInt(ColumnData :: getIndex));
return list;
}
7.实际使用
public void export() throws Exception {
String path = "D:/mytest/mycase.xlsx";
new File(path).createNewFile();
EasyExcelHelper easyExcelHelper = new EasyExcelHelper(path);
List<DemoVO> results=query();
//获取注解定义的标题
List<ColumnData> columnList = DynamicColumnUtil.conver(DemoVO.class);
//设置标题,数据
List<List<String>> titles = Lists.newArrayList();
columnList.stream().forEach(c -> titles.add(Lists.newArrayList(c.getColumnName())));
List<List<Object>> excelData = Lists.newArrayList();
//设置合并策略
int[] mergeColumnIndex = columnList.stream().mapToInt(ColumnData :: getIndex).toArray();
easyExcelHelper.mergeStrategy(1, mergeColumnIndex, 5);
boolean isFirst = true;//第一次需要设置动态标题
for (DemoVO o : data) {
//对象转map
Map<String,String> oMap = BeanUtils.describe(o);
List<Object> baseList = Lists.newArrayList();
for (ColumnData columnData : columnList) {
//按照标题顺序设置值
baseList.add(oMap.get(columnData.getFieldName()));
if (isFirst) {
for (String address : o.getAddress()) {
titles.add(address);
}
isFirst=false;
}
//设置值
for (String address : o.getAddress()) {
List<Object> row = Lists.newArrayList();
row.addAll(baseList);//公共部分
row.add(address));
excelData.add(row);
}
}
easyExcelHelper.createSheet(0, "测试", titles, excelData);
easyExcelHelper.getExcelWriter().finish();
}
EasyExcelHelper实际是以上Eaxyexcel功能的封装，部分代码上面均有展示，下方直接给出，方便大家参考：
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
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.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
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.AbstractColumnWidthStyleStrategy;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
import java.util.Objects;
import java.util.stream.IntStream;
/**
* @author penggaofeng
* @date 2021/1/25 13:41
* @description：1.辅助实现动态标题；2.实现行数据合并
* @modified By：
* @version: 1.0
*/
@Data
public class EasyExcelHelper {
private ExcelWriter excelWriter;
private ExcelMergeStrategy excelMergeStrategy;
public EasyExcelHelper(String path) {
excelWriter = EasyExcel.write(path).build();
}
/**
* 动态插入
*
* @param sheetStartNum
* @param sheetName sheet名称
* @param headList 动态标题，格式：List<List<String>>
* @param list 数据
*/
public void createSheet(Integer sheetStartNum, String sheetName, List headList, List list) {
WriteSheet sheet;
if (Objects.isNull(excelMergeStrategy)) {
sheet = EasyExcel.writerSheet(sheetStartNum, sheetName).
registerWriteHandler(defaultStyle()).
registerWriteHandler(new CustomerColumnWidthStyleStrategy()).build();
} else {
sheet = EasyExcel.writerSheet(sheetStartNum, sheetName).
registerWriteHandler(defaultStyle()).
registerWriteHandler(excelMergeStrategy).
registerWriteHandler(new CustomerColumnWidthStyleStrategy()).build();
}
WriteTable table = new WriteTable();
table.setTableNo(0);
table.setHead(headList);
excelWriter.write(list, sheet, table);
}
/**
* 创建合并策略
*
* @param mergeRowIndex 合并开始行 （从1开始，忽略标题）
* @param mergeColumnIndex 合并列（列下标，从0开始）
* @param eachRow 合并间隔，指定几条数据之间合并
*/
public void mergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, int eachRow) {
excelMergeStrategy = new ExcelMergeStrategy(mergeRowIndex, mergeColumnIndex, eachRow);
}
/**
* 设置头部样式
*
* @return
*/
private HorizontalCellStyleStrategy defaultStyle() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//头部样式
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteFont.setFontName("Arial");
headWriteFont.setBold(Boolean.FALSE);
headWriteFont.setColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteCellStyle.setWrapped(Boolean.FALSE);
headWriteCellStyle.setBorderRight(BorderStyle.NONE);
headWriteCellStyle.setBorderLeft(BorderStyle.NONE);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 列宽设置
* memo:当数据不存在标题时，defaultStyle头部样式将失效，该设置将会生效
*/
private class CustomerColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 7000;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead) {
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), MAX_COLUMN_WIDTH);
}
}
/**
* 相同值合并策略
*/
public class ExcelMergeStrategy implements CellWriteHandler {
/**
* 合并起始行
*/
private int mergeRowIndex;
/**
* 多少行合并一次
*/
private int eachRow;
/**
* 合并字段的下标
*/
private int[] mergeColumnIndex;
public ExcelMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, int eachRow) {
if (mergeRowIndex < 0) {
throw new IllegalArgumentException("mergeRowIndex must be greater than 0");
}
if (eachRow < 0) {
throw new IllegalArgumentException("eachRow must be greater than 0");
}
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.eachRow = eachRow;
}
@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) {
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
//合并条件：
//1.当前行>合并起始行，默认标题行（0）不参加合并
//2.间隔行（eachRow）的上下两条不参加合并
//2.1间隔行（eachRow）==0时，不设置间隔
if (isMerge(curRowIndex)) {
IntStream.range(0, mergeColumnIndex.length).anyMatch(i -> {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cellData, cell, curRowIndex, curColIndex);
return true;
}
return false;
});
}
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
/**
* 判断是否合并
* 1.当前位置必须大于开始位置：curRowIndex > mergeRowIndex
* 2.根据eachRow 判断数据分割的间隔
* 2.1如果根据eachRow=0，默认不合并
* 2.2如果1如果根据eachRow>0,分割后的第一条数据不会与之前的合并：（curRowIndex-mergeRowIndex）%eachRow==0
*
* @return
*/
private boolean isMerge(Integer curRowIndex) {
if ((curRowIndex > mergeRowIndex) && eachRow > 0) {
if ((curRowIndex - mergeRowIndex) % eachRow == 0) {
return false;
}
return true;
}
return false;
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, CellData cellData, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cellData.getType() == CellDataTypeEnum.STRING ? cellData.getStringValue() : cellData.getNumberValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (Objects.equals(curData, preData)) {
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);
}
}
}
}
}