image.png
要实现上述动态头，按每日统计，每月统计，每年统计。而时间是一直变化，所以我们需要表头也一直动态生成。
首先，我们需要定义所需要实体类
@Data
public class CountDayData {
@ExcelProperty(value = "业务员姓名")
private String saleName;
@ExcelProperty({"${day}", "订单总数"})
private Integer orderNum;
@ExcelProperty({"${day}", "销售额"})
private BigDecimal totalAmount;
@ExcelProperty({"${day}", "平均单价"})
private BigDecimal avaAmount;
@ExcelProperty({"${month}", "订单总数"})
private Integer orderNum1;
@ExcelProperty({"${month}", "销售额"})
private BigDecimal totalAmount1;
@ExcelProperty({"${month}", "平均单价"})
private BigDecimal avaAmount1;
@ExcelProperty({"${year}", "订单总数"})
private Integer orderNum2;
@ExcelProperty({"${year}", "销售额"})
private BigDecimal totalAmount2;
@ExcelProperty({"${year}", "平均单价"})
private BigDecimal avaAmount2;
}
创建 EasyExcelCellWriteHandler并继承CellWriteHandler
public class EasyExcelCellWriteHandler implements CellWriteHandler {
/**
错误信息处理时正则表达式的格式
*/
private final String EXCEL_ERROR_REG = "^(.*)(\\(错误:)(.*)(\\))$";
/**
操作列
*/
private final List<Integer> columnIndex;
private JSONObject headTitle;
PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");
public EasyExcelCellWriteHandler(List<Integer> columnIndex, Short colorIndex, HashMap<Integer, String> annotationsMap, HashMap<Integer, String[]> dropDownMap , String time, String month, String year, JSONObject sheetTitle) {
this.columnIndex = columnIndex;
this.headTitle = headTitle;
}
public EasyExcelCellWriteHandler(List<Integer> columnIndex, JSONObject headTitle) {
this.columnIndex = columnIndex;
this.headTitle = headTitle;
}
@Override
public 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)) {
ArrayList<Properties> propertiesList = new ArrayList<>();
for (String key : headTitle.keySet()){
Properties properties = new Properties();
properties.setProperty(key, headTitle.getString(key));
propertiesList.add(properties);
}
for (int i = 0 ; i < headNameList.size() ; i++){
for (Properties properties : propertiesList) {
//表头中如果有${}设置的单元格，则可以自定义赋值。 根据构造方法传入的jsonObject对象
headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));
}
}
}
}
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
设置 Excel 列的宽度
public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
// 单元格文本长度大于100换行
if (columnWidth >= 0) {
if (columnWidth > 100) {
columnWidth = 100;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
/**
* 计算长度
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData<?> cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
// 换行符（数据需要提前解析好）
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ?
cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
设置 EasyExcel 样式
public class EasyExcelUtils {
/**
* 设置excel样式
*/
public static HorizontalCellStyleStrategy getStyleStrategy() {
// 头的策略 样式调整
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 头背景 浅绿
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
// 头字号
headWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
//headWriteCellStyle.setWrapped(true);
// 设置细边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置边框颜色 25灰度
headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容的策略 宋体
WriteCellStyle contentStyle = new WriteCellStyle();
// 设置垂直居中
contentStyle.setWrapped(true);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置 水平居中
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// 内容字号
contentWriteFont.setFontHeightInPoints((short) 11);
// 字体样式
contentWriteFont.setFontName("宋体");
contentStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
}
}
headTitle格式为json，目的是为了方便有多个变化的表头一次性传递。
创建 EasyExcelSheetWriteHandler并继承SheetWriteHandler（这是为了创建标题）
public class EasyExcelSheetWriteHandler implements SheetWriteHandler {
private String title;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
public EasyExcelSheetWriteHandler() {
super();
}
public EasyExcelSheetWriteHandler(String title) {
this.title = title;
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
Row row1 = sheet.createRow(0);
row1.setHeight((short) 800);
Cell cell = row1.createCell(0);
//设置标题
cell.setCellValue(title);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 400);
font.setFontName("宋体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 9));
}
业务层
EasyExcelSheetWriteHandler writeHandler = new EasyExcelSheetWriteHandler(reportTitle);
JSONObject obj = new JSONObject();
obj.put("day", DateUtils.getDate(day));
obj.put("month", DateUtils.getMonth(day));
obj.put("year", DateUtils.getYear(day));
EasyExcelCellWriteHandler easyExcelTitleHandler = new EasyExcelCellWriteHandler(null, obj);
EasyExcel.write(filePath, CountDayData.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("Rebate Program List")
.registerWriteHandler(writeHandler)
.registerWriteHandler(easyExcelTitleHandler)
.doWrite(saleList);
注：filePath为文件的全路径（文件存放路径+文件名）
ExcelUtil为easyExcel的方法类
saleList为数据
