本工程主要实现了将Excel单元格中的中英文进行分离,保存到另外一个Excel文件的两列单元格。
import openpyxl
import pandas as pd
dev_file = '.xls'
dev_sheet = ''
dev_file_new = '.xls'
excel_sheet = pd.read_excel(dev_file, sheet_name=dev_sheet, engine="xlrd")
new_sheet_data = {'厂家': [], '型号': []}
for i in range(0, excel_sheet.iloc[:,0].size): # excel_sheet.iloc[:,0].size
#print("型号[" + str(i) + "]" + excel_sheet['型号'][i])
value_ori = excel_sheet['型号'][i]
if(type(value_ori) != float):
len_ori = len(value_ori)
if (len_ori != 0):
len_changjia = 0
index_xinghao = 0
for value in value_ori:
if '\u4e00' <= value <= '\u9fa5':
len_changjia += 1
else:
index_xinghao = len_changjia
if value == ' ':
index_xinghao += 1
break
value_changjia = value_ori[0:len_changjia]
value_xinghao_new = value_ori[index_xinghao:len_ori]
new_sheet_data['厂家'].append(value_changjia)
new_sheet_data['型号'].append(value_xinghao_new)
else:
new_sheet_data['厂家'].append("")
new_sheet_data['型号'].append("")
print(i)
df_dict = pd.DataFrame.from_dict(new_sheet_data)
df_dict.to_excel(dev_file_new, dev_sheet, index=False)
改进:添加部分英文厂家。
import openpyxl
import pandas as pd
dev_file = '.xls'
dev_sheet = ''
dev_file_new = '.xls'
list_vendors_len3 = ['', '', '', '', '', '']
list_vendors_len4 = ['', '']
list_vendors_len7 = ['']
excel_sheet = pd.read_excel(dev_file, sheet_name=dev_sheet, engine="xlrd")
new_sheet_data = {'厂家': [], '型号': []}
for i in range(0, excel_sheet.iloc[:,0].size): # excel_sheet.iloc[:,0].size
#print("型号[" + str(i) + "]" + excel_sheet['型号'][i])
value_ori = excel_sheet['型号'][i]
value_vendor = ""
value_product = ""
if (type(value_ori) != float):
len_ori = len(value_ori)
if (len_ori != 0):
len_vendor_Chinese = 0
len_vendor_English = 0
len_vendor = 0
index_product = 0
for value in value_ori:
if '\u4e00' <= value <= '\u9fa5':
if (len_vendor_English > len_vendor_Chinese):
break
len_vendor_Chinese += 1
else:
if (len_vendor_Chinese > 0):
len_vendor = len_vendor_Chinese
index_product = len_vendor
if value == ' ':
index_product += 1
break
else:
if 'A' <= value <= 'Z':
len_vendor_English += 1
if len_vendor_English > 7:
break
elif value == ' ':
if (len_vendor_English == 3):
for vendor in list_vendors_len3:
if (value_ori[0:3] == vendor):
len_vendor = 3
index_product = 4
break
elif (len_vendor_English == 4):
for vendor in list_vendors_len4:
if (value_ori[0:4] == vendor):
len_vendor = 4
index_product = 5
break
elif (len_vendor_English == 7):
for vendor in list_vendors_len7:
if (value_ori[0:7] == vendor):
len_vendor = 7
index_product = 8
break
else:
break
else:
break
value_vendor = value_ori[0:len_vendor]
value_product = value_ori[index_product:len_ori]
new_sheet_data['厂家'].append(value_vendor)
new_sheet_data['型号'].append(value_product)
print(i)
df_dict = pd.DataFrame.from_dict(new_sheet_data)
df_dict.to_excel(dev_file_new, dev_sheet, index=False)