easyExcel生成excel并导出自定义样式------添加复杂表头

爱被打了一巴掌 2024-02-05 17:33 230阅读 0赞

设置合并竖行单元格，表头设置

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;
 }

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)); 
 　　　　　　}); 
 　　　　} 
 
 　　　　} 
 　　} 
 }

![在这里插入图片描述][3119050f6939428e97513d30dda8a7ba.png]

下面为参考内容：  
调整内容：  
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);
        }
    }

![在这里插入图片描述][e5d728e096214c75bc085068e177171f.png]  
合并行：

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;
 }

![在这里插入图片描述][79ef1d026c794d549215c368543dd467.png]

[3119050f6939428e97513d30dda8a7ba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b7c56c05319f4ea3a6c372a39744c035.png
[e5d728e096214c75bc085068e177171f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/aaef722302354a6abd627484ac45998f.png
[79ef1d026c794d549215c368543dd467.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/0dbbc5fdc0ee420fbf53fb3dafe8e606.png