ExcelReader.save() - 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-14 21:36 UTC

ExcelReader.save() A

↳ Parent: ExcelReader

Complexity

Conditions

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
cc	2
c	2
b	0
f	0
dl	0
loc	6
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 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 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 = [sheet.title for sheet in self._wb]
80			return sheets
81
82			def get_keys(self):
83			''' Returns the keys in the sheet '''
84			if not self._ws:
85			raise KeyError("Sheet not specified")
86			keys = list(self._keys)
87			return keys
88
89			def get_variable_names(self):
90			''' Returns the variable names in the sheet '''
91			if not self._ws:
92			raise KeyError("Sheet not specified")
93			variable_names = []
94			for col in range(self._variable_start_column,
95			self._variable_end_column):
96
97			variable_names.append(self._ws.cell(column=col,
98			row=self._var_name_row).value)
99
100			return variable_names
101
102			def _set_key_column(self, key_column):
103			''' Set the key column '''
104			self._key_column = key_column
105			self._keys = {}
106			row = self._data_start_row
107			while True:
108			key = self._ws.cell(column=self._key_column, row=row)
109			if key.value:
110			# Check for duplicate key
111			if key.value not in self._keys:
112			self._keys[key.value] = row
113			else:
114			self._unlock_file()
115			raise ValueError("Duplicate key '%s' found at row '%s'." %
116			(key.value, row))
117			else:
118			self._data_end_row = row
119			break
120			row += 1
121
122			def _set_variable_start_column(self, variable_start_column):
123			''' Set the template column in the template '''
124			col = self._variable_start_column = variable_start_column
125			while True:
126			cell = self._ws.cell(column=col, row=self._var_name_row)
127			if cell.value:
128			col += 1
129			else:
130			self._variable_end_column = col
131			break
132
133			def set_sheet(self, sheet_name, key_column=1, var_name_row=1,
134			strict=False):
135			''' Sets the current sheet '''
136			self._sheet_name = sheet_name
137			self._strict = strict
138			try:
139			self._ws = self._wb.get_sheet_by_name(self._sheet_name)
140			except KeyError:
141			if not self._strict:
142			if self._lock:
143			self._ws = self._wb.create_sheet(self._sheet_name)
144			else:
145			raise IOError("File not locked for modification")
146			else:
147			self._unlock_file()
148			raise KeyError("Sheet '%s' not found" % self._sheet_name)
149
150			self._var_name_row = var_name_row
151			self._data_start_row = var_name_row + 1
152
153			self._key_column = key_column
154			self._set_key_column(key_column)
155
156			self._variable_start_column = key_column + 1
157			self._variable_end_column = self._variable_start_column
158			self._set_variable_start_column(key_column + 1)
159
160			def get_row_for_key(self, key):
161			''' Returns the row for a given key, or -1 if not matched '''
162			if len(key) > 255:
163			self._unlock_file()
164			raise ValueError("Key exceeds 255 characters")
165			if key in self._keys:
166			return self._keys[key]
167			return -1
168
169			def get_variables_for_key(self, key):
170			''' Returns the variables for a particular key '''
171			if not self._ws:
172			raise NameError("Sheet not specified")
173			variables = {}
174			col = self._variable_start_column
175			row = self.get_row_for_key(key)
176			while row >= 0 and col <= self._variable_end_column:
177			variable = self._ws.cell(column=col, row=self._var_name_row)
178			if variable.value:
179			variables[variable.value] = self._ws.cell(column=col,
180			row=row).value
181			col += 1
182			return variables
183
184			def get_last_row(self):
185			''' Returns the last row of data '''
186			return self._data_end_row
187
188			def set_values_for_variables(self, key, dictionary):
189			''' Sets values for variables for a given key '''
190			# Make sure the sheet has been specified
191			if not self._ws:
192			raise NameError("Sheet not specified")
193			# Check to make sure file was locked before allowing modifications
194			if not self._lock:
195			raise IOError("File not locked for modification")
196
197			# Build a dictionary of variables in the spreadsheet
198			variables = {}
199			col = self._variable_start_column
200			while True:
201			variable = self._ws.cell(column=col, row=self._var_name_row)
202			if variable.value:
203			variables[variable.value] = col
204			else:
205			self._variable_end_column = col
206			break
207			col += 1
208
209			# Find row for key
210			row = self.get_row_for_key(key)
211			if row == -1:
212			row = self._data_end_row
213			self._ws.cell(column=self._key_column, row=row).value = key
214			self._data_end_row += 1
215			self._keys[key] = row
216
217			# Fill in values
218			for k, value in dictionary.items():
219			# is key or value greater the excel 256 character limit?
220			if len(k) > 255:
221			self._unlock_file()
222			raise ValueError("Variable name exceeds 255 characters")
223			if len(k) == 0:
224			self._unlock_file()
225			raise ValueError("Variable name is blank")
226			if len(value) > 32767:
227			self._unlock_file()
228			raise ValueError("Variable value exceeds 32,767 characters")
229			if not k == key:
230			if k in variables:
231			self._ws.cell(column=variables[k], row=row).value = value
232			else:
233			if not self._strict:
234			# Add a new column at the end with variable
235			self._ws.cell(column=self._variable_end_column,
236			row=self._var_name_row).value = k
237			self._ws.cell(column=self._variable_end_column,
238			row=row).value = value
239			self._variable_end_column += 1
240			if self._variable_end_column > 16384:
241			raise ValueError("Exceeded max # of columns")
242			else:
243			self._unlock_file()
244			raise ValueError("Column '%s' missing in sheet '%s'" %
245			(k, self._sheet_name))
246
247			if __name__ == "__main__":
248			pass
249

StackStorm / st2contrib

Pull Request — master (#610)

ExcelReader.save() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like