PDA

View Full Version : Problems With Openpyxl Python3.4



Saint//+
09-29-2015, 04:12 AM
Im not sure what's going on with this... i've been using similar code to break down excell files and create reports but this seems to be broken.
normally i just write it out and do it interactively in Ipython, maybe i'm missing something in translation to a .py file? I still run it from Ipython though. Im stumped.

I keep getting an error message that the sheet in my excell workbook does not exist, but it definitely does. the code gets the name for the sheet with the get_active_sheet() function...

here is the error I'm getting


Edit: after reviewing this today, this code is all kinds of messed up -_-'
I'm gonna try to rewrite most of it and see if i can fix it.


Error:

%run Report_Formatter.py
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
/Users/jk/Desktop/Report_Formatter.py in <module>()
69 #file = 'TestingFormatter.xlsx'
70
---> 71 myfile = Input_File()
72 myfile.find_employers()
73 counts = myfile.employer_counts

/Users/jk/Desktop/Report_Formatter.py in __init__(self)
19 self.wb = openpyxl.load_workbook(self.file)
20 self.asheet = self.wb.get_active_sheet()
---> 21 self.sheet = self.wb.get_sheet_by_name(self.asheet)
22 self.employer_counts= {}
23 employer_list = []

/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/openpyxl/workbook/workbook.py in get_sheet_by_name(self, name)
212
213 """
--> 214 return self[name]
215
216 def __contains__(self, key):

/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/openpyxl/workbook/workbook.py in __getitem__(self, key)
231 if sheet.title == key:
232 return sheet
--> 233 raise KeyError("Worksheet {0} does not exist.".format(key))
234
235 def __delitem__(self, key):

KeyError: 'Worksheet <Worksheet "Report"> does not exist.'




My Code:

import openpyxl
from openpyxl.cell import get_column_letter

"""
Takes Van Export file and converts it into an excell spreadsheet with
tab 1 = Breakdown
tab 2... = store with employees + information on each
"""
class Input_File():
def __init__(self):
#loads input file
self.file = 'TestingFormatter.xlsx'
self.wb = openpyxl.load_workbook(self.file)
self.asheet = self.wb.get_active_sheet()
self.sheet = self.wb.get_sheet_by_name(self.asheet)
self.employer_counts= {}
employer_list = []
#self.employer_counts.setdefault(employer, {employees: 0, cards: 0, A_1: 0, A_2: 0, A_3: 0})

"""
searches input file
creates dictionary for each employer
with count of workers, cards, assessments
"""
def find_employers(self):
#searches first row to find column headings for employers, cards, and assessments
#stores results in variables
for column in range(1, sheet.get_highest_column()):
if self.sheet[str(get_column_letter(column)) + '1'].value == 'Employer':
e_ltr = get_column_letter(column)
for column in range(1, sheet.get_highest_column()):
if self.sheet[str(get_column_letter(column)) + '1'].value == 'LWW_Union_Assessment':
assmt_ltr = get_column_letter(column)
for column in range(1, sheet.get_highest_column()):
if self.sheet[str(get_column_letter(column)) + '1'].value == 'Whatever it Takes':
card_ltr = get_column_letter(column)

#searches column ltr for each employer.
#creates and populates dictionary for employer
self.employer_counts= {}
employer_list = []
for row in range(1, self.get_highest_row()):
# stores values in variables
employer = self.sheet[str(e_ltr) + str(row)].value
assmt = self.sheet[str(assmt_ltr) + str(row)].value
cards = self.sheet[str(card_ltr) + str(row)].value
#handles Employer_list
if employer not in employer_list:
employer_list.append(employer)
# sets up dictionary
self.employer_counts.setdefault(employer, {'employees': 0, 'cards': 0, 'A_1': 0, 'A_2': 0, 'A_3': 0})
# populates dictionary record for each employer.
employer_counts[employer]['employees'] += 1
if cards == 'Signed Card':
employer_counts[employer]['cards'] += 1
if assmt == '1=Leader':
employer_counts[employer]['A_1'] += 1
elif assmt == '2=Card Signer&Active':
employer_counts[employer]['A_2'] += 1
elif assmt == '3=Inactive/Undecided':
employer_counts[employer]['A_3'] += 1

myfile = Input_File()
myfile.find_employers()
counts = myfile.employer_counts
for e in myfile.employer_list:
print(e + '; '+ str(counts['employees']) + '; ' + str(counts['cards']) + '; ' + str(counts['A_1']) + '; ',
+ str(counts['A_2']) + '; ' + str(counts['A_3']) + '; ')