easyexcel导出(冻结固定标题行、自定义合并导出)

 一、冻结固定标题行

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.muyuan.platform.common.utils.CollUtils;
import com.muyuan.platform.common.utils.ObjUtils;
import org.apache.poi.ss.usermodel.Sheet;

import java.lang.reflect.Field;
import java.util.List;

/**
 * 冻结首行
 *
 * @author liao
 * @since 2022/10/24 16:31
 */
public class ExcelFreezeHeaderStrategy implements SheetWriteHandler {

    public int colSplit = 0, leftmostColumn = 0;

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //标题头设置两种方式 head和class
        List<List<String>> head = writeSheetHolder.getHead();
        //标题行
        Sheet sheet = writeSheetHolder.getSheet();
        //当head获取的为空时从class获取
        if (ObjUtils.isNull(head)){
            Class clazz = writeSheetHolder.getClazz();
            if (ObjUtils.isNotNull(clazz)) {
                head = getHead(clazz);
            }
        }
        int row = head.stream().mapToInt(List::size).max().orElse(1);
        sheet.createFreezePane(colSplit, row, leftmostColumn, row);
    }

    /**
     * 获取标题头<pre>通过class获取ExcelProperty注解中的标题头</pre>
     * @param clazz 类
     * @return 标题头
     */
    private List<List<String>> getHead(Class clazz){
        List<List<String>> result = CollUtils.newArrayList();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelProperty.class)){
                ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                if (ObjUtils.isNotNull(annotation)){
                    result.add(CollUtils.newArrayList(annotation.value()));
                }
            }
        }
        return result;
    }
}

导出方法调用

EasyExcel.write(response.getOutputStream(), clazz)
                    //默认样式
                    .registerWriteHandler(getHorizontalCellStyleStrategy())
                    .registerWriteHandler(new AutoColumnWidthStrategy())
                    //固定首行
                    .registerWriteHandler(new ExcelFreezeHeaderStrategy())
                    //设置不自动关闭流
                    .autoCloseStream(Boolean.FALSE)
                    .sheet(fileName)
                    .doWrite(list);

二、合并行导出(根据首列内容是否相同,合并指定列数)

1、合并策略方法(根据导出数据内容,生成合并范围)

    /**
     * 合并单元格策略<pre>根据首列相同,合并指定列数</pre>
     * @param mergeColumnIndexs 合并列数 前{x}列
     * @param hdrRow 表头行数-合并范围需要用到
     * @param list 导出的数据
     * @param <T> 导出数据-实体类
     * @return 合并范围
     */
    public static<T> List<CellRangeAddress> getOneColAddressList(int mergeColumnIndexs, int hdrRow, List<T> list){
        List<CellRangeAddress> addressList = CollUtils.newArrayList();
        if (CollUtils.isNotEmpty(list)){
            Class<?> aClass = list.get(0).getClass();
            Field f = null;
            Field[] fields = ReflectUtils.getFields(aClass);
            for (Field field : fields) {
                if (field.isAnnotationPresent(ExcelProperty.class)){
                    f = field;
                    break;
                }
            }
            if (f!= null){
                //list长度,最大行数
                int size = list.size();
                //防止下标越界使用
                int i2 = size-2;
                for (int i = 0; i < size; i++) {

                    //开始行 从 不合并标题
                    int firstRow = i+hdrRow;
                    //结束行
                    int lastRow =firstRow;
                    //开始列,结束列
                    //int firstCol,lastCol = 0;
                    String value = StrUtils.toStringBlank(ReflectUtils.getFieldValue(list.get(i),f));
                    boolean flag = true;
                    while (flag){
                        //最后一行不再进行匹配,直接退出
                        if (i>i2){
                            break;
                        }
                        String value1 = StrUtils.toStringBlank(ReflectUtils.getFieldValue(list.get(i+1),f));
                        //值相同则合并 -> 合并行数+1 下标+1遍历跳过相同行
                        if (StrUtils.equals(value1,value)){
                            i++;
                            lastRow++;

                        }else {
                            flag = false;
                        }
                        if (i==size){
                            flag = false;
                        }
                    }
                    if (lastRow != firstRow){
                        for (int i1 = 0; i1 < mergeColumnIndexs; i1++) {
                            addressList.add(new CellRangeAddress(firstRow,lastRow,i1,i1));
                        }
                    }
                }
            }
        }
        return addressList;
    }

2、执行策略

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * easyExcel合并单元格sheet级别执行-(提前生成合并范围)
 *
 * @author liao
 * @since 2022/7/4 17:20
 */
@Slf4j
public class ExcelCellMergeStrategy implements SheetWriteHandler {

    private List<CellRangeAddress> addressList;
    public ExcelCellMergeStrategy() {
    }

    public ExcelCellMergeStrategy(List<CellRangeAddress> addressList) {
        this.addressList = addressList;
    }
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //直接注入合并范围
        Sheet sheet = writeSheetHolder.getSheet();
        log.info("ExcelCellMergeStrategy|start|合并单元格|sheet名称:{}|开始执行",sheet.getSheetName());
        for (CellRangeAddress address : addressList) {
            sheet.addMergedRegionUnsafe(address);
        }
    }
}

2、导出方法调用

   /**
     * 导出
     * @param response servlet响应对象
     * @param fileName 文件名称
     * @param mergeColumnIndexs 合并列数 前{x}列
     * @param hdrRow 表头行数-合并范围需要用到
     * @param clazz 类
     * @param <T> 泛型
     */
    public static<T> void writerExcelOneMerge(HttpServletResponse response,String fileName,Class<T> clazz,int mergeColumnIndexs,int hdrRow,List<T> list){
        try {
            respInfoSettings(response,fileName);
            EasyExcel.write(response.getOutputStream(), clazz)
                    //默认样式
                    .registerWriteHandler(getHorizontalCellStyleStrategy())
                    //自定义合并单元格策略
                    .registerWriteHandler(new ExcelCellMergeStrategy(getOneColAddressList(mergeColumnIndexs,hdrRow,list)))
                    //固定标题
                    .registerWriteHandler(new ExcelFreezeHeaderStrategy())
                    //设置不自动关闭流
                    .autoCloseStream(Boolean.FALSE)
                    .sheet(fileName)
                    .doWrite(list);
        } catch (Exception e) {
            excpInfoReturned(response,e);
        }
    }

使用示例: