EasyPoi表头字体及样式自定义

本文主要是介绍EasyPoi表头字体及样式自定义，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

最近在研究EasyPoi的excel导出功能，发现其不支持表头各个单元格样式自定义，所以就对其导出功能进行了重写。

一、EasyPoi源码分析

在进行重写之前，首先要了解EasyPoi源码中，对表头处理的代码具体是如何实现的。查看源码可以看出，导出功能主要是通过ExcelExportService类来实现，而在其中可以看到向excel中填入表头及数据的方法为insertDataToSheet，具体代码如下：

protected void insertDataToSheet(Workbook workbook, ExportParams entity,List<ExcelExportEntity> entityList, Collection<?> dataSet,Sheet sheet) {try {dataHandler = entity.getDataHandler();if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) {needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());}dictHandler = entity.getDictHandler();commentHandler = entity.getCommentHandler();// 创建表格样式setExcelExportStyler((IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook));Drawing                 patriarch   = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();if (entity.isAddIndex()) {excelParams.add(indexExcelEntity(entity));}excelParams.addAll(entityList);sortAllParams(excelParams);int index = entity.isCreateHeadRows()? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0;int titleHeight = index;setCellWith(excelParams, sheet);setColumnHidden(excelParams, sheet);short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams);setCurrentIndex(1);createAddressList(sheet, index, excelParams, 0);Iterator<?>  its      = dataSet.iterator();List<Object> tempList = new ArrayList<Object>();while (its.hasNext()) {Object t = its.next();index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];tempList.add(t);if (index >= MAX_NUM) {break;}}if (entity.getFreezeCol() != 0) {sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);}mergeCells(sheet, excelParams, titleHeight);its = dataSet.iterator();for (int i = 0, le = tempList.size(); i < le; i++) {its.next();its.remove();}if (LOGGER.isDebugEnabled()) {LOGGER.debug("List data more than max ,data size is {}",dataSet.size());}// 发现还有剩余list 继续循环创建Sheetif (dataSet.size() > 0) {createSheetForMap(workbook, entity, entityList, dataSet);} else {// 创建合计信息addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);}} catch (Exception e) {LOGGER.error(e.getMessage(), e);throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);}
}

而在此方法中进一步分析，可看出表头数据及样式操作的方法为createHeaderAndTitle，跳转后可确定操作表头方法为createHeaderRow，具体代码如下：

private int createHeaderRow(ExportParams title, Sheet sheet, Workbook workbook, int index,List<ExcelExportEntity> excelParams, int cellIndex) {Row row  = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);int rows = getRowNums(excelParams, true);row.setHeight(title.getHeaderHeight());Row listRow = null;if (rows >= 2) {listRow = sheet.getRow(index + 1);if (listRow == null) {listRow = sheet.createRow(index + 1);listRow.setHeight(title.getHeaderHeight());}}int       groupCellLength = 0;CellStyle titleStyle      = getExcelExportStyler().getTitleStyle(title.getColor());for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {ExcelExportEntity entity = excelParams.get(i);// 加入换了groupName或者结束就，就把之前的那个换行if (StringUtils.isBlank(entity.getGroupName()) || i == 0 || !entity.getGroupName().equals(excelParams.get(i - 1).getGroupName())) {if (groupCellLength > 1) {sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex - groupCellLength, cellIndex - 1));}groupCellLength = 0;}if (StringUtils.isNotBlank(entity.getGroupName())) {createStringCell(row, cellIndex, entity.getGroupName(), titleStyle, entity);createStringCell(listRow, cellIndex, entity.getName(), titleStyle, entity);groupCellLength++;} else if (StringUtils.isNotBlank(entity.getName())) {createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);}if (entity.getList() != null) {// 保持原来的int tempCellIndex = cellIndex;cellIndex = createHeaderRow(title, sheet, workbook, rows == 1 ? index : index + 1, entity.getList(), cellIndex);List<ExcelExportEntity> sTitel = entity.getList();if (StringUtils.isNotBlank(entity.getName()) && sTitel.size() > 1) {PoiMergeCellUtil.addMergedRegion(sheet, index, index, tempCellIndex, tempCellIndex + getFieldLength(sTitel));}/*for (int j = 0, size = sTitel.size(); j < size; j++) {createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(),titleStyle, entity);cellIndex++;}*/cellIndex--;} else if (rows > 1 && StringUtils.isBlank(entity.getGroupName())) {createStringCell(listRow, cellIndex, "", titleStyle, entity);PoiMergeCellUtil.addMergedRegion(sheet, index, index + rows - 1, cellIndex, cellIndex);}cellIndex++;}if (groupCellLength > 1) {PoiMergeCellUtil.addMergedRegion(sheet, index, index, cellIndex - groupCellLength, cellIndex - 1);}return cellIndex;}

分析源码可看出，表头的样式主要是根据excelParams参数中存储的样式来设定，而此参数对应的类型（ExcelExportEntity）中，样式字段只有宽度和高度，而这就是EasyPoi不支持自定义表头样式的原因。

通过上述分析，我们可知，若要支持自定义表头样式，我们首先考虑在createHeaderRow方法的中增加表头样式的参数（List<ExcelExportExtendEntity> extendList），而此方法为private方法，所以只能在继承ExcelExportService类的自定义类中重载此方法。继而，需要依次重载createHeaderAndTitle、insertDataToSheet、createSheetForMap。而之所以要依次重载这些方法，主要原因在于，表头自定义样式的获取最好和本身excelParams参数内容的获取放在一起，防止重复调用。而excelParams参数内容是通过getAllExcelField方法来获取，源码如下：

public void getAllExcelField(String[] exclusions, String targetId, Field[] fields,List<ExcelExportEntity> excelParams, Class<?> pojoClass,List<Method> getMethods, ExcelEntity excelGroup) throws Exception {List<String>      exclusionsList = exclusions != null ? Arrays.asList(exclusions) : null;ExcelExportEntity excelEntity;// 遍历整个filedfor (int i = 0; i < fields.length; i++) {Field field = fields[i];// 先判断是不是collection,在判断是不是java自带对象,之后就是我们自己的对象了if (PoiPublicUtil.isNotUserExcelUserThis(exclusionsList, field, targetId)) {continue;}// 首先判断Excel 可能一下特殊数据用户回自定义处理if (field.getAnnotation(Excel.class) != null) {Excel  excel = field.getAnnotation(Excel.class);String name  = PoiPublicUtil.getValueByTargetId(excel.name(), targetId, null);if (StringUtils.isNotBlank(name)) {excelParams.add(createExcelExportEntity(field, targetId, pojoClass, getMethods, excelGroup));}} else if (PoiPublicUtil.isCollection(field.getType())) {ExcelCollection         excel = field.getAnnotation(ExcelCollection.class);ParameterizedType       pt    = (ParameterizedType) field.getGenericType();Class<?>                clz   = (Class<?>) pt.getActualTypeArguments()[0];List<ExcelExportEntity> list  = new ArrayList<ExcelExportEntity>();getAllExcelField(exclusions,StringUtils.isNotEmpty(excel.id()) ? excel.id() : targetId,PoiPublicUtil.getClassFields(clz), list, clz, null, null);excelEntity = new ExcelExportEntity();excelEntity.setName(PoiPublicUtil.getValueByTargetId(excel.name(), targetId, null));if (i18nHandler != null) {excelEntity.setName(i18nHandler.getLocaleName(excelEntity.getName()));}excelEntity.setOrderNum(Integer.valueOf(PoiPublicUtil.getValueByTargetId(excel.orderNum(), targetId, "0")));excelEntity.setMethod(PoiReflectorUtil.fromCache(pojoClass).getGetMethod(field.getName()));excelEntity.setList(list);excelParams.add(excelEntity);} else {List<Method> newMethods = new ArrayList<Method>();if (getMethods != null) {newMethods.addAll(getMethods);}newMethods.add(PoiReflectorUtil.fromCache(pojoClass).getGetMethod(field.getName()));ExcelEntity excel = field.getAnnotation(ExcelEntity.class);if (excel.show() && StringUtils.isEmpty(excel.name())) {throw new ExcelExportException("if use ExcelEntity ,name mus has value ,data: " + ReflectionToStringBuilder.toString(excel), ExcelExportEnum.PARAMETER_ERROR);}getAllExcelField(exclusions,StringUtils.isNotEmpty(excel.id()) ? excel.id() : targetId,PoiPublicUtil.getClassFields(field.getType()), excelParams, field.getType(),newMethods, excel.show() ? excel : null);}}
}

所以，也需要对 getAllExcelField方法进行重载。

二、自定义导出类CustomizeExportService代码

经过上述源码分析后，我们就可以通过继承ExcelExportService类并对其部分方法进行重写和重载，从而完成自定义样式开发。相关代码如下

1、CustomizeExportService类

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;
import cn.afterturn.easypoi.util.PoiExcelGraphDataUtil;
import cn.afterturn.easypoi.util.PoiMergeCellUtil;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.afterturn.easypoi.util.PoiReflectorUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.dromara.common.excel.annotation.HeadFontStyle;
import org.dromara.common.excel.annotation.HeadStyle;
import org.dromara.common.excel.domain.ExcelExportExtendEntity;import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;public class CustomizeExportService extends ExcelExportService {/*** 最大行数,超过自动多Sheet*/private static int MAX_NUM = 60000;/*** 获取需要导出的全部字段** @param targetId 目标ID*/public void getAllExcelField(ExportParams entity, String targetId, Field[] fields,List<ExcelExportEntity> excelParams, List<ExcelExportExtendEntity> extendParamsList, Class<?> pojoClass,List<Method> getMethods, ExcelEntity excelGroup, Workbook workbook) throws Exception {List<String> exclusionsList = entity.getExclusions() != null ? Arrays.asList(entity.getExclusions()) : null;// 遍历整个filedfor (int i = 0; i < fields.length; i++) {Field field = fields[i];// 先判断是不是collection,在判断是不是java自带对象,之后就是我们自己的对象了if (PoiPublicUtil.isNotUserExcelUserThis(exclusionsList, field, targetId)) {continue;}// 首先判断Excel 可能一下特殊数据用户回自定义处理if (field.getAnnotation(Excel.class) != null) {Excel excel = field.getAnnotation(Excel.class);String name = PoiPublicUtil.getValueByTargetId(excel.name(), targetId, null);if (StringUtils.isNotBlank(name)) {excelParams.add(createExcelExportEntity(field, targetId, pojoClass, getMethods, excelGroup));extendParamsList.add(createExcelExportExtendEntity(entity, field, pojoClass, workbook));}} else if (PoiPublicUtil.isCollection(field.getType())) {ExcelCollection excel = field.getAnnotation(ExcelCollection.class);ParameterizedType pt = (ParameterizedType) field.getGenericType();Class<?> clz = (Class<?>) pt.getActualTypeArguments()[0];List<ExcelExportEntity> list = new ArrayList<ExcelExportEntity>();List<ExcelExportExtendEntity> extendList = new ArrayList<>();getAllExcelField(entity,StringUtils.isNotEmpty(excel.id()) ? excel.id() : targetId,PoiPublicUtil.getClassFields(clz), list, extendList, clz, null, null, workbook);//ExcelExportEntityExcelExportEntity excelEntity = new ExcelExportEntity();excelEntity.setName(PoiPublicUtil.getValueByTargetId(excel.name(), targetId, null));if (i18nHandler != null) {excelEntity.setName(i18nHandler.getLocaleName(excelEntity.getName()));}excelEntity.setOrderNum(Integer.valueOf(PoiPublicUtil.getValueByTargetId(excel.orderNum(), targetId, "0")));excelEntity.setMethod(PoiReflectorUtil.fromCache(pojoClass).getGetMethod(field.getName()));excelEntity.setList(list);excelParams.add(excelEntity);//ExcelExportExtendEntityExcelExportExtendEntity extendEntity = createExcelExportExtendEntity(entity, field, pojoClass, workbook);extendEntity.setExtendList(extendList);extendParamsList.add(extendEntity);} else {List<Method> newMethods = new ArrayList<Method>();if (getMethods != null) {newMethods.addAll(getMethods);}newMethods.add(PoiReflectorUtil.fromCache(pojoClass).getGetMethod(field.getName()));ExcelEntity excel = field.getAnnotation(ExcelEntity.class);if (excel.show() && StringUtils.isEmpty(excel.name())) {throw new ExcelExportException("if use ExcelEntity ,name mus has value ,data: " + ReflectionToStringBuilder.toString(excel), ExcelExportEnum.PARAMETER_ERROR);}getAllExcelField(entity,StringUtils.isNotEmpty(excel.id()) ? excel.id() : targetId,PoiPublicUtil.getClassFields(field.getType()), excelParams, extendParamsList, field.getType(),newMethods, excel.show() ? excel : null, workbook);}}}protected void insertDataToSheet(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList, List<ExcelExportExtendEntity> extendList, Collection<?> dataSet, Sheet sheet) {try {dataHandler = entity.getDataHandler();if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) {needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());}dictHandler = entity.getDictHandler();commentHandler = entity.getCommentHandler();Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();if (entity.isAddIndex()) {excelParams.add(indexExcelEntity(entity));}excelParams.addAll(entityList);sortAllParams(excelParams);int index = entity.isCreateHeadRows()? createHeaderAndTitle(entity, sheet, workbook, excelParams, extendList) : 0;int titleHeight = index;setCellWith(excelParams, sheet);setColumnHidden(excelParams, sheet);short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams);setCurrentIndex(1);createAddressList(sheet, index, excelParams, 0);Iterator<?> its = dataSet.iterator();List<Object> tempList = new ArrayList<Object>();while (its.hasNext()) {Object t = its.next();index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];tempList.add(t);if (index >= MAX_NUM) {break;}}if (entity.getFreezeCol() != 0) {sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);}mergeCells(sheet, excelParams, titleHeight);its = dataSet.iterator();for (int i = 0, le = tempList.size(); i < le; i++) {its.next();its.remove();}if (LOGGER.isDebugEnabled()) {LOGGER.debug("List data more than max ,data size is {}",dataSet.size());}// 发现还有剩余list 继续循环创建Sheetif (dataSet.size() > 0) {createSheetForMap(workbook, entity, entityList, dataSet);} else {// 创建合计信息addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);}} catch (Exception e) {LOGGER.error(e.getMessage(), e);throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);}}@Overridepublic void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel export start ,class is {}", pojoClass);LOGGER.debug("Excel version is {}",entity.getType().equals(ExcelType.HSSF) ? "03" : "07");}if (workbook == null || entity == null || pojoClass == null || dataSet == null) {throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);}try {// 创建表格样式setExcelExportStyler((IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook));List<ExcelExportEntity> excelParams = new ArrayList<>();List<ExcelExportExtendEntity> extendList = new ArrayList<>();i18nHandler = entity.getI18nHandler();// 得到所有字段Field[] fields = PoiPublicUtil.getClassFields(pojoClass);ExcelTarget excelTarget = pojoClass.getAnnotation(ExcelTarget.class);String targetId = excelTarget == null ? null : excelTarget.value();getAllExcelField(entity, targetId, fields, excelParams, extendList, pojoClass,null, null, workbook);//获取所有参数后,后面的逻辑判断就一致了createSheetForMap(workbook, entity, excelParams, extendList, dataSet);} catch (Exception e) {LOGGER.error(e.getMessage(), e);throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e);}}public void createSheetForMap(Workbook workbook, ExportParams entity,List<ExcelExportEntity> entityList, List<ExcelExportExtendEntity> extendList, Collection<?> dataSet) {if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel version is {}",entity.getType().equals(ExcelType.HSSF) ? "03" : "07");}if (workbook == null || entity == null || entityList == null || dataSet == null) {throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);}super.type = entity.getType();if (type.equals(ExcelType.XSSF)) {MAX_NUM = 1000000;}if (entity.getMaxNum() > 0) {MAX_NUM = entity.getMaxNum();}Sheet sheet = null;try {sheet = workbook.createSheet(entity.getSheetName());} catch (Exception e) {// 重复遍历,出现了重名现象,创建非指定的名称Sheetsheet = workbook.createSheet();}if (dataSet.getClass().getClass().getName().contains("Unmodifiable")) {List dataTemp = new ArrayList<>();dataTemp.addAll(dataSet);dataSet = dataTemp;}insertDataToSheet(workbook, entity, entityList, extendList, dataSet, sheet);if (entity.isReadonly()) {sheet.protectSheet(UUID.randomUUID().toString());}sheet.setForceFormulaRecalculation(true);if (isAutoSize(entity, entityList)) {for (int len = Math.max(sheet.getRow(0).getLastCellNum(), sheet.getRow(1).getLastCellNum()), i = 0; i < len; i++) {sheet.autoSizeColumn(i, true);}}}private int createHeaderAndTitle(ExportParams entity, Sheet sheet, Workbook workbook,List<ExcelExportEntity> excelParams, List<ExcelExportExtendEntity> extendList) {int rows = 0, fieldLength = getFieldLength(excelParams);if (entity.getTitle() != null) {rows += createTitle2Row(entity, sheet, workbook, fieldLength);}createHeaderRow(entity, sheet, workbook, rows, excelParams, extendList, 0);rows += getRowNums(excelParams, true);if (entity.isFixedTitle()) {sheet.createFreezePane(0, rows, 0, rows);}return rows;}/*** 创建表头** @param title* @param sheet* @param workbook* @param index* @param excelParams excel字段标准信息* @param extendList  excel字段额外信息* @param cellIndex* @return*/private int createHeaderRow(ExportParams title, Sheet sheet, Workbook workbook, int index,List<ExcelExportEntity> excelParams, List<ExcelExportExtendEntity> extendList, int cellIndex) {Row row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index);int rows = getRowNums(excelParams, true);row.setHeight(title.getHeaderHeight());Row listRow = null;if (rows >= 2) {listRow = sheet.getRow(index + 1);if (listRow == null) {listRow = sheet.createRow(index + 1);listRow.setHeight(title.getHeaderHeight());}}int groupCellLength = 0;
//        CellStyle titleStyle = getExcelExportStyler().getTitleStyle(title.getColor());for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {ExcelExportEntity entity = excelParams.get(i);ExcelExportExtendEntity extendEntity = extendList.get(i);CellStyle headerCellStyle = extendEntity.getHeaderCellStyle();// 加入换了groupName或者结束就，就把之前的那个换行if (StringUtils.isBlank(entity.getGroupName()) || i == 0 || !entity.getGroupName().equals(excelParams.get(i - 1).getGroupName())) {if (groupCellLength > 1) {sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex - groupCellLength, cellIndex - 1));}groupCellLength = 0;}if (StringUtils.isNotBlank(entity.getGroupName())) {createStringCell(row, cellIndex, entity.getGroupName(), headerCellStyle, entity);createStringCell(listRow, cellIndex, entity.getName(), headerCellStyle, entity);groupCellLength++;} else if (StringUtils.isNotBlank(entity.getName())) {createStringCell(row, cellIndex, entity.getName(), headerCellStyle, entity);}if (entity.getList() != null) {// 保持原来的int tempCellIndex = cellIndex;cellIndex = createHeaderRow(title, sheet, workbook, rows == 1 ? index : index + 1, entity.getList(), extendEntity.getExtendList(), cellIndex);List<ExcelExportEntity> sTitel = entity.getList();if (StringUtils.isNotBlank(entity.getName()) && sTitel.size() > 1) {PoiMergeCellUtil.addMergedRegion(sheet, index, index, tempCellIndex, tempCellIndex + getFieldLength(sTitel));}/*for (int j = 0, size = sTitel.size(); j < size; j++) {createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(),titleStyle, entity);cellIndex++;}*/cellIndex--;} else if (rows > 1 && StringUtils.isBlank(entity.getGroupName())) {createStringCell(listRow, cellIndex, "", headerCellStyle, entity);PoiMergeCellUtil.addMergedRegion(sheet, index, index + rows - 1, cellIndex, cellIndex);}cellIndex++;}if (groupCellLength > 1) {PoiMergeCellUtil.addMergedRegion(sheet, index, index, cellIndex - groupCellLength, cellIndex - 1);}return cellIndex;}private int createAddressList(Sheet sheet, int index, List<ExcelExportEntity> excelParams, int cellIndex) {for (int i = 0; i < excelParams.size(); i++) {if (excelParams.get(i).isAddressList()) {ExcelExportEntity entity = excelParams.get(i);CellRangeAddressList regions = new CellRangeAddressList(index,this.type.equals(ExcelType.XSSF) ? 1000000 : 65535, cellIndex, cellIndex);DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(sheet.getDataValidationHelper().createExplicitListConstraint(getAddressListValues(entity)), regions);// 处理Excel兼容性问题if (dataValidation instanceof XSSFDataValidation) {dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);} else {dataValidation.setSuppressDropDownArrow(false);}sheet.addValidationData(dataValidation);}if (excelParams.get(i).getList() != null) {cellIndex = createAddressList(sheet, index, excelParams.get(i).getList(), cellIndex);} else {cellIndex++;}}return cellIndex;}private String[] getAddressListValues(ExcelExportEntity entity) {if (StringUtils.isNotEmpty(entity.getDict())) {String[] arr = new String[this.dictHandler.getList(entity.getDict()).size()];for (int i = 0; i < arr.length; i++) {arr[i] = this.dictHandler.getList(entity.getDict()).get(i).get("dictValue").toString();}return arr;} else if (entity.getReplace() != null && entity.getReplace().length > 0) {String[] arr = new String[entity.getReplace().length];for (int i = 0; i < arr.length; i++) {arr[i] = entity.getReplace()[i].split("_")[0];}return arr;}throw new ExcelExportException(entity.getName() + "没有可以创建下来的数据,请addressList不要设置为true");}private ExcelExportExtendEntity createExcelExportExtendEntity(ExportParams exportParams, Field field, Class<?> pojoClass, Workbook workbook) {ExcelExportExtendEntity entity = new ExcelExportExtendEntity();entity.setFieldName(field.getName());CellStyle headCellStyle = getExcelExportStyler().getTitleStyle(exportParams.getColor());setHeadStyle(headCellStyle, field, pojoClass, workbook);//设置表头样式setHeadFont(headCellStyle, field, pojoClass, workbook);//设置表头字体entity.setHeaderCellStyle(headCellStyle);return entity;}/*** 设置表头字体** @param headCellStyle* @param field*/private void setHeadFont(CellStyle headCellStyle, Field field, Class<?> pojoClass, Workbook workbook) {HeadFontStyle fontStyle = field.getAnnotation(HeadFontStyle.class);if (null == fontStyle)fontStyle = pojoClass.getAnnotation(HeadFontStyle.class);if (null != fontStyle) {Font font = workbook.createFont();font.setBold(fontStyle.bold());if (fontStyle.charset() != -1)font.setCharSet(fontStyle.charset());if (fontStyle.color() != -1)font.setColor(fontStyle.color());if (fontStyle.fontHeightInPoints() != -1)font.setFontHeightInPoints(fontStyle.fontHeightInPoints());if (StringUtils.isNotBlank(fontStyle.fontName()))font.setFontName(fontStyle.fontName());font.setItalic(fontStyle.italic());font.setStrikeout(fontStyle.strikeout());if (fontStyle.typeOffset() != -1)font.setTypeOffset(fontStyle.typeOffset());if (fontStyle.underline() != -1)font.setUnderline(fontStyle.underline());headCellStyle.setFont(font);}}/*** 设置表头样式** @param headCellStyle* @param field*/private void setHeadStyle(CellStyle headCellStyle, Field field, Class<?> pojoClass, Workbook workbook) {HeadStyle headStyle = field.getAnnotation(HeadStyle.class);if (null == headStyle)headStyle = pojoClass.getAnnotation(HeadStyle.class);if (null != headStyle) {headCellStyle.setFillPattern(headStyle.fillPatternType());int[] colorBackArr = headStyle.fillBackgroundColorRGB();if (colorBackArr.length == 3) {XSSFColor color = new XSSFColor();color.setRGB(intToByteArray(getIntFromColor(colorBackArr[0], colorBackArr[1], colorBackArr[2])));headCellStyle.setFillBackgroundColor(color);}int[] colorForeArr = headStyle.fillForegroundColorRGB();if (colorForeArr.length == 3) {XSSFColor color = new XSSFColor();color.setRGB(intToByteArray(getIntFromColor(colorForeArr[0], colorForeArr[1], colorForeArr[2])));headCellStyle.setFillForegroundColor(color);}headCellStyle.setBorderLeft(headStyle.borderLeft());headCellStyle.setBorderRight(headStyle.borderRight());headCellStyle.setBorderTop(headStyle.borderTop());headCellStyle.setBorderBottom(headStyle.borderBottom());if (headStyle.leftBorderColor() != -1)headCellStyle.setLeftBorderColor(headStyle.leftBorderColor());if (headStyle.rightBorderColor() != -1)headCellStyle.setRightBorderColor(headStyle.rightBorderColor());if (headStyle.topBorderColor() != -1)headCellStyle.setTopBorderColor(headStyle.topBorderColor());if (headStyle.bottomBorderColor() != -1)headCellStyle.setBottomBorderColor(headStyle.bottomBorderColor());if (headStyle.fillBackgroundColor() != -1) {headCellStyle.setFillBackgroundColor(headStyle.fillBackgroundColor());}if (headStyle.fillForegroundColor() != -1) {headCellStyle.setFillForegroundColor(headStyle.fillForegroundColor());}}}private ExcelExportEntity createExcelExportEntity(Field field, String targetId,Class<?> pojoClass,List<Method> getMethods, ExcelEntity excelGroup) throws Exception {Excel excel = field.getAnnotation(Excel.class);ExcelExportEntity excelEntity = new ExcelExportEntity();excelEntity.setType(excel.type());getExcelField(targetId, field, excelEntity, excel, pojoClass, excelGroup);if (getMethods != null) {List<Method> newMethods = new ArrayList<Method>();newMethods.addAll(getMethods);newMethods.add(excelEntity.getMethod());excelEntity.setMethods(newMethods);}return excelEntity;}/*** 注解到导出对象的转换*/private void getExcelField(String targetId, Field field, ExcelExportEntity excelEntity,Excel excel, Class<?> pojoClass, ExcelEntity excelGroup) throws Exception {excelEntity.setName(PoiPublicUtil.getValueByTargetId(excel.name(), targetId, null));excelEntity.setKey(field.getName());excelEntity.setWidth(excel.width());excelEntity.setHeight(excel.height());excelEntity.setNeedMerge(excel.needMerge());excelEntity.setMergeVertical(excel.mergeVertical());excelEntity.setMergeRely(excel.mergeRely());excelEntity.setReplace(excel.replace());excelEntity.setOrderNum(Integer.valueOf(PoiPublicUtil.getValueByTargetId(excel.orderNum(), targetId, "0")));excelEntity.setWrap(excel.isWrap());excelEntity.setExportImageType(excel.imageType());excelEntity.setSuffix(excel.suffix());excelEntity.setDatabaseFormat(excel.databaseFormat());excelEntity.setFormat(StringUtils.isNotEmpty(excel.exportFormat()) ? excel.exportFormat() : excel.format());excelEntity.setStatistics(excel.isStatistics());excelEntity.setHyperlink(excel.isHyperlink());excelEntity.setMethod(PoiReflectorUtil.fromCache(pojoClass).getGetMethod(field.getName()));excelEntity.setNumFormat(excel.numFormat());excelEntity.setColumnHidden(excel.isColumnHidden());excelEntity.setDict(excel.dict());excelEntity.setEnumExportField(excel.enumExportField());excelEntity.setTimezone(excel.timezone());excelEntity.setAddressList(excel.addressList());excelEntity.setDesensitizationRule(excel.desensitizationRule());if (excelGroup != null) {excelEntity.setGroupName(PoiPublicUtil.getValueByTargetId(excelGroup.name(), targetId, null));} else {excelEntity.setGroupName(excel.groupName());}if (i18nHandler != null) {excelEntity.setName(i18nHandler.getLocaleName(excelEntity.getName()));excelEntity.setGroupName(i18nHandler.getLocaleName(excelEntity.getGroupName()));}}private boolean isAutoSize(ExportParams entity, List<ExcelExportEntity> entityList) {if (entity.isAutoSize()) {return true;}AtomicBoolean autoSize = new AtomicBoolean(true);entityList.forEach(e -> {if (e.getWidth() != 10) {autoSize.set(false);return;}if (CollectionUtils.isNotEmpty(e.getList()) && !isAutoSize(entity, e.getList())) {autoSize.set(false);return;}});return autoSize.get();}/*** rgb转int*/private static int getIntFromColor(int Red, int Green, int Blue) {Red = (Red << 16) & 0x00FF0000;Green = (Green << 8) & 0x0000FF00;Blue = Blue & 0x000000FF;return 0xFF000000 | Red | Green | Blue;}/*** int转byte[]*/public static byte[] intToByteArray(int i) {byte[] result = new byte[4];result[0] = (byte) ((i >> 24) & 0xFF);result[1] = (byte) ((i >> 16) & 0xFF);result[2] = (byte) ((i >> 8) & 0xFF);result[3] = (byte) (i & 0xFF);return result;}}

2、自定义样式的存储类ExcelExportExtendEntity：

import lombok.Data;
import org.apache.poi.ss.usermodel.CellStyle;import java.util.List;
@Data
public class ExcelExportExtendEntity {/*** 字段名称*/private String fieldName;/*** 表头样式*/private CellStyle headerCellStyle;/*** 子表头样式*/private List<ExcelExportExtendEntity> extendList;
}

3、自定义注解HeadFontStyle、HeadStyle

import org.apache.poi.common.usermodel.fonts.FontCharset;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;import java.lang.annotation.*;@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface HeadFontStyle {/*** The name for the font (i.e. Arial)*/String fontName() default "";/*** Height in the familiar unit of measure - points*/short fontHeightInPoints() default -1;/*** Whether to use italics or not*/boolean italic() default false;/*** Whether to use a strikeout horizontal line through the text or not*/boolean strikeout() default false;/*** The color for the font** @see Font#COLOR_NORMAL* @see Font#COLOR_RED* @see HSSFPalette#getColor(short)* @see IndexedColors*/short color() default -1;/*** Set normal, super or subscript.** @see Font#SS_NONE* @see Font#SS_SUPER* @see Font#SS_SUB*/short typeOffset() default -1;/*** set type of text underlining to use** @see Font#U_NONE* @see Font#U_SINGLE* @see Font#U_DOUBLE* @see Font#U_SINGLE_ACCOUNTING* @see Font#U_DOUBLE_ACCOUNTING*/byte underline() default -1;/*** Set character-set to use.** @see FontCharset* @see Font#ANSI_CHARSET* @see Font#DEFAULT_CHARSET* @see Font#SYMBOL_CHARSET*/int charset() default -1;/*** Bold*/boolean bold() default false;
}

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;import java.lang.annotation.*;@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface HeadStyle {/*** Set the type of border to use for the left border of the cell*/BorderStyle borderLeft() default BorderStyle.THIN;/*** Set the type of border to use for the right border of the cell*/BorderStyle borderRight() default BorderStyle.THIN;/*** Set the type of border to use for the top border of the cell*/BorderStyle borderTop() default BorderStyle.THIN;/*** Set the type of border to use for the bottom border of the cell*/BorderStyle borderBottom() default BorderStyle.THIN;/*** Set the color to use for the left border** @see IndexedColors*/short leftBorderColor() default 54;/*** Set the color to use for the right border** @see IndexedColors*/short rightBorderColor() default 54;/*** Set the color to use for the top border** @see IndexedColors*/short topBorderColor() default 54;/*** Set the color to use for the bottom border** @see IndexedColors*/short bottomBorderColor() default 54;/*** Setting to one fills the cell with the foreground color... No idea about other values** @see FillPatternType#SOLID_FOREGROUND*/FillPatternType fillPatternType() default FillPatternType.SOLID_FOREGROUND;/*** Set the background fill color.** @see IndexedColors*/short fillBackgroundColor() default -1;/*** 按RGB顺序** @return rgb颜色*/int[] fillBackgroundColorRGB() default {};/*** Set the foreground fill color <i>Note: Ensure Foreground color is set prior to background color.</i>** @see IndexedColors*/short fillForegroundColor() default -1;/*** 按RGB顺序** @return rgb颜色*/int[] fillForegroundColorRGB() default {};
}

4、工具类EasyPoiExcelUtil

此类是调用自定义导出类来完成excel导出功能。

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.hutool.core.util.IdUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dromara.common.core.utils.file.FileUtils;
import org.dromara.common.excel.service.easypoi.CustomizeExportService;import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.util.List;public class EasyPoiExcelUtil {public static void exportExcel(String title, String sheetName, List<?> list, Class<?> pojoClass, HttpServletResponse response) {try {resetResponse(sheetName, response);ExportParams exportParams = new ExportParams(title, sheetName);Workbook sheets = getWorkbook(exportParams.getType(), list.size());new CustomizeExportService().createSheet(sheets, exportParams, pojoClass, list);
//            Workbook sheets = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);sheets.write(response.getOutputStream());sheets.close();} catch (Exception e) {throw new RuntimeException("导出Excel异常", e);}}/*** 重置响应体*/private static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {String filename = encodingFilename(sheetName);FileUtils.setAttachmentResponseHeader(response, filename);response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");}/*** 编码文件名*/public static String encodingFilename(String filename) {return IdUtil.fastSimpleUUID() + "_" + filename + ".xlsx";}private static Workbook getWorkbook(ExcelType type, int size) {if (ExcelType.HSSF.equals(type)) {return new HSSFWorkbook();} else {return new XSSFWorkbook();}}
}