基于Java的报表模块，支持自定义SQL和报表模板 -2025

package neatlogic.module.report.service; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import neatlogic.framework.common.dto.BasePageVo; import neatlogic.framework.dao.plugin.PageRowBounds; import neatlogic.framework.dto.RestVo; import neatlogic.framework.report.exception.ReportParamNameRepeatsException; import neatlogic.framework.report.exception.TableNotFoundInReportException; import neatlogic.framework.sqlrunner.SqlInfo; import neatlogic.framework.sqlrunner.SqlRunner; import neatlogic.framework.util.RestUtil; import neatlogic.framework.util.excel.ExcelBuilder; import neatlogic.framework.util.excel.SheetBuilder; import neatlogic.module.report.config.ReportConfig; import neatlogic.module.report.dao.mapper.ReportInstanceMapper; import neatlogic.module.report.dao.mapper.ReportMapper; import neatlogic.module.report.dto.*; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.collections4.MapUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.jsoup.Jsoup; import org.jsoup.nodes.Document; import org.jsoup.nodes.Element; import org.jsoup.select.Elements; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import javax.annotation.Resource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; import java.util.*; import java.util.stream.Collectors; @Service public class ReportServiceImpl implements ReportService { Logger logger = LoggerFactory.getLogger(ReportServiceImpl.class); @Resource private ReportMapper reportMapper; @Resource private ReportInstanceMapper reportInstanceMapper; @Resource private DataSource dataSource; private Connection getConnection() throws SQLException { return dataSource.getConnection(); } @Override public ReportVo getReportDetailById(Long reportId) { ReportVo reportVo = reportMapper.getReportById(reportId); if (reportVo != null) { reportVo.setParamList(reportMapper.getReportParamByReportId(reportId)); List<ReportAuthVo> reportAuthList = reportMapper.getReportAuthByReportId(reportVo.getId()); reportVo.setReportAuthList(reportAuthList); } return reportVo; } private Object getRemoteResult(RestVo restVo) { String result = RestUtil.sendPostRequest(restVo); try { return JSON.parse(result); } catch (Exception ex) { return result; } } /** * 查询报表实例的表格显示列配置 * * @param reportInstanceId 报表实例id * @return 结果集 */ @Override public Map<String, List<String>> getShowColumnsMap(Long reportInstanceId) { Map<String, List<String>> showColumnsMap = new HashMap<>(); /* 查询表格显示列配置 */ List<ReportInstanceTableColumnVo> columnList = reportInstanceMapper.getReportInstanceTableColumnList(reportInstanceId); if (CollectionUtils.isNotEmpty(columnList)) { /* 根据tableId分组 */ Map<String, List<ReportInstanceTableColumnVo>> columnMap = columnList.stream().collect(Collectors.groupingBy(ReportInstanceTableColumnVo::getTableId)); /* 根据sort排序并取出字段名，组装成tableId与字段列表的map */ for (Map.Entry<String, List<ReportInstanceTableColumnVo>> entry : columnMap.entrySet()) { List<String> columns = entry.getValue().stream().sorted(Comparator.comparing(ReportInstanceTableColumnVo::getSort)).map(ReportInstanceTableColumnVo::getColumn).collect(Collectors.toList()); showColumnsMap.put(entry.getKey(), columns); } } return showColumnsMap; } private List<Map<String, Object>> selectTableColumns(Map<String, List<String>> showColumnsMap, SelectVo select, List<Map<String, Object>> tmpList) { List<String> showColumnList = showColumnsMap.get(select.getId()); /* 筛选表格显示列 */ for (Map<String, Object> map : tmpList) { map.entrySet().removeIf(stringObjectEntry -> !showColumnList.contains(stringObjectEntry.getKey())); } /* 排序 */ List<Map<String, Object>> sqList = new ArrayList<>(); for (Map<String, Object> map : tmpList) { Map<String, Object> _map = new LinkedHashMap<>(); for (String s : showColumnList) { _map.put(s, map.get(s)); } sqList.add(_map); } return sqList; } private List<Map<String, Object>> selectTableColumns(List<String> showColumnList, List<Map<String, Object>> tmpList) { /* 筛选表格显示列 */ for (Map<String, Object> map : tmpList) { map.entrySet().removeIf(stringObjectEntry -> !showColumnList.contains(stringObjectEntry.getKey())); } /* 排序 */ List<Map<String, Object>> sqList = new ArrayList<>(); for (Map<String, Object> map : tmpList) { Map<String, Object> _map = new LinkedHashMap<>(); for (String s : showColumnList) { _map.put(s, map.get(s)); } sqList.add(_map); } return sqList; } private Map<String, List> wrapResultMapToMap(ResultMapVo resultMapVo, Map<String, Object> result, Map<String, List> returnMap) { StringBuilder key = new StringBuilder(); List<Map<String, Object>> resultList = null; if (resultMapVo.getGroupByList() != null && resultMapVo.getGroupByList().size() > 0) { for (int i = 0; i < resultMapVo.getGroupByList().size(); i++) { key.append(result.get(resultMapVo.getGroupByList().get(i))); if (i < resultMapVo.getGroupByList().size() - 1) { key.append("-"); } } } else { return null; } Map<String, Object> newResult = new HashMap<>(); if (!key.toString().equals("") && returnMap.containsKey(key.toString())) { resultList = returnMap.get(key.toString()); } else { resultList = new ArrayList<>(); returnMap.put(key.toString(), resultList); } // Iterator<Map.Entry<String, Object>> iter = // result.entrySet().iterator(); for (String str : resultMapVo.getPropertyList()) { newResult.put(str, result.get(str)); } resultList.add(newResult); return returnMap; } /** * 判断这一条数据是否已经存在 * * @param resultMapVo 结果数据 * @param resultList 结果数据 * @param result 结果数据 * @param checkMap 中途结果 * @return 结果 */ private Boolean isExists(ResultMapVo resultMapVo, List<Map<String, Object>> resultList, Map<String, Object> result, Map<String, Map<String, Object>> checkMap) { boolean isExists = false; StringBuilder key = new StringBuilder(); if (resultList == null) { resultList = new ArrayList<Map<String, Object>>(); } if (resultMapVo.getGroupByList() != null && resultMapVo.getGroupByList().size() > 0) { for (int i = 0; i < resultMapVo.getGroupByList().size(); i++) { key.append(result.get(resultMapVo.getGroupByList().get(i))); if (i < resultMapVo.getGroupByList().size() - 1) { key.append("-"); } } } else if (resultMapVo.getPropertyList() != null && resultMapVo.getPropertyLi