Skip to main content
 首页 » 编程设计

用于创建新的 Excel 工作簿并填充单元格的 Python 函数

2024年10月25日8cmt

我想编写一个 Python 函数,它将循环遍历 Excel (os.walk) 目录,将函数应用于每个文件,并使用 _edit.xlsx' 扩展名 ( '文件名*_edit**.xlsx')。我正在使用 xlsxWriter。

这是我必须处理的内容。此代码一次只对一个文件执行我想要的操作,但我希望它适用于我的文件夹目录中的所有文件:

os.chdir('\\file_directory') 
root ='\\file_directory'\\ 
allfiles = [os.path.join(root,f) for root, dirs, files in walk(root) for f in files] 
 
#the following should be written as a function to be applied to 'allfiles' in 'root' directory 
file_location ='\\file directory' 
workbook = xlrd.open_workbook(file_location) 
passenger = workbook.sheet_by_index(0) 
hvyTruck = workbook.sheet_by_index(1) 
ltTruck = workbook.sheet_by_index(2) 
 
#create a new output workbook 
output = xlsxwriter.Workbook('\\file directory\\oldWrkbkName_edit.xlsx') 
out_worksheet = output.add_worksheet()     
 
#headers 
out_worksheet.write('A1', 'StartTime') 
out_worksheet.write('B1', 'N_RT') 
out_worksheet.write('C1', 'N_L') 
#[StartTime]  
out_worksheet.write('A2', '6:00AM') 
out_worksheet.write('A3', '6:15AM') 
out_worksheet.write('A4', '6:30AM') 
out_worksheet.write('A5', '6:45AM') 
out_worksheet.write('A6', '7:00AM') 
out_worksheet.write('A7', '7:15AM') 
out_worksheet.write('A8', '7:30AM') 
out_worksheet.write('A9', '7:45AM') 
out_worksheet.write('A10', '8:00AM') 
#[N_RT]:  
out_worksheet.write('B2', (passenger.cell_value(rowx=6, colx=1) +   hvyTruck.cell_value(rowx=6, colx=1) + ltTruck.cell_value(rowx=6, colx=1) 
                 + passenger.cell_value(rowx=6, colx=2) + hvyTruck.cell_value(rowx=6, colx=2) + ltTruck.cell_value(rowx=6, colx=2))) 
out_worksheet.write('B3', (passenger.cell_value(rowx=7, colx=1) + hvyTruck.cell_value(rowx=7, colx=1) + ltTruck.cell_value(rowx=7, colx=1) 
                 + passenger.cell_value(rowx=7, colx=2) + hvyTruck.cell_value(rowx=7, colx=2) + ltTruck.cell_value(rowx=7, colx=2))) 
out_worksheet.write('B4', (passenger.cell_value(rowx=8, colx=1) + hvyTruck.cell_value(rowx=8, colx=1) + ltTruck.cell_value(rowx=8, colx=1) 
                 + passenger.cell_value(rowx=8, colx=2) + hvyTruck.cell_value(rowx=8, colx=2) + ltTruck.cell_value(rowx=8, colx=2))) 
out_worksheet.write('B5', (passenger.cell_value(rowx=9, colx=1) + hvyTruck.cell_value(rowx=9, colx=1) + ltTruck.cell_value(rowx=9, colx=1) 
                 + passenger.cell_value(rowx=9, colx=2) + hvyTruck.cell_value(rowx=9, colx=2) + ltTruck.cell_value(rowx=9, colx=2))) 
out_worksheet.write('B6', (passenger.cell_value(rowx=10, colx=1) + hvyTruck.cell_value(rowx=10, colx=1) + ltTruck.cell_value(rowx=10, colx=1) 
                 + passenger.cell_value(rowx=10, colx=2) + hvyTruck.cell_value(rowx=10, colx=2) + ltTruck.cell_value(rowx=10, colx=2))) 
out_worksheet.write('B7', (passenger.cell_value(rowx=11, colx=1) + hvyTruck.cell_value(rowx=11, colx=1) + ltTruck.cell_value(rowx=11, colx=1) 
                 + passenger.cell_value(rowx=11, colx=2) + hvyTruck.cell_value(rowx=11, colx=2) + ltTruck.cell_value(rowx=11, colx=2))) 
out_worksheet.write('B8', (passenger.cell_value(rowx=12, colx=1) + hvyTruck.cell_value(rowx=12, colx=1) + ltTruck.cell_value(rowx=12, colx=1) 
                 + passenger.cell_value(rowx=12, colx=2) + hvyTruck.cell_value(rowx=12, colx=2) + ltTruck.cell_value(rowx=12, colx=2))) 
out_worksheet.write('B9', (passenger.cell_value(rowx=13, colx=1) + hvyTruck.cell_value(rowx=13, colx=1) + ltTruck.cell_value(rowx=13, colx=1) 
                 + passenger.cell_value(rowx=13, colx=2) + hvyTruck.cell_value(rowx=13, colx=2) + ltTruck.cell_value(rowx=13, colx=2))) 
out_worksheet.write('B10', (passenger.cell_value(rowx=14, colx=1) + hvyTruck.cell_value(rowx=14, colx=1) + ltTruck.cell_value(rowx=14, colx=1) 
                 + passenger.cell_value(rowx=14, colx=2) + hvyTruck.cell_value(rowx=14, colx=2) + ltTruck.cell_value(rowx=14, colx=2))) 
 
#[N_L]: 
out_worksheet.write('C2', (passenger.cell_value(rowx=6, colx=3) + hvyTruck.cell_value(rowx=6, colx=3) + ltTruck.cell_value(rowx=6, colx=3))) 
out_worksheet.write('C3', (passenger.cell_value(rowx=7, colx=3) + hvyTruck.cell_value(rowx=7, colx=3) + ltTruck.cell_value(rowx=7, colx=3))) 
out_worksheet.write('C4', (passenger.cell_value(rowx=8, colx=3) + hvyTruck.cell_value(rowx=8, colx=3) + ltTruck.cell_value(rowx=8, colx=3))) 
out_worksheet.write('C5', (passenger.cell_value(rowx=9, colx=3) + hvyTruck.cell_value(rowx=9, colx=3) + ltTruck.cell_value(rowx=9, colx=3))) 
out_worksheet.write('C6', (passenger.cell_value(rowx=10, colx=3) + hvyTruck.cell_value(rowx=10, colx=3) + ltTruck.cell_value(rowx=10, colx=3))) 
out_worksheet.write('C7', (passenger.cell_value(rowx=11, colx=3) + hvyTruck.cell_value(rowx=11, colx=3) + ltTruck.cell_value(rowx=11, colx=3))) 
out_worksheet.write('C8', (passenger.cell_value(rowx=12, colx=3) + hvyTruck.cell_value(rowx=12, colx=3) + ltTruck.cell_value(rowx=12, colx=3))) 
out_worksheet.write('C9', (passenger.cell_value(rowx=13, colx=3) + hvyTruck.cell_value(rowx=13, colx=3) + ltTruck.cell_value(rowx=13, colx=3))) 
out_worksheet.write('C10', (passenger.cell_value(rowx=14, colx=3) + hvyTruck.cell_value(rowx=14, colx=3) + ltTruck.cell_value(rowx=14, colx=3))) 
 
output.close() 

非常感谢。

请您参考如下方法:

我想这就是你想要的:

(...) 
allfiles = [os.path.join(root,f) for root, dirs, files in walk(root) for f in files] 
 
for afile in allfiles: 
    workbook = xlrd.open_workbook(afile) 
 
    (...) 
 
    output = xlsxwriter.Workbook(afile[:-5] + '_edit.xlsx') 
 
    (...) 
 
    output.close()