Python 处理excel单元格字符串

Posted by Dapenson on May 28, 2020

Python 处理excel单元格字符串

使用Python对指定工作表中指定列的指定范围内每一个单元格进行字符串处理

示例程序

import openpyxl
from eprogress import LineProgress



key_cell = "F"
start_cell = 2
end_cell = 201
workbook_to_do = '2'
file_name_old = "要处理的文件名"
file_name_new = "处理后的另存文件名"

# 加载 excel 文件
wb = openpyxl.load_workbook(f'{file_name_old}.xlsx')

# 得到sheet对象
sheet = wb[f'{workbook_to_do}']


def str_deal(str_in):
    # 删除字符串中的空行和识别失败字符
    str_tmp = str_in
    str_tmp = str_tmp.replace('\n\n', '')
    str_tmp = str_tmp.replace('*|||*识别失败', '')
    str_tmp = str_tmp.replace('识别失败*|||*', '')
    str_out = str_tmp.replace('*|||*\n识别失败', '')
    return str_out


line_progress = LineProgress(title='处理进度')
for cell in range(start_cell, end_cell+1):
    if sheet[f'{key_cell}{cell}'].value:
        sheet[f'{key_cell}{cell}'] = str_deal(sheet[f'{key_cell}{cell}'].value)
        print(f"  正在处理 {key_cell}{cell} ")
        line_progress.update(cell)
        # time.sleep(0.05)


# 指定不同的文件名,可以另存为别的文件
wb.save(f'{file_name_new}.xlsx')

参考教程

白月黑羽 -读写Excel