Python查找目录下EXCEL文件包含指定内容的批注位置

布满荆棘的人生 2021-11-05 15:20 325阅读 0赞

import os
    from openpyxl import load_workbook
    path=r"C:\git"
    select = input("select:")
    def del_file(path):
        ls = os.listdir(path)
        for i in ls:
            c_path = os.path.join(path, i)
            if os.path.isdir(c_path):
                del_file(c_path)
            elif os.path.splitext(c_path)[1] == '.xlsx' and r"~$" not in r''+c_path:
                workbook  = load_workbook(c_path)
                worksheets = workbook.sheetnames
                for sheet in worksheets:
                    count = 0
                    worksheet1 = workbook[u''+sheet]
                    num_rows = worksheet1.max_row
                    num_cols = worksheet1.max_column
                    for rown in range(num_rows):
                        for coln in range(num_cols):
                            cell = worksheet1.cell(rown+1,coln+1).comment
                            if cell is not None and select in str(cell):
                                print(c_path)
                                print("row:"+str(rown+1)+"  col:"+str(coln+1))
                                count += 1
                                break
                        if count > 0:
                            break
                    if  count > 0:
                        break
    del_file(path)

如果想将查找到的批注保存到txt文件中：

import os,re
    from openpyxl import load_workbook
    path=r"C:\git"
    file = open(r'jx.txt','wb')
    
    def del_file(path):
        ls = os.listdir(path)
        for i in ls:
            c_path = os.path.join(path, i)
            if os.path.isdir(c_path):
                del_file(c_path)
            elif os.path.splitext(c_path)[1] == '.xlsx' and r"~$" not in r''+c_path:
                workbook  = load_workbook(c_path)
                worksheets = workbook.sheetnames
                for sheet in worksheets:
                    count = 0
                    worksheet1 = workbook[u''+sheet]
                    num_rows = worksheet1.max_row
                    num_cols = worksheet1.max_column
                    for rown in range(num_rows):
                        for coln in range(num_cols):
                            cell = worksheet1.cell(rown+1,coln+1).comment
                            if cell is not None:
                                file.write(cell.text.encode())
                                print(cell.text)
    del_file(path)
    file.close()