欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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("文件生成完毕!");
}

}