class readexcel(object):
    """ Simple OS-independent class for extracting data from an Excel File.

    http://gizmojo.org/code/readexcel/    
    
    Uses the xlrd module (version 0.5.2 or later), supporting Excel versions:
    2004, 2002, XP, 2000, 97, 95, 5, 4, 3
    
    Data is extracted via iterators that return one row at a time -- either 
    as a dict or as a list. The dict generator assumes that the worksheet is 
    in tabular format with the first "data" row containing the variable names
    and all subsequent rows containing values. 
        
    Extracted data is represented fairly logically. By default dates are 
    returned as strings in "yyyy/mm/dd" format or "yyyy/mm/dd hh:mm:ss", as 
    appropriate. However, when specifying date_as_tuple=True, dates will be 
    returned as a (Year, Month, Day, Hour, Min, Second) tuple, for usage with 
    mxDateTime or DateTime. Numbers are returned as either INT or FLOAT, 
    whichever is needed to support the data. Text, booleans, and error codes 
    are also returned as appropriate representations. Quick Example:
    
        xls = readexcel('testdata.xls')
        for sname in xls.book.sheet_names():
            for row in xls.iter_dict(sname):
                print row
    """ 
    def __init__(self, filename):
        """ Wraps an XLRD book """
        if not os.path.isfile(filename):
            raise ValueError, "%s is not a valid filename" % filename
        self.book = xlrd.open_workbook(filename)
        self.sheet_keys = {}
    def is_data_row(self, sheet, i):
        values = sheet.row_values(i)
        if isinstance(values[0], basestring) and values[0].startswith('#'):
            return False # ignorable comment row
        for v in values:
            if bool(v):
                return True #+ row full of (valid) False values?
        return False
    def _parse_row(self, sheet, row_index, date_as_tuple):
        """ Sanitize incoming excel data """
        # Data Type Codes:
        #  EMPTY 0
        #  TEXT 1 a Unicode string 
        #  NUMBER 2 float 
        #  DATE 3 float 
        #  BOOLEAN 4 int; 1 means TRUE, 0 means FALSE 
        #  ERROR 5 
        values = []
        for type, value in zip(
                sheet.row_types(row_index), sheet.row_values(row_index)):
            if type == 2:
                if value == int(value):
                    value = int(value)
            elif type == 3:
                datetuple = xlrd.xldate_as_tuple(value, self.book.datemode)
                if date_as_tuple:
                    value = datetuple
                else:
                    # time only no date component
                    if datetuple[0] == 0 and datetuple[1] == 0 and \
                       datetuple[2] == 0: 
                        value = "%02d:%02d:%02d" % datetuple[3:]
                    # date only, no time
                    elif datetuple[3] == 0 and datetuple[4] == 0 and \
                         datetuple[5] == 0:
                        value = "%04d/%02d/%02d" % datetuple[:3]
                    else: # full date
                        value = "%04d/%02d/%02d %02d:%02d:%02d" % datetuple
            elif type == 5:
                value = xlrd.error_text_from_code[value]
            values.append(value)
        return values
    def iter_dict(self, sname, date_as_tuple=False):
        """ Iterator for the worksheet's rows as dicts """
        sheet = self.book.sheet_by_name(sname) # XLRDError
        # parse first row, set dict keys & first_row_index
        keys = []
        first_row_index = None
        for i in range(sheet.nrows):
            if self.is_data_row(sheet, i): 
                headings = self._parse_row(sheet, i, False)
                for j, var in enumerate(headings):
                    # replace duplicate headings with "F#".
                    if not var or var in keys:
                        var = u'F%s' % (j)
                    keys.append(var.strip())
                first_row_index = i + 1
                break
        self.sheet_keys[sname] = keys
        # generate a dict per data row 
        if first_row_index is not None:
            for i in range(first_row_index, sheet.nrows):
                if self.is_data_row(sheet, i): 
                    yield dict(map(None, keys, 
                            self._parse_row(sheet, i, date_as_tuple)))
    def iter_list(self, sname, date_as_tuple=False):
        """ Iterator for the worksheet's rows as lists """
        sheet = self.book.sheet_by_name(sname) # XLRDError
        for i in range(sheet.nrows):
            if self.is_data_row(sheet, i): 
                yield self._parse_row(sheet, i, date_as_tuple)