百度关键词搜索排名查询关键词的分类和优化

 要实现上述动态头，按每日统计，每月统计，每年统计。而时间是一直变化，所以我们需要表头也一直动态生成。

首先，我们需要定义所需要实体类

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;public String getSaleName() {return saleName;}public void setSaleName(String saleName) {this.saleName = saleName;}public Integer getOrderNum() {return orderNum;}public void setOrderNum(Integer orderNum) {this.orderNum = orderNum;}public BigDecimal getTotalAmount() {return totalAmount;}public void setTotalAmount(BigDecimal totalAmount) {this.totalAmount = totalAmount;}public BigDecimal getAvaAmount() {return avaAmount;}public void setAvaAmount(BigDecimal avaAmount) {this.avaAmount = avaAmount;}public Integer getOrderNum1() {return orderNum1;}public void setOrderNum1(Integer orderNum1) {this.orderNum1 = orderNum1;}public BigDecimal getTotalAmount1() {return totalAmount1;}public void setTotalAmount1(BigDecimal totalAmount1) {this.totalAmount1 = totalAmount1;}public BigDecimal getAvaAmount1() {return avaAmount1;}public void setAvaAmount1(BigDecimal avaAmount1) {this.avaAmount1 = avaAmount1;}public Integer getOrderNum2() {return orderNum2;}public void setOrderNum2(Integer orderNum2) {this.orderNum2 = orderNum2;}public BigDecimal getTotalAmount2() {return totalAmount2;}public void setTotalAmount2(BigDecimal totalAmount2) {this.totalAmount2 = totalAmount2;}public BigDecimal getAvaAmount2() {return avaAmount2;}public void setAvaAmount2(BigDecimal avaAmount2) {this.avaAmount2 = 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;}@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)) {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));}}}}}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}

headTitle格式为json，目的是为了方便有多个变化的表头一次性传递。

创建 EasyExcelSheetWriteHandler并继承SheetWriteHandler（这是为了创建标题）

public class EasyExcelSheetWriteHandler implements SheetWriteHandler {private String title;@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}public EasyExcelSheetWriteHandler() {super();}public EasyExcelSheetWriteHandler(String title) {this.title = title;}@Overridepublic 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);ExcelUtil<Report> util = new ExcelUtil<>(Report.class);util.getLocalFile(filePath);util.exportEasyExcelFile(filePath, saleList, "统计", writeHandler, easyExcelTitleHandler);注：filePath为文件的全路径（文件存放路径+文件名）ExcelUtil为easyExcel的方法类saleList为数据

exportEasyExcelFile方法

    /*** 对list数据源将其里面的数据导入到excel表单（EasyExcel）保存到本地* @param list 导出数据集合* @param sheetName 工作表的名称* @return 结果*/public void exportEasyExcelFile(String fileName, List<T> list, String sheetName, EasyExcelSheetWriteHandler writeHandler, EasyExcelCellWriteHandler easyExcelTitleHandler) throws Exception{EasyExcel.write(fileName, clazz).sheet(sheetName).relativeHeadRowIndex(1).registerWriteHandler(writeHandler).registerWriteHandler(easyExcelTitleHandler).doWrite(list);}

参考文章：https://blog.csdn.net/Jul_C18672868641/article/details/129022583    https://blog.csdn.net/m0_47185078/article/details/125444869