动态透视报表 + 查询接口 + Excel导出

动态透视报表 + 查询接口 + Excel导出

• ✅动态行维度（产品 / 型号 / 项目 任意组合）
• ✅动态列维度（月份）
• ✅a / f 子表头
• ✅SQL 透视（适合 GaussDB）
• ✅查询接口 + EasyExcel 导出接口
• ✅可复用报表引擎

整体架构：

Controller ↓ ReportService ↓ PivotEngine（核心透视引擎） ↓ MyBatis ↓ GaussDB

一、数据库示例

表：sales_plan

product model itemmonthtypevalue

示例数据

二、报表请求参数（核心）

publicclassReportRequest{// 行维度privateList<String>rowDims;// 列维度privateStringcolDim;// 指标privateStringmeasure;}

示例：

{"rowDims":["product","model","item"],"colDim":"month","measure":"value"}

三、透视SQL生成引擎

核心类：

PivotEngine

publicclassPivotEngine{

生成透视 SQL：

publicstaticStringbuildPivotSql(Stringtable,List<String>rowDims,StringcolDim,Stringmeasure,List<String>colValues){StringBuildersql=newStringBuilder();sql.append("SELECT ");// 行维度for(Stringr:rowDims){sql.append(r).append(",");}// 透视列for(Stringv:colValues){sql.append("SUM(CASE WHEN ").append(colDim).append("='").append(v).append("' THEN ").append(measure).append(" ELSE 0 END) AS \"").append(v).append("\",");}sql.append("SUM(").append(measure).append(") total ");sql.append("FROM ").append(table);sql.append(" GROUP BY ");for(inti=0;i<rowDims.size();i++){sql.append(rowDims.get(i));if(i<rowDims.size()-1){sql.append(",");}}returnsql.toString();}

生成SQL：

SELECTproduct,model,item,SUM(CASEWHENmonth='1月'THENvalueELSE0END)"1月",SUM(CASEWHENmonth='2月'THENvalueELSE0END)"2月",SUM(CASEWHENmonth='3月'THENvalueELSE0END)"3月",SUM(value)totalFROMsales_planGROUPBYproduct,model,item

四、MyBatis

@MapperpublicinterfaceReportMapper{

查询列维度值：

@Select("SELECT DISTINCT ${col} FROM ${table} ORDER BY ${col}")List<String>queryColValues(@Param("table")Stringtable,@Param("col")Stringcol);

执行透视SQL：

@Select("${sql}")List<Map<String,Object>>queryPivot(@Param("sql")Stringsql);

五、Service

@ServicepublicclassReportService{

@AutowiredReportMappermapper;

核心逻辑：

publicMap<String,Object>report(ReportRequestreq){// 1 查询月份List<String>cols=mapper.queryColValues("sales_plan",req.getColDim());// 2 构建SQLStringsql=PivotEngine.buildPivotSql("sales_plan",req.getRowDims(),req.getColDim(),req.getMeasure(),cols);// 3 执行List<Map<String,Object>>data=mapper.queryPivot(sql);Map<String,Object>result=newHashMap<>();result.put("cols",cols);result.put("data",data);returnresult;}

六、查询接口

@PostMapping("/report/query")publicMap<String,Object>query(@RequestBodyReportRequestreq){returnservice.report(req);}

返回数据：

{"cols":["1月","2月","3月","4月"],"data":[{"product":"A","model":"A1","item":"P1","1月":10,"2月":20,"3月":30,"4月":40,"total":100}]}

七、EasyExcel 动态表头

生成表头：

publicstaticList<List<String>>buildHead(List<String>rowDims,List<String>months,Map<String,String>typeMap){List<List<String>>head=newArrayList<>();for(Stringr:rowDims){head.add(Arrays.asList(r));}for(Stringm:months){head.add(Arrays.asList(m,typeMap.get(m)));}head.add(Arrays.asList("合计"));returnhead;}

八、Excel导出接口

@PostMapping("/report/export")publicvoidexport(@RequestBodyReportRequestreq,HttpServletResponseresponse)throwsException{Map<String,Object>result=service.report(req);List<String>cols=(List<String>)result.get("cols");List<Map<String,Object>>data=(List<Map<String,Object>>)result.get("data");List<List<String>>head=ExcelUtil.buildHead(req.getRowDims(),cols,getTypeMap());List<List<Object>>rows=ExcelUtil.buildRows(req.getRowDims(),cols,data);EasyExcel.write(response.getOutputStream()).head(head).sheet("报表").doWrite(rows);}

九、最终能力

这个报表引擎支持：

十、最终效果（与你图片一样）

产品 型号 项目 1月 2月 3月 4月 5月 合计 a a a f f A A1 P1 10 20 30 40 50 150 B B1 P1 20 40 60 80 100 300