日常开发时客户提出了下面样式的需求,于是记录一下。
导入EasyExcel依赖
<!-- 引入easyexcel导出依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
工具类
实现EasyExcel行和列合并都需要实现CellWriteHandler
接口
行合并
public class ExcelFillCellMergePrevCol implements CellWriteHandler {
private static final String KEY ="%s-%s";
//所有的合并信息都存在了这个map里面
Map<String, Integer> mergeInfo = new HashMap<>();
public ExcelFillCellMergePrevCol() {
}
public ExcelFillCellMergePrevCol(Map<String, Integer> mergeInfo) {
this.mergeInfo = mergeInfo;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@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();
Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
if(null != num){
// 合并最后一行 ,列
mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);
}
}
public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
Sheet sheet = writeSheetHolder.getSheet();
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
sheet.addMergedRegion(cellRangeAddress);
}
//num从第几列开始增加多少列
// curRowIndex 在第几行进行行合并
// curColIndex 在第几列进行合并
// num 合并多少格
// 比如我上图中中心需要在第三行 从0列开始合并三列 所以我可以传入 (3,0,2)
public void add (int curRowIndex, int curColIndex , int num){
mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
}
}
列合并
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex; //数组存放这一行需要合并那几列 [0,1,2] 在这mergeRowIndex行中合并 0 、1、2列
private int mergeRowIndex; // 存放需要向上合并的列
private Integer noMergeRowIndex;// 不要合并的列
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, Integer noMergeRowIndex) {
this.mergeColumnIndex = mergeColumnIndex;
this.mergeRowIndex = mergeRowIndex;
this.noMergeRowIndex = noMergeRowIndex;
}
@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 integer, Boolean aBoolean) {
}
@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 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();
Row preRow = cell.getSheet().getRow(curRowIndex - 1);
if (preRow == null) {
// 当获取不到上一行数据时,使用缓存sheet中数据
preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
}
Cell preCell = preRow.getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
//不需要合并的列直接跳出
if ( (noMergeRowIndex != null) && noMergeRowIndex == (curRowIndex - 1) ){
return;
}
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//此处需要注意:所以获取每一行第一列数据和上一行第一列数据进行比较,如果相等合并
boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
if (dataBool && equals) {
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 CellStyleStrategy extends AbstractCellStyleStrategy {
private WriteCellStyle headWriteCellStyle;
private List<WriteCellStyle> contentWriteCellStyleList;
private CellStyle headCellStyle;
private List<CellStyle> contentCellStyleList;
public CellStyleStrategy(WriteCellStyle headWriteCellStyle,
List<WriteCellStyle> contentWriteCellStyleList) {
this.headWriteCellStyle = headWriteCellStyle;
this.contentWriteCellStyleList = contentWriteCellStyleList;
}
public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
this.headWriteCellStyle = headWriteCellStyle;
contentWriteCellStyleList = new ArrayList<WriteCellStyle>();
contentWriteCellStyleList.add(contentWriteCellStyle);
}
@Override
protected void initCellStyle(Workbook workbook) {
if (headWriteCellStyle != null) {
headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
}
if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) {
contentCellStyleList = new ArrayList<CellStyle>();
for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {
contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
}
}
}
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
if (headCellStyle == null) {
return;
}
cell.setCellStyle(headCellStyle);
}
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
return;
}
cell.setCellStyle(contentCellStyleList.get(0));
}
}
填充数据导出
实体类
由于特殊原因只展示部分,其他属性类推即可
@Data
@NoArgsConstructor
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(20)
public class TelephoneInfo {
@ExcelProperty("部门")
private String departmentName;
@ExcelProperty("现场")
@ColumnWidth(40)
private String sceneName;
@ExcelProperty("团队")
@ColumnWidth(40)
private String teamName;
@ExcelProperty({"团队人力","团队人力"})
private Integer teamManpower;
ExcelProperty({"团队人力","上线人力"})
private Integer onlineManpower;
}
正文
void test(){
// 查询出所有的记录
List<AgentTelephoneInfo> list = statisticsService.queryStatistics(agentTelephoneInfo);
// 创建列合并工具类对象
ExcelFillCellMergePrevCol mergePrevCol = new ExcelFillCellMergePrevCol();
int size = list.stream().filter(item -> StringUtils.isNotEmpty(item.getFlag())&&(Integer.valueOf(item.getFlag()) < 2) ).collect(Collectors.toList()).size();
// 因为上图上方需要合并的行数不确定所以动态合并
for (int i = 0 ; i < size ; i++ ){
mergePrevCol.add( i+2,0,2); // i + 2 ? 因为表头占了两行所以要加2 不然报出不能合并的错误 A1:A2已经合并
// (i + 2 , 0 , 2) 从i+2 行操作 在第0列合并2列
}
EasyExcel.write(EasyExcelUtil.getOutputStream("外呼汇总报表", response), AgentTelephoneInfo.class)
.sheet("外呼汇总报表")
.registerWriteHandler(horizontalCellStyleStrategyBuilder()) // 字体表格样式工具类,下方展示
.registerWriteHandler(new ExcelFillCellMergeStrategy(size,new int[]{0,1,2},size + 1)) // 行合并
// size 从这一行开始下边的向上合并 合并 0、1、2 三列 size+1 不要向上合并
// 好像直接把第一个参数 变为size + 1 貌似就可以,懒得改了,能用就行吧 》=《
.registerWriteHandler(mergePrevCol)
.doWrite(list);
}
public CellStyleStrategy horizontalCellStyleStrategyBuilder() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return new CellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
最终效果:
参考:EasyExcel模板导出(行和列自动合并)_Lzfnemo2009的博客-CSDN博客_easyexcel模板导出