python常用代码

python常用代码

1.excel某一列转为list

def excel_one_line_to_list(standard_anytunnel):
    result = []
    df = pd.read_excel(standard_anytunnel, usecols=[9], names=None)  # 读取第9列，不要列名
    df_li = df.values.tolist()

    for s_li in df_li:
        result.append(s_li[0])
    return result

2.实现vlookup功能

def add_vip_status(src_File, des_Flie):
    df_grade = pd.read_excel(src_File)
    df_grade.head()
    df_sinfo = pd.read_excel(des_Flie)
    df_sinfo.head()
    # 筛选第二张表少量列
    df_sinfo = df_sinfo[["any_tunnel_vip", "status"]]
    df_sinfo.head()
    df_merge = pd.merge(left=df_grade, right=df_sinfo, left_on="vip", right_on="any_tunnel_vip")
    df_merge.head()
    df_merge.to_excel("result.xls", index=False)

3.原有sheet表新增一列

def add_tunnel_type(result_file):
    workbook = xlrd.open_workbook(result_file)
    sheet1 = workbook.sheet_by_name('Sheet1')
    newbook = copy(workbook)
    newsheet = newbook.get_sheet(0)
    for index in range(sheet1.nrows):
        if index == 0:
            newsheet.write(index, 20, label='tunnel_type')
        else:
            newsheet.write(index, 20, label='anytunnel')
    newbook.save(result_file)

4.python读取xml格式的xls文件

用xlrd读xls类型的文件，结果一直报错
xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'<?xml ve'

def html_to_xls(html_file):
    # 拿到xls文件内容
    html = open(html_file, 'r').read()

    # 转换为真正xls格式excel
    try:
        df = pd.read_html(html)
        bb = pd.ExcelWriter(vip_file)
        df[0].to_excel(bb)
        bb.close()
    except Exception as e:
        pass

5.python按行读取txt内容

def read_text(self):
    current_path = os.path.abspath(__file__)
    ip_txt = os.path.abspath(os.path.dirname(current_path) + os.path.sep + ".") + '/../conf/ip.txt'
    ip_content = []
    with open(ip_txt, 'r') as f:
        for line in f.readlines():
            line = line.strip('
')  # 去掉列表中每一个元素的换行符
            ip_content.append(line)
    str_ip_content = ','.join(ip_content)
    return str_ip_content