当前位置：首页 > news >正文

第1篇：Mysql数据库表结构导出字段到Excel(一个sheet中)

news 2025/7/28 14:49:51

package com.xx.util;import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.sql.*;
import java.io.*;public class DatabaseToExcel {public static void main(String[] args) throws Exception {// 数据库连接配置String url = "jdbc:mysql://localhost:3306";String user = "root";String password = "root";String tableName = "table_name";String databases = "database_name";// 连接数据库Connection conn = DriverManager.getConnection(url, user, password);DatabaseMetaData metaData = conn.getMetaData();// 获取表字段信息/**参数catalog一个包含目录名称的字符串 。架构一个包含架构名称模式的字符串 。table一个包含表名称模式的字符串。col一个包含列名称模式的字符串。*/ResultSet resultSet = metaData.getColumns(databases, null, null, "%");// 创建Excel工作簿和工作表Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet("TableInfo");// 写入表头Row header = sheet.createRow(0);header.createCell(0).setCellValue("Name");header.createCell(1).setCellValue("Datatype");header.createCell(2).setCellValue("Comment");header.createCell(3).setCellValue("Null Option");header.createCell(4).setCellValue("Is PK");header.createCell(5).setCellValue("Is FK");header.createCell(5).setCellValue("tableName");int rowNum = 1;while (resultSet.next()) {// 读取字段名和类型String columnName = resultSet.getString("COLUMN_NAME");String typeName = resultSet.getString("TYPE_NAME");String columnSize = resultSet.getString("COLUMN_SIZE");String remarks = resultSet.getString("REMARKS");String tableName1 = resultSet.getString("TABLE_NAME");// 写入数据到ExcelRow row = sheet.createRow(rowNum);row.createCell(0).setCellValue(columnName);row.createCell(1).setCellValue(typeName + "(" + columnSize + ")");row.createCell(2).setCellValue(remarks);row.createCell(3).setCellValue("NULL");row.createCell(4).setCellValue("NO");row.createCell(5).setCellValue("NO");row.createCell(5).setCellValue(tableName1);rowNum++;}// 关闭连接resultSet.close();conn.close();// 写入Excel文件FileOutputStream out = new FileOutputStream("TableInfo.xlsx");workbook.write(out);out.close();System.out.println("Excel文件已生成！");}
}