easyexcel实现文件导入导出请看上篇博客：springboot集成easyExcel实现文件导入导出

上篇文章已经知道如何使用easyExcel实现简单的文件导入导出，但是导出的表头和格式都是固定统一的，有时候就不太符合实际的业务需求，例如报销单，申请表等复杂的表头，这片文章将介绍如何实现动态的设置表头和单元格

maven配置

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.8</version></dependency><!--hutool工具包--><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.5.1</version></dependency>

实现导入导出的代码参考上一篇，本篇主要针对上一篇的代码配置进行了一些修改

复杂表头设置

这种方式是直接对表格的头部一行一行的进行赋值

public BaseResponse<?> downloadTemplate(HttpServletResponse response) throws Exception {response.setCharacterEncoding("UTF-8");response.setHeader("Content-disposition", "海缆路由表模板");List<List<String>> header = head();EasyExcel.write(response.getOutputStream()).head(header).sheet("模板").doWrite(Collections.EMPTY_LIST);return BaseResponse.success(true);}/*** 下载模板的自定义表头* @return*/private static List<List<String>> head() {List<List<String>> list = new ArrayList<>();List<String> head0 = new ArrayList<>();head0.add("序号");list.add(head0);Map<String, List<String>> map = getHeader();map.forEach((k, v) -> {String deviceCategory = k;List<String> ls = v;ls.forEach(e -> {List<String> head = new ArrayList<>();head.add(deviceCategory);head.add(e);list.add(head);});});List<String> head1 = new ArrayList<>();head1.add("备注");list.add(head1);List<String> head2 = new ArrayList<>();head2.add("埋深");list.add(head2);return list;}/*** 下载模板的自定义表头第二行* @return*/private static Map<String, List<String>> getHeader() {Map<String, List<String>> map = new HashMap<>();List<String> aList = new ArrayList<>();List<String> sList = new ArrayList<>();List<String> subList = new ArrayList<>();String column1 = "X";aList.add(column1);String column2 = "Y";aList.add(column2);String column3 = "B";sList.add(column3);String column4 = "L";sList.add(column4);String subColumn = "其它";subList.add(subColumn);subList.add("小计3");map.put("坐标", aList);map.put("经纬度", sList);return map;}

实现的复杂表头实际效果如图

除了这种方式，还可以使用注解的方式对表头字段进行动态赋值

excel实体类

@Data@ApiModel("角色管理")public class TSRoleVo extends ExcelModel {@ExcelIgnore@ApiModelProperty("id")private String id;@ExcelProperty(value = {"角色表列表","导出人:${title}","角色名称"} , index = 0)@ApiModelProperty(value = "角色名称")@ColumnWidth(25)private String roleName;//角色名称@ExcelProperty(value = {"角色表列表","导出人:${title}","角色编码"} , index = 1)@ApiModelProperty(value = "角色编码")@ColumnWidth(25)private String roleCode;//角色编码@ExcelProperty(value = {"角色表列表","导出人:${title}","部门权限组ID"} , index = 2)@ApiModelProperty(value = "部门权限组ID")@ColumnWidth(25)private String departAgId;//组织机构ID 部门权限组ID@Overridepublic boolean validation(Map<String, List<String>> validationArgs) {return false;}}

注解 @ExcelProperty 中的 ${title} 即为我们所需要动态配置的表头的内容，而value中名字一样的例如“角色表列表”等表示在导出excel是字段为合并的单元格，@ColumnWidth 注解表示设置当前字段的单元格的宽度, 实际效果如图

动态设置表头

@Slf4jpublic class EasyExcelTitleHandler implements CellWriteHandler {/**错误信息处理时正则表达式的格式*/private final String EXCEL_ERROR_REG = "^(.*)(\\(错误:)(.*)(\\))$";/**操作列*/private final List<Integer> columnIndex;private String title;PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");public EasyExcelTitleHandler(List<Integer> columnIndex, Short colorIndex, HashMap<Integer, String> annotationsMap, HashMap<Integer, String[]> dropDownMap , String title) {this.columnIndex = columnIndex;this.title = title;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {// 动态设置表头字段if (!ObjectUtils.isEmpty(head)) {List<String> headNameList = head.getHeadNameList();if (CollectionUtils.isNotEmpty(headNameList)) {Properties properties = new Properties();properties.setProperty("title", title);for (int i = 0 ; i < headNameList.size() ; i++){// 循环遍历替换headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));}}}}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {if(isHead){// 设置列宽Sheet sheet = writeSheetHolder.getSheet();writeSheetHolder.getSheet().getRow(0).setHeight((short)(1.8*256));Workbook workbook = writeSheetHolder.getSheet().getWorkbook();Drawing<?> drawing = sheet.createDrawingPatriarch();// 设置标题字体样式WriteCellStyle headWriteCellStyle = new WriteCellStyle();WriteFont headWriteFont = new WriteFont();// 字体headWriteFont.setFontName("Arial");// 文字大小headWriteFont.setFontHeightInPoints((short)12);// 是否加粗headWriteFont.setBold(false);headWriteCellStyle.setWriteFont(headWriteFont);headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);cell.setCellStyle(cellStyle);}}}

修改EasyExcelTitleHandler处理类，在beforeCellCreate方法中对于上面所说的 ${title} 的值做替换处理，需要替换的值在构造的时候传入。afterCellDispose方法是对于单元格字体的整体样式做一个修改。

这时候已经实现了动态表头的设置，接下来是对于单元格字体的指定修改

新增实体类

/*** 样式信息类*/@Datapublic class CellStyleModel {/*** sheet名称*/private String sheetName;/*** 列索引*/private int colIndex;/*** 行索引*/private int rowIndex;/*** 字体名称*/private String fontName;/*** 字体大小*/private Double fontHeight;/*** 字体颜色*/private Object fontColor;/*** 字体加粗*/private Boolean fontBold;/*** 字体斜体*/private Boolean fontItalic;/*** 字体下划线*/private Byte fontUnderLine;/*** 字体上标下标*/private Short fontTypeOffset;/*** 字体删除线*/private Boolean fontStrikeout;/*** 背景颜色*/private Object backgroundColor;/*** 上边框线条类型*/private BorderStyle borderTop;/*** 右边框线条类型*/private BorderStyle borderRight;/*** 下边框线条类型*/private BorderStyle borderBottom;/*** 左边框线条类型*/private BorderStyle borderLeft;/*** 上边框线条颜色*/private Object topBorderColor;/*** 上边框线条颜色*/private Object rightBorderColor;/*** 下边框线条颜色*/private Object bottomBorderColor;/***/private Object leftBorderColor;/*** 水平对齐方式*/private HorizontalAlignment horizontalAlignment;/*** 垂直对齐方式*/private VerticalAlignment verticalAlignment;/*** 自动换行方式*/private Boolean wrapText;/*** 生成字体名称样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontName 字体名称（默认宋体）* @return*/public static CellStyleModel createFontNameCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, fontName, null, null, null, null, null, null, null);}/*** 生成字体名称大小信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontHeight 字体大小* @return*/public static CellStyleModel createFontHeightCellStyleModel(String sheetName, int rowIndex, int columnIndex, Double fontHeight) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, fontHeight, null, null, null, null, null, null);}/*** 得到RBG自定义颜色** @param redNum 红色数值* @param greenNum 绿色数值* @param blueNum 蓝色数值* @return*/public static XSSFColor getRGBColor(int redNum, int greenNum, int blueNum) {XSSFColor color = new XSSFColor(new byte[]{(byte) redNum, (byte) greenNum, (byte) blueNum}, new DefaultIndexedColorMap());return color;}/*** 生成字体颜色样式信息（支持自定义RGB颜色）** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param redNum红色数值* @param greenNum 绿色数值* @param blueNum蓝色数值* @return*/public static CellStyleModel createFontColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, int redNum, int greenNum, int blueNum) {XSSFColor fontColor = getRGBColor(redNum, greenNum, blueNum);return createFontColorCellStyleModel(sheetName, rowIndex, columnIndex, fontColor);}/*** 生成字体颜色样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontColor 字体颜色* @return*/public static CellStyleModel createFontColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object fontColor) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, fontColor, null, null, null, null, null);}/*** 生成字体加粗样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontBold 字体加粗* @return*/public static CellStyleModel createFontBoldCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean fontBold) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, fontBold, null, null, null, null);}/*** 生成字体斜体样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontItalic 字体斜体* @return*/public static CellStyleModel createFontItalicCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean fontItalic) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, fontItalic, null, null, null);}/*** 生成字体下划线样式信息** @param sheetNamesheet页名称* @param rowIndex行号* @param columnIndex 列号* @param fontUnderLine 字体下划线* @return*/public static CellStyleModel createFontUnderLineCellStyleModel(String sheetName, int rowIndex, int columnIndex, Byte fontUnderLine) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, fontUnderLine, null, null);}/*** 生成字体上标下标样式信息** @param sheetNamesheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontTypeOffset 字体上标下标* @return*/public static CellStyleModel createFontTypeOffsetCellStyleModel(String sheetName, int rowIndex, int columnIndex, Short fontTypeOffset) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, fontTypeOffset, null);}/*** 生成字体删除线样式信息** @param sheetNamesheet页名称* @param rowIndex行号* @param columnIndex 列号* @param fontStrikeout 字体删除线* @return*/public static CellStyleModel createFontStrikeoutCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean fontStrikeout) {return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, fontStrikeout);}/*** 生成字体样式信息** @param sheetNamesheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontName 字体名称（默认宋体）* @param fontHeight字体大小* @param fontColor字体颜色* @param fontBold 字体加粗* @param fontItalic字体斜体* @param fontUnderLine 字体下划线* @param fontTypeOffset 字体上标下标* @param fontStrikeout 字体删除线* @return*/public static CellStyleModel createFontCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine, Short fontTypeOffset, Boolean fontStrikeout) {return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic, fontUnderLine, fontTypeOffset, fontStrikeout, null);}/*** 生成背景颜色样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex列号* @param backgroundColor 背景颜色* @return*/public static CellStyleModel createBackgroundColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object backgroundColor) {return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, null, backgroundColor);}/*** 生成背景颜色样式信息（支持自定义RGB颜色）** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param redNum红色数值* @param greenNum 绿色数值* @param blueNum蓝色数值* @return*/public static CellStyleModel createBackgroundColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, int redNum, int greenNum, int blueNum) {XSSFColor backgroundColor = getRGBColor(redNum, greenNum, blueNum);return createBackgroundColorCellStyleModel(sheetName, rowIndex, columnIndex, backgroundColor);}/*** 生成样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex列号* @param fontName 字体名称（宋体）* @param fontHeight字体大小* @param fontColor 字体颜色* @param fontBold 字体加粗* @param fontItalic字体斜体* @param fontUnderLine 字体下划线* @param fontTypeOffset 字体上标下标* @param fontStrikeout 字体删除线* @param backgroundColor 背景颜色* @return*/public static CellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor) {return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic, fontUnderLine, fontTypeOffset, fontStrikeout, backgroundColor, null, null, null, null, null, null, null, null);}/*** 生成上边框线条颜色样式信息** @param sheetNamesheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param topBorderColor 上边框线条颜色* @return*/public static CellStyleModel createTopBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object topBorderColor) {return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, topBorderColor, null, null, null);}/*** 生成右边框线条颜色样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex列号* @param rightBorderColor 右边框线条颜色* @return*/public static CellStyleModel createRightBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object rightBorderColor) {return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, null, rightBorderColor, null, null);}/*** 生成下边框线条颜色样式信息** @param sheetName sheet页名称* @param rowIndex行号* @param columnIndex 列号* @param bottomBorderColor 下边框线条颜色* @return*/public static CellStyleModel createBottomBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object bottomBorderColor) {return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, null, null, bottomBorderColor, null);}/*** 生成左边框线条颜色样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex列号* @param leftBorderColor 左边框线条颜色* @return*/public static CellStyleModel createLeftBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object leftBorderColor) {return createBorderColorCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, leftBorderColor);}/*** 生成上边框线条类型样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param borderTop 上边框线条类型* @return*/public static CellStyleModel createTopBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderTop) {return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, borderTop, null, null, null);}/*** 生成右边框线条类型样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param borderRight 右边框线条类型* @return*/public static CellStyleModel createRightBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderRight) {return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, null, borderRight, null, null);}/*** 生成下边框线条类型样式信息** @param sheetName sheet页名称* @param rowIndex行号* @param columnIndex 列号* @param borderBottom 下边框线条类型* @return*/public static CellStyleModel createBottomBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderBottom) {return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, null, null, borderBottom, null);}/*** 生成左边框线条类型样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param borderLeft 左边框线条类型* @return*/public static CellStyleModel createLeftBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderLeft) {return createBorderLineTypeCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, borderLeft);}/*** 生成边框线条颜色样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param borderColor 边框线条颜色* @return*/public static CellStyleModel createBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object borderColor) {return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, null, borderColor);}/*** 生成边框线条颜色样式信息** @param sheetName sheet页名称* @param rowIndex行号* @param columnIndex 列号* @param topBorderColor 上边框线条颜色* @param rightBorderColor 右边框线条颜色* @param bottomBorderColor 下边框线条颜色* @param leftBorderColor 左边框线条颜色* @return*/public static CellStyleModel createBorderColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor, Object leftBorderColor) {return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, topBorderColor, rightBorderColor, bottomBorderColor, leftBorderColor);}/*** 生成边框线条类型样式信息** @param sheetNamesheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param borderLineType 边框线条类型* @return*/public static CellStyleModel createBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderLineType) {return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, borderLineType, null);}/*** 生成边框线条类型样式信息** @param sheetName sheet页名称* @param rowIndex行号* @param columnIndex 列号* @param borderTop 上边框线条类型* @param borderRight 右边框线条类型* @param borderBottom 下边框线条类型* @param borderLeft 左边框线条类型* @return*/public static CellStyleModel createBorderLineTypeCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderTop, BorderStyle borderRight, BorderStyle borderBottom, BorderStyle borderLeft) {return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, borderTop, borderRight, borderBottom, borderLeft, null, null, null, null);}/*** 生成边框样式信息** @param sheetNamesheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param borderLineType 边框线条类型* @param borderColor 边框线条颜色* @return*/public static CellStyleModel createBorderCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderLineType, Object borderColor) {return createBorderCellStyleModel(sheetName, rowIndex, columnIndex, borderLineType, borderLineType, borderLineType, borderLineType, borderColor, borderColor, borderColor, borderColor);}/*** 生成边框样式信息** @param sheetName sheet页名称* @param rowIndex行号* @param columnIndex 列号* @param borderTop 上边框线条类型* @param borderRight 右边框线条类型* @param borderBottom下边框线条类型* @param borderLeft 左边框线条类型* @param topBorderColor 上边框线条颜色* @param rightBorderColor 右边框线条颜色* @param bottomBorderColor 下边框线条颜色* @param leftBorderColor 左边框线条颜色* @return*/public static CellStyleModel createBorderCellStyleModel(String sheetName, int rowIndex, int columnIndex, BorderStyle borderTop, BorderStyle borderRight, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor, Object leftBorderColor) {return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, null, null, borderTop, borderRight, borderBottom, borderLeft, topBorderColor, rightBorderColor, bottomBorderColor, leftBorderColor);}/*** 生成样式信息** @param sheetName sheet页名称* @param rowIndex行号* @param columnIndex 列号* @param fontName字体名称（宋体）* @param fontHeight 字体大小* @param fontColor 字体颜色* @param fontBold字体加粗* @param fontItalic 字体斜体* @param fontUnderLine字体下划线* @param fontTypeOffset 字体上标下标* @param fontStrikeout字体删除线* @param backgroundColor 背景颜色* @param borderTop 上边框线条类型* @param borderRight 右边框线条类型* @param borderBottom下边框线条类型* @param borderLeft 左边框线条类型* @param topBorderColor 上边框线条颜色* @param rightBorderColor 右边框线条颜色* @param bottomBorderColor 下边框线条颜色* @param leftBorderColor 左边框线条颜色* @return*/public static CellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor, BorderStyle borderTop, BorderStyle borderRight, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor, Object leftBorderColor) {return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic, fontUnderLine, fontTypeOffset, fontStrikeout, backgroundColor, borderTop, borderRight, borderBottom, borderLeft, topBorderColor, rightBorderColor, bottomBorderColor, leftBorderColor, null, null);}/*** 生成水平对齐方式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param horizontalAlignment 水平对齐方式* @return*/public static CellStyleModel createHorizontalAlignmentCellStyleModel(String sheetName, int rowIndex, int columnIndex, HorizontalAlignment horizontalAlignment) {return createAlignmentCellStyleModel(sheetName, rowIndex, columnIndex, horizontalAlignment, null);}/*** 生成垂直对齐方式信息** @param sheetName sheet页名称* @param rowIndex行号* @param columnIndex 列号* @param verticalAlignment 垂直对齐方式* @return*/public static CellStyleModel createVerticalAlignmentCellStyleModel(String sheetName, int rowIndex, int columnIndex, VerticalAlignment verticalAlignment) {return createAlignmentCellStyleModel(sheetName, rowIndex, columnIndex, null, verticalAlignment);}/*** 生成对齐方式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param horizontalAlignment 水平对齐方式* @param verticalAlignment 垂直对齐方式* @return*/public static CellStyleModel createAlignmentCellStyleModel(String sheetName, int rowIndex, int columnIndex, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) {return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, horizontalAlignment, verticalAlignment);}/*** 生成样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontName 字体名称（宋体）* @param fontHeight字体大小* @param fontColor 字体颜色* @param fontBold 字体加粗* @param fontItalic字体斜体* @param fontUnderLine 字体下划线* @param fontTypeOffset字体上标下标* @param fontStrikeout 字体删除线* @param backgroundColor背景颜色* @param borderTop 上边框线条类型* @param borderRight 右边框线条类型* @param borderBottom 下边框线条类型* @param borderLeft左边框线条类型* @param topBorderColor上边框线条颜色* @param rightBorderColor 右边框线条颜色* @param bottomBorderColor 下边框线条颜色* @param leftBorderColor左边框线条颜色* @param horizontalAlignment 水平对齐方式* @param verticalAlignment 垂直对齐方式* @return*/public static CellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor, BorderStyle borderTop, BorderStyle borderRight, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor, Object leftBorderColor, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) {return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, fontItalic, fontUnderLine, fontTypeOffset, fontStrikeout, backgroundColor, borderTop, borderRight, borderBottom, borderLeft, topBorderColor, rightBorderColor, bottomBorderColor, leftBorderColor, horizontalAlignment, verticalAlignment, null);}/*** 生成自动换行样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param wrapText 自动换行* @return*/public static CellStyleModel createWrapTextCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean wrapText) {return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, wrapText);}/*** 生成样式信息** @param sheetName sheet页名称* @param rowIndex 行号* @param columnIndex 列号* @param fontName 字体名称（宋体）* @param fontHeight字体大小* @param fontColor 字体颜色* @param fontBold 字体加粗* @param fontItalic字体斜体* @param fontUnderLine 字体下划线* @param fontTypeOffset字体上标下标* @param fontStrikeout 字体删除线* @param backgroundColor背景颜色* @param borderTop 上边框线条类型* @param borderRight 右边框线条类型* @param borderBottom 下边框线条类型* @param borderLeft左边框线条类型* @param topBorderColor上边框线条颜色* @param rightBorderColor 右边框线条颜色* @param bottomBorderColor 下边框线条颜色* @param leftBorderColor左边框线条颜色* @param horizontalAlignment 水平对齐方式* @param verticalAlignment 垂直对齐方式* @param wrapText 自动换行* @return*/public static CellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName, Double fontHeight, Object fontColor, Boolean fontBold, Boolean fontItalic, Byte fontUnderLine, Short fontTypeOffset, Boolean fontStrikeout, Object backgroundColor, BorderStyle borderTop, BorderStyle borderRight, BorderStyle borderBottom, BorderStyle borderLeft, Object topBorderColor, Object rightBorderColor, Object bottomBorderColor, Object leftBorderColor, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, Boolean wrapText) {CellStyleModel cellStyleModel = new CellStyleModel();//sheet页名称cellStyleModel.setSheetName(sheetName);//行号cellStyleModel.setRowIndex(rowIndex);//列号cellStyleModel.setColIndex(columnIndex);//设置字体样式//字体名称（比如宋体）fontName = fontName != null && StrUtil.equals(fontName, "") ? "宋体" : fontName;cellStyleModel.setFontName(fontName);//字体大小fontHeight = fontHeight != null && fontHeight <= 0 ? null : fontHeight;cellStyleModel.setFontHeight(fontHeight);//字体颜色fontColor = fontColor != null && (fontColor instanceof IndexedColors == false && fontColor instanceof XSSFColor == false)? null : fontColor;cellStyleModel.setFontColor(fontColor);//字体加粗cellStyleModel.setFontBold(fontBold);//字体斜体cellStyleModel.setFontItalic(fontItalic);//字体下划线fontUnderLine = fontUnderLine != null && (fontUnderLine != Font.U_NONE && fontUnderLine != Font.U_SINGLE && fontUnderLine != Font.U_DOUBLE&& fontUnderLine != Font.U_DOUBLE_ACCOUNTING && fontUnderLine != Font.U_SINGLE_ACCOUNTING) ? null : fontUnderLine;cellStyleModel.setFontUnderLine(fontUnderLine);//字体上标下标fontTypeOffset = fontTypeOffset != null && (fontTypeOffset != Font.SS_NONE && fontTypeOffset != Font.SS_SUB && fontTypeOffset != Font.SS_SUPER)? null : fontTypeOffset;cellStyleModel.setFontTypeOffset(fontTypeOffset);//字体删除线cellStyleModel.setFontStrikeout(fontStrikeout);//背景颜色backgroundColor = backgroundColor != null && (backgroundColor instanceof IndexedColors == false && backgroundColor instanceof XSSFColor == false)? null : backgroundColor;cellStyleModel.setBackgroundColor(backgroundColor);//边框样式//上边框线条类型cellStyleModel.setBorderTop(borderTop);//右边框线条类型cellStyleModel.setBorderRight(borderRight);//下边框线条类型cellStyleModel.setBorderBottom(borderBottom);//左边框线条类型cellStyleModel.setBorderLeft(borderLeft);//上边框颜色类型cellStyleModel.setTopBorderColor(topBorderColor);//右边框颜色类型cellStyleModel.setRightBorderColor(rightBorderColor);//下边框颜色类型cellStyleModel.setBottomBorderColor(bottomBorderColor);//左边框颜色类型cellStyleModel.setLeftBorderColor(leftBorderColor);//对齐方式//水平对齐方式cellStyleModel.setHorizontalAlignment(horizontalAlignment);//垂直对齐方式cellStyleModel.setVerticalAlignment(verticalAlignment);//自动换行cellStyleModel.setWrapText(wrapText);return cellStyleModel;}}

新增样式处理器

/*** 自定义单元格样式处理器（支持字体样式、背景颜色、边框样式、对齐方式、自动换行）*/public class CustomCellStyleHandler extends AbstractRowWriteHandler {/*** sheet页名称列表*/private List<String> sheetNameList;/*** 样式信息*/private List<CellStyleModel> cellStyleList = new ArrayList<>();/*** 自定义样式适配器构造方法** @param cellStyleList 样式信息*/public CustomCellStyleHandler(List<CellStyleModel> cellStyleList) {if (CollectionUtil.isEmpty(cellStyleList)) {return;}cellStyleList = cellStyleList.stream().filter(x -> x != null//判断sheet名称KEY是否存在&& StrUtil.isNotBlank(x.getSheetName())//字体样式//判断字体颜色KEY是否存在&& (x.getFontColor() == null || x.getFontColor() instanceof IndexedColors|| x.getFontColor() instanceof XSSFColor)//判断背景颜色KEY是否存在&& (x.getBackgroundColor() == null || x.getBackgroundColor() instanceof IndexedColors|| x.getBackgroundColor() instanceof XSSFColor)//边框样式// 判断上边框线条颜色KEY是否存在&& (x.getTopBorderColor() == null || x.getTopBorderColor() instanceof IndexedColors|| x.getTopBorderColor() instanceof XSSFColor)// 判断右边框线条颜色KEY是否存在&& (x.getRightBorderColor() == null || x.getRightBorderColor() instanceof IndexedColors|| x.getRightBorderColor() instanceof XSSFColor)// 判断下边框线条颜色KEY是否存在&& (x.getBottomBorderColor() == null || x.getBottomBorderColor() instanceof IndexedColors|| x.getBottomBorderColor() instanceof XSSFColor)// 判断左边框线条颜色KEY是否存在&& (x.getLeftBorderColor() == null || x.getLeftBorderColor() instanceof IndexedColors|| x.getLeftBorderColor() instanceof XSSFColor)).collect(Collectors.toList());this.cellStyleList = cellStyleList;sheetNameList = this.cellStyleList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {Sheet sheet = writeSheetHolder.getSheet();//不需要添加样式，或者当前sheet页不需要添加样式if (cellStyleList == null || cellStyleList.size() <= 0 || sheetNameList.contains(sheet.getSheetName()) == false) {return;}//获取当前行的样式信息List<CellStyleModel> rowCellStyleList = cellStyleList.stream().filter(x ->StrUtil.equals(x.getSheetName(), sheet.getSheetName()) && x.getRowIndex() == relativeRowIndex).collect(Collectors.toList());//该行不需要设置样式if (rowCellStyleList == null || rowCellStyleList.size() <= 0) {return;}for (CellStyleModel cellStyleModel : rowCellStyleList) {//设置单元格样式setCellStyle(cellStyleModel, row);}//删除已添加的样式信息cellStyleList.removeAll(rowCellStyleList);//重新获取要添加的sheet页姓名sheetNameList = cellStyleList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());}/*** 给单元格设置样式** @param cellStyleModel 样式信息* @param row 行对象*/private void setCellStyle(CellStyleModel cellStyleModel, Row row) {//背景颜色Object backgroundColor = cellStyleModel.getBackgroundColor();//自动换行Boolean wrapText = cellStyleModel.getWrapText();//列索引int colIndex = cellStyleModel.getColIndex();//边框样式Cell cell = row.getCell(colIndex);if (cell == null) {cell = row.createCell(colIndex);}XSSFCellStyle style = (XSSFCellStyle) cell.getRow().getSheet().getWorkbook().createCellStyle();// 克隆出一个 stylestyle.cloneStyleFrom(cell.getCellStyle());//设置背景颜色if (backgroundColor != null) {//使用IndexedColors定义的颜色if (backgroundColor instanceof IndexedColors) {style.setFillForegroundColor(((IndexedColors) backgroundColor).getIndex());}//使用自定义的RGB颜色else if (backgroundColor instanceof XSSFColor) {style.setFillForegroundColor((XSSFColor) backgroundColor);}style.setFillPattern(FillPatternType.SOLID_FOREGROUND);}//设置自动换行if (wrapText != null) {style.setWrapText(wrapText);}//设置字体样式setFontStyle(row, style, cellStyleModel);//设置边框样式setBorderStyle(style, cellStyleModel);//设置对齐方式setAlignmentStyle(style, cellStyleModel);cell.setCellStyle(style);}/*** 设置字体样式** @param row 行对象* @param style单元格样式* @param cellStyleModel 样式信息*/private void setFontStyle(Row row, XSSFCellStyle style, CellStyleModel cellStyleModel) {//字体名称String fontName = cellStyleModel.getFontName();//字体大小Double fontHeight = cellStyleModel.getFontHeight();//字体颜色Object fontColor = cellStyleModel.getFontColor();//字体加粗Boolean fontBold = cellStyleModel.getFontBold();//字体斜体Boolean fontItalic = cellStyleModel.getFontItalic();//字体下划线Byte fontUnderLine = cellStyleModel.getFontUnderLine();//字体上标下标Short fontTypeOffset = cellStyleModel.getFontTypeOffset();//字体删除线Boolean fontStrikeout = cellStyleModel.getFontStrikeout();//不需要设置字体样式if (fontName == null && fontHeight == null && fontColor == null && fontBold == null && fontItalic == null&& fontUnderLine == null && fontTypeOffset == null && fontStrikeout == null) {return;}XSSFFont font = null;//样式存在字体对象时，使用原有的字体对象if (style.getFontIndex() != 0) {font = style.getFont();}//样式不存在字体对象时，创建字体对象else {font = (XSSFFont) row.getSheet().getWorkbook().createFont();//默认字体为宋体font.setFontName("宋体");}//设置字体名称if (fontName != null) {font.setFontName(fontName);}//设置字体大小if (fontHeight != null) {font.setFontHeight(fontHeight);}//设置字体颜色if (fontColor != null) {//使用IndexedColors定义的颜色if (fontColor instanceof IndexedColors) {font.setColor(((IndexedColors) fontColor).getIndex());}//使用自定义的RGB颜色else if (fontColor instanceof XSSFColor) {font.setColor((XSSFColor) fontColor);}}//设置字体加粗if (fontBold != null) {font.setBold(fontBold);}//设置字体斜体if (fontItalic != null) {font.setItalic(fontItalic);}//设置字体下划线if (fontUnderLine != null) {font.setUnderline(fontUnderLine);}//设置字体上标下标if (fontTypeOffset != null) {font.setTypeOffset(fontTypeOffset);}//设置字体删除线if (fontStrikeout != null) {font.setStrikeout(fontStrikeout);}style.setFont(font);}/*** 设置边框样式** @param style单元格样式* @param cellStyleModel 样式信息*/private void setBorderStyle(XSSFCellStyle style, CellStyleModel cellStyleModel) {//上边框线条类型BorderStyle borderTop = cellStyleModel.getBorderTop();//右边框线条类型BorderStyle borderRight = cellStyleModel.getBorderRight();//下边框线条类型BorderStyle borderBottom = cellStyleModel.getBorderBottom();//左边框线条类型BorderStyle borderLeft = cellStyleModel.getBorderLeft();//上边框颜色类型Object topBorderColor = cellStyleModel.getTopBorderColor();//右边框颜色类型Object rightBorderColor = cellStyleModel.getRightBorderColor();//下边框颜色类型Object bottomBorderColor = cellStyleModel.getBottomBorderColor();//左边框颜色类型Object leftBorderColor = cellStyleModel.getLeftBorderColor();//不需要设置边框样式if (borderTop == null && borderRight == null && borderBottom == null && borderLeft == null && topBorderColor == null&& rightBorderColor == null && bottomBorderColor == null && leftBorderColor == null) {return;}//设置上边框线条类型if (borderTop != null) {style.setBorderTop(borderTop);}//设置右边框线条类型if (borderRight != null) {style.setBorderRight(borderRight);}//设置下边框线条类型if (borderBottom != null) {style.setBorderBottom(borderBottom);}//设置左边框线条类型if (borderLeft != null) {style.setBorderLeft(borderLeft);}//设置上边框线条颜色if (topBorderColor != null) {//使用IndexedColors定义的颜色if (topBorderColor instanceof IndexedColors) {style.setTopBorderColor(((IndexedColors) topBorderColor).getIndex());}//使用自定义的RGB颜色else if (topBorderColor instanceof XSSFColor) {style.setTopBorderColor((XSSFColor) topBorderColor);}}//设置右边框线条颜色if (rightBorderColor != null) {//使用IndexedColors定义的颜色if (rightBorderColor instanceof IndexedColors) {style.setRightBorderColor(((IndexedColors) rightBorderColor).getIndex());}//使用自定义的RGB颜色else if (rightBorderColor instanceof XSSFColor) {style.setRightBorderColor((XSSFColor) rightBorderColor);}}//设置下边框线条颜色if (bottomBorderColor != null) {//使用IndexedColors定义的颜色if (bottomBorderColor instanceof IndexedColors) {style.setBottomBorderColor(((IndexedColors) bottomBorderColor).getIndex());}//使用自定义的RGB颜色else if (bottomBorderColor instanceof XSSFColor) {style.setBottomBorderColor((XSSFColor) bottomBorderColor);}}//设置左边框线条颜色if (leftBorderColor != null) {//使用IndexedColors定义的颜色if (leftBorderColor instanceof IndexedColors) {style.setLeftBorderColor(((IndexedColors) leftBorderColor).getIndex());}//使用自定义的RGB颜色else if (topBorderColor instanceof XSSFColor) {style.setLeftBorderColor((XSSFColor) leftBorderColor);}}}/*** 设置对齐方式** @param style单元格样式* @param cellStyleModel 样式信息*/private void setAlignmentStyle(XSSFCellStyle style, CellStyleModel cellStyleModel) {//水平对齐方式HorizontalAlignment horizontalAlignment = cellStyleModel.getHorizontalAlignment();//垂直对齐方式VerticalAlignment verticalAlignment = cellStyleModel.getVerticalAlignment();//不需要设置对齐方式if (horizontalAlignment == null && verticalAlignment == null) {return;}//设置水平对齐方式if (horizontalAlignment != null) {style.setAlignment(horizontalAlignment);}//设置垂直对齐方式if (verticalAlignment != null) {style.setVerticalAlignment(verticalAlignment);}}}

修改excelUtil工具类

@Repositorypublic class EasyExcelUtil<T extends ExcelModel> {/*** 导出excel* @param outputStream 输出流* @param dataList导出的数据* @param classT 模板类* @param sheetNamesheetName* @param writeHandlers 样式处理类*/public void writeExcelWithModel(OutputStream outputStream, List<T> dataList, Class<? extends ExcelModel> classT, String sheetName, WriteHandler... writeHandlers) {// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 单元格策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 初始化表格样式HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy);if (null != writeHandlers && writeHandlers.length > 0) {for (WriteHandler writeHandler : writeHandlers) {excelWriterSheetBuilder.registerWriteHandler(writeHandler);}}// 开始导出excelWriterSheetBuilder.doWrite(dataList);}/*** 使用 模型 来读取Excel* @param fileInputStream Excel的输入流* @param clazz 模型的类* @param arg 验证用的参数，可以没有* @return 返回 模型 的列表(为object列表,需强转)*/public EasyExcelListener<T> readExcelWithModel(InputStream fileInputStream, Class<?> clazz, Map<String, List<String>> arg) {EasyExcelListener<T> listener = new EasyExcelListener<>();listener.setArg(arg);ExcelReader excelReader = EasyExcel.read(fileInputStream, clazz, listener).build();ReadSheet readSheet = EasyExcel.readSheet(0).build();excelReader.read(readSheet);excelReader.finish();return listener;}//规定excel格式public void createExcel(OutputStream os, List<T> data, T t, String sheetName) {EasyExcelTitleHandler easyExcelTitleHandler = new EasyExcelTitleHandler(null,null,null,null , null);writeExcelWithModel(os, data, t.getClass(), sheetName, easyExcelTitleHandler);}public static void setResponseHeader(HttpServletResponse response, String fileName) {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String excelName;try {excelName = URLEncoder.encode(fileName, "UTF-8");} catch (Exception e) {throw new CodeException(ResultCode.EXCEL_FAILURE);}response.addHeader("Access-Control-Expose-Headers", "*");response.setHeader("Content-disposition", "attachment;filename=" + excelName + ".xlsx");}public static InputStream getInputStream(MultipartFile file) {String fileName = file.getOriginalFilename();if (StringUtils.isBlank(fileName)) {throw new CodeException(ResultCode.EXCEL_FAILURE);}// 支持的excel格式List<String> excelFormat = Arrays.asList(".xlsx",".xls");if (!excelFormat.contains(fileName.substring(fileName.lastIndexOf(".")))) {throw new CodeException(ResultCode.EXCEL_FAILURE);}InputStream inputStream;try {inputStream = file.getInputStream();} catch (Exception e) {throw new CodeException(ResultCode.EXCEL_FAILURE);}return inputStream;}}

将导出excel方法中的样式处理器的参数类型修改为WriteHandler，此时再调用writeExcelWithModel的方法，就实现了整体的自定义样式

使用实例

public void downloadTemplate(HttpServletResponse response) {List<TSRoleVo> data = new ArrayList<>();EasyExcelUtil<TSRoleVo> excelUtil = new EasyExcelUtil<>();List<CellStyleModel> cellStyleList = new ArrayList<>();//设置单元格字体,字体大小,字体颜色,加粗,斜体,下划线,上标,删除线cellStyleList.add(CellStyleModel.createFontCellStyleModel("角色表模版", 0, 0, "Arial", 14D, IndexedColors.BLACK, false, false,null,null, false));//设置对齐方式 设置第二行右靠齐cellStyleList.add(CellStyleModel.createAlignmentCellStyleModel("角色表模版", 1, 0, HorizontalAlignment.RIGHT, VerticalAlignment.BOTTOM));//设置单元格背景颜色cellStyleList.add(CellStyleModel.createBackgroundColorCellStyleModel("角色表模版", 0, 0, IndexedColors.BLUE_GREY));//设置单元格边框类型和边框颜色cellStyleList.add(CellStyleModel.createBorderCellStyleModel("角色表模版", 0, 0, BorderStyle.DOUBLE, IndexedColors.RED));//设置自动换行cellStyleList.add(CellStyleModel.createWrapTextCellStyleModel("角色表模版", 0, 0, true));EasyExcelUtil.setResponseHeader(response,"角色表模版");try {excelUtil.writeExcelWithModel(response.getOutputStream(), data , TSRoleVo.class, "角色表模版" ,new EasyExcelTitleHandler(null,null,null,null,"xxx") ,new CustomCellStyleHandler(cellStyleList));} catch (IOException e) {e.printStackTrace();}}

最终效果