python脚本处理------解析excel 表格

用 Python 将 excel 表格中的内容转换成 文本文件形式

使用到的 API

pandas 做 表格解析
argparse 做参数配置
math 函数判断无效数字 nan
re 函数做正则表达式查找

具体代码

import pandas as pd
import argparse
import math
import re

##########  config   argparse   ######
parse = argparse.ArgumentParser();
## 要解析的 excel 表格名称
parse.add_argument('--excel_file','-e',default='./excel',help='excel2txt.py -e ***config excel name')
## 要解析的列的名字，默认都进行解析
parse.add_argument('--ip_name','-i',nargs='+',default='all',help='excel2txt.py -i *** to add generate file ip name')
## 文件保存的地方
parse.add_argument('--load_save','-l',default='./',help='excel2txt.py -s *** save file load')
## 解析的表格中的 sheet 名称
parse.add_argument('--sheet_config','-s',default='erb_interface_config',help='excel2txt.py -s *** config excel sheet')

args = parse.parse_args()

excel_n = args.excel_file
ip_n = args.ip_name
save_load = args.load_save
sheet_config = args.sheet_config
########################################

excel_file = '{}.xlsx'.format(excel_n)

###### read .xlsx file,   alias = erb interface config
df = pd.read_excel(excel_file,sheet_name=sheet_config)
df = df.loc[:,~df.columns.str.contains('^Unnamed')]      
###  remove 'Unnamed: xx' which in the df list
###### take this column to list
labels_row1 = list(df.columns.values)   ### get first row item
#labels_col1 = df['SystemAdapter erb config'].tolist()
labels_col1 = df[labels_row1[0]].tolist()
#print('labels_col1 : ',labels_col1)
#print('labels_row1: ',labels_row1)

##########   ip  list  define    ##########
start_index = labels_row1.index('EX')
end_index = labels_row1.index('Comment')
ip_list = labels_row1[start_index+1:end_index]

def writetxt(save_load,ip_name):
    if (ip_name == 'all'):
        ip_name = ip_list
        for item in ip_name:
            sum_num = 0
            if (item == 'UNNAMED: 18'):
                continue
            ip_index = labels_row1.index(item.upper())
            ipname = item.replace("_",".")
            
            ip_col = df[labels_row1[ip_index]].tolist()
            file_name = '{0}{1}_cfg.rb'.format(save_load,ipname.replace(".","_").lower())
         
            for n in range(len(ip_col)):
                if (math.isnan(ip_col[n])):
                    sum_num += 1
                    #print(sum_num)
            with open(file_name,'w',encoding = 'utf-8') as f:
                f.write('# {0}\nrequire_relative \"./cmn_cfg.rb\"\n'.format(41*'-'))
                f.write("#\n#{0}\n# SA_IP type\n#{0}\nIP_NAME\t\t\t= \"{1}\"\n#{0}\n#Interface erb config".format(41*'-',
                ipname.replace(".","_").lower()))
            
                for m in range(len(ip_col)):
                    if (math.isnan(df[labels_row1[ip_index]].tolist()[m])):
                        if (pd.isna(labels_col1[m])):
                            continue
                        else:
                            f.write('\n#{0}\n# {1}'.format(41*'-',labels_col1[m]))  
                    else:
                        f.write('\n{0}\t\t\t= {1}'.format(labels_col1[m],int(ip_col[m]))) 
                f.close()
        
        print("finish all ip ~~")
    else:
        sum_num = 0
        
        ipname = ip_name[0]
        text_line = re.search('_',ipname)
        if text_line == None:
            ipname = ipname.replace("_",".")
        
        ip_index = labels_row1.index(ipname.upper())
        ip_col = df[labels_row1[ip_index]].tolist()
        file_name = '{0}{1}_cfg.rb'.format(save_load,ipname.replace(".","_").lower())

        for n in range(len(ip_col)):
            if (math.isnan(ip_col[n])):
                sum_num += 1
                #print(sum_num)

        if (sum_num > 13):
            print('IP name : {}    don\'t have parameters !!! '.format(ipname))
            
        with open(file_name,'w',encoding = 'utf-8') as f:
            f.write('# {0}\nrequire_relative \"./cmn_cfg.rb\"\n'.format(41*'-'))
            f.write("#\n#{0}\n# SA_IP type\n#{0}\nIP_NAME\t\t\t= \"{1}\"\n#{0}\n#Interface erb config".format(41*'-',
            ipname.replace(".","_").lower()))
 
            for m in range(len(ip_col)):
                if (math.isnan(df[labels_row1[ip_index]].tolist()[m])):
                    if (pd.isna(df[labels_row1[0]].tolist()[m])):
                        continue
                    else:
                        f.write('\n#{0}\n# {1}'.format(41*'-',labels_col1[m]))  
                else:
                    f.write('\n{0}\t\t\t= {1}'.format(labels_col1[m],int(ip_col[m]))) 
            f.close()

        print('finish {} ip ~~'.format(ipname))

writetxt(save_load,ip_n)