easyexcel 设置标题_EasyExcel导出小结：动态标题、标题格式、相同值合并

1. 实列相关依赖

com.alibaba

easyexcel

2.2.6

commons-beanutils

commons-beanutils

1.9.3

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>

* @param list 数据，格式List>;

*/

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 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 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 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 data = query();

List columnList = conver(DemoVO.class);

//excel数据

List> excelData = Lists.newArrayList();

for (DemoVO o : data) {

//对象转map

Map oMap = BeanUtils.describe(o);

List 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 address;

}

2.注解对应对象

@Builder

@Data

public class ColumnData {

/**

* excel 标题

*/

private String columnName;

/**

* 字段 属性

*/

private String fieldName;

/**

* 排序

*/

private int index;

}

3.注解转对象工具类

public static List conver(final Class clazz) {

List 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 results=query();

//获取注解定义的标题

List columnList = DynamicColumnUtil.conver(DemoVO.class);

//设置标题,数据

List> titles = Lists.newArrayList();

columnList.stream().forEach(c -> titles.add(Lists.newArrayList(c.getColumnName())));

List> 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 oMap = BeanUtils.describe(o);

List 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 row = Lists.newArrayList();

row.addAll(baseList);//公共部分

row.add(address));

excelData.add(row);

}

}

easyExcelHelper.createSheet(0, "测试", titles, excelData);

easyExcelHelper.getExcelWriter().finish();

}

8.EasyExcelHelper工具类完整代码

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>

* @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 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 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 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);

}

}

}

}

}