java调用poi生成数据库表结构

程序员文章站 2024-03-21 23:10:40

...

近日，由于工作需要，需要将整理数据库表结构，由于此前安装PD没有成功，而又没有其他合适的工具，于是自己动手：基于POI实现将某个数据库实例中的表，导出到Excel文档中。下面是具体步骤。
1、创建maven功能，引入对应的jar：

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
	 <groupId>mysql</groupId>
	 <artifactId>mysql-connector-java</artifactId>
	 <version>6.0.6</version>
	</dependency>

2、ExcelUtils类
package cn.test.tablestructure.util;

import java.awt.Color;

import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**

excel表格工具类
@author sanshizhang
@date 2021/08/24
*/
public final class ExcelUtils {

/**
- 获取每个表格的样式
- @param wb
- @return
  */
  public static XSSFCellStyle getBorderStyle(XSSFWorkbook wb) {
  XSSFCellStyle border = wb.createCellStyle();
  border.setBorderBottom(BorderStyle.THIN); // 下边框
  border.setBorderLeft(BorderStyle.THIN);// 左边框
  border.setBorderTop(BorderStyle.THIN);// 上边框
  border.setBorderRight(BorderStyle.THIN);// 右边框
  return border;
  }
/**
- 生成一个cell，带边框样式
- @param wb
- @param row
- @param index
- @param value
- @return
  */
  public static XSSFCell getDefaultCell(XSSFWorkbook wb, XSSFRow row, Integer index, String value) {
  XSSFCell c0 = row.createCell(index);
  c0.setCellStyle(getBorderStyle(wb));
  c0.setCellValue(value);
  return c0;
  }
/**
- 超链接表格样式
- @param wb
- @return
  /
  public static XSSFCellStyle getLinkStyle(XSSFWorkbook wb) {
  / 设置为超链接的样式*/
  XSSFCellStyle linkStyle = wb.createCellStyle();
  XSSFFont cellFont = wb.createFont();
  cellFont.setUnderline((byte)1);
  // 边框
  linkStyle.setBorderBottom(BorderStyle.THIN); // 下边框
  linkStyle.setBorderLeft(BorderStyle.THIN);// 左边框
  linkStyle.setBorderTop(BorderStyle.THIN);// 上边框
  linkStyle.setBorderRight(BorderStyle.THIN);// 右边框
  
  XSSFColor color = new XSSFColor(Color.BLUE, null);
  cellFont.setColor(color);
  linkStyle.setFont(cellFont);
  return linkStyle;
  }
/**
- 生成超链接
- @param wb
- @param sheetName
- @param location
- @return
  */
  public static org.apache.poi.ss.usermodel.Hyperlink getHyperlink(XSSFWorkbook wb, String sheetName,
  String location) {
  Hyperlink link = wb.getCreationHelper().createHyperlink(HyperlinkType.URL);
  link.setAddress("#" + sheetName + “!” + location);
  return (org.apache.poi.ss.usermodel.Hyperlink)link;
  }
  }

3、主类
package cn.test.tablestructure;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;

import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.test.tablestructure.util.ExcelUtils;

/**

Hello world!

*/
public class App {
private static String classname = “com.mysql.cj.jdbc.Driver”;
// nullCatalogMeansCurrent=true ，只返回对应数据库中的表
// useInformationSchema=true, 获取表的描述信息 String comment = tableset.getString(“REMARKS”);
private static String url =
“jdbc:mysql://192.168.0.1:3306/test?characterEncoding=UTF-8&nullCatalogMeansCurrent=true&useInformationSchema=true”;
private static String username = “root”;
private static String password = “root”;

public static void main(String[] args) throws Exception {

    String filename = "e:/***项目数据库设计.xlsx";
    Class.forName(classname);
    Connection conn = DriverManager.getConnection(url, username, password);
    DatabaseMetaData metadata = conn.getMetaData();
    ResultSet tableset = metadata.getTables(null, "%", "%", new String[] {"TABLE"});
    int tablesize = 0;
    XSSFWorkbook book = new XSSFWorkbook();
    XSSFSheet sheet = book.createSheet("数据库表");
    XSSFRow row = sheet.createRow(0);
    ExcelUtils.getDefaultCell(book, row, 0, "表名称");
    ExcelUtils.getDefaultCell(book, row, 1, "表说明");
    // 设置列的宽度，以一个字符的1/256的宽度作为一个单位
    sheet.setColumnWidth(0, 30 * 256);
    sheet.setColumnWidth(1, 30 * 256);

    /* 设置为超链接的样式*/
    XSSFCellStyle linkStyle = ExcelUtils.getLinkStyle(book);
    int index = 1;
    while (tableset.next()) {
        String tableName = tableset.getString("table_name");
        String comment = tableset.getString("REMARKS");
        tablesize++;
        row = sheet.createRow(index);

        Hyperlink link = ExcelUtils.getHyperlink(book, tableName, "A3");
        XSSFCell c = ExcelUtils.getDefaultCell(book, row, 0, tableName);
        c.setHyperlink(link);
        c.setCellStyle(linkStyle);
        ExcelUtils.getDefaultCell(book, row, 1, comment);
        index++;

        ResultSet colset = metadata.getColumns(null, "%", tableName, "%");
        XSSFSheet tsheet = book.createSheet(tableName);
        XSSFRow row1 = tsheet.createRow(0);
        XSSFCell rtn = ExcelUtils.getDefaultCell(book, row1, 0, "返回");
        Hyperlink link1 = ExcelUtils.getHyperlink(book, "数据库表", "A" + tablesize);
        rtn.setHyperlink(link1);
        rtn.setCellStyle(linkStyle);

        row1 = tsheet.createRow(1);
        ExcelUtils.getDefaultCell(book, row1, 0, "字段名称");
        ExcelUtils.getDefaultCell(book, row1, 1, "字段类型");
        ExcelUtils.getDefaultCell(book, row1, 2, "字段长度型");
        ExcelUtils.getDefaultCell(book, row1, 3, "是否允许为空：1允许，0不允许");
        ExcelUtils.getDefaultCell(book, row1, 4, "默认值");
        ExcelUtils.getDefaultCell(book, row1, 5, "备注");
        // 将备注这一列，设置宽一点
        int width = 30;
        tsheet.setColumnWidth(5, width * 256);
        // 从第二行开始，第一行是返回按钮
        int col = 2;
        while (colset.next()) {
            String columnName = colset.getString("COLUMN_NAME");
            String columnType = colset.getString("TYPE_NAME");
            int datasize = colset.getInt("COLUMN_SIZE");
            // int digits = colset.getInt("DECIMAL_DIGITS");
            int nullable = colset.getInt("NULLABLE");// 1、允许为空，0不允许
            String defvalue = colset.getString("COLUMN_DEF");/// 默认值
            String remarks = colset.getString("REMARKS");

            row1 = tsheet.createRow(col);
            ExcelUtils.getDefaultCell(book, row1, 0, columnName);
            ExcelUtils.getDefaultCell(book, row1, 1, columnType);
            ExcelUtils.getDefaultCell(book, row1, 2, datasize + "");
            ExcelUtils.getDefaultCell(book, row1, 3, nullable + "");
            ExcelUtils.getDefaultCell(book, row1, 4, defvalue);
            ExcelUtils.getDefaultCell(book, row1, 5, remarks);

            col++;
        }
        // 关闭数据库信息
        colset.close();
    }
    // 写文件
    book.write(new FileOutputStream(new File(filename)));
    book.close();

    // 关闭数据库信息
    tableset.close();
    conn.close();

    System.out.println("文件生成完毕！");
}

}

相关标签：研发管理数据库 poi excel

上一篇： Java工具类：JavaBean生成数据库表

下一篇：用 Postman 做接口自动化（三）断言（后置脚本）

java调用poi生成数据库表结构

Java工具类：JavaBean生成数据库表