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

Source Code for Module tlib.base.XlsHelper

  1  import os 
  2  import xlrd 
  3  import csv 
  4  from Helper import Helper 
  5  from HelperException import FileNotExistException 
6 7 -class XlsHelper(Helper):
8 """ 9 Helper class for excel data operations on excel spread sheet 10 """ 11 12 _data = {} 13 _path = None 14
15 - def __init__(self, path, isFirstRowHeader=True):
16 """ 17 Constructor for class 18 19 @param path : path to the excel file 20 @param isFirstRowHeader : is the first row of the excel file a header row? 21 If true, the return result for reach row will be dictionary, otherwise, it will be a list 22 23 """ 24 if not os.access(path, os.F_OK): 25 raise FileNotExistException('Cannot find the xls file [%s]' % path) 26 self._path = path 27 self.xls2dict(isFirstRowHeader=isFirstRowHeader)
28
29 - def xls2dict(self, sheet=None, isFirstRowHeader=True):
30 """ 31 Convert excel data to dictionary (key for sheet name), the result will be stored in data attribute 32 33 @param sheet: name of the specific sheet to fetch the data 34 @param isFirstRowHeader : is the first row of the excel file a header row? 35 If true, the return result for reach row will be dictionary, otherwise, it will be a list 36 """ 37 with xlrd.open_workbook(self._path) as wb: 38 for s in range(wb.nsheets): 39 worksheet = wb.sheet_by_index(s) 40 if not sheet or worksheet.name in sheet: 41 num_rows, curr_row = worksheet.nrows, 0 42 self._data[worksheet.name] = [] 43 if isFirstRowHeader: 44 keyValues = [x.value for x in worksheet.row(0)] 45 curr_row = 1 46 while curr_row < num_rows: 47 if isFirstRowHeader: 48 row = dict() 49 for idx, val in enumerate(worksheet.row(curr_row)): 50 if row.has_key(keyValues[idx]): 51 if not isinstance(row[keyValues[idx]], list): 52 row[keyValues[idx]] = [row[keyValues[idx]]] 53 row[keyValues[idx]].append(unicode(val.value).encode('utf-8').strip()) 54 else: 55 row[keyValues[idx]] = unicode(val.value).encode('utf-8').strip() 56 else: 57 row = list() 58 for val in worksheet.row(curr_row): 59 row.append(unicode(val.value).encode('utf-8').strip()) 60 curr_row += 1 61 self._data[worksheet.name].append(row)
62 63 @property
64 - def data(self):
65 """ 66 The excel data extracted from the file 67 """ 68 return self._data
69
70 - def getSheetData(self, sheet):
71 """ 72 Get the excel sheet data on specific sheet 73 74 @param sheet: name of the sheet to fetch the data 75 76 @return: a list of data from the specified sheet or empty list if no sheet found 77 """ 78 return self._data.get(sheet) or []
79
80 81 -class CsvHelper(Helper):
82 83 _data = [] 84 _path = None 85
86 - def __init__(self, path):
87 """ 88 Constructor for class 89 90 @param path : path to the excel file 91 @param isFirstRowHeader : is the first row of the excel file a header row? 92 If true, the return result for reach row will be dictionary, otherwise, it will be a list 93 """ 94 if not os.access(path, os.F_OK): 95 raise FileNotExistException('Cannot find the csv file [%s]' % path) 96 self._path = path 97 self.csv2dict()
98
99 - def csv2dict(self):
100 """ 101 Convert csv data to lists, the result will be stored in data attribute 102 """ 103 with open(self._path, 'rb') as f: 104 rows = csv.DictReader(f) 105 for row in rows: 106 self._data.append(row)
107 108 @property
109 - def data(self):
110 """ 111 The csv data extracted from the file 112 """ 113 return self._data
114