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

引入jar

   <!--引入 阿里的  easyexcel  如果报错 需要引入  asm   jar-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
              <version>2.2.6</version>
        </dependency>

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

 

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<ExportJtgxldVo> listExport = new ArrayList<ExportJtgxldVo>();
        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;
}
/**
 * 创建表头
 * @return
 */
private static List<List<String>> head() {
    List<List<String>> list = new ArrayList<List<String>>();
    List<String> head0 = new ArrayList<String>();
    head0.add("序号");
    List<String> head1 = new ArrayList<String>();
    head1.add("姓名");
    List<String> head2 = new ArrayList<String>();
    head2.add("查询线索：身份证号");
    List<String> head3 = new ArrayList<String>();
    head3.add("户号");
    List<String> head4 = new ArrayList<String>();
    head4.add("关联结果");
    List<String> head5 = new ArrayList<String>();
    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;

/**
 * @Author:sunyuanquan
 * @Description:家庭关系落地
 * @Date:Created in 10:17 2022/3/13
 */
@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;

/**
 * @Author:
 * @Description:
 * @Date:Created in 15:08 2022/3/13
 */
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;
    }
    /**
     * 创建表头
     * @return
     */
    private static List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("序号");
        List<String> head1 = new ArrayList<String>();
        head1.add("姓名");
        List<String> head2 = new ArrayList<String>();
        head2.add("查询线索：身份证号");
        List<String> head3 = new ArrayList<String>();
        head3.add("户号");
        List<String> head4 = new ArrayList<String>();
        head4.add("关联结果");
        List<String> head5 = new ArrayList<String>();
        head5.add("户籍地址");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        list.add(head4);
        list.add(head5);
        return list;
    }

    /**
     * 创建数据
     * @return
     */
    private static List<ExportJtgxldVo> dataList() {
        List<ExportJtgxldVo> list = new ArrayList<ExportJtgxldVo>();
        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;

/**
 * @Author:sunyuanquan
 * @Description:导出合并表头
 * @Date:Created in 15:12 2022/3/13
 */
public class MyMergeStrategy  extends AbstractMergeStrategy {
    //合并坐标集合
    private List<CellRangeAddress> cellRangeAddresss;
    //构造
    public MyMergeStrategy() {
        List<CellRangeAddress> list = new ArrayList<>();
        //合并 单元格坐标
        CellRangeAddress item1 = new CellRangeAddress(0, 0, 4, 7);
        list.add(item1);
        this.cellRangeAddresss = list;
    }
    /**
     * merge
     * @param sheet
     * @param cell
     * @param head
     * @param relativeRowIndex
     */
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        //合并单元格
        /**
         *  ****加个判断:if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {}****
         * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
         * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
         * 但此时A2,A3已经是合并的单元格了
         */
        if (CollectionUtils.isNotEmpty(cellRangeAddresss)) {
            if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
                for (CellRangeAddress item : cellRangeAddresss) {
                    sheet.addMergedRegionUnsafe(item);
                }
            }
        }
    }
}

2.3合并后单元格效果