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

动态透视报表 查询接口 Excel导出✅动态行维度产品 / 型号 / 项目 任意组合✅动态列维度月份✅a / f 子表头✅SQL 透视适合 GaussDB✅查询接口 EasyExcel 导出接口✅可复用报表引擎整体架构Controller ↓ ReportService ↓ PivotEngine核心透视引擎 ↓ MyBatis ↓ GaussDB一、数据库示例表sales_planproduct model itemmonthtypevalue示例数据productmodelitemmonthtypevalueAA1P11月a10AA1P12月a20AA1P13月a30AA1P14月f40二、报表请求参数核心publicclassReportRequest{// 行维度privateListStringrowDims;// 列维度privateStringcolDim;// 指标privateStringmeasure;}示例{rowDims:[product,model,item],colDim:month,measure:value}三、透视SQL生成引擎核心类PivotEnginepublicclassPivotEngine{生成透视 SQLpublicstaticStringbuildPivotSql(Stringtable,ListStringrowDims,StringcolDim,Stringmeasure,ListStringcolValues){StringBuildersqlnewStringBuilder();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(inti0;irowDims.size();i){sql.append(rowDims.get(i));if(irowDims.size()-1){sql.append(,);}}returnsql.toString();}生成SQLSELECTproduct,model,item,SUM(CASEWHENmonth1月THENvalueELSE0END)1月,SUM(CASEWHENmonth2月THENvalueELSE0END)2月,SUM(CASEWHENmonth3月THENvalueELSE0END)3月,SUM(value)totalFROMsales_planGROUPBYproduct,model,item四、MyBatisMapperpublicinterfaceReportMapper{查询列维度值Select(SELECT DISTINCT ${col} FROM ${table} ORDER BY ${col})ListStringqueryColValues(Param(table)Stringtable,Param(col)Stringcol);执行透视SQLSelect(${sql})ListMapString,ObjectqueryPivot(Param(sql)Stringsql);五、ServiceServicepublicclassReportService{AutowiredReportMappermapper;核心逻辑publicMapString,Objectreport(ReportRequestreq){// 1 查询月份ListStringcolsmapper.queryColValues(sales_plan,req.getColDim());// 2 构建SQLStringsqlPivotEngine.buildPivotSql(sales_plan,req.getRowDims(),req.getColDim(),req.getMeasure(),cols);// 3 执行ListMapString,Objectdatamapper.queryPivot(sql);MapString,ObjectresultnewHashMap();result.put(cols,cols);result.put(data,data);returnresult;}六、查询接口PostMapping(/report/query)publicMapString,Objectquery(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 动态表头生成表头publicstaticListListStringbuildHead(ListStringrowDims,ListStringmonths,MapString,StringtypeMap){ListListStringheadnewArrayList();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{MapString,Objectresultservice.report(req);ListStringcols(ListString)result.get(cols);ListMapString,Objectdata(ListMapString,Object)result.get(data);ListListStringheadExcelUtil.buildHead(req.getRowDims(),cols,getTypeMap());ListListObjectrowsExcelUtil.buildRows(req.getRowDims(),cols,data);EasyExcel.write(response.getOutputStream()).head(head).sheet(报表).doWrite(rows);}九、最终能力这个报表引擎支持能力支持动态行维度✅动态列维度✅SQL透视✅查询接口✅Excel导出✅EasyExcel动态表头✅百万行导出✅十、最终效果与你图片一样产品 型号 项目 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