Package tlib :: Package base :: Module XlsModerator |
[hide private]
[frames] | no frames]

# Source Code for Module tlib.base.XlsModerator

     1  import os
     2  import xlrd
     3  from ExceptionHelper import FileNotExistError
     4
     5 -class XlsModerator(object):
     6      """
     7      Helper class for excel data operations on excel spread sheet
     8      """
     9
    10      _data = {}
    11      _path = None
    12
    13 -    def __init__(self, path, isFirstRowHeader=True):
    14          """
    15          Constructor for class
    16
    17          @param path : path to the excel file
    18          @param isFirstRowHeader : is the first row of the excel file a header row?
    19                  If true, the return result for reach row will be dictionary, otherwise, it will be a list
    20
    21          """
    22          if not os.access(path, os.F_OK):
    23              raise FileNotExistError('Cannot find the xls file [%s]' % path)
    24          self._path = path
    25          self.xls2dict(isFirstRowHeader=isFirstRowHeader)
    26
    27 -    def xls2dict(self, sheet=None, isFirstRowHeader=True):
    28          """
    29          Convert excel data to dictionary (key for sheet name), the result will be stored in data attribute
    30
    31          @param sheet: name of the specific sheet to fetch the data
    32          @param isFirstRowHeader : is the first row of the excel file a header row?
    33                  If true, the return result for reach row will be dictionary, otherwise, it will be a list
    34          """
    35          with xlrd.open_workbook(self._path) as wb:
    36              for s in range(wb.nsheets):
    37                  worksheet = wb.sheet_by_index(s)
    38                  if not sheet or worksheet.name in sheet:
    39                      num_rows, curr_row = worksheet.nrows, 0
    40                      self._data[worksheet.name] = []
    41                      if isFirstRowHeader:
    42                          keyValues = [x.value for x in worksheet.row(0)]
    43                          curr_row = 1
    44                      while curr_row < num_rows:
    45                          if isFirstRowHeader:
    46                              row = dict()
    47                              for idx, val in enumerate(worksheet.row(curr_row)):
    48                                  if row.has_key(keyValues[idx]):
    49                                      if not isinstance(row[keyValues[idx]], list):
    50                                          row[keyValues[idx]] = [row[keyValues[idx]]]
    51                                      row[keyValues[idx]].append(unicode(val.value).encode('utf-8').strip())
    52                                  else:
    53                                      row[keyValues[idx]] = unicode(val.value).encode('utf-8').strip()
    54                          else:
    55                              row = list()
    56                              for val in worksheet.row(curr_row):
    57                                  row.append(unicode(val.value).encode('utf-8').strip())
    58                          curr_row += 1
    59                          self._data[worksheet.name].append(row)
    60
    61      @property
    62 -    def data(self):
    63          """
    64          The excel data extracted from the file
    65          """
    66          return self._data
    67
    68 -    def get_sheet_data(self, sheet):
    69          """
    70          Get the excel sheet data on specific sheet
    71
    72          @param sheet: name of the sheet to fetch the data
    73
    74          @return: a list of data from the specified sheet or empty list if no sheet found
    75          """
    76          return self._data.get(sheet) or []
    77