Python：Excel单元格中英文分离

本工程主要实现了将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)