mysql表结构转excel表格
最近需要写⽂档，由于开发模式是先开发后写⽂档（不想吐槽。。。），数据库表结构什么的都搞好了，然后写⽂档的时候需要贴表结构，什么字段，类型，相关说明需要⼀⼀对应起来，数据库表10多张，字段⼜多，⼿动复制粘贴太蛋疼了，于是就写了个将表结构转excel表格的简单实现（丑是丑了点，毕竟实现功能了不是）。
package net.cloudkit.management.util;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.sql.*;
import java.util.*;
/**
* @author : gongtao
* @version : 2017/9/6
*/
public class TableToExcel {
private String tableName = "";//表名
private String[] colNames; // 列名数组
private String[] colComment; // 列名数组
private String[] colTypes; //列名类型数组
private int[] colSizes; //列名⼤⼩数组
//数据库连接
private static final String URL ="jdbc:mysql://192.168.1.101:3306/management?useUnicode=true&characterEncoding=UTF-8";
private static final String NAME = "root";
private static final String PASS = "admin123";
private static final String DRIVER ="sql.jdbc.Driver";
mysql文档手机版public void genEntitySomeTable(List<String> tableNames){
for(int p=0;p<tableNames.size();p++){
(p);
//创建连接
Connection con = null;
//查要⽣成实体类的表
String sql = "select * from " + tableName;
String sql2 = "show full fields from " + tableName;
PreparedStatement pStemt = null;
PreparedStatement pStemt2 = null;
try {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
con = Connection(URL,NAME,PASS);
pStemt = con.prepareStatement(sql);
ResultSetMetaData rsmd = MetaData();
pStemt2 = con.prepareStatement(sql2);
ResultSet uteQuery();
int size = ColumnCount(); //统计列
colNames = new String[size];
colTypes = new String[size];
colSizes = new int[size];
colComment = new String[size];
int j=0;
while (()) {
//System.out.Object(9));
colComment[j]=Object(9).toString();
j++;
}
for (int i = 0; i < size; i++) {
colNames[i] = ColumnName(i + 1);
colTypes[i] = ColumnTypeName(i + 1);
if (colTypes[i] .equals("INT")){
colTypes[i] = "INTEGER";
}
colSizes[i] = ColumnDisplaySize(i + 1);
}
createExcel();
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if (con != null){
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
System.out.println("⽣成完毕！");
}
public void createExcel() throws Exception{
//获取Excel模版⽂件⽬录
String path = "D:/template.xlsx";
path = placeAll("%20", " ");
try(InputStream fileInputStream = new FileInputStream(path);
//通过Excel模板⽬录获取Excel模版⽂件
XSSFWorkbook workbook1 = new XSSFWorkbook(OPCPackage.open(fileInputStream));
//利⽤POI3.8及其以上，每个Sheet可以存1,048,576⾏数据，每⾏可以有16,384列数据
Workbook workbook = new SXSSFWorkbook(workbook1, 100)){
//重命名sheet⼯作表名称：第⼏个⼯作表
workbook.setSheetName(0, tableName);
//创建sheet⼯作表
SXSSFSheet sheet = (SXSSFSheet) SheetAt(0);
//从模板sheet⼯作表第⼏⾏开始插⼊（注意⾏、列、单元格都是从0开始数）
int startRow = 1;
for (int i = 0;i<colSizes.length;i++){
Row row = ateRow(startRow++);
}
try(ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
workbook.write(outputStream);
//输出⽬录
String filePath = "D:/" + tableName + ".xlsx";
File file = new File(filePath);
try(FileOutputStream fileOutputStream = new FileOutputStream(file);
ByteArrayInputStream byteArrayInputStream = new ByteArray())) { byte[] bytes = new byte[1024];
int len;
while ((len = ad(bytes)) != -1){
fileOutputStream.write(bytes,0,len);
}
}
}
}
}
public static void main(String[] args) {
TableToExcel tableToExcel = new TableToExcel();
List<String> dataList = new ArrayList<>();
//添加表名
dataList.add("operation_log");
}
}
数据库表结构：
CREATE TABLE `operation_log` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`DATA_ID` bigint(20) DEFAULT NULL COMMENT '操作数据ID',
`TABLE_NAME` varchar(64) DEFAULT NULL COMMENT '对应表名',
`MODULE_NAME` varchar(64) DEFAULT NULL COMMENT '模块名称',
`OPERATION` varchar(64) DEFAULT NULL COMMENT '具体操作',
`TYPE` int(11) DEFAULT NULL COMMENT '类型:1、INFO 2、DEBUG 3、ERROR',
`MESSAGE` varchar(5120) DEFAULT NULL COMMENT '信息',
`OPERATION_USER` varchar(32) DEFAULT NULL COMMENT '操作⼈',
`ROW_STATUS` int(8) DEFAULT NULL COMMENT '记录状态',
`MODIFY_TIME` bigint(20) DEFAULT NULL COMMENT '修改时间',
`CREATE_TIME` bigint(20) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '操作⽇志表';
模板excel: ⽣成的excel:
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论