python读取当前目录下的所有的excel文件

1,使用dos中的dir命令搜索

查看dir用法

dir /？

搜索.xlsx文件，选项/b是除去多余的信息，/s递归搜索，包含当前目录的子目录。

dir *.xlsx /b /s 

结果输出到文件，供其他程序调用

dir *.xlsx /b /s >filelist.txt

2, 用python自己的函数os.walk()

列出当前文件夹及其子文件夹中的所有文件

在根据后缀选出.xlsx

import os
filelist=[]

for root, dirs, files in os.walk(".", topdown=False):
    for name in files:
        str=os.path.join(root, name)
        if str.split('.')[-1]=='xlsx':
            filelist.append(str)

filelist

3，下面是一个用openpyxl库处理表格的样例。

import os
from openpyxl import Workbook    
from openpyxl import load_workbook

os.system("DIR *.* /B>list.txt")
f = open("list.txt", "r")
name=[]
while True:    
    line = f.readline()
    if line:        
        line=line[0:len(line)-1] #delete char '\n'
        #print (line)
        if  line.endswith('xlsx',len(line)-4,len(line) ):
            name.append(line)
    else:    
        break 
		
if len(name)==0:
	print('No .xlsx file in this directory')
	exit()

outbook = Workbook()   #输出book对象  
outsheet = outbook.active   #获取book的sheet   

rbook = load_workbook( name[0] ) 
rsheet = rbook.get_sheet_names()  
sheet1 = rbook.get_sheet_by_name(rsheet[0])
for j in range(1,12):
    outsheet.cell(row=1,column=j).value = sheet1.cell(row=1,column=j).value
    
outr=2
for n in name:
    print('name: ',end='')
    print(n)
    rbook = load_workbook( n ) 
    rsheet = rbook.get_sheet_names()  
    sheet1 = rbook.get_sheet_by_name(rsheet[0])
    
    for i in range(1,9):       
        if sheet1.cell(row=i,column=2).value==None and not sheet1.cell(row=i,column=3).value == None:
            for j in range(1,10):
                outsheet.cell(row=outr,column=j).value = sheet1.cell(row=i,column=j).value
                
                print ( sheet1.cell(row=i,column=j).value )
            break;
    outr=outr+1
outbook.save('out.xlsx')  #保存输出