ExcelReader.get_variable_names() - Code Metrics - Inspection of "Make "excel_file" config attribute optional since..." - StackStorm/st2contrib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 699639...872adf )

by Tomaz

created 2016-11-15 15:39 UTC

ExcelReader.get_variable_names() A

↳ Parent: ExcelReader

Complexity

Conditions

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	3
c	0
b	0
f	0
dl	0
loc	12
rs	9.4285

"""
Copyright 2016 Brocade Communications Systems, Inc.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
    http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
"""

from openpyxl import Workbook, load_workbook
import os
import time


class ExcelReader(object):
    def __init__(self, excel_file, lock=False, lock_retries=3, lock_delay=1):
        ''' Loads the excel configuration file '''
        self._filename = excel_file
        self._lock = lock

        # Set class variables
        self._strict = True
        self._wb = None
        self._ws = None
        self._sheet_name = None
        self._key_column = -1
        self._keys = []
        self._var_name_row = -1
        self._variable_start_column = -1
        self._variable_end_column = -1
        self._data_start_row = -1
        self._data_end_row = -1

        if not os.path.isfile(self._filename):
            raise ValueError('File "%s" doesn\'t exist' % (self._filename))

        if lock:
            # is file already locked?
            attempts = 0
            while os.path.isfile("%s.lock" % excel_file):
                time.sleep(lock_delay)
                attempts += 1
                if attempts >= lock_retries:
                    raise IOError("File is locked")
            # it's now ours, so lock it
            try:
                open("%s.lock" % excel_file, 'a').close()
            except IOError:
                raise IOError("Could not lock spreadsheet '%s'" % excel_file)
        try:
            self._wb = load_workbook(excel_file, data_only=True)
        except IOError:
            # Create a blank workbook
            self._wb = Workbook()

    def __del__(self):
        ''' Get rid of lock file if as cleanup '''
        try:
            self._unlock_file()
        except OSError:
            pass

    def _unlock_file(self):
        ''' Attempts to remove the locked file '''
        if self._lock:
            os.remove("%s.lock" % self._filename)
            self._lock = False

    def save(self):
        ''' Saves spreadsheet to disk '''
        if not self._lock:
            raise IOError("Trying to save file not locked for modification")
        self._wb.save(self._filename)
        self._unlock_file()

    def get_sheets(self):
        ''' Returns an array of the sheet names '''
        sheets = [sheet.title for sheet in self._wb]
        return sheets

    def get_keys(self):
        ''' Returns the keys in the sheet '''
        if not self._ws:
            raise KeyError("Sheet not specified")
        keys = list(self._keys)
        return keys

    def get_variable_names(self):
        ''' Returns the variable names in the sheet '''
        if not self._ws:
            raise KeyError("Sheet not specified")
        variable_names = []
        for col in range(self._variable_start_column,
                         self._variable_end_column):

            variable_names.append(self._ws.cell(column=col,
                                  row=self._var_name_row).value)

        return variable_names

    def _set_key_column(self, key_column):
        ''' Set the key column '''
        self._key_column = key_column
        self._keys = {}
        row = self._data_start_row
        while True:
            key = self._ws.cell(column=self._key_column, row=row)
            if key.value:
                # Check for duplicate key
                if key.value not in self._keys:
                    self._keys[key.value] = row
                else:
                    self._unlock_file()
                    raise ValueError("Duplicate key '%s' found at row '%s'." %
                                     (key.value, row))
            else:
                self._data_end_row = row
                break
            row += 1

    def _set_variable_start_column(self, variable_start_column):
        ''' Set the template column in the template '''
        col = self._variable_start_column = variable_start_column
        while True:
            cell = self._ws.cell(column=col, row=self._var_name_row)
            if cell.value:
                col += 1
            else:
                self._variable_end_column = col
                break

    def set_sheet(self, sheet_name, key_column=1, var_name_row=1,
                  strict=False):
        ''' Sets the current sheet '''
        self._sheet_name = sheet_name
        self._strict = strict
        try:
            self._ws = self._wb.get_sheet_by_name(self._sheet_name)
        except KeyError:
            if not self._strict:
                if self._lock:
                    self._ws = self._wb.create_sheet(self._sheet_name)
                else:
                    raise IOError("File not locked for modification")
            else:
                self._unlock_file()
                raise KeyError("Sheet '%s' not found" % self._sheet_name)

        self._var_name_row = var_name_row
        self._data_start_row = var_name_row + 1

        self._key_column = key_column
        self._set_key_column(key_column)

        self._variable_start_column = key_column + 1
        self._variable_end_column = self._variable_start_column
        self._set_variable_start_column(key_column + 1)

    def get_row_for_key(self, key):
        ''' Returns the row for a given key, or -1 if not matched '''
        if len(key) > 255:
            self._unlock_file()
            raise ValueError("Key exceeds 255 characters")
        if key in self._keys:
            return self._keys[key]
        return -1

    def get_variables_for_key(self, key):
        ''' Returns the variables for a particular key '''
        if not self._ws:
            raise NameError("Sheet not specified")
        variables = {}
        col = self._variable_start_column
        row = self.get_row_for_key(key)
        while row >= 0 and col <= self._variable_end_column:
            variable = self._ws.cell(column=col, row=self._var_name_row)
            if variable.value:
                variables[variable.value] = self._ws.cell(column=col,
                                                          row=row).value
            col += 1
        return variables

    def get_last_row(self):
        ''' Returns the last row of data '''
        return self._data_end_row

    def set_values_for_variables(self, key, dictionary):
        ''' Sets values for variables for a given key '''
        # Make sure the sheet has been specified
        if not self._ws:
            raise NameError("Sheet not specified")
        # Check to make sure file was locked before allowing modifications
        if not self._lock:
            raise IOError("File not locked for modification")

        # Build a dictionary of variables in the spreadsheet
        variables = {}
        col = self._variable_start_column
        while True:
            variable = self._ws.cell(column=col, row=self._var_name_row)
            if variable.value:
                variables[variable.value] = col
            else:
                self._variable_end_column = col
                break
            col += 1

        # Find row for key
        row = self.get_row_for_key(key)
        if row == -1:
            row = self._data_end_row
            self._ws.cell(column=self._key_column, row=row).value = key
            self._data_end_row += 1
            self._keys[key] = row

        # Fill in values
        for k, value in dictionary.items():
            # is key or value greater the excel 256 character limit?
            if len(k) > 255:
                self._unlock_file()
                raise ValueError("Variable name exceeds 255 characters")
            if len(k) == 0:
                self._unlock_file()
                raise ValueError("Variable name is blank")
            if len(value) > 32767:
                self._unlock_file()
                raise ValueError("Variable value exceeds 32,767 characters")
            if not k == key:
                if k in variables:
                    self._ws.cell(column=variables[k], row=row).value = value
                else:
                    if not self._strict:
                        # Add a new column at the end with variable
                        self._ws.cell(column=self._variable_end_column,
                                      row=self._var_name_row).value = k
                        self._ws.cell(column=self._variable_end_column,
                                      row=row).value = value
                        self._variable_end_column += 1
                        if self._variable_end_column > 16384:
                            raise ValueError("Exceeded max # of columns")
                    else:
                        self._unlock_file()
                        raise ValueError("Column '%s' missing in sheet '%s'" %
                                         (k, self._sheet_name))

if __name__ == "__main__":
    pass


1			"""
2			Copyright 2016 Brocade Communications Systems, Inc.
3			Licensed under the Apache License, Version 2.0 (the "License");
4			you may not use this file except in compliance with the License.
5			You may obtain a copy of the License at
6			http://www.apache.org/licenses/LICENSE-2.0
7			Unless required by applicable law or agreed to in writing, software
8			distributed under the License is distributed on an "AS IS" BASIS,
9			WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
10			See the License for the specific language governing permissions and
11			limitations under the License.
12			"""
13
14			from openpyxl import Workbook, load_workbook
15			import os
16			import time
17
18
19			class ExcelReader(object):
20			def __init__(self, excel_file, lock=False, lock_retries=3, lock_delay=1):
21			''' Loads the excel configuration file '''
22			self._filename = excel_file
23			self._lock = lock
24
25			# Set class variables
26			self._strict = True
27			self._wb = None
28			self._ws = None
29			self._sheet_name = None
30			self._key_column = -1
31			self._keys = []
32			self._var_name_row = -1
33			self._variable_start_column = -1
34			self._variable_end_column = -1
35			self._data_start_row = -1
36			self._data_end_row = -1
37
38			if not os.path.isfile(self._filename):
39			raise ValueError('File "%s" doesn\'t exist' % (self._filename))
40
41			if lock:
42			# is file already locked?
43			attempts = 0
44			while os.path.isfile("%s.lock" % excel_file):
45			time.sleep(lock_delay)
46			attempts += 1
47			if attempts >= lock_retries:
48			raise IOError("File is locked")
49			# it's now ours, so lock it
50			try:
51			open("%s.lock" % excel_file, 'a').close()
52			except IOError:
53			raise IOError("Could not lock spreadsheet '%s'" % excel_file)
54			try:
55			self._wb = load_workbook(excel_file, data_only=True)
56			except IOError:
57			# Create a blank workbook
58			self._wb = Workbook()
59
60			def __del__(self):
61			''' Get rid of lock file if as cleanup '''
62			try:
63			self._unlock_file()
64			except OSError:
65			pass
66
67			def _unlock_file(self):
68			''' Attempts to remove the locked file '''
69			if self._lock:
70			os.remove("%s.lock" % self._filename)
71			self._lock = False
72
73			def save(self):
74			''' Saves spreadsheet to disk '''
75			if not self._lock:
76			raise IOError("Trying to save file not locked for modification")
77			self._wb.save(self._filename)
78			self._unlock_file()
79
80			def get_sheets(self):
81			''' Returns an array of the sheet names '''
82			sheets = [sheet.title for sheet in self._wb]
83			return sheets
84
85			def get_keys(self):
86			''' Returns the keys in the sheet '''
87			if not self._ws:
88			raise KeyError("Sheet not specified")
89			keys = list(self._keys)
90			return keys
91
92			def get_variable_names(self):
93			''' Returns the variable names in the sheet '''
94			if not self._ws:
95			raise KeyError("Sheet not specified")
96			variable_names = []
97			for col in range(self._variable_start_column,
98			self._variable_end_column):
99
100			variable_names.append(self._ws.cell(column=col,
101			row=self._var_name_row).value)
102
103			return variable_names
104
105			def _set_key_column(self, key_column):
106			''' Set the key column '''
107			self._key_column = key_column
108			self._keys = {}
109			row = self._data_start_row
110			while True:
111			key = self._ws.cell(column=self._key_column, row=row)
112			if key.value:
113			# Check for duplicate key
114			if key.value not in self._keys:
115			self._keys[key.value] = row
116			else:
117			self._unlock_file()
118			raise ValueError("Duplicate key '%s' found at row '%s'." %
119			(key.value, row))
120			else:
121			self._data_end_row = row
122			break
123			row += 1
124
125			def _set_variable_start_column(self, variable_start_column):
126			''' Set the template column in the template '''
127			col = self._variable_start_column = variable_start_column
128			while True:
129			cell = self._ws.cell(column=col, row=self._var_name_row)
130			if cell.value:
131			col += 1
132			else:
133			self._variable_end_column = col
134			break
135
136			def set_sheet(self, sheet_name, key_column=1, var_name_row=1,
137			strict=False):
138			''' Sets the current sheet '''
139			self._sheet_name = sheet_name
140			self._strict = strict
141			try:
142			self._ws = self._wb.get_sheet_by_name(self._sheet_name)
143			except KeyError:
144			if not self._strict:
145			if self._lock:
146			self._ws = self._wb.create_sheet(self._sheet_name)
147			else:
148			raise IOError("File not locked for modification")
149			else:
150			self._unlock_file()
151			raise KeyError("Sheet '%s' not found" % self._sheet_name)
152
153			self._var_name_row = var_name_row
154			self._data_start_row = var_name_row + 1
155
156			self._key_column = key_column
157			self._set_key_column(key_column)
158
159			self._variable_start_column = key_column + 1
160			self._variable_end_column = self._variable_start_column
161			self._set_variable_start_column(key_column + 1)
162
163			def get_row_for_key(self, key):
164			''' Returns the row for a given key, or -1 if not matched '''
165			if len(key) > 255:
166			self._unlock_file()
167			raise ValueError("Key exceeds 255 characters")
168			if key in self._keys:
169			return self._keys[key]
170			return -1
171
172			def get_variables_for_key(self, key):
173			''' Returns the variables for a particular key '''
174			if not self._ws:
175			raise NameError("Sheet not specified")
176			variables = {}
177			col = self._variable_start_column
178			row = self.get_row_for_key(key)
179			while row >= 0 and col <= self._variable_end_column:
180			variable = self._ws.cell(column=col, row=self._var_name_row)
181			if variable.value:
182			variables[variable.value] = self._ws.cell(column=col,
183			row=row).value
184			col += 1
185			return variables
186
187			def get_last_row(self):
188			''' Returns the last row of data '''
189			return self._data_end_row
190
191			def set_values_for_variables(self, key, dictionary):
192			''' Sets values for variables for a given key '''
193			# Make sure the sheet has been specified
194			if not self._ws:
195			raise NameError("Sheet not specified")
196			# Check to make sure file was locked before allowing modifications
197			if not self._lock:
198			raise IOError("File not locked for modification")
199
200			# Build a dictionary of variables in the spreadsheet
201			variables = {}
202			col = self._variable_start_column
203			while True:
204			variable = self._ws.cell(column=col, row=self._var_name_row)
205			if variable.value:
206			variables[variable.value] = col
207			else:
208			self._variable_end_column = col
209			break
210			col += 1
211
212			# Find row for key
213			row = self.get_row_for_key(key)
214			if row == -1:
215			row = self._data_end_row
216			self._ws.cell(column=self._key_column, row=row).value = key
217			self._data_end_row += 1
218			self._keys[key] = row
219
220			# Fill in values
221			for k, value in dictionary.items():
222			# is key or value greater the excel 256 character limit?
223			if len(k) > 255:
224			self._unlock_file()
225			raise ValueError("Variable name exceeds 255 characters")
226			if len(k) == 0:
227			self._unlock_file()
228			raise ValueError("Variable name is blank")
229			if len(value) > 32767:
230			self._unlock_file()
231			raise ValueError("Variable value exceeds 32,767 characters")
232			if not k == key:
233			if k in variables:
234			self._ws.cell(column=variables[k], row=row).value = value
235			else:
236			if not self._strict:
237			# Add a new column at the end with variable
238			self._ws.cell(column=self._variable_end_column,
239			row=self._var_name_row).value = k
240			self._ws.cell(column=self._variable_end_column,
241			row=row).value = value
242			self._variable_end_column += 1
243			if self._variable_end_column > 16384:
244			raise ValueError("Exceeded max # of columns")
245			else:
246			self._unlock_file()
247			raise ValueError("Column '%s' missing in sheet '%s'" %
248			(k, self._sheet_name))
249
250			if __name__ == "__main__":
251			pass
252

StackStorm / st2contrib

Push — master ( 699639...872adf )

ExcelReader.get_variable_names() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like