先看效果
根据序号(A) 相同 合并A-G列。
导出代码:
List<TdSchemaCheckModel> excelData = new ArrayList<>();
/*创建工作部的对象(包含输出流,模板路径)*/
// 添加合并单元格地址
List<CellRangeAddress> list = new ArrayList<>();
list.add(new CellRangeAddress(3,excelData.size()+3,0,13));
OutputStream outputStream =new FileOutputStream("E:/myexcel.xlsx");
ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(templateFileName).build();
/*创建工作表的对象*/
WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new ExcelMergeUtil(0,list)).build();
/*创建工作表的设置,false为数据不全部加入内存中,为了不过多消耗内存!!!!*/
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
/*写入数据excelData,配置,工作表*/
excelWriter.fill(excelData, fillConfig, writeSheet);
//获取当前时间,并修改格式 加入map集合中,填充工作表中的 date属性值 HH:mm:ss
SimpleDateFormat simpleDateFormatOne = new SimpleDateFormat("yyyy年MM月dd日");
String format = simpleDateFormatOne.format(new Date());
Map<String, Object> map = new HashMap<String, Object>();
map.put("date", format);
/*工作部中填充时间的格式,及工作表*/
excelWriter.fill(map, writeSheet);
/*填充完成*/
excelWriter.finish();
/*关闭输出流*/
outputStream.close();
其中策略关键代码 :
WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new ExcelMergeUtil(0,list)).build();
这段代码中registerWriteHandler(new ExcelMergeUtil(0,list))
其中new ExcelMergeUtil 为重新的策略,是实现合并单元格的主要代码。
ExcelMergeUtil
package com.cnpc.center.schemaCheck.utils;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import java.util.List;
public class ExcelMergeUtil extends AbstractMergeStrategy {
/**
* 合并开始行
*/
private Integer startRow = 0;
/**
* list表格所有的合并列集合
*/
private List<CellRangeAddress> cellRangeAddressList = null;
public ExcelMergeUtil() {
}
public ExcelMergeUtil(int startRow, List<CellRangeAddress> cellRangeAddressList) {
this.startRow = startRow;
this.cellRangeAddressList = cellRangeAddressList;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
// 设置样式
CellStyle cellStyle = cell.getCellStyle();
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
cellStyle.setWrapText(true);
//在这里判断从哪一行开始调用合并的方法
if (cell.getRowIndex() > this.startRow) {
if (relativeRowIndex == null || relativeRowIndex == 0) {
return;
}
mergeColumn(sheet, cell, head, relativeRowIndex);
}
}
/**
* 合并单元格
*
* @param sheet
* @param cell
* @param head
* @param relativeRowIndex
*/
protected void mergeColumn(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
int rowIndex = cell.getRowIndex();
int colIndex = cell.getColumnIndex();
// * 根据模板 自定义合并到那一列 从0开始
if (colIndex>=6){
return;
}
sheet = cell.getSheet();
// 获取当前行
Row row = sheet.getRow(rowIndex);
// 获取当前行第一列
Cell cellValueCureen = row.getCell(0);
// 获取前一行
Row preRow = sheet.getRow(rowIndex - 1);
//获取前一列
Cell preCell = preRow.getCell(colIndex);
//获取前一行第一列
Cell cellValuePre = preRow.getCell(0);
// 比较序号是否相同 序号不相同,直接return
if (!cellValueCureen.getStringCellValue().equals(cellValuePre.getStringCellValue())){
return;
}
// 上一行是否有策略
Boolean isC = true;
// 获取已经合并的区域
// 上一行已经合并过了,不再合并
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
for (int i = 0; i < mergedRegions.size(); i++) {
CellRangeAddress cellAddresses = mergedRegions.get(i);
if (cellAddresses.isInRange(rowIndex - 1,preCell.getColumnIndex())) {
;
sheet.removeMergedRegion(i);
// 新加策略
cellAddresses.setLastRow(rowIndex);
sheet.addMergedRegion(cellAddresses);
isC = false;
break;
}
}
if (isC){
CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex - 1,rowIndex,cell.getColumnIndex(),cell.getColumnIndex());
sheet.addMergedRegion(cellAddresses);
}
}
}