EasyExcel合并表头，控制文字大小及样式处理

引入jar

com.alibaba
easyexcel
2.2.6

一实现表头合并，表格展示大小及样式简单处理,实现效果如下图所示：

1.1生成表头和数据 1.1.1control层

@ResultAnnotation(moduleName = EModule.PLSFLD_JTGXLD, operateType = EOperate.SEARCH, description = "家庭关系落地（单个或批量）导出",operateLogger = false)
@ApiOperation(value = "家庭关系落地（单个或批量）-导出")
@ApiResponses(value = {@ApiResponse(code = 200, message = "请求成功", response = PageResult.class)})
@GetMapping("/exportPlSfldJtgxld")
public void exportPlSfldJtgxld(HjdQuery hjdQuery, HttpServletResponse response)throws IOException {
    try{
        String date = DateUtil.format(new Date(), DatePattern.PURE_DATE_PATTERN);
        String fileName = "家庭关系落地" + "-" + date +".xlsx";
        this.resolveResponse(response, fileName);

        ServletOutputStream outputStream = response.getOutputStream();
        plsfldJtgxService.exportPlSfldHj(hjdQuery,outputStream);
    }catch (Exception e){
        log.error("下载文件失败",e);
        this.resetResponse(response,e);
    }
}

private void resolveResponse(HttpServletResponse response, String fileName){
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String encodeFileName = URLEncoder.createDefault().encode(fileName, StandardCharsets.UTF_8);
    response.setHeader("Content-disposition", "attachment;filename=" + encodeFileName);
    response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
}
private void resetResponse(HttpServletResponse response, Exception exception)throws IOException {
    // 重置response
    response.reset();
    response.setContentType("application/json");
    response.setCharacterEncoding("utf-8");
    PageResult pageResult = PageResult.failed("下载文件失败，" + exception.getMessage());
    response.getWriter().println(JSON.toJSonString(pageResult));
}

1.1.2service层

List listExport = new ArrayList();
        dataExportTransport(listExport,list);
        EasyExcel.write(outputStream,ExportJtgxldVo.class).head(head()).sheet("Sheet1")
                .registerWriteHandler(new MyMergeStrategy())//自定义合并 单元格
                .registerWriteHandler(createTableStyle())
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(listExport);//数据

private static WriteHandler createTableStyle() {
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 背景
    headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    // 设置字体
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short) 14);
    headWriteCellStyle.setWriteFont(headWriteFont);
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // 背景
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short) 13);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    // 设置边框的样式
    contentWriteCellStyle.setBorderBottom(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderLeft(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderRight(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderTop(BorderStyle.DASHED);
    // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    return horizontalCellStyleStrategy;
}

private static List> head() {
    List> list = new ArrayList>();
    List head0 = new ArrayList();
    head0.add("序号");
    List head1 = new ArrayList();
    head1.add("姓名");
    List head2 = new ArrayList();
    head2.add("查询线索：身份证号");
    List head3 = new ArrayList();
    head3.add("户号");
    List head4 = new ArrayList();
    head4.add("关联结果");
    List head5 = new ArrayList();
    head5.add("户籍地址");
    list.add(head0);
    list.add(head1);
    list.add(head2);
    list.add(head3);
    list.add(head4);
    list.add(head5);
    return list;
}

1.1.3entity

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;


@Data
public class ExportJtgxldVo {

    @ExcelProperty("序号")
    @ColumnWidth(6)
    private String xh;

    @ExcelProperty("姓名")
    @ColumnWidth(10)
    private String name;

    @ExcelProperty("查询线索：身份证号")
    @ColumnWidth(25)
    private String sfzh;

    @ExcelProperty("户号")
    @ColumnWidth(16)
    private String hh;

    @ExcelProperty("关联结果")
    @ColumnWidth(10)
    private String nameR;

    @ExcelProperty("关联结果")
    @ColumnWidth(25)
    private String sfzhR;

    @ExcelProperty("关联结果")
    @ColumnWidth(20)
    private String hzgxR;

    @ExcelProperty("关联结果")
    @ColumnWidth(23)
    private String phoneR;

    @ExcelProperty("户籍地详细地址")
    @ColumnWidth(40)
    private String hjdxxdz;

}

二测试
2.1生成表头和数据

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.surfilter.business.plsfld.entity.vo.ExportJtgxldVo;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


public class test {
    public static void main(String[] args) {
        // 写法1
        String fileName = "D:\" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName,ExportJtgxldVo.class).head(head()).sheet("Sheet1")
                .registerWriteHandler(new MyMergeStrategy())//自定义合并 单元格
                .registerWriteHandler(createTableStyle())
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(dataList());
    }

    private static WriteHandler createTableStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 14);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 13);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置边框的样式
        contentWriteCellStyle.setBorderBottom(BorderStyle.DASHED);
        contentWriteCellStyle.setBorderLeft(BorderStyle.DASHED);
        contentWriteCellStyle.setBorderRight(BorderStyle.DASHED);
        contentWriteCellStyle.setBorderTop(BorderStyle.DASHED);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
    
    private static List> head() {
        List> list = new ArrayList>();
        List head0 = new ArrayList();
        head0.add("序号");
        List head1 = new ArrayList();
        head1.add("姓名");
        List head2 = new ArrayList();
        head2.add("查询线索：身份证号");
        List head3 = new ArrayList();
        head3.add("户号");
        List head4 = new ArrayList();
        head4.add("关联结果");
        List head5 = new ArrayList();
        head5.add("户籍地址");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        list.add(head4);
        list.add(head5);
        return list;
    }

    
    private static List dataList() {
        List list = new ArrayList();
        for (int i = 0; i < 10; i++) {
            ExportJtgxldVo exportJtgxldVo = new ExportJtgxldVo();
            exportJtgxldVo.setXh(String.valueOf(i));
            exportJtgxldVo.setName("姓名三");
            exportJtgxldVo.setSfzh("3708321211302519");
            exportJtgxldVo.setHh("1122344");
            exportJtgxldVo.setNameR("姓名三");
            exportJtgxldVo.setSfzhR("3708321992302519");
            exportJtgxldVo.setPhoneR("1846375141");
            exportJtgxldVo.setHzgxR("与户主关系：户主");
            exportJtgxldVo.setHjdxxdz("户籍地址户籍地址户籍地址户籍地址户籍地址户籍地址户籍地址户籍地址");
            list.add(exportJtgxldVo);
        }
        return list;
    }
}

2.2合并单元格

import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.metadata.Head;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;


public class MyMergeStrategy  extends AbstractMergeStrategy {
    //合并坐标集合
    private List cellRangeAddresss;
    //构造
    public MyMergeStrategy() {
        List list = new ArrayList<>();
        //合并 单元格坐标
        CellRangeAddress item1 = new CellRangeAddress(0, 0, 4, 7);
        list.add(item1);
        this.cellRangeAddresss = list;
    }
    
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        //合并单元格
        
        if (CollectionUtils.isNotEmpty(cellRangeAddresss)) {
            if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
                for (CellRangeAddress item : cellRangeAddresss) {
                    sheet.addMergedRegionUnsafe(item);
                }
            }
        }
    }
}

2.3合并后单元格效果

EasyExcel合并表头，控制文字大小及样式处理

Java相关栏目本月热门文章