MySQL table structure goto Excel table

Recently need to write documents, because development mode is first developed after writing documents (do not want to spit groove ...) ), the database table structure and so on, and then write the document need to paste table structure, what field, type, related instructions need one by one corresponding to, database table more than 10, fields and more, manual copy and paste too painful, so wrote a table structure to Excel table simple implementation (ugly is ugly point, After all, the function is not).

 PackageNet.cloudkit.management.util;ImportOrg.apache.poi.openxml4j.opc.OPCPackage;ImportOrg.apache.poi.ss.usermodel.Row;ImportOrg.apache.poi.ss.usermodel.Workbook;ImportOrg.apache.poi.xssf.streaming.SXSSFSheet;ImportOrg.apache.poi.xssf.streaming.SXSSFWorkbook;ImportOrg.apache.poi.xssf.usermodel.XSSFWorkbook;ImportJava.io.*;ImportJava.sql.*;ImportJava.util.*;/** * @author: Gongtao *@version: 2017/9/6*/ Public classTabletoexcel {PrivateString tableName = "";//Table name    PrivateString[] colnames;//column an array group    PrivateString[] colcomment;//column an array group    PrivateString[] Coltypes;//array of column name types    Private int[] colsizes;//array of column name size//Database Connection    Private Static FinalString URL = "Jdbc:mysql://192.168.1.101:3306/management?useunicode=true&characterencoding=utf-8"; Private Static FinalString NAME = "root"; Private Static FinalString PASS = "admin123"; Private Static FinalString DRIVER = "Com.mysql.jdbc.Driver";  Public voidGenentitysometable (list<string>tablenames) {         for(intP=0;p<tablenames.size ();p + +) {TableName=Tablenames.get (P); //Create a connectionConnection con =NULL; //Check the table to generate the entity classString 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 (); intSize = Rsmd.getcolumncount ();//Statistics ColumnColnames =NewString[size]; Coltypes=NewString[size]; Colsizes=New int[size]; Colcomment=NewString[size]; intJ=0;  while(Rsresultset.next ()) {//System.out.println (Rsresultset.getobject (9));Colcomment[j]=rsresultset.getobject (9). toString (); J++; }                 for(inti = 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 ("Build Finished!" "); }     Public voidCreateexcel ()throwsexception{//get the Excel template file directoryString Path = "D:/template.xlsx"; Path= Path.replaceall ("%20", "" "); Try(InputStream FileInputStream =NewFileInputStream (path); //get an Excel template file from the Excel templates catalogXssfworkbook Workbook1 =NewXssfworkbook (Opcpackage.open (FileInputStream)); //with POI3.8 and above, each sheet can store 1,048,576 rows of data, each row can have 16,384 columns of dataWorkbook Workbook =NewSxssfworkbook (WORKBOOK1, 100)){            //Rename a sheet sheet name: The first few worksheetsWorkbook.setsheetname (0, TableName); //Create a sheet worksheetSxssfsheet sheet = (sxssfsheet) workbook.getsheetat (0); //Insert from the first few lines of the template Sheet worksheet (note that rows, columns, cells are all starting from 0)            intStartRow = 1;  for(inti = 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 =NewBytearrayoutputstream ())                {Workbook.write (OutputStream); //Output DirectoryString FilePath = "d:/" + TableName + ". xlsx"; File File=NewFile (FilePath); Try(FileOutputStream FileOutputStream =Newfileoutputstream (file); Bytearrayinputstream Bytearrayinputstream=NewBytearrayinputstream (Outputstream.tobytearray ())) {                    byte[] bytes =New byte[1024]; intLen;  while(len = bytearrayinputstream.read (bytes))! =-1) {fileoutputstream.write (bytes,0, Len); }                }            }        }    }     Public Static voidMain (string[] args) {tabletoexcel tabletoexcel=NewTabletoexcel (); List<String> dataList =NewArraylist<>(); //Add table nameDatalist.add ("Operation_log");    Tabletoexcel.genentitysometable (dataList); }}

database table structure:

CREATE TABLE' Operation_log ' (' ID ')bigint( -) not NULLAuto_increment COMMENT'ID', ' data_id 'bigint( -)DEFAULT NULLCOMMENT'Operation Data ID', ' table_name 'varchar( -)DEFAULT NULLCOMMENT'corresponding table name', ' module_name 'varchar( -)DEFAULT NULLCOMMENT'Module Name', ' Operation 'varchar( -)DEFAULT NULLCOMMENT'Specific Operation', ' TYPE 'int( One)DEFAULT NULLCOMMENT'type: 1, INFO 2, DEBUG 3, ERROR', ' MESSAGE 'varchar(5120)DEFAULT NULLCOMMENT'Information', ' Operation_user 'varchar( +)DEFAULT NULLCOMMENT'Operating Person', ' Row_status 'int(8)DEFAULT NULLCOMMENT'Record Status', ' Modify_time 'bigint( -)DEFAULT NULLCOMMENT'Modification Time', ' Create_time 'bigint( -)DEFAULT NULLCOMMENT'creation Time',  PRIMARY KEY(' ID ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8 COMMENT ' Operation log table ';

Template Excel:

Generated Excel:

MySQL table structure goto Excel table