- 一 、效果图
- 单表
- 多表
- 二、如何使用
- pom文件
- 工具类
一 、效果图 单表
数据库表结构如下
执行main方法后
生成的excel文件
支持数据库所有表的结构,生成excel
工具类mysql mysql-connector-java 8.0.17 org.apache.poi poi-ooxml 3.17
package com.mabo.database;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mabo.controller.TableInfo;
import com.mabo.excel.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class DatabaseUtils {
public static void main(String[] args) throws Exception {
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/xxx?serverTimezone=GMT";
String user = "root";
String password = "xxx";
//生成的excel文件存储路径
String filePath="G:/";
//数据库的所有表生成excel
// createDataBaseExcels(driver, url, user, password,filePath);
//数据库的 file_info 表生成 excel
createDataBaseExcel(driver, url, user, password,"file_info",filePath);
}
public static void createDataBaseExcels(String driver, String url, String user, String password,String filePath) throws Exception {
JSONArray array = getTablesInfo(driver, url, user, password);
String[] strArray = { "名称", "类型", "长度", "必填" ,"注释","备注"};
createDataBaseExcels(array,strArray,filePath);
}
public static void createDataBaseExcel(String driver, String url, String user, String password,String tableName,String filePath) throws Exception {
String[] strArray = { "名称", "类型", "长度", "必填" ,"注释","备注"};
JSONArray jsonArray = getTableInfo(driver, url, user, password, tableName);
String fileName=filePath+tableName+".xls";
createDataBaseExcel(jsonArray, strArray,fileName);
System.out.println(tableName+"表格生成成功,文件路径: "+fileName);
}
public static void createDataBaseExcels(JSONArray jsonArray, String[] strArray,String filePath) throws IOException {
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject jsonObject = jsonArray.getJSONObject(i);
String table = jsonObject.getString("table");
JSONArray fields = jsonObject.getJSONArray("fields");
String fileName=filePath+table+".xls";
createDataBaseExcel(fields, strArray,fileName);
System.out.println(table+"表格生成成功,文件路径: "+fileName);
}
}
public static void createDataBaseExcel(JSONArray jsonArray, String[] strArray, String fileName) throws IOException {
// 创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("sheet1");
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 添加标题行
HSSFCell cell = null;
for (int i = 0; i < strArray.length; i++) {
// 获取行内对应单元格
cell = row.createCell(i);
// 单元格赋值
cell.setCellValue(strArray[i]);
}
// 写入实体数据，实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
int i = 0;
for (int j = 0; j < jsonArray.size(); j++) {
JSONObject json=jsonArray.getJSONObject(j);
i++;
row = sheet.createRow(i );
String name = json.getString("name");
String type = json.getString("type").toLowerCase();
Integer size = json.getInteger("size");
String isNeed="是";
if (json.getBoolean("isNull").equals(true)){
isNeed="否";
}
String remarks = json.getString("remarks");
// 添加数据行
for (int z = 0; z < strArray.length; z++) {
cell = row.createCell(z);
// 获取行内对应单元格
// 单元格赋值
if (z==0){
cell.setCellValue(name);
}
else if (z==1){
cell.setCellValue(type);
}
else if (z==2){
cell.setCellValue(size);
}
else if (z==3){
cell.setCellValue(isNeed);
}
else if (z==4){
cell.setCellValue(remarks);
}
}
}
// // 第六步，将文件存到指定位置
FileOutputStream fout = new FileOutputStream(fileName);
try {
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
finally {
fout.close();
}
}
public static JSONArray getTablesInfo(String driver, String url, String user, String password) throws Exception {
JSONArray array = new JSONArray();
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
DatabaseMetaData metaData = connection.getMetaData();
//从元数据中获取到所有的表名
String[] types = {"TABLE"};
//mysql-connector-java 6.0以下用这个方法
//rs = db.getTables(null, null, null, new String[] { "TABLE" });
//mysql-connector-java 6.x用这个方法
ResultSet tableResultSet = metaData.getTables(connection.getCatalog(), connection.getCatalog(), "%", types);
while (tableResultSet.next()) {
JSONObject json = new JSONObject();
String tableName = tableResultSet.getString("TABLE_NAME");
json.put("table",tableName);
// 获取表字段结构
ResultSet columnResultSet = metaData.getColumns(null, "%", tableName, "%");
JSONArray fields = new JSONArray();
while (columnResultSet.next()) {
JSONObject field = new JSONObject();
// 字段名称
String columnName = columnResultSet.getString("COLUMN_NAME");
boolean repeat=false;
//循环查看当前值是否重复
for (int i = 0; i < fields.size(); i++) {
JSONObject jsonObject = fields.getJSONObject(i);
String column_name = jsonObject.getString("name");
if (columnName.equals(column_name)){
repeat=true;
break;
}
}
if (repeat){
continue;
}
field.put("name",columnName);
// 数据类型
String columnType = columnResultSet.getString("TYPE_NAME");
field.put("type",columnType);
// 字段长度
int datasize = columnResultSet.getInt("COLUMN_SIZE");
field.put("size",datasize);
// 小数部分位数
int digits = columnResultSet.getInt("DECIMAL_DIGITS");
field.put("digits",digits);
// 是否可为空 1代表可空 0代表不可为空
int nullable = columnResultSet.getInt("NULLABLE");
if (nullable==1){
field.put("isNull",true);
}
else {
field.put("isNull",false);
}
// 描述
String remarks = columnResultSet.getString("REMARKS");
field.put("remarks",remarks);
fields.add(field);
}
json.put("fields",fields);
array.add(json);
}
return array;
}
public static JSONArray getTableInfo(String driver, String url, String user, String password,String tableName) throws Exception {
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
DatabaseMetaData metaData = connection.getMetaData();
//从元数据中获取到所有的表名
String[] types = {"TABLE"};
//mysql-connector-java 6.0以下用这个方法
//rs = db.getTables(null, null, null, new String[] { "TABLE" });
//mysql-connector-java 6.x用这个方法
ResultSet tableResultSet = metaData.getTables(connection.getCatalog(), connection.getCatalog(), "%", types);
while (tableResultSet.next()) {
JSONObject json = new JSONObject();
String table = tableResultSet.getString("TABLE_NAME");
if (table.equals(tableName)){
ResultSet columnResultSet = metaData.getColumns(null, "%", tableName, "%");
JSONArray fields = new JSONArray();
while (columnResultSet.next()) {
JSONObject field = new JSONObject();
// 字段名称
String columnName = columnResultSet.getString("COLUMN_NAME");
boolean repeat=false;
//循环查看当前值是否重复
for (int i = 0; i < fields.size(); i++) {
JSONObject jsonObject = fields.getJSONObject(i);
String column_name = jsonObject.getString("name");
if (columnName.equals(column_name)){
repeat=true;
break;
}
}
if (repeat){
continue;
}
field.put("name",columnName);
// 数据类型
String columnType = columnResultSet.getString("TYPE_NAME");
field.put("type",columnType);
// 字段长度
int datasize = columnResultSet.getInt("COLUMN_SIZE");
field.put("size",datasize);
// 小数部分位数
int digits = columnResultSet.getInt("DECIMAL_DIGITS");
field.put("digits",digits);
// 是否可为空 1代表可空 0代表不可为空
int nullable = columnResultSet.getInt("NULLABLE");
if (nullable==1){
field.put("isNull",true);
}
else {
field.put("isNull",false);
}
// 描述
String remarks = columnResultSet.getString("REMARKS");
field.put("remarks",remarks);
fields.add(field);
}
return fields;
}
}
return null;
}
}
