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

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

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

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

　　　　} 
　　} 
}

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

合并行：

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;<br><br>import com.alibaba.excel.metadata.Head;<br>import com.alibaba.excel.write.merge.AbstractMergeStrategy;<br>import org.apache.poi.ss.usermodel.*;<br>import org.apache.poi.ss.util.CellRangeAddress;<br><br>import java.util.List;<br>import java.util.Map;<br>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;
    }