框架学习

根据sql语句导出excel,如果字段没有注释,可以取别名,或者直接显示字段名,意义也清晰

/** * 导出excel 全表导出 * * @return */ public Collection getAllColumnName(String tableName, WhHsData hs) throws Exception { WhHsWheres wheres = new WhHsWheres().SetValue("TABLE_NAME","=",

/**
 * 导出excel 全表导出
 *
 * @return
 */
public Collection<? extends String> getAllColumnName(String tableName, WhHsData hs) throws Exception
{
    WhHsWheres wheres = new WhHsWheres().SetValue("TABLE_NAME","=", tableName, "");
    String sql = "SELECT substring_index(substring_index(COLUMN_COMMENT, \" \", 1), \",\", 1) mCOLUMN_COMMENT from information_schema.`COLUMNS` ";
    List columns = hs.list(sql, wheres, "");
    Collection<String> columnTitle = new ArrayList<>();
    if (columns != null && columns.size() > 0)
    {
        for (int i = 0; i < columns.size(); i++)
        {
            columnTitle.add(((Map<String, Object>) columns.get(i)).get("mCOLUMN_COMMENT").toString());
        }
    }
    return columnTitle;
}
/**
 * 导出excel 多表联合自定义字段查询导出
 * @date 2021-10-28
 * CASE 必须 取别名,作为列名
 *
 * @return
 */
public String excelPageDiyColumn(String sql, WhHsData hs, HttpServletResponse response, WhHsWheres mainWheres) throws Exception
{
    sql = IgnoreCaseReplace(sql, " left join ", " LEFT JOIN ");
    sql = IgnoreCaseReplace(sql, " from ", " FROM ");
    sql = IgnoreCaseReplace(sql, " case ", " CASE ");
    sql = IgnoreCaseReplace(sql, " contract ", " CONTRACT ");
    String tableName = ""; // 主表名称
    String headTitle = ""; // 表格顶部标题
    String fileName = ""; // 导出文件名
    String tablesList1 = ""; // 所有表名
    String columnList1 = ""; // 所有字段名
    Map<String, ArrayList> toQuery = new HashMap<>();  // 初始化需要查询的表名和字段名
    Map<String, String> tableColumn = new HashMap<>(); // 目标表名与对应的字段名 比如 a.corpId对应finance_apply的corpId
    String columnAlias = ""; // 自定义的列名
    WhHsWheres wheres = new WhHsWheres();
    while (sql.indexOf("  ") >= 0) {
        sql = sql.replaceAll("  ", " ");
    }
    while (sql.indexOf(", ") >= 0) {
        sql = sql.replaceAll(", ", ",");
    }
    sql = IgnoreCaseReplace(sql, " as ", " AS ");
    String[] a = sql.split(" FROM ");
    columnList1 = a[0].substring(6).trim(); // 截去select
    String[] bb = columnList1.split(",");
    int ll = bb.length; // 总的字段数目
    ArrayList<String> columnTitle = new ArrayList<>(); // 最终的字段名清单
    if(!StringUtils.isEmpty(a[1])) { // 表名处理
        tablesList1 = a[1].trim();
        String[] b = tablesList1.split(" LEFT JOIN ");
        int l = b.length;
        String[] tableNames = new String[l];
        String[] tableAlias = new String[l];
        // 遍历所有表名 start
        for (int i = 0; i < b.length; i++) {
            String tmp = b[i].trim();
            String[] tmp1 = tmp.split(" ");
            if (tmp1.length > 1) {
                if (tmp.indexOf("AS") > 0) {
                    tableAlias[i] = tmp1[2];
                } else {
                    tableAlias[i] = tmp1[1];
                }
            }
            tableNames[i] = tmp1[0]; // 每个tmp1[0] 预期就是表名字了
        }// 遍历所有表名 end

        // 主表名称即 导出标题
        tableName = tableNames[0];
        wheres.SetValue("TABLE_NAME", "=", tableName, "%S");
        String tableNameSql = "SELECT table_comment from information_schema.TABLES";
        Map<String, Object> cnTable = hs.detail(tableNameSql, wheres);
        String cnTableName = cnTable.get("table_comment").toString();
        headTitle = cnTableName + "明细";
        fileName = cnTableName + "中文.xls";
        // 主表名称即 导出标题 end

        // 组装表头 start
        if (!StringUtils.isEmpty(a[0])) { // 字段名处理
            // 遍历所有查询字段 start
            for (int i = 0; i < ll; i++)
            {
                String tmp = bb[i].trim();
                String[] tmp1 = tmp.split(" ");
                if (tmp1.length > 1) {
                    columnAlias = tmp1[tmp1.length - 1]; // CASE 必须 取别名,作为列名
                    columnTitle.add(columnAlias); // a.column alias 或者 a.column as alias
                }
                else
                {
                    // 从表别名到真实表名 检索比对
                    for (int j = 0; j < l; j++) // l为表的个数
                    {
                        String tableAliasPointColumnName = tmp1[0]; // 每个tmp1[0] 预期就是字段名字了 a.column
                        String currentTableAlias = tableAlias[j];
                        String currentTableName = tableNames[j];
                        String[] tmp2 = tableAliasPointColumnName.split("\\.");
                        if (tmp2.length > 1) {
                            String currentColumnName = tmp2[1];
                            if (tmp2[0].equals(currentTableAlias)) {
                                if(currentColumnName.equals("*")) // a.* 查询全表
                                {
                                    columnTitle.addAll(getAllColumnName(currentTableName, hs));
                                }
                                else 
               {
                                    String columnSql = "SELECT substring_index(substring_index(COLUMN_COMMENT, \" \", 1), \",\", 1) mCOLUMN_COMMENT from information_schema.`COLUMNS` ";
                                    wheres = new WhHsWheres();
                                    wheres.SetValue("TABLE_NAME", "=", currentTableName, "%s");
                                    wheres.SetValue("COLUMN_NAME", "=", currentColumnName, "%s");
                                    Map<String, Object> columns = hs.detail(columnSql, wheres);
                                    if(StringUtils.isEmpty(columns.get("mCOLUMN_COMMENT").toString()))
                                    {
                                        columnTitle.add(currentColumnName);
                                    }
                                    else
                                    {
                                        columnTitle.add(columns.get("mCOLUMN_COMMENT").toString());
                                    }
                                }
                            }
                        }
                    } // 从表别名到真实表名 检索比对 end
                }
            }// 遍历所有查询字段 end
        }// 组装表头 end
    }
    // 组装数据
    List list = hs.list(sql, mainWheres, "");
    ExportExcelPoi excel = new ExportExcelPoi();
    int columnNum = columnTitle.size();
    String[] columnTitleArray = new String[columnNum];
    excel.exportExcel(fileName, headTitle, columnTitle.toArray(columnTitleArray), list, response);
    return "";
}