python Excel转sql

Excel模板：

表名	test
数据类型	String	int
字段名	col1	col2
	a	1

import openpyxl
import openpyxl.utils as utils

"""
excel转sql语句
"""
def excelTosql():
    workbook = openpyxl.load_workbook('D:\\tmp\\Excel转sql模板.xlsx')
    sheet=workbook['Sheet1']
    tbl_name=sheet['B1'].value
    colNum=sheet.max_column
    rowNum=sheet.max_row
    types=[]
    for cell in sheet[2]:
        types.append(cell.value)

    fileds='('

    for col in range(2,colNum+1):
        fileds=fileds+sheet[utils.get_column_letter(col)+'3'].value
        if col==colNum:  #如果是最后一列
            fileds=fileds+')'
        else:
            fileds=fileds+','

    prefix='insert into %s%s values'%(tbl_name,fileds)
    for row in range(4,rowNum+1):
        values='('
        for col in range(2,colNum+1):
            cel_value=sheet[utils.get_column_letter(col)+str(row)].value

            if types[col-1]=='int':
                if cel_value == None:
                    cel_value = 'null'
                data=cel_value
            else:
                if cel_value == None:
                    cel_value = ''
                data="'"+str(cel_value)+"'"
            values=values+str(data)
            if col==colNum:  #如果是最后一列
                values=values+');'
            else:
                values=values+','
        sqlSc=prefix+values
        print(sqlSc)

if __name__=='__main__':
    excelTosql()