Pulling data from two excel files with openpyxl in python

Question

I am trying to pull data from two excel files using openpyxl, one file includes two columns, employee names and hours worked, the other,two columns, employee names and hourly wage. Ultimately, I'd like the files compared by name, have wage * hours worked, and then dumped into a third sheet by name and wages payable, but at this point, I'm struggling to get the items from two rows in the first sheet into excel to be able to manipulate them. I thought I'd create two lists from the columns, the combine them into a dictionary, but I don't think that will get me where I need to be. Any suggestions on how to get this data into python to manipulate it would be fantastic!

import openpyxl 

wb = openpyxl.load_workbook("Test_book.xlsx")
sheet=wb.get_sheet_by_name('Hours')
employee_names=[]
employee_hours=[]
for row in sheet['A']:
    employee_names.append(row.value)
for row in sheet['B']:
    employee_hours.append(row.value)
my_dict=dict(zip(employee_names,employee_hours))
print(my_dict)

why isn't this working? Can you show sample output and desired output? — TinyTheBrontosaurus, Nov 25 '17 at 19:00

score 0 · Answer 1 · answered Nov 25 '17 at 19:04

0

A list comprehension may do it. and using zip to iterate over

my_dict = {name:hours for name, hours in zip(sheet['A'], sheet['b'])}

what zip is doing is iterating through parallel lists.

answered Nov 25 '17 at 19:04

TinyTheBrontosaurus

4,010
6
21
34

I was able to get the data into the spreadsheet using the list comprehension. Thank you! – user9008244 Nov 26 '17 at 01:48

Pulling data from two excel files with openpyxl in python

1 Answers1