JAVA 将数据库表导出数据结构为excle文本

All In丶

已于 2024-04-08 15:30:04 修改

阅读量134

点赞数 2

文章标签： java 数据库数据结构

于 2024-04-03 14:34:06 首次发布

本文链接：https://blog.csdn.net/superPojo/article/details/137344865

版权

<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi-ooxml</artifactId>  
    <version>5.0.0</version> <!-- 使用你需要的版本 -->  
</dependency>

import org.apache.poi.ss.usermodel.*;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.sql.*;  
  
public class ExportTableStructureToExcel {  
    public static void main(String[] args) {  
        String url = "jdbc:mysql://localhost:3306/your_database";  
        String user = "your_username";  
        String password = "your_password";  
        String tableName = "your_table_name"; // 要导出的表名  
        String desktopPath = System.getProperty("user.home") + File.separator + "Desktop" + File.separator;  
        String outputFile = desktopPath + "table_structure.xlsx";  
  
        try (Connection conn = DriverManager.getConnection(url, user, password)) {  
            DatabaseMetaData metaData = conn.getMetaData();  
            ResultSet columns = metaData.getColumns(null, null, tableName, null);  
  
            Workbook workbook = new XSSFWorkbook();  
            Sheet sheet = workbook.createSheet(tableName + " Structure");  
  
            // 创建表头  
            Row headerRow = sheet.createRow(0);  
            headerRow.createCell(0).setCellValue("Column Name");  
            headerRow.createCell(1).setCellValue("Data Type");  
            headerRow.createCell(2).setCellValue("Column Size");  
            headerRow.createCell(3).setCellValue("Nullable");  
            // 根据需要添加更多列  
  
            int rowNum = 1;  
            while (columns.next()) {  
                Row row = sheet.createRow(rowNum++);  
                row.createCell(0).setCellValue(columns.getString("COLUMN_NAME"));  
                row.createCell(1).setCellValue(columns.getString("TYPE_NAME"));  
                row.createCell(2).setCellValue(columns.getString("COLUMN_SIZE"));  
                row.createCell(3).setCellValue(columns.getString("IS_NULLABLE"));  
                // 根据需要填充更多单元格  
            }  
  
            try (FileOutputStream outputStream = new FileOutputStream(outputFile)) {  
                workbook.write(outputStream);  
                System.out.println("Table structure exported to " + outputFile);  
            }  
        } catch (SQLException | IOException e) {  
            e.printStackTrace();  
        }  
    }  
}