Python-automatically searches for files based on the workbook data,

Python-automatically searches for files based on the workbook data,

Recently, I just got started with python and found some small tasks to train my hands. I hope I can continue to improve my problem solving skills in practice.

Recently, the manager assigned me a very tedious task: a very important project has been completed, but it has to be audited (similar approval and so on ), the process file of the entire project to be submitted. This project has been completed for a whole year, including countless meetings and countless solutions. Therefore, many files (drawings, workbooks, Word documents, and images) have been created, large and small ). The current task is to find the specified 300 files to be audited from a large folder.

It is easy to do this by copying the file name in the workbook, searching it in the search bar, and finding it. It's okay to look for more than 10 files. It's too much anti-lock to do more than 300 manual jobs, isn't it?

Solution:

Python script:

1 import openpyxl 2 import OS 3 # obtain the excel file 4 wb = openpyxl. load_workbook ('d:/201704.xlsx ') 5 # obtain the column value 6 def get_delivery (wb): 7 # obtain the form name 8 sheet_names = wb. get_sheet_names () 9 # obtain table 10 sheet = wb. get_sheet_by_name (sheet_names [0]) 11 # Get the column value, except for the first row Title 12 deliveries = [cellobj. value for cellobj in sheet ['C'] if cellobj. value! = None] 13 return deliveres14 15 my_deliverys = get_delivery (wb) 16 nofound = [] 17 # print (my_delivery) 18 for delivery in my_deliverys: 19 if OS. system ('search. bat % s' % delivery) = nofound. append (delivery) 21 22 for x in nofound: 23 if x! = 'Deliverable list: ': 24 print ("file not found: % s" % x) 25 print ("program ended ")

Search. bat code:

1 @ echo off 2 3 set "FileName = % 1" 4 set "FilePath = D: \" 5 echo is searching for files... 6 7 for/f "delims =" % B in ('dir/a-d/s/B "% FilePath % \ * % FileName %" 2 ^> nul ') do (8 if/I "% ~ Nxb "equ" % FileName % "(9 echo, % b10) 11)

Ah ~~ All of a sudden, the mood is good, and you can escape from the complex work in the sun to drink tea...