1 import os
2 import xlrd
3 import csv
4 from Helper import Helper
5 from HelperException import FileNotExistException
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
65 """
66 The excel data extracted from the file
67 """
68 return self._data
69
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
82
83 _data = []
84 _path = None
85
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
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
110 """
111 The csv data extracted from the file
112 """
113 return self._data
114