import pandas as pd

import numpy as np

filename ='demo_text.sql'

# 讀取excel

def readExcel(excelPath, sheet_name):

df = pd.read_excel(excelPath, sheet_name=sheet_name, header=None)

# 最大行

? ? nrows = df.shape[0]

print("行數(shù):\n" +str(nrows))

# 最大列

? ? ncols = df.columns.size

print("列數(shù):\n" +str(ncols))

for rowIndexin range(nrows):

source = df.iloc[rowIndex, 1]

npp = df.iloc[rowIndex, 2]

fuseCode = df.iloc[rowIndex, 3]

newFuseCode = df.iloc[rowIndex, 5]

sql =""

? ? ? ? print("source:" +str(source) +",npp:" +str(npp) +",fuseCode:" +str(fuseCode) +",newFuseCode:" +str(newFuseCode) +"\n")

if str(fuseCode) =="-" and str(npp) !="-":

if str(source) =="Others":

if str(newFuseCode) =="-":

sql ="update demo_table1 set reason = 'CancelOtherReInputReason' where fuse_policy_code = '" +str(

fuseCode) +"' ;"

? ? ? ? ? ? ? ? else:

sql ="update demo_table1 set new_fuse_code ='" +str(

newFuseCode).strip() +"',reason = 'CancelOtherReInputReason' where fuse_policy_code = '" +str(

fuseCode) +"' ;"

? ? ? ? ? ? if str(source) =="BACKDOOR" or str(source) =="Backdoor":

if str(newFuseCode) =="-":

sql ="update demo_table2?set reason = 'CancelOtherReInputReason' where npp = '" +str(npp) +"' ;"

? ? ? ? ? ? ? else:

sql ="update demo_table2?set new_fuse_code ='" +str(newFuseCode).strip() +"',reason = 'CancelOtherReInputReason' where npp = '" +str(npp) +"' ;"

? ? ? ? ? ? if str(source) =="Offline" or str(source) =="OFFLINE":

if str(newFuseCode) =="-":

sql ="update demo_table3?set reason = 'CancelOtherReInputReason' where associated_policy_code = '" +str(

npp) +"' ;"

? ? ? ? ? ? ? ? ? ? sql = sql +"\n" +"update demo_table4?set reason = 'CancelOtherReInputReason' where associated_policy_code = '" +str(

npp) +"' ;"

? ? ? ? ? ? ? ? else:

sql ="update demo_table3?set new_fuse_code ='" +str(

newFuseCode).strip() +"',reason = 'CancelOtherReInputReason' where associated_policy_code = '" +str(

npp) +"' ;"

? ? ? ? ? ? ? ? ? ? sql = sql +"\n" +"update demo_table4?set new_fuse_code ='" +str(newFuseCode).strip() +"',reason = 'CancelOtherReInputReason' where associated_policy_code = '" +str(

npp) +"' ;"

? ? ? ? ? ? print("==================================\n")

print(sql)

txt_file =open(filename, "a", encoding="utf-8")# 以寫的格式打開先打開文件

? ? ? ? ? ? txt_file.write(sql)

txt_file.write("\n")

txt_file.close()

# Main 入口

if __name__ =="__main__":

print("this is a excel \n")

readExcel("/Users/xiaobao/Desktop/rev_ops-manual.xlsx", "Sheet2")