filename=table_structures.xls
tables.to.export=%
column.width=15
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
user=DANIEL
password=123456
driver=oracle.jdbc.driver.OracleDriver

/*
?*?Copyright?2008?the?original?author?or?authors.
?*?
?*?Licensed?under?the?Apache?License,?Version?2.0?(the?"License");
?*?you?may?not?use?this?file?except?in?compliance?with?the?License.
?*?You?may?obtain?a?copy?of?the?License?at
?*?
?*??????? http://www.apache.org/licenses/LICENSE-2.0
?*?
?*?Unless?required?by?applicable?law?or?agreed?to?in?writing,?software
?*?distributed?under?the?License?is?distributed?on?an?"AS?IS"?BASIS,
?*?WITHOUT?WARRANTIES?OR?CONDITIONS?OF?ANY?KIND,?either?express?or?implied.
?*?See?the?License?for?the?specific?language?governing?permissions?and
?*?limitations?under?the?License.
?*
?*?Author:?山風小子( http://www.tkk7.com/BlueSUN )
?*?Email?:?realbluesun@hotmail.com
? */ ?


import ?groovy.sql.Sql
import ?jxl. *
import ?jxl.write. *

Properties?properties? = ? new ?Properties();
properties.load( new ?FileInputStream( " conf.properties " ));

def?filename? = ?properties.getProperty( ' filename ' )
def?tablesToExport? = ?properties.getProperty( ' tables.to.export ' )
def?columnWidth? = ?properties.getProperty( ' column.width ' )
def?url? = ?properties.getProperty( ' url ' )
def?user? = ?properties.getProperty( ' user ' )
def?password? = ?properties.getProperty( ' password ' )
def?driver? = ??properties.getProperty( ' driver ' )

def?sql? = ?Sql.newInstance(url,?user,?password,?driver)

def?sqlStmt? = ? """
??select?
????????a.table_name,?
????????a.column_name,?
????????(select?
????????????d.constraint_type?
?????????from
????????????all_constraints?d,
????????????all_cons_columns?e
?????????where?
????????????c.owner? = ?d.owner?and
????????????d.owner? = ?e.owner?and
????????????c.table_name? = ?d.table_name?and
????????????d.table_name? = ?e.table_name?and
????????????b.column_name? = ?e.column_name?and
????????????d.constraint_name? = ?e.constraint_name?and
????????????d.constraint_type? = ? ' P ' ?and?
????????rownum? = ? 1
????????)?as?constraint_type,
????????a.data_type,?
????????a.data_length,?
????????a.data_precision,?
????????a.data_scale,?
????????a.nullable,?
????????a.data_default,?
????????b.comments,
????????c.comments?as?tab_comments
???from?
????????all_tab_columns?a,?
????????all_col_comments?b,
????????all_tab_comments?c
???where?
????????a.owner? = ?b.owner?and
????????b.owner? = ?c.owner?and
????????a.table_name?? = ??b.table_name?and
????????b.table_name?? = ??c.table_name?and
????????a.column_name?? = ??b.column_name?and
????????a.table_name?like?? ? ??and?
????????a.owner? = ? ? ?
"""

Map?tables? = ? new ?HashMap()

sql.eachRow(sqlStmt,?[tablesToExport,?user]){?row? ->

????Map?column? = ? new ?HashMap()
????column.put( ' column_name ' ,?row.column_name);
????column.put( ' constraint_type ' ,?row.constraint_type);
????column.put( ' data_type ' ,?row.data_type);
????column.put( ' data_length ' ,?row.data_length);
????column.put( ' data_precision ' ,?row.data_precision);
????column.put( ' data_scale ' ,?row.data_scale);
????column.put( ' nullable ' ,?row.nullable);
????column.put( ' data_default ' ,?row.data_default);
????column.put( ' comments ' ,?row.comments);


????String?tableName? = ?row.table_name
????String?tableComments? = ?row.tab_comments
????Set?columns? = ?tables.get(tableName) ? .columns
????
???? if ?( null ? == ?columns)?{
????????columns? = ? new ?HashSet();
????????columns? << ?column
????????
????????tables.put(tableName,?[tableComments:tableComments,?columns:columns])
????}? else ?{
????????columns? << ?column
????}
}

println? " to?export?table?structures "

class ?WritableSheetCategory?{
???? static ?insertRow(WritableSheet?writableSheet,?List?row,? int ?x,? int ?y)?{
????????row.eachWithIndex?{?col,?i? ->
????????????Label?cell? = ? new ?Label(x? + ?i,?y,?col)
????????????writableSheet.addCell(cell)
????????}
????}????
}


WritableWorkbook?writableWorkBook? = ?
????Workbook.createWorkbook(
???????? new ?File(filename))
WritableSheet?writableSheet? = ?writableWorkBook.createSheet( " 第一頁 " ,? 0 )


WritableFont?writableFontForTableName? =
???? new ?WritableFont(WritableFont.TIMES,? 10 ,?WritableFont.BOLD)
WritableCellFormat?writableCellFormatForTableName? = ?
???? new ?WritableCellFormat(writableFontForTableName)
// writableCellFormatForTableName.setAlignment(jxl.format.Alignment.CENTRE)
writableCellFormatForTableName.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE)


WritableFont?writableFontForTableComments? =
???? new ?WritableFont(WritableFont.TIMES,? 10 ,?WritableFont.NO_BOLD)
WritableCellFormat?writableCellFormatForTableComments? = ?
???? new ?WritableCellFormat(writableFontForTableComments)
// writableCellFormatForTableComments.setAlignment(jxl.format.Alignment.CENTRE)
writableCellFormatForTableComments.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE)


int ?line? = ? 0
List?titleRow? = ?[
???? ' 表名 ' ,? ' 表注釋 ' ,? ' 字段名稱 ' ,? ' 是否主鍵 ' ,? ' 字段類型 ' ,? ' 字段長度 ' ,? ' 整數位數 ' ,? ' 小數位數 ' ,? ' 允許空值 ' ,? ' 缺省值 ' ,? ' 字段注釋 '
]


try ?{
????columnWidth? = ?Integer.parseInt(columnWidth)?
}? catch ?(Exception?e)?{
????columnWidth? = ? 15
????System.err.println(e.getMessage())
}
for ?( int ?i? = ? 0 ;?i? < ?titleRow.size();?i ++ )?{
????writableSheet.setColumnView(i,?columnWidth)
}

use?(WritableSheetCategory)?{
????writableSheet.insertRow(titleRow,?line ++ ,? 0 )
}

tables.each?{?tableName,?tableInfo? ->
????String?tableComments? = ?tableInfo.tableComments
????Set?columns? = ?tableInfo.columns

????Label?tableNameCell? = ? new ?Label( 0 ,?line,?tableName,?writableCellFormatForTableName)
????writableSheet.addCell(tableNameCell)

????Label?tableCommentsCell?? = ? new ?Label( 1 ,?line,?tableComments? ? ? "" ? + ?tableComments?:? "" ,?writableCellFormatForTableComments)
????writableSheet.addCell(tableCommentsCell)

????columns.each?{?column? ->
????????List?row? = ?[
????????????column.column_name? ? ? "" ? + ?column.column_name?:? "" ,
????????????column.constraint_type? ? ? "" ? + ?column.constraint_type?:? "" ,
????????????column.data_type? ? ? "" ? + ?column.data_type?:? "" ,
????????????column.data_length? ? ? "" ? + ?column.data_length?:? "" ,
????????????column.data_precision? ? ? "" ? + ?column.data_precision?:? "" ,
????????????column.data_scale? ? ? "" ? + ?column.data_scale?:? "" ,
????????????column.nullable? ? ? "" ? + ?column.nullable?:? "" ,
????????????column.data_default? ? ? "" ? + ?column.data_default?:? "" ,
????????????column.comments? ? ? "" ? + ?column.comments?:? ""
????????]
????????
????????use?(WritableSheetCategory)?{
????????????writableSheet.insertRow(row,? 2 ,?line ++ )
????????}
????}

????writableSheet.mergeCells( 0 ,?line? - ?columns.size(),? 0 ,?line? - ? 1 )
????writableSheet.mergeCells( 1 ,?line? - ?columns.size(),? 1 ,?line? - ? 1 )

????line? += ? 2
}

writableWorkBook.write();
writableWorkBook.close();

println? " done! "