设置合并竖行单元格，表头设置
OutputStream outputStream = ExcelUtils.getResponseOutputStream(response, fileName); //根据数据组装需要合并的单元格 Map<String, List<String>> strategyMap = addMerStrategy(importAssignExcelDemo()); ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream) .registerWriteHandler(new ReadCustomCellStyleStrategy(new WriteCellStyle(), new WriteCellStyle())) .registerWriteHandler(new DemoMergeStrategy(strategyMap))//添加的竖行内容一致时，合并单元格 .excelType(ExcelTypeEnum.XLSX); ExcelWriter excelWriter = excelWriterBuilder.build(); String customer = "***表头名称"; String dateStr = new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
//getHeader（）获取自定义表头 ExcelUtils.writeOnly(excelWriter, importAssignExcelDemo(), ImportDemoExcelDTO.class, 1, fileName + "数据", getHeader(customer, dateStr));
private List<List<String>> getHeader(String customer, String dateStr) { /** * 打算展示成如下样子 * |客户：xxx 公司 (这一行需要合并单元格) * |单号: SO22222222222222| 日期: 2020-01-01 (分别需要合并单元格) * |产品ID|产品名称|价格|数量|总金额|备注| */ List<List<String>> list = new ArrayList<>(); //反射获取excel表头所有字段 List<String> attributeList = getExcelObjectAttribute(); //拼装表头 attributeList.forEach(excelName->{ List<String> head = new ArrayList<>(); head.add(customer); head.add(dateStr); head.add(excelName); list.add(head); }); return list;
/** * 反射获取excel对象excel注解属性坐表头 */ public static List<String> getExcelObjectAttribute() { List<String> list = new ArrayList(); try { //获取类名的包名地址 Class<?> clazz = Class.forName("com.。。。.importexcel.ImportExcelDTO"); // 得到所有定义字段 Field[] allFields = clazz.getDeclaredFields(); // 得到所有field并存放到一个list中. for (Field field : allFields) { //判断并获取excel注解信息 if (field.isAnnotationPresent(ExcelProperty.class)) { ExcelProperty excelProperty = field.getDeclaredAnnotation(ExcelProperty.class); list.add(excelProperty.value()[0]); } } } catch (Exception e) { e.printStackTrace(); } return list; }
/** * 重载：生成excel文件指定sheet页方法 * * @param excelWriter * @param data * @param clazz * @param sheetNo * @param sheetName * @param <T> * @param head 重载添加表头参数 */ public static <T> void writeOnly(ExcelWriter excelWriter, List<T> data, Class clazz, Integer sheetNo, String sheetName, List<List<String>> head) { ExcelWriterSheetBuilder excelWriterSheetBuilder; WriteSheet writeSheet = new WriteSheet(); excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter); excelWriterSheetBuilder.sheetNo(sheetNo); excelWriterSheetBuilder.sheetName(sheetName); writeSheet.setSheetNo(sheetNo); writeSheet.setSheetName(sheetName); writeSheet.setClazz(clazz); writeSheet.setHead(head); excelWriter.write(data, writeSheet); }
/** * 合并竖行单元格 * @param excelDtoList * @return */ private Map<String, List<String>> addMerStrategy(List<ImportDemoExcelDTO> excelDtoList) { Map<String, List<String>> strategyMap = new HashMap<>(); ImportDemoExcelDTO preExcelDto = null; //是否合并 int startCol = 0; List<String> mapList = new ArrayList<>(); String mapTest = ""; for (int i = 0; i < excelDtoList.size(); i++) { ImportDemoExcelDTO importAssignExcelDTO = excelDtoList.get(i); if (preExcelDto != null) { //从第二行开始判断是否需要合并 if (!preExcelDto.getLeaderName().equals(importAssignExcelDTO.getLeaderName())) { mapTest = startCol + "," + (i); //第0行是表头 startCol = i + 1; if (i == excelDtoList.size() - 1) { mapList.add(mapTest); //最后一条清空不用合并 mapTest = ""; } } else if (i == excelDtoList.size() - 1) { mapTest = startCol + "," + (startCol+1);//竖行合并 } } else { startCol = i + 1; } preExcelDto = importAssignExcelDTO; if (StringUtils.isNotBlank(mapTest)) { mapList.add(mapTest); } } //1代表第一列，excel从0开始。（至合并第一列） strategyMap.put("1", mapList); return strategyMap; }
package com.****.write.style; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.util.List; import java.util.Map; public class DemoMergeStrategy extends AbstractMergeStrategy {
//竖行合并，合并列：开始行，结束行 private Map<String, List<String>> strategyMap; private Sheet sheet; public DemoMergeStrategy(Map<String, List<String>> strategyMap) { this.strategyMap = strategyMap; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { this.sheet = sheet;
System.out.println(cell.getRowIndex()+"，测试记录行列，"+cell.getColumnIndex());
if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
/** * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的， * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3， * 但此时A2,A3已经是合并的单元格了 */
for (Map.Entry<String, List<String>> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
entry.getValue().forEach(rowRange -> {
//添加一个合并请求
String startRow = rowRange.split(",")[0];
String endRow = rowRange.split(",")[1];
sheet.addMergedRegionUnsafe(new CellRangeAddress(Integer.parseInt(startRow), Integer.parseInt(endRow), columnIndex, columnIndex));
});
}
}
}
}
效果实现
下面为参考内容：
调整内容：
OutputStream outputStream = ExcelUtils.getResponseOutputStream(response, excelName);
ExcelWriter excelWriter;
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream);
int lastCol = ExportCommentsTaskExcelDTO.class.getDeclaredFields().length-1;
//根据数据组装需要合并的单元格
Map<String, List<String>> strategyMap = addMerStrategy(commentsTaskExcelDTOList);
excelWriterBuilder
.registerWriteHandler(new CommentsCustomExcelHeader(excelName, createTime, lastCol))//文档名、时间、表头合并结束列数
.registerWriteHandler(new CommentsExcelStyleStrategy(new WriteCellStyle(), new WriteCellStyle()))
.registerWriteHandler(new CommentsExcelMergeStrategy(strategyMap)).relativeHeadRowIndex(3)//真实数据从第三行开始
.excelType(ExcelTypeEnum.XLSX);
excelWriter = excelWriterBuilder.build();
ExcelUtils.writeOnly(excelWriter, commentsTaskExcelDTOList, ExportCommentsTaskExcelDTO.class, 1, excelName + "数据");
DetectionSheetWriteHandler 复杂表头样式整理
这个类继承SheetWriteHandler 抽象类，实现afterSheetCreate方法，进行自定义表头策略，传入自定义的表头信息，及自定义样式。
public class DetectionSheetWriteHandler implements SheetWriteHandler {
private String dataTime;
public DetectionSheetWriteHandler(){}
public DetectionSheetWriteHandler(String dataTime){
this.dataTime = dataTime;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
//设置第一行标题
Row row1 = sheet.createRow(1);
row1.setHeight((short) 800);
Cell row1Cell1 = row1.createCell(0);
row1Cell1.setCellValue(" 统 计 表");
CellStyle row1CellStyle = workbook.createCellStyle();
row1CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
row1CellStyle.setAlignment(HorizontalAlignment.CENTER);
Font row1Font = workbook.createFont();
row1Font.setBold(true);
row1Font.setFontName("宋体");
row1Font.setFontHeightInPoints((short) 18);
row1CellStyle.setFont(row1Font);
row1Cell1.setCellStyle(row1CellStyle);
//合并单元格，起始行,结束行,起始列,结束列
sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 5));
// sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 22, 23));
// 设置第二行标题
Row row2 = sheet.createRow(2);
row2.setHeight((short) 400);
Cell row2Cell1 = row2.createCell(0);
row2Cell1.setCellValue("时间范围："+ dataTime);
CellStyle row2CellStyle = workbook.createCellStyle();
row2CellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
row2CellStyle.setAlignment(HorizontalAlignment.RIGHT);
Font row2Font = workbook.createFont();
row2Font.setFontName("宋体");
row2Font.setFontHeightInPoints((short) 10);
row2CellStyle.setFont(row2Font);
row2Cell1.setCellStyle(row2CellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 0, 5));
}
}
自定义excel内容格式
DetectionCellStyleStrategy 类 自定义excel内容的样式
public class DetectionCellStyleStrategy {
/**
* 导出excel时的样式配置
*
* @param headFont
* contentFont字体大小
* @return
*/
public static HorizontalCellStyleStrategy getStyleStrategy(short headFont, short contentFont) {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为灰色
// headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints(headFont);
// 字体样式
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
headWriteCellStyle.setWrapped(true);
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
headWriteCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
headWriteCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
// FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints(contentFont);
// 字体样式
contentWriteFont.setFontName("宋体");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 自动换行
contentWriteCellStyle.setWrapped(true);
// 水平对齐方式
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
效果：
————————————————
版权声明：本文为CSDN博主「笑依」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
参考原文链接：https://blog.csdn.net/weixin_50539367/article/details/108530250
合并行：
excelWriterBuilder
.registerWriteHandler(new CommentsCustomExcelHeader(excelName, createTime, lastCol))
.registerWriteHandler(new CommentsExcelStyleStrategy(new WriteCellStyle(), new WriteCellStyle()))
.registerWriteHandler(new CommentsExcelMergeStrategy(strategyMap))
.relativeHeadRowIndex(2)
.excelType(ExcelTypeEnum.XLSX);
package com.****.excel.write.style;
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 java.util.List;
import java.util.Map;
public class CommentsExcelMergeStrategy extends AbstractMergeStrategy { //需要合并的行：合并的开始，结束列 private Map<String, String> strategyMap; private Sheet sheet; public CommentsExcelMergeStrategy(Map<String, String> strategyMap) { this.strategyMap = strategyMap; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { this.sheet = sheet; if (cell.getRowIndex() >= 3 && cell.getColumnIndex() == 0) { /** * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的， * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3， * 但此时A2,A3已经是合并的单元格了 */ for (Map.Entry<String, String> entry : strategyMap.entrySet()) { System.out.println(entry.getKey()+"，内容，"+entry.getValue()); if(entry.getKey().equals(cell.getRowIndex()+"")){ Integer rowIndex = Integer.valueOf(entry.getKey()); //添加一个合并请求 String startCol = entry.getValue().split(",")[0]; String endCol = entry.getValue().split(",")[1]; sheet.addMergedRegionUnsafe(new CellRangeAddress(rowIndex, rowIndex, Integer.parseInt(startCol), Integer.parseInt(endCol))); //设置样式，加粗，靠左 CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Font font = sheet.getWorkbook().createFont(); cellStyle.setAlignment(HorizontalAlignment.LEFT); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); font.setBold(true); cellStyle.setFont(font); cell.setCellStyle(cellStyle); } } } } }
/**
* 横行领导名称合并单元格
*
* @param excelDtoList
* @return
*/
private Map<String, String> addMerStrategy(List<Object> excelDtoList, int endCol) {
Map<String, String> strategyMap = new HashMap<>();
//第0行是表头
int startCol = 3;
String mapTest = "";
for (int i = 0; i < excelDtoList.size(); i++) {
ExportCommentsTaskExcelDTO commentsTaskExcelDTO = (ExportCommentsTaskExcelDTO) excelDtoList.get(i);
//从第二行开始判断是否需要合并
if (StringUtils.contains(commentsTaskExcelDTO.getSortNum(), "(") && Objects.isNull(strategyMap.get(startCol+i))) {
mapTest = 0 + "," + endCol;
strategyMap.put(startCol + i + "", mapTest);
}
}
return strategyMap;
}
效果：
