easyexcel新增自定义表格默认样式

在使用easyexcel默认配置导出excel的情况下标题是加粗、宋体，内容样式是Calibri字体。业务希望标题不加粗，内容使用宋体。所以需要自定义一个全局的导出样式使用。

定义全局样式工具类

我们需要分别定义表头的样式和内容的样式，代码如下：

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * EasyExcel 样式工具类
 */
public class CustomExcelStyleUtil {
    
    /**
     * 标题样式
     *
     */
    public static WriteCellStyle getHeadStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        
        // 字体
        WriteFont headWriteFont = new WriteFont();
        //设置字体名字
        headWriteFont.setFontName("宋体");
        //设置字体大小
        headWriteFont.setFontHeightInPoints((short) 11);
        //取消字体加粗
        headWriteFont.setBold(Boolean.FALSE);
        //在样式用应用设置的字体;
        headWriteCellStyle.setWriteFont(headWriteFont);

        //设置底边框;
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置左边框;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        //设置右边框;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        //设置顶边框;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);

        //取消标题自动换行;
        headWriteCellStyle.setWrapped(Boolean.FALSE);
        // 锁定标题
        headWriteCellStyle.setLocked(true);
    
        //设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        
        return headWriteCellStyle;
    }
    
    
    /**
     * 内容样式
     */
    public static WriteCellStyle getContentStyle() {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        
        // 设置字体
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 11);//设置字体大小
        contentWriteFont.setFontName("宋体"); //设置字体名字
        contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;
        
        //设置样式;
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        
        // contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中
        // contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        contentWriteCellStyle.setWrapped(Boolean.FALSE); //设置自动换行;
        
        return contentWriteCellStyle;
    }
}

使用自定义样式

我们在导出的时候指定使用我们刚刚创建的配置即可

List<ExportEarthquakePolicyListVO> exportPolicyLists = riskEarthquakePolicyService.exportEarthquakePolicyList(searchDTO, companyCode);

    if (exportPolicyLists == null || exportPolicyLists.isEmpty()) {
      return;
    }
    
    // 设置文本内省
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    // 设置字符编码
    response.setCharacterEncoding("utf-8");
    String fileTimeStr = DateUtil.format(DateUtil.date(), "yyyyMMddHHmm");
    String fileName = URLEncoder.encode("标的清单_" + fileTimeStr, StandardCharsets.UTF_8);
    // 设置响应头
    response.setHeader("Content-disposition", "filename=" + fileName + ".xlsx");
  
    // 使用try-with-resources确保资源释放
    try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ExportEarthquakePolicyListVO.class)
            .registerWriteHandler(new HorizontalCellStyleStrategy(CustomExcelStyleUtil.getHeadStyle(),CustomExcelStyleUtil.getContentStyle()))
            .build()
    ) {
      WriteSheet writeSheet = EasyExcel.writerSheet("标的清单").build();
      excelWriter.write(exportPolicyLists, writeSheet);
    }
  }