bytes_in = io.BytesIO(data)
wb = openpyxl.load_workbook(bytes_in) # 打开已存在的execl文件,格式必须xlsx
ws = wb[wb.sheetnames[0]] # 选择第一张sheet表
rows = ws.max_row # 获取表的最大行数
columns = ws.max_column # 获取表的最大列数
column_heading = [ws.cell(row=1,column=x).value for x in range(1,columns+1)] # 读取excel第一行的值,写入list
column_name = ['单位名称','委托项目名称','单位地址','邮政编码','传真','联系人','联系人电话','联系人邮箱','客户行业','星级','所属区域']# excel必需字段
# print(column_heading) # 文件第一行title
if len([name for name in column_name if name not in column_heading]) == 0: # 返回字段组成的list为空,则说明文件列标题未包含MySQL需要的字段
print(' - 检查完成,执行写入')
# 判断Excel中各字段所在列号
unit_name = column_heading.index(column_name[0])
entrust_project_name = column_heading.index(column_name[1])
unit_address = column_heading.index(column_name[2])
mail_code = column_heading.index(column_name[3])
fax = column_heading.index(column_name[4])
contacts = column_heading.index(column_name[5])
contacts_phone = column_heading.index(column_name[6])
contacts_email = column_heading.index(column_name[7])
customer_industry = column_heading.index(column_name[8])
customer_level = column_heading.index(column_name[9])
region = column_heading.index(column_name[10])
try:
if ws.cell(row=2,column=1).value == None:
table_start_line = 3
else:
table_start_line = 2
data1 = []
for row in range(table_start_line, rows + 1):
for column in range(1, columns + 1): # 因为从第1列开始,所以此处从1开始
data1.append(str(ws.cell(row=row, column=column).value) if ws.cell(row=row, column=column).value != None else " ") # 以字符串形式保存数据到MySQL为None转为空数据
time = datetime.utcnow() + timedelta(hours=8)
Customer.create(
name=name,
unit_name=data1[unit_name],
entrust_project_name=data1[entrust_project_name],
unit_address=data1[unit_address],
mail_code=data1[mail_code],
fax=data1[fax],
create_time=time,
update_time=time,
contacts=data1[contacts],
contacts_phone=data1[contacts_phone],
contacts_email=data1[contacts_email],
customer_industry=data1[customer_industry],
customer_level=data1[customer_level],
region=data1[region],
status=0
)
data1 = []
msg = '导入成功'
except Exception as e:
msg = '导入失败' + str(e)
else:
msg = '文件列标题不完全包含数据库需要的字段,请检查文件。'
wb.close() # 关闭excel
create_stat("create_data", name)
return{'code': 0,'msg':msg}