https://qiita.com/kikuchiTakuya/items/53990fca06fb9ba1d8a7
https://qiita.com/taashi/items/07bf75201a074e208ae5
入力jsonファイル
vim tab1.json
{
"schema_name": "TEST",
"table_name": "TAB1",
"table_desc": "テストテーブル1",
"cols": [
{ "col_name": "COL1", "col_type": "TIMESTAMP(6)", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "COL2", "col_type": "NUMBER(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "COL3", "col_type": "NUMBER", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "COL4", "col_type": "VARCHAR2(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "COL5", "col_type": "VARCHAR2(10)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "RANGE",
"partition_col": "COL1",
"partition_defs" : [
{ "partition_name": "P1", "partition_limit": "TIMESTAMP' 2017-01-01 00:00:00'" },
{ "partition_name": "P2", "partition_limit": "TIMESTAMP' 2018-01-01 00:00:00'" },
{ "partition_name": "P3", "partition_limit": "TIMESTAMP' 2019-01-01 00:00:00'" },
{ "partition_name": "PMAX", "partition_limit": "MAXVALUE" }
]
},
"primary": { "constraint_name": "TAB1PK", "index_cols": ["COL1","COL2"] },
"indexes": [
{ "index_name": "IND1_1", "index_cols": ["COL2"] ,"unique": "true"},
{ "index_name": "IND1_2", "index_cols": ["COL3","COL4"] ,"unique": "false"}
]
}
vim json2excel.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import sys
import json
import openpyxl
from openpyxl.styles.borders import Border, Side
import re
def main():
args = sys.argv
if len(args) > 1:
file_name = args[1]
else:
file_name = 'tab1.json'
base_name = re.split('\.',file_name)[0]
json_open = open(file_name, 'r')
ddl = json.load(json_open)
wb = openpyxl.Workbook()
ws = wb.active
ws.title = base_name
s1 = Side(style='thin', color='000000')
b1 = Border(top=s1, bottom=s1, left=s1, right=s1)
f1 = openpyxl.styles.PatternFill(patternType='solid',fgColor='DAEEF3', bgColor='DAEEF3')
ws.cell(3,2).value = 'スキーマ名'
ws.cell(4,2).value = 'テーブル名'
ws.cell(5,2).value = 'テーブル論理名'
ws.cell(6,2).value = '備考'
ws.cell(3,3).value = ddl["schema_name"]
ws.cell(4,3).value = ddl["table_name"]
ws.cell(5,3).value = ddl["table_desc"]
# セル罫線(スキーマ名、テーブル名、テーブル論理名、備考)
for i in range(3,7):
for j in range(2,14):
ws.cell(i,j).border = b1
# セル着色(スキーマ名、テーブル名、テーブル論理名、備考)
for i in range(3,7):
ws.cell(i,2).border = b1
# セル連結(スキーマ名、テーブル名、テーブル論理名、備考)
for i in range(3,7):
ws.merge_cells( 'C'+str(i)+':'+'M'+str(i) )
ws.column_dimensions['A'].width = 8.38
ws.column_dimensions['B'].width = 13.64
ws.column_dimensions['C'].width = 14.13
ws.column_dimensions['D'].width = 11.5
ws.column_dimensions['E'].width = 10.38
ws.column_dimensions['F'].width = 4.63
ws.column_dimensions['G'].width = 4.63
ws.column_dimensions['H'].width = 6.88
ws.column_dimensions['I'].width = 24.5
ws.column_dimensions['J'].width = 12
ws.column_dimensions['K'].width = 8.5
ws.column_dimensions['L'].width = 7.5
ws.column_dimensions['M'].width = 7.75
# カラム
ws.cell(8,2).value = '項番'
ws.cell(8,3).value = 'カラム名'
ws.cell(8,4).value = 'カラム論理名'
ws.cell(8,5).value = 'データ型'
ws.cell(8,6).value = '桁数'
ws.cell(8,7).value = '精度'
ws.cell(8,8).value = 'not null'
ws.cell(8,9).value = '備考'
ws.cell(8,10).value = 'パーティション'
# セル罫線(カラムヘッダー)
# セル着色(カラムヘッダー)
for i in range(8,10):
for j in range(2,14):
ws.cell(i,j).border = b1
ws.cell(i,j).fill = f1
# セル連結(カラムヘッダー) B..I
for s in list( map( chr, range(66, 74) ) ):
ws.merge_cells(s+'8'+':'+s+'9')
row_col_start = 10
row_col_end = 10
row = 10
for i,col in enumerate(ddl["cols"]):
ws.cell(row,2).value = i+1
ws.cell(row,3).value = col["col_name"]
ws.cell(row,4).value = col["col_desc"]
for j,s in enumerate( re.split('[,()]',col["col_type"]) ):
ws.cell(row,5+j).value = s
# dateかタイムを含む場合、データ型のみ出力。それ以外は、データ型、桁数、精度を出力
if j==0 and ( s.lower().find('date') >= 0 or s.lower().find('time') >= 0):
break
# not null
if col["not_null"] == "true":
ws.cell(row,8).value = "●"
row = row+1
row_col_end = row
# セル罫線(カラムデータ)
for i in range(row_col_start,row_col_end):
for j in range(2,14):
ws.cell(i,j).border = b1
# パーティション
if len(ddl["partition"]) > 0:
partition_type = ddl["partition"]["partition_type"]
partition_col = ddl["partition"]["partition_col"]
ws.cell(9,10).value = partition_type
for i in range(row_col_start,row_col_end):
if ws.cell(i,3).value == partition_col:
ws.cell(i,10).value = 1
row_part_header = row_col_end + 2
ws.cell(row_part_header,2).value = '項番'
ws.cell(row_part_header,3).value = 'パーティション名'
ws.cell(row_part_header,4).value = 'パーティション条件'
# セル罫線(パーティションヘッダー)
# セル着色(パーティションヘッダー)
for j in range(2,14):
ws.cell(row_part_header,j).border = b1
ws.cell(row_part_header,j).fill = f1
# セル連結(パーティションヘッダー)
ws.merge_cells('D'+str(row_part_header)+':'+'M'+str(row_part_header) )
row_part_start = row_part_header+1
row_part_end = row_part_header+1
row = row_part_header+1
for i,partition_def in enumerate(ddl["partition"]["partition_defs"]):
ws.cell(row,2).value = i+1
# キーがあるかチェック
if "partition_name" in partition_def.keys():
ws.cell(row,3).value = partition_def["partition_name"]
if "partition_limit" in partition_def.keys():
ws.cell(row,4).value = partition_def["partition_limit"]
# セル連結(パーティション条件)
ws.merge_cells('D'+str(row)+':'+'M'+str(row) )
row = row+1
row_part_end = row
# セル罫線(パーティションデータ)
for i in range(row_part_start,row_part_end):
for j in range(2,14):
ws.cell(i,j).border = b1
else:
ws.cell(9,10).value = 'なし'
item_start = 11
item_end = 11
item = 11
# 主キー
if len(ddl["primary"]) > 0:
constraint_name = ddl["primary"]["constraint_name"]
ws.cell(8,item).value = constraint_name
ws.cell(9,item).value = '主キー'
for i,index_col in enumerate(ddl["primary"]["index_cols"]):
for j in range(row_col_start,row_col_end):
if ws.cell(j,3).value == index_col:
ws.cell(j,item).value = i+1
item = item+1
item_end = item
# インデックス
if len(ddl["indexes"]) > 0:
for index in ddl["indexes"]:
index_name = index["index_name"]
unique = index["unique"]
ws.cell(8,item).value = index_name
if unique == 'true':
ws.cell(9,item).value = 'ユニーク'
for i,index_col in enumerate(index["index_cols"]):
for j in range(row_col_start,row_col_end):
if ws.cell(j,3).value == index_col:
ws.cell(j,item).value = i+1
item = item+1
item_end = item
# インデックス3個以上対応
# セル罫線(カラムヘッダー追加分)
for i in range(8,row_col_end):
for j in range(14,item_end):
ws.cell(i,j).border = b1
# セル着色(カラムヘッダー追加分)
for i in range(8,10):
for j in range(14,item_end):
ws.cell(i,j).fill = f1
wb.save(base_name+'.xlsx')
wb.close()
if __name__ == '__main__':
main()
python3 json2excel.py tab1.json
