xlrd package 在excel文件模糊搜索中的应用

通过搜索excel文件的关键词定位指标所在行的信息，获取指标的相应信息。
import pandas as pd
import xlrd
import os
import warnings
#warnings.filterwarnings("ignore")


#构建236个公司的字典
companys=[]
for k in range(236):
    new_company={'excel_name':'','com_name':'','bvd_number':'','status':'','country':'','state':'','county':'','DOI':'','NLF':'','NAICS':'','NAICS_NAME':''
    ,'SIC':'','SIC_NAME':''}
    companys.append(new_company)
def del_null(ls):
    while '' in ls:
        ls.remove('')
    return ls



#---------------------------------企业名称，bvd_number的获取
files=os.listdir('D:/company')
for k,j in enumerate(files):
    excel=r'D:/company/%s'%j
    companys[k]['excel_name']=j
    data=xlrd.open_workbook(excel)
    sheet=data.sheets()[0]
    companys[k]['com_name']=sheet.cell_value(0,1)
    #不规则名称
    if companys[k]['com_name']=='':
        companys[k]['com_name']=sheet.cell_value(0,2)
    bvds=del_null(sheet.row_values(3))
    #print(bvds)
    if bvds:
        companys[k]['bvd_number']=bvds[2]
    #不规则bvd_number
    if companys[k]['bvd_number']=='':
        bvds=del_null(sheet.row_values(4))
        if bvds:
            companys[k]['bvd_number']=bvds[2]


#-------------------------------status,country,county,state,Date of Incorporation, National Legal Form, NAICS ,SIC获取
for k,j in enumerate(files):
    excel=r'D:/company/%s'%j
    data=xlrd.open_workbook(excel)
    sheet=data.sheets()[0]
    for rowidx in range(sheet.nrows):
            row = sheet.row(rowidx)
            for colidx, cell in enumerate(row):
                    if cell.value == "Status":
                        #print(j,del_null(sheet.row_values(rowidx)))
                        if len(del_null(sheet.row_values(rowidx)))==2:
                            companys[k]['status']=del_null(sheet.row_values(rowidx))[1]
                    if cell.value == "India":
                        companys[k]['country']='India'
                    if cell.value == "England":
                        companys[k]['country']='England'
                    if cell.value == "Nepal":
                        companys[k]['country']='Nepal'
                    if cell.value == "State":
                        #print(j,del_null(sheet.row_values(rowidx)))
                        if len(del_null(sheet.row_values(rowidx)))==2:
                            companys[k]['state']=del_null(sheet.row_values(rowidx))[1]
                    if cell.value == "County":
                        #print(j,del_null(sheet.row_values(rowidx)))
                        if len(del_null(sheet.row_values(rowidx)))==2:
                            companys[k]['county']=del_null(sheet.row_values(rowidx))[1]
                    if cell.value == "Date of incorporation":
                        #print(j,del_null(sheet.row_values(rowidx)))
                        if len(del_null(sheet.row_values(rowidx)))==4:
                            companys[k]['DOI']=del_null(sheet.row_values(rowidx))[1]
                    if cell.value == "National legal form":
                        #print(j,del_null(sheet.row_values(rowidx)))
                        if len(del_null(sheet.row_values(rowidx)))==4:
                            companys[k]['NLF']=del_null(sheet.row_values(rowidx))[1]
                    if cell.value == "NAICS 2017 code(s) {derived from NIC 2008 codes}":
                        #print(j,del_null(sheet.row_values(rowidx+2)))
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]
                    if cell.value == "NAICS 2017 code(s) {derived from US SIC codes}":
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]

                    if cell.value == "NAICS 2017 code(s) {derived from NSIC codes}":
                        #print(j,del_null(sheet.row_values(rowidx+2)))
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]
                    if cell.value == "NACE Rev. 2 code(s) {derived from UK SIC (2007) codes}":
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['NAICS']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['NAICS_NAME']=del_null(sheet.row_values(rowidx+2))[2]

                    if cell.value == "US SIC code(s) {derived from NIC 2008 codes}":
                        #print(j,del_null(sheet.row_values(rowidx+2)))
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]
                    if cell.value == "US SIC code(s)":
                        #print(j,del_null(sheet.row_values(rowidx+2)))
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]
                    if cell.value == "US SIC code(s) {derived from UK SIC (2007) codes}":
                        #print(j,del_null(sheet.row_values(rowidx+2)))
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]
                    if cell.value == "US SIC code(s) {derived from NSIC codes}":
                        #print(j,del_null(sheet.row_values(rowidx+2)))
                        if len(del_null(sheet.row_values(rowidx+2)))==3:
                            companys[k]['SIC']=del_null(sheet.row_values(rowidx+2))[0]
                            companys[k]['SIC_NAME']=del_null(sheet.row_values(rowidx+2))[2]


df=pd.DataFrame(companys)
print(df)
df.to_csv('d:/基本指标.csv')
#---------------------------------------------测试