java导出数据库表信息生成excel
时间: 2023-08-04 07:21:23 浏览: 54
您可以使用Java中的JDBC API和Apache POI库来将数据库表信息导出为Excel表格。下面是一个简单的示例代码,可以将MySQL数据库中的表信息导出为Excel表格:
```java
import java.sql.*;
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
public class ExportTableToExcel {
public static void main(String[] args) throws Exception {
// 连接到MySQL数据库
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
// 查询表信息
String sql = "SHOW TABLES";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
// 创建Excel表格
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Table Info");
// 添加表头
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("Table Name");
headerRow.createCell(1).setCellValue("Number of Columns");
// 添加表信息
int rowNum = 1;
while (rs.next()) {
String tableName = rs.getString(1);
sql = "SELECT COUNT(*) FROM information_schema.columns WHERE table_name = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, tableName);
ResultSet rs2 = stmt.executeQuery();
rs2.next();
int numColumns = rs2.getInt(1);
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(tableName);
row.createCell(1).setCellValue(numColumns);
}
// 输出Excel表格
FileOutputStream outputStream = new FileOutputStream("table_info.xlsx");
workbook.write(outputStream);
workbook.close();
// 关闭连接
rs.close();
stmt.close();
conn.close();
}
}
```
该代码使用了Apache POI库来创建Excel表格,并使用JDBC API从MySQL数据库中查询表信息。您可以根据需要修改该代码以适应不同的数据库和导出要求。