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