做业务不花钱的网站有哪些,淘宝搜索关键词排名,中关村在线官网首页,深圳4a广告公司有哪些要实现上述动态头#xff0c;按每日统计#xff0c;每月统计#xff0c;每年统计。而时间是一直变化#xff0c;所以我们需要表头也一直动态生成。
首先#xff0c;我们需要定义所需要实体类
public class CountDayData {ExcelProperty(value 业务员姓名)p… 要实现上述动态头按每日统计每月统计每年统计。而时间是一直变化所以我们需要表头也一直动态生成。
首先我们需要定义所需要实体类
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 ListInteger columnIndex;private JSONObject headTitle;PropertyPlaceholderHelper placeholderHelper new PropertyPlaceholderHelper(${, });public EasyExcelCellWriteHandler(ListInteger columnIndex, Short colorIndex, HashMapInteger, String annotationsMap, HashMapInteger, String[] dropDownMap , String time, String month, String year, JSONObject sheetTitle) {this.columnIndex columnIndex;this.headTitle headTitle;}public EasyExcelCellWriteHandler(ListInteger 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)) {ListString headNameList head.getHeadNameList();if (CollectionUtils.isNotEmpty(headNameList)) {ArrayListProperties 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);ExcelUtilReport 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, ListT 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
