sql python excel_python解析EXCEL到SQL

# coding: utf-8

"""

# @Time : 2020/11/26 15:32

# @Author : Gina Gao

# @File :

# @Software: PyCharm

# @Descript:

"""

import openpyxl

import time

dbname={

'CH_DB_MID':'CHDB',

'CGENIUS' :'CG',

'PGENIUS' :'PG',

'DATAPOOL':'DP'

}

tranconfig={

'CH_DB_MID|CGENIUS':'TRAN_CHDB_C',

'CGENIUS|PGENIUS' :'TRAN_C_P',

'DATAPOOL|CGENIUS' :'TRAN_DP_C',

'DATAPOOL|PGENIUS' :'TRAN_DP_P',

'DATAPOOL|CUSTOMIZE':'TRAN_DP_CU'

}

path=r'E:\数据\Z.规划设计部\1-策划工作规范\002-策划案&转换文档模板.xlsx'

file_path=r'E:\转换开发2020\\'

#读表操作

workbook=openpyxl.load_workbook(path)

sheet_names = workbook.sheetnames

sheet=workbook['策划案转换文档模板'] ##等价sheet=workbook[sheet_names[1]]

# print(sheet.cell(1,2).value)

dbs=dbname.get(sheet['B4'].value)

dbm=dbname.get(sheet['B5'].value)

tran=tranconfig.get("{}|{}".format(sheet['B4'].value,sheet['B5'].value))

row1='USE'+'\t'+''+tran+'\n'+'GO'+'\n'

row1+=('/' + '*'*66)

row1+='\n'

codename=sheet['B1'].value

tabname=dbs+'_'+sheet['C8'].value+'_2_'+dbm+'_'+sheet['I8'].value

procname="{}:{}_{}".format('名 称',codename,tabname)

file_name111="{}_{}".format(codename,tabname)

row1+=procname

row1+='\n'

row1+=('源 表:'+ sheet['B4'].value+'\t'+sheet['B8'].value+'\t'+sheet['C8'].value)

row1+='\n'

row1+=('目的表:'+sheet['B5'].value+'\t'+sheet['H8'].value+'\t'+sheet['I8'].value)

row1+='\n'

row1+=('创 建:'+'GINA'+'\t'+time.strftime("%Y-%m-%d", time.localtime()) )

row1+='\n'

row1+=('文档路径:'+path)

row1+='\n'

row1+=('示 例:' +'\n'+'select ' + '\''+sheet['I8'].value+ '\''+','+'\''+tabname+'\''+','+'\''+sheet['I8'].value+ '\''+'\n'+'from '+sheet['B4'].value+'..'+sheet['C8'].value+'\n'+ 'where isvalid=1' )

row1+='\n'

row1+=('*'*66+ '/')

row1+='\n'

row1+=('CREATE PROC '+ dbs+'_'+sheet['C8'].value+'_2_'+dbm+'_'+sheet['I8'].value+'AS (@SEQ INT)'+'\n')

row1+=('AS'+'\n')

row1+=('BEGIN'+'\n')

row1+=('\tSELECT'+'\n')

row1+=('\t SEQ AS SEQ'+'\n')

row1+=('\t'+','+'GETDATE() AS MTIME'+'\n')

row1+=('\t'+','+'ISVALID AS ISVALID'+'\n')

max_len = max(len(i[1].value) for i in sheet[10:27])

for i in sheet[10:27]:

row1 +=('\t,'+i[1].value.ljust(max_len,' ')+' as '+i[7].value)

row1 += '\n'

row1+=('FROM '+sheet['B4'].value+'..'+sheet['C8'].value+'\n'+'where isvalid=1 and SEQ=@SEQ'+'\n' +'\n')

row1+=('END'+'\n')

file_name ='{}{}.sql'.format(file_path,file_name111)

with open(file_name,'w') as file_obj:

file_obj.write(row1)