根据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 "";
}