【表结构导出为Excel】写了一个表结构导出为excel的demo，支持多种格式，工作时还是蛮有用的

代码演示使用的是pgsql数据库，要是使用MySQL的话，把查询语句换为：

SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '你的表名';

就可以了!

可以支持多数据源：

package org.example.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class JdbcConfig {
    @Bean(name = "healthDs")
    @ConfigurationProperties(prefix = "spring.datasource.dynamic.health")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "health")
    public JdbcTemplate jdbcTemplate1(@Qualifier("healthDs") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }


    @Bean(name = "kgProjectManageDs")
    @ConfigurationProperties(prefix = "spring.datasource.dynamic.kg-project-manage")
    public DataSource dataSource2() {
        System.out.println("注入kg-project-manage数据源");
        System.out.println("注入kg-project-manage数据源");
        System.out.println("注入kg-project-manage数据源");
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "kgProjectManage")
    public JdbcTemplate jdbcTemplate2(@Qualifier("kgProjectManageDs") DataSource dataSource2) {
        System.out.println("将kg-project-manage数据源加载到JDBCTem中");
        System.out.println("将kg-project-manage数据源加载到JDBCTem中");
        System.out.println("将kg-project-manage数据源加载到JDBCTem中");
        return new JdbcTemplate(dataSource2);
    }
}

在yml中配置你的数据源

spring:
  datasource:
    dynamic:
      health:
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://xxx?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
        username: xxx
        password: xxx
      kg-project-manage:
        driver-class-name: org.postgresql.Driver
        jdbc-url: jdbc:postgresql://xxx?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
        username: xxx
        password: xxx

实现的源码：

package org.example.jdbcTest;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.config.JdbcConfig;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

@SpringBootTest
@RunWith(SpringRunner.class)
public class jdbcTem {

    private Map<String, String> dataSourceList;

    private Map<String, JdbcTemplate> jdbcTemplateMap;

    private String str = "SELECT \n" +
            "    cols.column_name AS \"filed\",\n" +
            "    CASE \n" +
            "        WHEN cols.udt_name IN ('varchar', 'char') THEN cols.udt_name || '(' || cols.character_maximum_length || ')'\n" +
            "        WHEN cols.udt_name IN ('numeric') THEN cols.udt_name || '(' || cols.numeric_precision || ',' || cols.numeric_scale || ')'\n" +
            "          ELSE cols.udt_name\n" +
            "    END AS \"filedType\",\n" +
            "    pgd.description AS \"describe\"\n" +
            "FROM \n" +
            "    information_schema.columns cols\n" +
            "LEFT JOIN \n" +
            "    pg_description pgd ON pgd.objsubid = cols.ordinal_position AND pgd.objoid = (\n" +
            "        SELECT c.oid FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace \n" +
            "        WHERE c.relname = '{&}' AND n.nspname = 'public'\n" +
            "    )\n" +
            "WHERE \n" +
            "    cols.table_schema = 'public'  \n" +
            "    AND cols.table_name = '{&}'  \n" +
            "ORDER BY cols.ordinal_position;  ";

    @Autowired
    private JdbcConfig jdbcConfig;

    @Before
    public void before() {
        Map<String, String> arrayList = new HashMap<>();
        arrayList.put("kb_project_manage", "领导履职记录主表（lead_performance_record）,领导履职记录子表（lead_performance_record_line）");
        arrayList.put("kg_occupation_health", "履职人员（files_record）");
        dataSourceList = arrayList;
        HashMap<String, JdbcTemplate> jdbcTemplateHashMap = new HashMap<>();
        jdbcTemplateHashMap.put("kb_project_manage", new JdbcTemplate(jdbcConfig.dataSource2()));
        jdbcTemplateHashMap.put("kg_occupation_health", new JdbcTemplate(jdbcConfig.dataSource1()));
        jdbcTemplateMap = jdbcTemplateHashMap;
    }

    @Test
    public void name() throws IOException {

        Workbook workbook = new XSSFWorkbook();
        // 设置表格表头与行头的样式
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 16);
        font.setBold(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        CellStyle cellStyleBt = workbook.createCellStyle();
        cellStyleBt.setAlignment(HorizontalAlignment.CENTER);
        cellStyleBt.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleBt.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleBt.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
        CellStyle cellStyleLm = workbook.createCellStyle();
        cellStyleLm.setAlignment(HorizontalAlignment.CENTER);
        cellStyleLm.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleLm.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleLm.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // ---
        dataSourceList.forEach((key, value) -> {
            Sheet sheet = workbook.createSheet(key);
            String[] split = value.split(",");
            // Headers
            int rowNum = 1;
            for (String s : split) {
                int i1 = s.indexOf("（");
                String substring = s.substring(i1 + 1, s.length() - 1);
                String sql = str.replace("{&}", substring);
                JdbcTemplate jdbcTemplate = jdbcTemplateMap.get(key);
                List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql);
                // 设置表头
                setExcelBt(cellStyleBt, s, sheet, rowNum, resultList, font);
                //  列格式
                for (int i = 0; i < resultList.get(0).size(); i++) {
                    Row dataRow = sheet.createRow(rowNum++);
                    int colNum = 1;
                    // 设置行头
                    Cell cell1 = dataRow.createCell(0);
                    if (i == 0) {
                        cell1.setCellValue("字段名");
                    } else if (i == 1) {
                        cell1.setCellValue("字段类型");
                    } else {
                        cell1.setCellValue("描述");
                    }
                    cell1.setCellStyle(cellStyleLm);
                    for (Map<String, Object> stringObjectMap : resultList) {
                        Cell cell = dataRow.createCell(colNum++);
                        if (i == 0) {
                            cell.setCellValue((String) stringObjectMap.get("filed"));
                        } else if (i == 1) {
                            cell.setCellValue((String) stringObjectMap.get("filedType"));
                        } else {
                            cell.setCellValue((String) stringObjectMap.get("describe"));
                        }
                        cell.setCellStyle(cellStyle);
                    }
                }
                rowNum += 4;

                /* 写入表头，假设第一次循环时写入表头  --- (行格式)
                if (rowNum == 0) {
                    Row headerRow = sheet.createRow(rowNum++);
                    int colNum = 0;
                    for (String colName : resultList.get(0).keySet()) {
                        Cell cell = headerRow.createCell(colNum++);
                        cell.setCellValue(colName);
                    }
                }
                // 写入数据
                for (Map<String, Object> row : resultList) {
                    Row dataRow = sheet.createRow(rowNum++);
                    int colNum = 0;
                    for (Object valueData : row.values()) {
                        Cell cell = dataRow.createCell(colNum++);
                        if (valueData instanceof String) {
                            cell.setCellValue((String) valueData);
                        } else if (valueData instanceof Integer) {
                            cell.setCellValue((Integer) valueData);
                        } else if (valueData instanceof Double) {
                            cell.setCellValue((Double) valueData);
                        } // 添加其他数据类型的处理逻辑，例如日期、布尔值等
                    }
                }*/

            }
            // 手动设置行高
            for (int i = 0; i < rowNum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    row.setHeightInPoints(40); // 设置每行的高度为20磅
                }
            }
            // 设置列宽度
            int numColumns = sheet.getRow(1).getPhysicalNumberOfCells(); // 假设第一行是最宽的列
            for (int col = 0; col < numColumns; col++) {
                sheet.setColumnWidth(col, 5000); // 设置列宽度，单位为 1/256 个字符宽度
            }

        });

        // 将工作簿写入到文件
        try (FileOutputStream outputStream = new FileOutputStream("C:\\Users\\26615\\Desktop\\xxx" + ".xlsx")) {
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        workbook.close();

    }

    /**
     * 每个表新增表头信息
     *
     * @param cellStyle
     * @param key
     * @param sheet
     * @param rowNum
     * @param resultList
     */
    private void setExcelBt(CellStyle cellStyle, String key, Sheet sheet, int rowNum,
                            List<Map<String, Object>> resultList, Font font) {
        // 合并表头列
        sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, resultList.size()));
        // 获取合并后的起始单元格
        Row row = sheet.getRow(rowNum - 1); // 假设第0行是合并后的区域的行
        if (row == null) {
            row = sheet.createRow(rowNum - 1);
        }

        Cell cellBt = row.getCell(0); // 假设合并后的区域的起始单元格是第0列（A列）
        if (cellBt == null) {
            cellBt = row.createCell(0);
        }
        cellStyle.setFont(font);
        cellBt.setCellValue(key);
        cellBt.setCellStyle(cellStyle);

    }

}
导出后效果

要是想要竖着排表字段，只需要把/* */注释内的内容放出来，在把与他同级的删除掉就好