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 ="com.mysql.jdbc.Driver"; public void genEntitySomeTable(List<String> tableNames){ for(int p=0;p<tableNames.size();p++){ tableName=tableNames.get(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 = DriverManager.getConnection(URL,NAME,PASS); pStemt = con.prepareStatement(sql); ResultSetMetaData rsmd = pStemt.getMetaData(); pStemt2 = con.prepareStatement(sql2); ResultSet rsResultSet=pStemt2.executeQuery(); int size = rsmd.getColumnCount(); //统计列 colNames = new String[size]; colTypes = new String[size]; colSizes = new int[size]; colComment = new String[size]; int j=0; while (rsResultSet.next()) { //System.out.println(rsResultSet.getObject(9)); colComment[j]=rsResultSet.getObject(9).toString(); j++; } for (int i = 0; i < size; i++) { colNames[i] = rsmd.getColumnName(i + 1); colTypes[i] = rsmd.getColumnTypeName(i + 1); if (colTypes[i] .equals("INT")){ colTypes[i] = "INTEGER"; } colSizes[i] = rsmd.getColumnDisplaySize(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 = path.replaceAll("%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) workbook.getSheetAt(0); //从模板sheet工作表第几行开始插入（注意行、列、单元格都是从0开始数） int startRow = 1; for (int i = 0;i<colSizes.length;i++){ Row row = sheet.createRow(startRow++); row.createCell(1).setCellValue(colNames[i]); row.createCell(2).setCellValue(colTypes[i] + "(" + colSizes[i] + ")"); row.createCell(3).setCellValue(colComment[i]); } 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 ByteArrayInputStream(outputStream.toByteArray())) { byte[] bytes = new byte[1024]; int len; while ((len = byteArrayInputStream.read(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"); tableToExcel.genEntitySomeTable(dataList); } }
数据库表结构：
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:
