Python function to create a new excel workbook and populate cells

Question

I want to write a Python function that will loop through a directory of Excel (os.walk), apply the function to each file, and save each the output from each file with an _edit.xlsx' extension ('filename*_edit**.xlsx'). I'm working with xlsxWriter.

Here's what I have to work with. This code does what I want for only one file at a time, but I would like it to apply to all files in my folder directory:

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()

Many thanks.

So what's your question? (i.e. what about your code is insufficient?) — wnnmaw, Jul 15 '14 at 19:38
In my folder directory, I have a huge number of files in which I have to manipulate in the exact same way. The code I provided works on just one file at a time--and I want some way to apply it to all the files in my directory. I'm assuming that will be through creating a def function, but I'm not sure how to approach it. — user30958, Jul 15 '14 at 19:46
if you have to apply the exact same thing to multiple files.. just make a list of the file locations.. do a for loop on the list, put in the file path for each one when you loop through and put all of the worksheet stuff in a def that you call inside the for loop — John Ruddell, Jul 15 '14 at 19:51
I understand that is what I'm suppose to do. I've never worked with def functions before--and am rather new to Python. I have the logic down, but need a bit of help visualizing it. — user30958, Jul 15 '14 at 19:55

dwitvliet · Accepted Answer · 2014-07-15T20:05:20.833

2

I think this is what you want:

(...)
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()

edited Jul 15 '14 at 20:05

answered Jul 15 '14 at 19:59

dwitvliet

7,242
7
36
62

can you explain '^.*\\(.*?)\.xlxs$' to me? I'm getting an error message of "unbalanced parenthesis" – user30958 Jul 15 '14 at 20:09
@user30958 Sorry, my mistake - the expression wasn't cross-platform compatible. I updated it to a simpler version, which doesn't need regular expressions at all. – dwitvliet Jul 15 '14 at 20:10

Python function to create a new excel workbook and populate cells

1 Answers1