python--automatically find files based on spreadsheet data

Recently, just contact Python, find a little task to practice practiced hand, I hope that in practice, constantly exercise their ability to solve problems.

The manager has recently set up a very tedious task for me: There is a very big project to be done, but to be audited (or something like that, anyway), submit the process document for the whole project. This project has been done for a whole year, in which the countless meetings, set numerous plans, so also formed a large and small number of documents (drawings, spreadsheets, Word documents, images). The task now is to find the 300 specified files from an audit in a large folder.

It's easy to do this with a copy of the file name in the spreadsheet, and then go to the search bar to search for it and find it. 10 A dozen files look good, more than 300 manual dry is too locked, is not it?

Solution Ideas:

1. Use Python to read 300 file names in the spreadsheet and clear spaces

2. Pass each file name as a parameter to the Windows batch file (. bat), and let it find out if the file exists and return the result

3. Python receives results print out

4. Through the results, again manually confirm whether the program is really not found, whether it is only slightly different names.

Python script:

1 Import OPENPYXL 2 import OS 3 #获得excel文件 4 wb = Openpyxl.load_workbook (' d:/201704.xlsx ') 5 #获取列值 6 def get_delivery (WB): 7     #获取表单名称 8     sheet_names = Wb.get_sheet_names () 9     #获取表10     sheet=wb.get_sheet_by_name (Sheet_names[0]) One     #获取列值, except for the first row of the title of     deliveries = [Cellobj.value for cellobj in sheet[' C '] if cellobj.value! = none]13     return Deliveries14 My_deliverys = get_delivery (WB) + Nofound = []17 # print (my_delivery) +/-delivery in My_deliverys:19
  if Os.system (' search.bat%s '% delivery) = = 1:20         nofound.append (delivery) for x in nofound:23     if x! = ' Deliverable Column Table: ':         print ("File not found:%s"%x) print ("End of Program")

Search.bat Code:

1 @echo off 2  3 Set "filename=%1" 4 Set "Filepath=d:\" 5 echo 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 "%file Name% "(9     echo,%%b10   ) 11)

Ah ~ ~ Suddenly the mood is good, but also can escape from the complicated work to bask in the sun to drink tea ...