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:
python Code:
%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:
python 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']) + '; ')