easyexcel 动态合并单元格
目前操作excel文档的底层都是用poi来进行的，在早期工作开发过程中，是基于poi，然后对每一个数据单元格进行操作代码编写，后面有一些比较好的开源项目，像easyexcel、easypoi、hutool等，由于身处电商之都，就选择了阿里开源的easyexcel来做二次开发，源码相对来说并没有那么枯燥，有兴趣可以细细研读一番，对抽象能力，编码规范等一些重要的编程功底都有所帮助的。
单元格的动态合并
在实际项目开发过程中，不可避免地会有生成excel文档的需求，同时也不可避免地会有对excel里面内容的一些微小操作，所以，我们在创建workbook、sheet、row、cell会对其相应的有一些样式的要求。
easyexcel有提供writehandler接口提供自由发挥的空间。
workbook创建前后，所有操作完成后过程中提供了改writehandler(相当于操作workbook过程的3个切面处理)。
public interface WorkbookWriteHandler extends WriteHandler {
/**
* Called before create the workbook
*/
void beforeWorkbookCreate();
/**
* Called after the workbook is created
*
* @param writeWorkbookHolder
*/
void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder);
/**
* Called after all operations on the workbook have been completed
*
* @param writeWorkbookHolder
*/
void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder);
}
sheet创建前后有SheetWriteHandler：
public interface SheetWriteHandler extends WriteHandler {
/**
* Called before create the sheet
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder);
/**
* Called after the sheet is created
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder);
}
Row创建前后有RowWriteHandler：
public interface RowWriteHandler extends WriteHandler {
/**
* Called before create the row
*
* @param writeSheetHolder
* @param writeTableHolder
* Nullable.It is null without using table writes.
* @param rowIndex
* @param relativeRowIndex
* Nullable.It is null in the case of fill data.
* @param isHead
* Nullable.It is null in the case of fill data.
*/
void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,
Integer relativeRowIndex, Boolean isHead);
/**
* Called after the row is created
*
* @param writeSheetHolder
* @param writeTableHolder
* Nullable.It is null without using table writes.
* @param row
* @param relativeRowIndex
* Nullable.It is null in the case of fill data.
* @param isHead
* Nullable.It is null in the case of fill data.
*/
void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead);
/**
* Called after all operations on the row have been completed.This method is not called when fill the data.
*
* @param writeSheetHolder
* @param writeTableHolder
* Nullable.It is null without using table writes.
* @param row
* @param relativeRowIndex
* Nullable.It is null in the case of fill data.
* @param isHead
* Nullable.It is null in the case of fill data.
*/
void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead);
}
Cell操作过程有CellWriteHandler：
public interface CellWriteHandler extends WriteHandler {
/**
* Called before create the cell
*
* @param writeSheetHolder
* @param writeTableHolder
* Nullable.It is null without using table writes.
* @param row
* @param head
* Nullable.It is null in the case of fill data and without head.
* @param columnIndex
* @param relativeRowIndex
* Nullable.It is null in the case of fill data.
* @param isHead
* It will always be false when fill data.
*/
void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head,
Integer columnIndex, Integer relativeRowIndex, Boolean isHead);
/**
* Called after the cell is created
*
* @param writeSheetHolder
* @param writeTableHolder
* Nullable.It is null without using table writes.
* @param cell
* @param head
* Nullable.It is null in the case of fill data and without head.
* @param relativeRowIndex
* Nullable.It is null in the case of fill data.
* @param isHead
* It will always be false when fill data.
*/
void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead);
/**
* Called after the cell data is converted
*
* @param writeSheetHolder
* @param writeTableHolder
* Nullable.It is null without using table writes.
* @param cell
* @param head
* Nullable.It is null in the case of fill data and without head.
* @param cellData
* Nullable.It is null in the case of add header.
* @param relativeRowIndex
* Nullable.It is null in the case of fill data.
* @param isHead
* It will always be false when fill data.
*/
void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData,
Cell cell, Head head, Integer relativeRowIndex, Boolean isHead);
/**
* Called after all operations on the cell have been completed
*
* @param writeSheetHolder
* @param writeTableHolder
* Nullable.It is null without using table writes.
* @param cell
* @param head
* Nullable.It is null in the case of fill data and without head.
* @param cellDataList
* Nullable.It is null in the case of add header.There may be several when fill the data.
* @param relativeRowIndex
* Nullable.It is null in the case of fill data.
* @param isHead
* It will always be false when fill data.
*/
void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead);
}
因为是动态合并单元格，只用到了CellWriteHandler，不过项目提供了一个AbstractMergeStrategy，比较友好，让你只关心合并的工作，不需要对其他的想太多。
public abstract class AbstractMergeStrategy implements CellWriteHandler {
@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) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
return;
}
merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
}
/**
* merge
*
* @param sheet
* @param cell
* @param head
* @param relativeRowIndex
*/
protected abstract void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex);
}
自定义单元格合并策略
public class CellMergeWriterHandler {
public static CellWriteHandler getCellWriteHandler(CellMergeEnum cellMergeEnum) {
if(CellMergeEnum.ROW.equals(cellMergeEnum)) {
return new RowMergeWriterHandler();
}
if(CellMergeEnum.COLUMN.equals(cellMergeEnum)) {
return new ColumnMergeWriterHandler();
}
return null;
}
public static CellWriteHandler getCellWriteHandler(CellMergeEnum cellMergeEnum, List<Integer> cols) {
if(CellMergeEnum.ROW.equals(cellMergeEnum)) {
return new RowMergeWriterHandler(cols);
}
if(CellMergeEnum.COLUMN.equals(cellMergeEnum)) {
return new ColumnMergeWriterHandler(cols);
}
return null;
}
public static class ColumnMergeWriterHandler extends AbstractMergeStrategy {
private List<Integer> mergeCols;
public ColumnMergeWriterHandler() {}
public ColumnMergeWriterHandler(List<Integer> mergeCols) {
this.mergeCols = mergeCols;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if(CollectionUtils.isNotEmpty(this.mergeCols) && !this.mergeCols.contains(cell.getColumnIndex())) {
return;
}
mergeCell(sheet, cell, CellMergeEnum.COLUMN);
}
}
public static class RowMergeWriterHandler extends AbstractMergeStrategy {
private List<Integer> mergeCols;
public RowMergeWriterHandler() {}
public RowMergeWriterHandler(List<Integer> mergeCols) {
this.mergeCols = mergeCols;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if(CollectionUtils.isNotEmpty(mergeCols) && !mergeCols.contains(cell.getColumnIndex())) {
return;
}
mergeCell(sheet, cell, CellMergeEnum.ROW);
}
}
private static void mergeCell(Sheet sheet, Cell cell, CellMergeEnum cellMergeEnum) {
if (Objects.isNull(cell)) {
return;
}
int rowIndex = cell.getRowIndex(), colIndex = cell.getColumnIndex();
Row preRow = null;
Cell preCell = null;
if(CellMergeEnum.ROW.equals(cellMergeEnum)) {
preRow = sheet.getRow(rowIndex - 1);
preCell = preRow.getCell(colIndex);
}
if(CellMergeEnum.COLUMN.equals(cellMergeEnum)) {
if(colIndex == 0) {
return;
}
preRow = cell.getRow();
preCell = preRow.getCell(colIndex - 1);
}
if (Objects.isNull(preRow)||Objects.isNull(preCell)) {
return;
}
mergeRows(sheet, preCell, cell);
}
/**
* 行单元格合并
*
* @param sheet
* @param preCell
* @param curCell
*/
private static void mergeRows(Sheet sheet, Cell preCell, Cell curCell) {
Object preCellValue = getCellValue(preCell), curCellValue = getCellValue(curCell);
if(Objects.isNull(curCellValue)) {
return;
}
if(!"".equals(preCellValue)) {
if(!preCellValue.equals(curCellValue)) {
return;
}
curCell.setCellType(CellType.BLANK);
sheet.addMergedRegion(new CellRangeAddress(preCell.getRowIndex(), curCell.getRowIndex(), preCell.getColumnIndex(), curCell.getColumnIndex()));
return;
}
List<CellRangeAddress> list = sheet.getMergedRegions();
CellRangeAddress rangeAddress = list.stream().filter(e -> compareColAndRow(e, preCell)).findFirst().orElse(null);
if(Objects.isNull(rangeAddress)) {
if("".equals(curCellValue)) {
curCell.setCellType(CellType.BLANK);
sheet.addMergedRegion(new CellRangeAddress(preCell.getRowIndex(), curCell.getRowIndex(), preCell.getColumnIndex(), curCell.getColumnIndex()));
return;
}
return;
}
int firstRow = rangeAddress.getFirstRow(), firstColumn = rangeAddress.getFirstColumn();
String value = String.valueOf(getCellValue(sheet.getRow(firstRow).getCell(firstColumn)));
if(!value.equals(curCellValue)) {
return;
}
int lastRow = curCell.getRowIndex(), lastColumn = curCell.getColumnIndex();
for (int i = 0; i < list.size(); i++) {
if(rangeAddress.equals(list.get(i))) {
sheet.removeMergedRegion(i);
curCell.setCellType(CellType.BLANK);
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
return;
}
}
}
private static boolean compareColAndRow(CellRangeAddress cellRangeAddress, Cell cell) {
return cellRangeAddress.getFirstColumn()<=cell.getColumnIndex() && cellRangeAddress.getLastColumn()>=cell.getColumnIndex()
&&cellRangeAddress.getFirstRow()<=cell.getRowIndex()&&cellRangeAddress.getLastRow()>=cell.getRowIndex();
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
protected static Object getCellValue(Cell cell) {
if (Objects.isNull(cell)) {
return "";
}
CellType cellTypeEnum = cell.getCellTypeEnum();
switch (cellTypeEnum) {
case STRING:
return cell.getStringCellValue();
case BOOLEAN:
return cell.getBooleanCellValue();
case NUMERIC:
return cell.getNumericCellValue();
default:
return "";
}
}
public enum CellMergeEnum {
ROW, COLUMN;
}
}
通过模板实现数据填充
模板内容：
填充数据代码：
public class ExcelMergeTest {
public static void main(String[] args) {
Map<String, List<ExcelDto>> map = new HashMap<>(2);
map.put("a", getListDtos());
map.put("b", getListDtos());
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(new File("F:\\template2.xlsx"))
.withTemplate("F:\\SourceCode\\easyexcel\\src\\main\\resources\\template2.xlsx");
excelWriterBuilder.inMemory(Boolean.TRUE);
ExcelWriter excelWriter = excelWriterBuilder.build();
WriteSheet writeSheet = EasyExcel.writerSheet(0)
.registerWriteHandler(CellMergeWriterHandler.getCellWriteHandler(CellMergeWriterHandler.CellMergeEnum.COLUMN, Arrays.asList(4)))
.registerWriteHandler(CellMergeWriterHandler.getCellWriteHandler(CellMergeWriterHandler.CellMergeEnum.ROW, Arrays.asList(0,1,2)))
.build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(new FillWrapper("a", getListDtos()), fillConfig, writeSheet);
excelWriter.fill(new FillWrapper("b", getListDtos()), fillConfig, writeSheet);
excelWriter.finish();
}
public static List<ExcelDto> getListDtos() {
List<ExcelDto> list = new ArrayList<>();
int t = 1000, i = 0;
while (i<1) {
list.add(ExcelDto.builder().name("张三").age(2).sex(2).work("打工仔").profession("打工仔").build());
list.add(ExcelDto.builder().name("李四").age(2).sex(2).work("打工仔").profession("打工仔").build());
list.add(ExcelDto.builder().name("李四").age(1).sex(1).work("打工仔").profession("打工仔").build());
list.add(ExcelDto.builder().name("李四").age(2).sex(2).work("打工仔").profession("打工仔").build());
list.add(ExcelDto.builder().name("赵六").age(2).sex(2).work("老板").profession("老板").build());
i ++;
}
return list;
}
}
完成效果：
合并单元格的本质还是要知道要合并单元格的坐标，然后通过sheet合并渲染。
扫一扫
611
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
