vim excel2json.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import json
import sys
import openpyxl
import re
def main():
args = sys.argv
if len(args) > 1:
file_name = args[1]
else:
file_name = 'tab1.xlsx'
base_name = re.split('\.',file_name)[0]
wb = openpyxl.load_workbook(filename=file_name, read_only=True)
ws = wb.worksheets[0]
f1 = open(base_name+'.json', mode='w', encoding='utf-8', newline='\n')
# カラム数取得
# パーティション数取得
# パーティション開始行取得
partion_cnt = 0
row_part_header = ws.max_row+1
col_cnt = 0
flg = 0
for i in range(10,ws.max_row+1):
if flg == 0 and ws.cell(i,2).value is not None:
col_cnt += 1
#最初にヌルの行が見つかったらflgを1にセット
if ws.cell(i,3).value is None:
flg = 1
if flg == 1 and ws.cell(i,2).value == "項番":
row_part_header = i
if i > row_part_header and ws.cell(i,2).value is not None:
partion_cnt += 1
# 主キー数取得
# インデックス数取得
primary_cnt = 0
index_cnt = 0
if ws.cell(9,11).value == "主キー":
primary_cnt = 1
for i in range(11 + primary_cnt,100):
if ws.cell(8,i).value is None:
break
index_cnt += 1
f1.write('{\n')
f1.write(' "schema_name": "'+ws.cell(3,3).value+'",\n')
f1.write(' "table_name": "'+ws.cell(4,3).value+'",\n')
f1.write(' "table_desc": "'+ws.cell(5,3).value+'",\n')
f1.write(' "cols": [\n')
for row in ws.iter_rows(min_row=10):
if row[1].value is None:
break
if row[5].value is not None and row[6].value is not None:
d1 = row[4].value+'('+row[5].value+','+row[6].value+')'
elif row[5].value is not None and row[6].value is None:
d1 = row[4].value+'('+row[5].value+')'
else:
d1 = row[4].value
if row[7].value == '●':
d2 = "true"
else:
d2 = "false"
if row[1].value != col_cnt:
d3 = ","
else:
d3 = ""
f1.write(' {{ "col_name": "{}", "col_type": "{}", "not_null": "{}", "col_desc": "{}" }}{}\n'.format(row[2].value,d1,d2,row[3].value,d3) )
f1.write(' ],\n')
# パーティション出力
if ws.cell(9,10).value == "なし":
f1.write(' "partition": {},\n')
else:
f1.write(' "partition": {\n')
f1.write(' "partition_type": "{}",\n'.format(ws.cell(9,10).value) )
part_key = ""
# パーティションキーの取得
for row in ws.iter_rows(min_row=10):
if row[9].value == 1:
part_key = row[2].value
break
if row[1].value is None:
break
f1.write(' "partition_col": "{}",\n'.format(part_key) )
f1.write(' "partition_defs" : [\n')
for row in ws.iter_rows(min_row=row_part_header+1):
if row[1].value is None:
break
if row[1].value != partion_cnt:
d3 = ","
else:
d3 = ""
# hashの場合はpartion_nameの出力
if ws.cell(9,10).value.lower() != "hash":
f1.write(' {{ "partition_name": "{}", "partition_limit": "{}" }}{}\n'.format(row[2].value,row[3].value,d3) )
else:
f1.write(' {{ "partition_name": "{}" }}{}\n'.format(row[2].value,d3) )
f1.write(' ]\n')
f1.write(' },\n')
# 主キー出力
if primary_cnt == 0:
f1.write(' "primary": {},\n')
else:
# 主キーカラムの取得
col_position = 1
primary_key_cols = ""
for i in range(1, col_cnt +1):
for row in ws.iter_rows(min_row=10):
if row[1].value is None:
break
if row[10].value == col_position:
primary_key_cols += '"'+row[2].value +'"'+ ","
break
col_position += 1
primary_key_cols = primary_key_cols[:-1]
f1.write(' "primary": {{ "constraint_name": "{}", "index_cols": [{}] }},\n'.format(ws.cell(8,11).value,primary_key_cols) )
# インデックス出力
if index_cnt == 0:
f1.write(' "indexes": [[]]\n')
else:
f1.write(' "indexes": [\n')
for i in range(1,index_cnt+1):
index_name = ws.cell(8,10+ primary_cnt + i ).value
if i != index_cnt:
d3 = ","
else:
d3 = ""
if ws.cell(9,10+ primary_cnt + i ).value == "ユニーク":
d4 = "true"
else:
d4 = "false"
# インデックスカラムの取得
col_position = 1
index_cols = ""
for j in range(1, col_cnt +1):
for row in ws.iter_rows(min_row=10):
if row[1].value is None:
break
if row[9 + primary_cnt + i].value == col_position:
index_cols += '"'+row[2].value +'"'+ ","
break
col_position += 1
index_cols = index_cols[:-1]
f1.write(' {{ "index_name": "{}", "index_cols": [{}] ,"unique": "{}"}}{}\n'.format(index_name,index_cols,d4,d3) )
f1.write(' ]\n')
f1.write('}\n')
wb.close()
f1.close()
if __name__ == '__main__':
main()
python3 excel2json.py tab1.xlsx
