ExcelReader.get_variables_for_key() - Code Metrics - Inspection of "Microsoft Excel Pack" - StackStorm/st2contrib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#610)

unknown

created 2016-11-12 21:18 UTC

ExcelReader.get_variables_for_key() B

↳ Parent: ExcelReader

Complexity

Conditions

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
cc	5
c	2
b	0
f	0
dl	0
loc	14
rs	8.5454

"""
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 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 = []
        for sheet in self._wb:
            sheets.append(sheet.title)
        return sheets

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

    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 lock:
39			# is file already locked?
40			attempts = 0
41			while os.path.isfile("%s.lock" % excel_file):
42			time.sleep(lock_delay)
43			attempts += 1
44			if attempts >= lock_retries:
45			raise IOError("File is locked")
46			# it's now ours, so lock it
47			try:
48			open("%s.lock" % excel_file, 'a').close()
49			except IOError:
50			raise IOError("Could not lock spreadsheet '%s'" % excel_file)
51			try:
52			self._wb = load_workbook(excel_file, data_only=True)
53			except IOError:
54			# Create a blank workbook
55			self._wb = Workbook()
56
57			def __del__(self):
58			''' Get rid of lock file if as cleanup '''
59			try:
60			self._unlock_file()
61			except OSError:
62			pass
63
64			def _unlock_file(self):
65			''' Attempts to remove the locked file '''
66			if self._lock:
67			os.remove("%s.lock" % self._filename)
68			self._lock = False
69
70			def save(self):
71			''' Saves spreadsheet to disk '''
72			if not self._lock:
73			raise IOError("Trying to save file not locked for modification")
74			self._wb.save(self._filename)
75			self._unlock_file()
76
77			def get_sheets(self):
78			''' Returns an array of the sheet names '''
79			sheets = []
80			for sheet in self._wb:
81			sheets.append(sheet.title)
82			return sheets
83
84			def get_keys(self):
85			''' Returns the keys in the sheet '''
86			if not self._ws:
87			raise KeyError("Sheet not specified")
88			keys = []
89			for key in self._keys:
90			keys.append(key)
91			return keys
92
93			def _set_key_column(self, key_column):
94			''' Set the key column '''
95			self._key_column = key_column
96			self._keys = {}
97			row = self._data_start_row
98			while True:
99			key = self._ws.cell(column=self._key_column, row=row)
100			if key.value:
101			# Check for duplicate key
102			if key.value not in self._keys:
103			self._keys[key.value] = row
104			else:
105			self._unlock_file()
106			raise ValueError("Duplicate key '%s' found at row '%s'." %
107			(key.value, row))
108			else:
109			self._data_end_row = row
110			break
111			row += 1
112
113			def _set_variable_start_column(self, variable_start_column):
114			''' Set the template column in the template '''
115			col = self._variable_start_column = variable_start_column
116			while True:
117			cell = self._ws.cell(column=col, row=self._var_name_row)
118			if cell.value:
119			col += 1
120			else:
121			self._variable_end_column = col
122			break
123
124			def set_sheet(self, sheet_name, key_column=1, var_name_row=1,
125			strict=False):
126			''' Sets the current sheet '''
127			self._sheet_name = sheet_name
128			self._strict = strict
129			try:
130			self._ws = self._wb.get_sheet_by_name(self._sheet_name)
131			except KeyError:
132			if not self._strict:
133			if self._lock:
134			self._ws = self._wb.create_sheet(self._sheet_name)
135			else:
136			raise IOError("File not locked for modification")
137			else:
138			self._unlock_file()
139			raise KeyError("Sheet '%s' not found" % self._sheet_name)
140
141			self._var_name_row = var_name_row
142			self._data_start_row = var_name_row + 1
143
144			self._key_column = key_column
145			self._set_key_column(key_column)
146
147			self._variable_start_column = key_column+1
148			self._variable_end_column = self._variable_start_column
149			self._set_variable_start_column(key_column+1)
150
151			def get_row_for_key(self, key):
152			''' Returns the row for a given key, or -1 if not matched '''
153			if len(key) > 255:
154			self._unlock_file()
155			raise ValueError("Key exceeds 255 characters")
156			if key in self._keys:
157			return self._keys[key]
158			return -1
159
160			def get_variables_for_key(self, key):
161			''' Returns the variables for a particular key '''
162			if not self._ws:
163			raise NameError("Sheet not specified")
164			variables = {}
165			col = self._variable_start_column
166			row = self.get_row_for_key(key)
167			while row >= 0 and col <= self._variable_end_column:
168			variable = self._ws.cell(column=col, row=self._var_name_row)
169			if variable.value:
170			variables[variable.value] = self._ws.cell(column=col,
171			row=row).value
172			col += 1
173			return variables
174
175			def get_last_row(self):
176			''' Returns the last row of data '''
177			return self._data_end_row
178
179			def set_values_for_variables(self, key, dictionary):
180			''' Sets values for variables for a given key '''
181			# Make sure the sheet has been specified
182			if not self._ws:
183			raise NameError("Sheet not specified")
184			# Check to make sure file was locked before allowing modifications
185			if not self._lock:
186			raise IOError("File not locked for modification")
187
188			# Build a dictionary of variables in the spreadsheet
189			variables = {}
190			col = self._variable_start_column
191			while True:
192			variable = self._ws.cell(column=col, row=self._var_name_row)
193			if variable.value:
194			variables[variable.value] = col
195			else:
196			self._variable_end_column = col
197			break
198			col += 1
199
200			# Find row for key
201			row = self.get_row_for_key(key)
202			if row == -1:
203			row = self._data_end_row
204			self._ws.cell(column=self._key_column, row=row).value = key
205			self._data_end_row += 1
206			self._keys[key] = row
207
208			# Fill in values
209			for k, value in dictionary.items():
210			# is key or value greater the excel 256 character limit?
211			if len(k) > 255:
212			self._unlock_file()
213			raise ValueError("Variable name exceeds 255 characters")
214			if len(k) == 0:
215			self._unlock_file()
216			raise ValueError("Variable name is blank")
217			if len(value) > 32767:
218			self._unlock_file()
219			raise ValueError("Variable value exceeds 32,767 characters")
220			if not k == key:
221			if k in variables:
222			self._ws.cell(column=variables[k], row=row).value = value
223			else:
224			if not self._strict:
225			# Add a new column at the end with variable
226			self._ws.cell(column=self._variable_end_column,
227			row=self._var_name_row).value = k
228			self._ws.cell(column=self._variable_end_column,
229			row=row).value = value
230			self._variable_end_column += 1
231			if self._variable_end_column > 16384:
232			raise ValueError("Exceeded max # of columns")
233			else:
234			self._unlock_file()
235			raise ValueError("Column '%s' missing in sheet '%s'" %
236			(k, self._sheet_name))
237
238			if __name__ == "__main__":
239			pass
240

StackStorm / st2contrib

Pull Request — master (#610)

ExcelReader.get_variables_for_key() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like