1、行合并
@Slf4j
public class ExcelRowHandler implements RowWriteHandler, SheetWriteHandler {
private Integer startRowIndex = 0;
private Integer endRowIndex;
private Set<Integer> mergeColSet = new HashSet<>();
private Integer sheetNo = 0;
private String sheetName;
public ExcelRowHandler() {}
public ExcelRowHandler(Integer startRowIndex, Integer endRowIndex) {
this.startRowIndex = startRowIndex;
this.endRowIndex = endRowIndex;
}
public ExcelRowHandler(Integer startRowIndex, Integer endRowIndex, Set<Integer> mergeColSet) {
this.startRowIndex = startRowIndex;
this.endRowIndex = endRowIndex;
this.mergeColSet = mergeColSet;
}
public ExcelRowHandler(Integer startRowIndex, Integer endRowIndex, Set<Integer> mergeColSet, String sheetName, Integer sheetNo) {
this.startRowIndex = startRowIndex;
this.endRowIndex = endRowIndex;
this.mergeColSet = mergeColSet;
this.sheetName = sheetName;
this.sheetNo = sheetNo;
}
private Map<Integer, Object> columnDataMap = new HashMap<>();
private Map<Object, Integer> currentDataFirstRowMap = new HashMap<>();
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
if (!isHead) {
Sheet sheet = writeSheetHolder.getSheet();
int currentRow = row.getRowNum();
int preRow = currentRow - 1;
if (currentRow >= startRowIndex && currentRow <= endRowIndex && null != mergeColSet && !mergeColSet.isEmpty()) {
for (Integer i : mergeColSet) {
Object currentColData = "";
if (null != row.getCell(i)) {
CellType cellType = row.getCell(i).getCellType();
switch (cellType) {
case NUMERIC -> currentColData = row.getCell(i).getNumericCellValue();
case STRING -> currentColData = row.getCell(i).getStringCellValue();
}
}
if (currentRow == startRowIndex && !TextUtil.isEmpty(currentColData)) {
currentDataFirstRowMap.put(getKey(currentColData, i), currentRow);
columnDataMap.put(i, currentColData);
} else {
Object preData = columnDataMap.get(i);
if (!TextUtil.isEmpty(currentColData)) {
if (TextUtil.isEmpty(preData)) {
currentDataFirstRowMap.put(getKey(currentColData, i), currentRow);
columnDataMap.put(i, currentColData);
} else {
if (!preData.equals(currentColData)) {
Integer preDataFirstRow = currentDataFirstRowMap.get(getKey(preData, i));
if (null != preDataFirstRow && preDataFirstRow != preRow) {
log.info("合并数据:{},开始行:{},结束行:{},列:{}", preData, preDataFirstRow, preRow, i);
mergeRegion(sheet, preDataFirstRow, preRow, i);
}
currentDataFirstRowMap.remove(getKey(preData, i));
columnDataMap.remove(i);
currentDataFirstRowMap.put(getKey(currentColData, i), currentRow);
columnDataMap.put(i, currentColData);
} else if (currentRow == endRowIndex) {
Integer preDataFirstRow = currentDataFirstRowMap.get(getKey(preData, i));
if (null != preDataFirstRow) {
log.info("合并数据:{},开始行:{},结束行:{},列:{}", preData, preDataFirstRow, preRow, i);
mergeRegion(sheet, preDataFirstRow, currentRow, i);
}
}
}
} else {
if (!TextUtil.isEmpty(preData)) {
Integer preDataFirstRow = currentDataFirstRowMap.get(getKey(preData, i));
if (null != preDataFirstRow && preDataFirstRow != preRow) {
log.info("合并数据:{},开始行:{},结束行:{},列:{}", preData, preDataFirstRow, preRow, i);
mergeRegion(sheet, preDataFirstRow, preRow, i);
}
}
currentDataFirstRowMap.remove(getKey(preData,i));
columnDataMap.remove(i);
}
}
}
}
}
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (null == sheetName) {
return;
}
writeWorkbookHolder.getCachedWorkbook().setSheetName(sheetNo, sheetName);
}
private String getKey(Object data, int col) {
return data + "-" + col;
}
private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int col) {
CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, col, col);
sheet.addMergedRegion(cellAddresses);
}
}
2、列合并
@Slf4j
public class ExcelColHandler implements RowWriteHandler {
private Integer firstColIndex = 0;
private Integer lastColIndex = 0;
public ExcelColHandler() {}
public ExcelColHandler(Integer firstColIndex, Integer lastColIndex) {
this.firstColIndex = firstColIndex;
this.lastColIndex = lastColIndex;
}
private Map<String, Integer> dataFirstColMap = new HashMap<>();
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
if (!isHead) {
Sheet sheet = writeSheetHolder.getSheet();
int currentRow = row.getRowNum();
String preData = null;
for (Integer i = firstColIndex; i <= lastColIndex; i++) {
String currentData = row.getCell(i).getStringCellValue();
if (!TextUtil.isEmpty(currentData)) {
if (Objects.equals(firstColIndex, i)) {
preData = currentData;
dataFirstColMap.put(currentData, i);
} else {
if (null != preData) {
if (!currentData.equals(preData)) {
Integer firstCol = dataFirstColMap.get(preData);
if (null != firstCol && firstCol != i - 1) {
mergeRegion(sheet, currentRow, firstCol, i - 1);
}
preData = currentData;
dataFirstColMap.remove(preData);
dataFirstColMap.put(currentData, i);
}
} else {
preData = currentData;
dataFirstColMap.put(currentData, i);
}
}
} else if (!TextUtil.isEmpty(preData)) {
Integer firstCol = dataFirstColMap.get(preData);
if (null != firstCol && firstCol != i - 1) {
mergeRegion(sheet, currentRow, firstCol, i - 1);
}
dataFirstColMap.remove(preData);
preData = null;
}
}
}
}
private void mergeRegion(Sheet sheet, int row, int firstCol, int lastCol) {
CellRangeAddress cellAddresses = new CellRangeAddress(row, row, firstCol, lastCol);
sheet.addMergedRegion(cellAddresses);
}
}