def interface_excel_to_database(file_path, sheet_name = '接口基础数据'):
'''将excel里sheet页为interface_base_data的内容写入数据库interface_base_data表中'''
xh = xlrd_handle()
xh.set_file_path(file_path)
xh.set_sheet_name(sheet_name)
xh.open_workbook()
xh.open_sheet()
interface_data = xh.value_to_array()
if interface_data:
for i in range(1, len(interface_data)):
sql = "INSERT INTO interface_base_data (interface_tag,project,main_module,second_module,third_module,method,url,headers,body,is_check,checkpoint,interface_status,remark,author,create_time,modify_time) VALUES (\'" +\
str(interface_data[i][0]) + "\',\'" + str(interface_data[i][1]) + "\',\'" + str(interface_data[i][2]) + "\',\'" + str(interface_data[i][3]) + "\',\'" + str(interface_data[i][4]) + "\',\'" + \
str(interface_data[i][5]) + "\',\'" + str(interface_data[i][6]) + "\',\'" + str(interface_data[i][7]) + "\',\'" + str(interface_data[i][8]) + "\',\'" + str(interface_data[i][9]) + "\',\'" + \
str(interface_data[i][10]) + "\',\'" + str(interface_data[i][11]) + "\',\'" + str(interface_data[i][12]) + "\',\'" + str(interface_data[i][13]) + "\', now(),now())"
sql_exc(sql)
def process_excel_to_database(file_path, sheet_name = '流程记录'):
'''将excel里sheet页为process_record的内容写入数据库process_record表中'''
xh = xlrd_handle()
xh.set_file_path(file_path)
xh.set_sheet_name(sheet_name)
xh.open_workbook()
xh.open_sheet()
interface_data = xh.value_to_array()
if interface_data:
for i in range(1, len(interface_data)):
sql = "INSERT INTO process_record (project,task,process_tag,main_scene,second_scene,third_scene,process_status,is_exc,interface_tag,input_parameter,output_parameter,new_checkpoint,check_status,max_exc_num,max_fail_exc_num,success_jump,fail_jump,remark,author,create_time) VALUES (\'" +\
str(interface_data[i][0]) + "\',\'" + str(interface_data[i][1]) + "\',\'" + str(interface_data[i][2]) + "\',\'" + str(interface_data[i][3]) + "\',\'" + str(interface_data[i][4]) + "\',\'" + \
str(interface_data[i][5]) + "\',\'" + str(interface_data[i][6]) + "\',\'" + str(interface_data[i][7]) + "\',\'" + str(interface_data[i][8]) + "\',\'" + str(interface_data[i][9]) + "\',\'" + \
str(interface_data[i][10]) + "\',\'" + str(interface_data[i][11]) + "\',\'" + str(interface_data[i][12]) + "\',\'" + str(interface_data[i][13]) + "\',\'" + str(interface_data[i][14]) + "\',\'" + \
str(interface_data[i][15]) + "\',\'" + str(interface_data[i][16]) + "\',\'" + str(interface_data[i][17]) + "\',\'" + str(interface_data[i][18]) + "\', now())"
sql_exc(sql)
def columns_to_excel(table_name, file_path):
'''
数据库指定表字段名写入excel
'''
columns_result = get_table_all_columns(table_name)
if columns_result:
if columns_result['row_num'] == 1:
write_data_temp = columns_result['data'][0][0].split(',')
write_data = []
write_data.append(write_data_temp)
range_num = len(write_data_temp)
xh = xlrd_handle()
xh.set_file_path(file_path)
xh.set_sheet_name(table_name)
xh.open_workbook()
xh.open_sheet()
xh.write_to_sheet(write_data, [0], range(range_num))
def database_to_excel(table_name, file_path):
'''
数据库指定表数据写入excel
'''
sql_result = get_table_all_columns(table_name)
if sql_result:
if sql_result['row_num'] > 0:
sql = "select " + sql_result['data'][0][0] + " from " + table_name
write_data = sql_exc(sql)
if write_data:
if write_data['row_num'] > 0:
range_num = len(write_data['data'][0])
xh = xlrd_handle()
xh.set_file_path(file_path)
xh.set_sheet_name(table_name)
xh.open_workbook()
xh.open_sheet()
xh.write_to_sheet(write_data['data'],range(1,write_data['row_num'] + 1),range(range_num))
from public.xlrd_handle import xlrd_handle
#excel写
a = (['a', 'b', 'c', 4], [5, 6, 7, 8], [9, 10, 11, 12])
xh = xlrd_handle()
xh.set_sheet_name('create')
xh.set_file_path(r'C:\Users\Administrator\Desktop\abc.xls')
xh.write_to_sheet(a, [1, 3, 5], range(5, 9))
