def algo19(db, ref):
#Column T: must be a date>= 25/04/2015 and >=Column S
start_col_loc = column_index_from_string(
etl.find_in_header(db, 'DD - Start')) - 1
comp_col_loc = column_index_from_string(etl.find_in_header(db,
'DD - End')) - 1
bad_date = []
start = ""
comp = ""
for row in db.rows[1:]:
try:
start = datetime(day = int(etl.xstr(row[start_col_loc].value)), \
month = int(etl.xstr(row[start_col_loc+1].value)), \
year = int(etl.xstr(row[start_col_loc+2].value)))
comp = datetime(day = int(etl.xstr(row[comp_col_loc].value)), \
month = int(etl.xstr(row[comp_col_loc+1].value)), \
year = int(etl.xstr(row[comp_col_loc+2].value)))
if comp < start:
bad_date.append(str(start) + ' @ row ' + str(row[0].row))
except:
bad_date.append(str(start) + ' @ row ' + str(row[0].row))
return db, ref, return_message('Malformatted date or before Start Date',
bad_date)
def iter_rows(self, range_string='', row_offset=0, column_offset=1):
""" Returns a squared range based on the `range_string` parameter,
using generators.
:param range_string: range of cells (e.g. 'A1:C4')
:type range_string: string
:param row_offset: additional rows (e.g. 4)
:type row: int
:param column_offset: additonal columns (e.g. 3)
:type column: int
:rtype: generator
"""
if range_string:
min_col, min_row, max_col, max_row = get_range_boundaries(range_string, row_offset, column_offset)
else:
min_col = column_index_from_string(self.min_col)
max_col = self.max_col
if max_col is not None:
max_col = column_index_from_string(self.max_col) + 1
min_row = self.min_row
max_row = self.max_row
return self.get_squared_range(min_col, min_row, max_col, max_row)
def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column + 1), start_row + 1, get_column_letter(end_column + 1), end_row + 1)
elif len(range_string.split(':')) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string in self._merged_cells:
self._merged_cells.remove(range_string)
min_col, min_row = coordinate_from_string(range_string.split(':')[0])
max_col, max_row = coordinate_from_string(range_string.split(':')[1])
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
# Mark cell as unmerged
for col in xrange(min_col,max_col+1):
for row in xrange(min_row,max_row+1):
if not (row == min_row and col == min_col):
self._get_cell('%s%s' % (get_column_letter(col), row)).merged = False
else:
msg = 'Cell range %s not known as merged.' % range_string
raise InsufficientCoordinatesException(msg)
def algo20(db,ref):
#Output if completion date < current date and activity status not like completed
status_col_loc = column_index_from_string(etl.find_in_header(db, 'Activity Status')) - 1
comp_col_loc = column_index_from_string(etl.find_in_header(db, 'DD - End')) - 1
bad_date = []
valid = True
comp = ""
for row in db.rows[1:]:
try:
comp = datetime(day = int(etl.xstr(row[comp_col_loc].value)), \
month = int(etl.xstr(row[comp_col_loc+1].value)), \
year = int(etl.xstr(row[comp_col_loc+2].value)))
except:
valid = False
if valid:
try:
if comp < datetime.now() and 'completed' not in row[status_col_loc].value.lower():
bad_date.append('%s row %i' % (str(comp),row[0].row))
except:
bad_date.append('%s row %i' % (str(comp),row[0].row))
return db, ref, return_message('Bad date or Completion Date has passed for ' ,bad_date)
def algo20(db, ref):
#Output if completion date < current date and activity status not like completed
status_col_loc = column_index_from_string(
etl.find_in_header(db, 'Activity Status')) - 1
comp_col_loc = column_index_from_string(etl.find_in_header(db,
'DD - End')) - 1
bad_date = []
valid = True
comp = ""
for row in db.rows[1:]:
try:
comp = datetime(day = int(etl.xstr(row[comp_col_loc].value)), \
month = int(etl.xstr(row[comp_col_loc+1].value)), \
year = int(etl.xstr(row[comp_col_loc+2].value)))
except:
valid = False
if valid:
try:
if comp < datetime.now(
) and 'completed' not in row[status_col_loc].value.lower():
bad_date.append('%s row %i' % (str(comp), row[0].row))
except:
bad_date.append('%s row %i' % (str(comp), row[0].row))
return db, ref, return_message(
'Bad date or Completion Date has passed for ', bad_date)
def iter_rows(workbook_name,
sheet_name,
xml_source,
shared_date,
string_table,
range_string='',
row_offset=0,
column_offset=0):
archive = get_archive_file(workbook_name)
source = xml_source
if range_string:
min_col, min_row, max_col, max_row = get_range_boundaries(
range_string, row_offset, column_offset)
else:
min_col, min_row, max_col, max_row = read_dimension(xml_source=source)
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col) + 1
max_row += 6
style_table = read_style_table(archive.read(ARC_STYLE))
source.seek(0)
p = iterparse(source)
return get_squared_range(p, min_col, min_row, max_col, max_row,
string_table, style_table, shared_date)
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if start_row is None or start_column is None or end_row is None or end_column is None:
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column + 1), start_row + 1, get_column_letter(end_column + 1), end_row + 1)
elif len(range_string.split(':')) != 2:
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
# Make sure top_left cell exists - is this necessary?
min_col, min_row = coordinate_from_string(range_string.split(':')[0])
max_col, max_row = coordinate_from_string(range_string.split(':')[1])
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col)
# Blank out the rest of the cells in the range
for col in xrange(min_col, max_col + 1):
for row in xrange(min_row, max_row + 1):
if not (row == min_row and col == min_col):
# PHPExcel adds cell and specifically blanks it out if it doesn't exist
self._get_cell('%s%s' % (get_column_letter(col), row)).value = None
self._get_cell('%s%s' % (get_column_letter(col), row)).merged = True
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
def algo19(db,ref):
#Column T: must be a date>= 25/04/2015 and >=Column S
start_col_loc = column_index_from_string(etl.find_in_header(db, 'DD - Start')) - 1
comp_col_loc = column_index_from_string(etl.find_in_header(db, 'DD - End')) - 1
bad_date = []
start = ""
comp = ""
for row in db.rows[1:]:
try:
start = datetime(day = int(etl.xstr(row[start_col_loc].value)), \
month = int(etl.xstr(row[start_col_loc+1].value)), \
year = int(etl.xstr(row[start_col_loc+2].value)))
comp = datetime(day = int(etl.xstr(row[comp_col_loc].value)), \
month = int(etl.xstr(row[comp_col_loc+1].value)), \
year = int(etl.xstr(row[comp_col_loc+2].value)))
if comp < start:
bad_date.append(str(start) + ' @ row ' + str(row[0].row))
except:
bad_date.append(str(start) + ' @ row ' + str(row[0].row))
return db, ref, return_message('Malformatted date or before Start Date', bad_date)
def get_params(filename, chipstring = 'C', xstring = 'D', ystring = 'E', mstring = 'F'):
import openpyxl
from openpyxl.cell import column_index_from_string
wb = openpyxl.load_workbook(filename)
sheet = wb.get_sheet_by_name('Sheet1')
chip_arr = np.array([])
x_arr = np.array([])
y_arr = np.array([])
m_arr = np.array([])
chipcol = column_index_from_string(chipstring)
xcol = column_index_from_string(xstring)
ycol = column_index_from_string(ystring)
mcol = column_index_from_string(mstring)
for i in np.arange(1,10):
chip = sheet.cell(row=i, column=chipcol).value
chip_arr = np.append(chip_arr, chip)
xval = sheet.cell(row=i, column=xcol).value
x_arr = np.append(x_arr, xval)
yval = sheet.cell(row=i, column=ycol).value
if chip == 1. or chip == 1:
yval += CHIP2YLEN
y_arr = np.append(y_arr, yval)
mval = sheet.cell(row=i, column=mcol).value
m_arr = np.append(m_arr, mval)
return [x_arr, y_arr, m_arr]
def iter_rows(self, range_string='', row_offset=0, column_offset=1):
""" Returns a squared range based on the `range_string` parameter,
using generators.
:param range_string: range of cells (e.g. 'A1:C4')
:type range_string: string
:param row_offset: additional rows (e.g. 4)
:type row: int
:param column_offset: additonal columns (e.g. 3)
:type column: int
:rtype: generator
"""
if range_string:
min_col, min_row, max_col, max_row = get_range_boundaries(range_string, row_offset, column_offset)
else:
min_col = column_index_from_string(self.min_col)
max_col = column_index_from_string(self.max_col) + 1
min_row = self.min_row
max_row = self.max_row + 6
return self.get_squared_range(min_col, min_row, max_col, max_row)
def reach(self, db):
"""how is the vdc accessible"""
#TODO: use index() method instead of dict
dist_loc = column_index_from_string(etl.find_in_header(db,
'District')) - 1
vdc_loc = column_index_from_string(
etl.find_in_header(db, 'VDC / Municipalities')) - 1
acc_look = column_index_from_string(
etl.find_in_header(self.s_dict['acc'],
'DistrictVDC Concatenation')) - 1
acc_acc = column_index_from_string(
etl.find_in_header(self.s_dict['acc'], 'NeKSAP ACCESS')) - 1
dist_vals = etl.get_values(db.columns[dist_loc][1:])
vdc_vals = etl.get_values(db.columns[vdc_loc][1:])
acc_look_vals = etl.get_values(
self.s_dict['acc'].columns[acc_look][1:])
acc_acc_vals = etl.get_values(self.s_dict['acc'].columns[acc_acc][1:])
#make dict for access, concatenate dist and vdc
acc_dict = dict(zip(acc_look_vals, acc_acc_vals))
d_v_conc = [dist_vals[i] + vdc_vals[i] for i in xrange(len(dist_vals))]
ret = []
for v in d_v_conc:
if v in acc_dict and v != '':
ret.append(acc_dict[v])
else:
ret.append('')
return ret
def swapValues(self):
# Swap columns based on matching rules
# Check date
if len(SwapValues.yearSV.get()) != 4:
tkinter.messagebox.showerror(
'Date Error', 'Please enter the full date (Ex 2016 not 16)')
# Perform swap if date format is correct
else:
# Prep workbook and sheet
SwapValues.newWb = openpyxl.Workbook()
SwapValues.newSheet = SwapValues.newWb.get_sheet_by_name('Sheet')
SwapValues.newSheet.title = 'Swapped Sheet'
# Format sheet with correct headers
for key, value in sorted(self.headersDict.items()):
SwapValues.newSheet.cell(
row=self.startRow,
column=column_index_from_string(key)).value = value
# Pull data from matching defined place
for rowNum in range(1, FileSelection.sheet.max_row):
for self.budgetPeriod in range(1, 13):
self.printRow += 1
for key, value in sorted(self.mappingRulesDict.items()):
SwapValues.newSheet.cell(
row=self.printRow,
column=column_index_from_string(
key)).value = self.getVal(value, rowNum)
# Export new workbook
self.exportSheet()
def consolidate_specfic(baseline_ret, wsl, which_sheet):
"""consolidate a given sheet type and remove old data"""
cons_wb = Workbook()
cons = cons_wb.active
cons.title = which_sheet
to_add = []
ag_skip = []
cnts = {}
baseline_ret = baseline_ret.get_sheet_by_name(which_sheet)
#trim baseline to be between Implementing agency and additional comments
iav = column_index_from_string(find_in_header(baseline_ret,'Implementing Agency'))-1
acv = column_index_from_string(find_in_header(baseline_ret,'Additional comments'))
luv = column_index_from_string(find_in_header(baseline_ret,'Last Update'))
baseline = Workbook().active
for i,r in enumerate(baseline_ret.rows):
#insert header
if i == 0:
baseline.append(get_values(r[iav:acv]) + get_values([r[luv-1]]))
#else, add last update dates. try and format if not null
else:
if r[luv-1].value is not None:
try:
baseline.append(get_values(r[iav:acv]) + [r[luv-1].value.strftime('%d/%m/%Y')])
except Exception, e:
baseline.append(get_values(r[iav:acv]) + [convert_date(r[luv-1].value, '%d/%m/%Y', '%d/%m/%Y')])
def get_data_rows(sheet, col_ids, data_location):
left_column, left_row, right_column, right_row, right_defined = \
get_square_indices(sheet, data_location)
left_column = max(left_column, column_index_from_string(min(*col_ids)))
right_column = min(right_column, column_index_from_string(max(*col_ids)))
data = get_range(sheet, left_column, left_row, right_column, right_row)
for i, row in enumerate(data):
selected = tuple(filter(lambda x: x.column in col_ids, row))
yield left_row + i, selected
def __init__(self, files, params):
ExcelHandler.filePath = files.filePath.get()
ExcelHandler.wb = files.wb
ExcelHandler.sheet = files.sheet
try:
ExcelHandler.searchCol = column_index_from_string(str(params.searchCol.get()).upper())
ExcelHandler.pasteCol = column_index_from_string(str(params.pasteCol.get()).upper())
except ValueError:
ParamSelection.sColEntry.focus_set()
tkinter.messagebox.showerror('Error', 'Please enter valid column names.')
def validatePNCol(self, columnValue): if not (columnValue.isalpha() or columnValue == ''): return False elif columnValue != '': try: column_index_from_string(columnValue.upper()) except ValueError: return False return True
def col_row_range(self):
"""
获取行号和列号的范围 A4:Q7 -> [4,7] [A,Q]
"""
cell_start,cell_end = self.theader_range.split(":")
min_col,min_row = ce.column_index_from_string(self.ws[cell_start].column),self.ws[cell_start].row
max_col,max_row = ce.column_index_from_string(self.ws[cell_end].column),self.ws[cell_end].row
self.rownum_range = [min_row,max_row]
self.colnum_range = [ce.get_column_letter(min_col),ce.get_column_letter(max_col)]
#表头区域大小
self.theader_area_size = [max_row-min_row+1,max_col-min_col+1]
def form_column_list(first_char, last_char):
"""
form_column_list(first_char, last_char)
Формирование листа букв A-...: [first_char, ..., last_char]
Возвращает лист.
"""
start_index = column_index_from_string(first_char)
end_index = column_index_from_string(last_char)
range_list = range(start_index, end_index+1)
out_list = [get_column_letter(i) for i in range_list]
return out_list
def get_dor_sheet_and_day_column(dor, sheet, date, next_month_after=31):
day = date.day
if day <= next_month_after:
month = "{:%B}".format(date)
else: # go to next month
next_month = date + datetime.timedelta(15)
month = "{:%B}".format(next_month)
dor_sheet = dor.get_sheet_by_name(sheet)
month_cell = search_in_column(dor_sheet, month, 1, start=1, end=dor_sheet.max_column)
month_column_index = column_index_from_string(month_cell.column)
day_cell = search_in_column(dor_sheet, day, 2, start=month_column_index, end=dor_sheet.max_column)
column_index = column_index_from_string(day_cell.column)
return dor_sheet, column_index
def get_all_cells(self,cell_ranges):
"""
根据给定的区域，获取该区域中的所有单元格 A4:A5
"""
result_cells = []
min_col = 0
min_row = 0
max_col = 0
max_row = 0
cell_start,cell_end = cell_ranges.split(":")
min_col,min_row = ce.column_index_from_string(self.ws[cell_start].column),self.ws[cell_start].row
max_col,max_row = ce.column_index_from_string(self.ws[cell_end].column),self.ws[cell_end].row
for c_t in range(min_col,max_col+1):
for r_t in range(min_row,max_row+1):
result_cells.append(ce.get_column_letter(c_t)+str(r_t))
return result_cells
def write_worksheet_cols(doc, worksheet, style_table):
"""Write worksheet columns to xml."""
if worksheet.column_dimensions:
start_tag(doc, 'cols')
for column_string, columndimension in \
sorted(iteritems(worksheet.column_dimensions)):
col_index = column_index_from_string(column_string)
col_def = {'min': str(col_index), 'max': str(col_index)}
if columndimension.width != -1:
col_def['customWidth'] = '1'
if not columndimension.visible:
col_def['hidden'] = 'true'
if columndimension.outline_level > 0:
col_def['outlineLevel'] = str(columndimension.outline_level)
if columndimension.collapsed:
col_def['collapsed'] = 'true'
if columndimension.auto_size:
col_def['bestFit'] = 'true'
if column_string in worksheet._styles:
col_def['style'] = str(style_table[hash(
worksheet.get_style(column_string))])
if columndimension.width > 0:
col_def['width'] = str(columndimension.width)
else:
col_def['width'] = '9.10'
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
def write_worksheet_cols(doc, worksheet, style_table=None):
"""Write worksheet columns to xml.
style_table is ignored but required
for compatibility with the dumped worksheet <cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
col_def = dict(dimension)
style = worksheet._styles.get(label)
if col_def == {} and style is None:
continue
elif style is not None:
col_def['style'] = '%d' % style
idx = column_index_from_string(label)
cols.append((idx, col_def))
if cols == []:
return
start_tag(doc, 'cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
col_def.update({'min':v, 'max':v})
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
def write_worksheet_cols(doc, worksheet, style_table):
"""Write worksheet columns to xml."""
if worksheet.column_dimensions:
start_tag(doc, 'cols')
for column_string, columndimension in \
sorted(iteritems(worksheet.column_dimensions)):
col_index = column_index_from_string(column_string)
col_def = {'min': str(col_index), 'max': str(col_index)}
if columndimension.width != -1:
col_def['customWidth'] = '1'
if not columndimension.visible:
col_def['hidden'] = 'true'
if columndimension.outline_level > 0:
col_def['outlineLevel'] = str(columndimension.outline_level)
if columndimension.collapsed:
col_def['collapsed'] = 'true'
if columndimension.auto_size:
col_def['bestFit'] = 'true'
if column_string in worksheet._styles:
col_def['style'] = str(style_table[hash(worksheet.get_style(column_string))])
if columndimension.width > 0:
col_def['width'] = str(columndimension.width)
else:
col_def['width'] = '9.10'
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row = int(element.get("r"))
if max_row is not None and row > max_row:
break
if min_row <= row:
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
data_type = cell.get('t', 'n')
style_id = cell.get('s')
formula = cell.findtext(FORMULA_TAG)
value = cell.findtext(VALUE_TAG)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(self, row, column_str,
value, data_type, style_id)
if element.tag in (CELL_TAG, VALUE_TAG, FORMULA_TAG):
# sub-elements of rows should be skipped
continue
element.clear()
def write_worksheet_cols(doc, worksheet):
"""Write worksheet columns to xml."""
if worksheet.column_dimensions:
start_tag(doc, "cols")
for column_string, columndimension in worksheet.column_dimensions.iteritems():
col_index = column_index_from_string(column_string)
col_def = {}
col_def["collapsed"] = str(columndimension.style_index)
col_def["min"] = str(col_index)
col_def["max"] = str(col_index)
if columndimension.width != worksheet.default_column_dimension.width:
col_def["customWidth"] = "true"
if not columndimension.visible:
col_def["hidden"] = "true"
if columndimension.outline_level > 0:
col_def["outlineLevel"] = str(columndimension.outline_level)
if columndimension.collapsed:
col_def["collapsed"] = "true"
if columndimension.auto_size:
col_def["bestFit"] = "true"
if columndimension.width > 0:
col_def["width"] = str(columndimension.width)
else:
col_def["width"] = "9.10"
tag(doc, "col", col_def)
end_tag(doc, "cols")
def GetSched(day, n):
for i in range(1, 12):
done = False
col = get_column_letter(
column_index_from_string((w.coordinate)[0]) + n)
cls = col + str(int((w.coordinate)[1:]) + i)
time = 'A' + str(int((w.coordinate)[1:]) + i)
for range_ in sheet.merged_cell_ranges:
merged_cells = list(openpyxl.utils.rows_from_range(range_))
for row in merged_cells:
if cls in row:
if cls == merged_cells[0][0] and sheet[
cls].value != None and "Week" not in sheet[
cls].value:
apd = [
list(filter(None, sheet[cls].value.split('\n'))),
sheet[time].value
]
for i in range(1, len(merged_cells)):
apd.append(sheet[time[0] +
(merged_cells[i][0])[1:]].value)
day.append(apd)
done = True
if sheet[cls].value != None and "Week" not in sheet[
cls].value and done == False:
day.append([
list(filter(None, sheet[cls].value.split('\n'))),
sheet[time].value
])
for i in day:
if len(i) > 2:
day[day.index(i)] = [
i[0], ((i[1].split("-"))[0] + '-' + (i[-1].split("-"))[1])
]
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source)
for _event, element in p:
if element.tag == '{%s}c' % SHEET_MAIN_NS:
coord = element.get('r')
column_str, row = RE_COORDINATE.match(coord).groups()
row = int(row)
column = column_index_from_string(column_str)
if min_col <= column <= max_col and min_row <= row <= max_row:
data_type = element.get('t', 'n')
style_id = element.get('s')
formula = element.findtext('{%s}f' % SHEET_MAIN_NS)
value = element.findtext('{%s}v' % SHEET_MAIN_NS)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=" + formula
if not (value or formula or style_id):
# this cell is pointless and should not have been
# written in the first place
continue
yield RawCell(row, column_str, coord, value, data_type,
style_id, None)
# sub-elements of cells should be skipped
if (element.tag == '{%s}v' % SHEET_MAIN_NS
or element.tag == '{%s}f' % SHEET_MAIN_NS):
continue
element.clear()
def coord2rowcol(coord):
"""
Transform a spreadsheet style coordinate into a 0, 0 based index.
"""
col, row_idx = coordinate_from_string(coord)
col_idx = column_index_from_string(col)
return row_idx - 1, col_idx - 1
def write_cols(worksheet):
"""Write worksheet columns to xml.
<cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
dimension.style = worksheet._styles.get(label)
col_def = dict(dimension)
if col_def == {}:
continue
idx = column_index_from_string(label)
cols.append((idx, col_def))
if not cols:
return
el = Element('cols')
for idx, col_def in sorted(cols):
v = "%d" % idx
cmin = col_def.get('min') or v
cmax = col_def.get('max') or v
col_def.update({'min': cmin, 'max': cmax})
el.append(Element('col', col_def))
return el
def write_sheetviews(worksheet):
views = Element('sheetViews')
sheetviewAttrs = {'workbookViewId': '0'}
if not worksheet.show_gridlines:
sheetviewAttrs['showGridLines'] = '0'
view = SubElement(views, 'sheetView', sheetviewAttrs)
selectionAttrs = {}
topLeftCell = worksheet.freeze_panes
if topLeftCell:
colName, row = coordinate_from_string(topLeftCell)
column = column_index_from_string(colName)
pane = 'topRight'
paneAttrs = {}
if column > 1:
paneAttrs['xSplit'] = str(column - 1)
if row > 1:
paneAttrs['ySplit'] = str(row - 1)
pane = 'bottomLeft'
if column > 1:
pane = 'bottomRight'
paneAttrs.update(dict(topLeftCell=topLeftCell,
activePane=pane,
state='frozen'))
view.append(Element('pane', paneAttrs))
selectionAttrs['pane'] = pane
if row > 1 and column > 1:
SubElement(view, 'selection', {'pane': 'topRight'})
SubElement(view, 'selection', {'pane': 'bottomLeft'})
selectionAttrs.update({'activeCell': worksheet.active_cell,
'sqref': worksheet.selected_cell})
SubElement(view, 'selection', selectionAttrs)
return views
def _get_row(self, element, min_col=1, max_col=None):
"""Return cells from a particular row"""
col_counter = min_col
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
for gap in range(col_counter, column):
# pad row with missing cells
yield ReadOnlyCell(self, row, None, None)
data_type = cell.get('t', 'n')
style_id = int(cell.get('s', 0))
formula = cell.findtext(FORMULA_TAG)
value = cell.find(VALUE_TAG)
if value is not None:
value = value.text
if formula is not None:
if not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(self, row, column_str,
value, data_type, style_id)
col_counter = column + 1
if max_col is not None:
while col_counter <= max_col:
yield ReadOnlyCell(self, row, None, None)
col_counter += 1
def append(self, list_or_dict):
"""Appends a group of values at the bottom of the current sheet.
* If it's a list: all values are added in order, starting from the first column
* If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)
:param list_or_dict: list or dict containing values to append
:type list_or_dict: list/tuple or dict
Usage:
* append(['This is A1', 'This is B1', 'This is C1'])
* **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
* **or** append({0 : 'This is A1', 2 : 'This is C1'})
:raise: TypeError when list_or_dict is neither a list/tuple nor a dict
"""
row_idx = len(self.row_dimensions)
if isinstance(list_or_dict, (list, tuple)):
for col_idx, content in enumerate(list_or_dict):
self.cell(row=row_idx, column=col_idx).value = content
elif isinstance(list_or_dict, dict):
for col_idx, content in iteritems(list_or_dict):
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx) - 1
self.cell(row=row_idx, column=col_idx).value = content
else:
raise TypeError('list_or_dict must be a list or a dict')
def write_worksheet_sheetviews(doc, worksheet):
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
selectionAttrs = {}
topLeftCell = worksheet.freeze_panes
if topLeftCell:
colName, row = coordinate_from_string(topLeftCell)
column = column_index_from_string(colName)
pane = 'topRight'
paneAttrs = {}
if column > 1:
paneAttrs['xSplit'] = str(column - 1)
if row > 1:
paneAttrs['ySplit'] = str(row - 1)
pane = 'bottomLeft'
if column > 1:
pane = 'bottomRight'
paneAttrs.update(dict(topLeftCell=topLeftCell,
activePane=pane,
state='frozen'))
tag(doc, 'pane', paneAttrs)
selectionAttrs['pane'] = pane
if row > 1 and column > 1:
tag(doc, 'selection', {'pane': 'topRight'})
tag(doc, 'selection', {'pane': 'bottomLeft'})
selectionAttrs.update({'activeCell': worksheet.active_cell,
'sqref': worksheet.selected_cell})
tag(doc, 'selection', selectionAttrs)
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
def write_worksheet_sheetviews(doc, worksheet):
start_tag(doc, "sheetViews")
start_tag(doc, "sheetView", {"workbookViewId": "0"})
selectionAttrs = {}
topLeftCell = worksheet.freeze_panes
if topLeftCell:
colName, row = coordinate_from_string(topLeftCell)
column = column_index_from_string(colName)
pane = "topRight"
paneAttrs = {}
if column > 1:
paneAttrs["xSplit"] = str(column - 1)
if row > 1:
paneAttrs["ySplit"] = str(row - 1)
pane = "bottomLeft"
if column > 1:
pane = "bottomRight"
paneAttrs.update(dict(topLeftCell=topLeftCell, activePane=pane, state="frozen"))
tag(doc, "pane", paneAttrs)
selectionAttrs["pane"] = pane
if row > 1 and column > 1:
tag(doc, "selection", {"pane": "topRight"})
tag(doc, "selection", {"pane": "bottomLeft"})
selectionAttrs.update({"activeCell": worksheet.active_cell, "sqref": worksheet.selected_cell})
tag(doc, "selection", selectionAttrs)
end_tag(doc, "sheetView")
end_tag(doc, "sheetViews")
def get_cells(p,
min_row,
min_col,
max_row,
max_col,
_re_coordinate=RE_COORDINATE):
for _event, element in p:
if element.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}c':
coord = element.get('r')
column_str, row = _re_coordinate.match(coord).groups()
row = int(row)
column = column_index_from_string(column_str)
if min_col <= column <= max_col and min_row <= row <= max_row:
data_type = element.get('t', 'n')
style_id = element.get('s')
value = element.findtext(
'{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v'
)
yield RawCell(row, column_str, coord, value, data_type,
style_id, None)
if element.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v':
continue
element.clear()
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source, tag=[ROW_TAG], remove_blank_text=True)
for _event, element in p:
if element.tag == ROW_TAG:
row = int(element.get("r"))
if max_row is not None and row > max_row:
break
if min_row <= row:
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
data_type = cell.get('t', 'n')
style_id = cell.get('s')
formula = cell.findtext(FORMULA_TAG)
value = cell.findtext(VALUE_TAG)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(row, column_str, value, data_type,
style_id)
if element.tag in (CELL_TAG, VALUE_TAG, FORMULA_TAG):
# sub-elements of rows should be skipped
continue
element.clear()
def write_cols(xf, worksheet, style_table=None):
"""Write worksheet columns to xml.
style_table is ignored but required
for compatibility with the dumped worksheet <cols> may never be empty -
spec says must contain at least one child
"""
cols = []
for label, dimension in iteritems(worksheet.column_dimensions):
dimension.style = worksheet._styles.get(label)
col_def = dict(dimension)
if col_def == {}:
continue
idx = column_index_from_string(label)
cols.append((idx, col_def))
if not cols:
return
with xf.element('cols'):
for idx, col_def in sorted(cols):
v = "%d" % idx
cmin = col_def.get('min') or v
cmax = col_def.get('max') or v
col_def.update({'min': cmin, 'max': cmax})
c = Element('col', col_def)
xf.write(c)
def create_get_filter_dict(trziste, name, cfg):
column = cfg[name]
column_ix = column_index_from_string(column) - 1
def f(row):
return { 'trziste' : trziste,
name : row[column_ix].value }
return f
def _write_comment_shape(self, root, comment, idx):
# get zero-indexed coordinates of the comment
row = comment._parent.row - 1
column = column_index_from_string(comment._parent.column) - 1
attrs = {
"id": "_x0000_s%s" % (idx+1026),
"type": "#_x0000_t202",
"style": "position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden",
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = SubElement(root, "{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns, {"color2":"#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns, {"color":"black", "obscured":"t"})
SubElement(shape, "{%s}path" % vmlns, {"{%s}connecttype"%officens:"none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns, {"style":"mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns, {"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = str(row)
SubElement(client_data, "{%s}Column" % excelns).text = str(column)
def _write_comment_shape(self, root, comment, idx):
# get zero-indexed coordinates of the comment
row = comment._parent.row - 1
column = column_index_from_string(comment._parent.column) - 1
attrs = {
"id": "_x0000_s%s" % (idx+1026),
"type": "#_x0000_t202",
"style": "position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden",
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = SubElement(root, "{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns, {"color2":"#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns, {"color":"black", "obscured":"t"})
SubElement(shape, "{%s}path" % vmlns, {"{%s}connecttype"%officens:"none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns, {"style":"mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns, {"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = str(row)
SubElement(client_data, "{%s}Column" % excelns).text = str(column)
def importDatabase(self, file_name):
from openpyxl.cell import column_index_from_string
wb = load_workbook(filename=file_name, use_iterators=True)
ws = wb.get_sheet_by_name(name='Members')
# Read and confirm the header
for row in ws.iter_rows():
membAttr = {}
for cell in row:
if cell._value:
col = column_index_from_string(cell.column)
if cell.row > 1:
# Read the remaining members
membAttr[self.impExpMemberHeader[col - 1]] = cell.value
elif cell.row == 1 and cell.value != self.impExpMemberHeader[col - 1]:
self.statBar.showMessage("Error in header in '{}'".format(file_name), Util.messageDelay)
return False
if membAttr:
member = self.getMember(membAttr['FirstName'], membAttr['LastName'], membAttr['Birthday'])
if member:
self.updateItem('Member', membAttr, member)
else:
self.addItem('Member', membAttr)
return True
def write_worksheet_cols(doc, worksheet):
"""Write worksheet columns to xml."""
if worksheet.column_dimensions:
start_tag(doc, 'cols')
for column_string, columndimension in \
worksheet.column_dimensions.iteritems():
col_index = column_index_from_string(column_string)
col_def = {}
col_def['collapsed'] = str(columndimension.style_index)
col_def['min'] = str(col_index)
col_def['max'] = str(col_index)
if columndimension.width != \
worksheet.default_column_dimension.width:
col_def['customWidth'] = 'true'
if not columndimension.visible:
col_def['hidden'] = 'true'
if columndimension.outline_level > 0:
col_def['outlineLevel'] = str(columndimension.outline_level)
if columndimension.collapsed:
col_def['collapsed'] = 'true'
if columndimension.auto_size:
col_def['bestFit'] = 'true'
if columndimension.width > 0:
col_def['width'] = str(columndimension.width)
else:
col_def['width'] = '9.10'
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
def validateEntry(self, varName, curEntryVal): # Validates the entry based on entry type. Returns True if pass, False if fail if self.nameDict[varName]['type'] == 'column': if not (curEntryVal.isalpha() or curEntryVal == ''): return False elif curEntryVal != '': try: column_index_from_string(str(curEntryVal).upper()) except ValueError: return False elif ParamSelection.offsetMode.get() == 'char': if not (curEntryVal.isdigit() or curEntryVal == ''): return False return True
def checkData(self, rowNum, IDTag):
# Iterate though all selected data entry columns
for columnName, propertyDict in ColumnSelection.columnsToImportDict.items():
# Get value of IFS control
pyperclip.copy(propertyDict[1])
try:
subprocess.call(['helper\GetControlValue.exe'])
except FileNotFoundError:
print('Could not locate GetControlValue.exe')
# Store text in IFS control
controlText = str(pyperclip.paste())
# Get value of current cell
curCell = FileSelection.sheet.cell(row=rowNum, column=column_index_from_string(columnName))
excelText = str(curCell.value)
# Compare values for non blank cells
if excelText != None:
if controlText == excelText:
# print(IDTag + ' checked. PASS' ### Debugging ###
pass
# Data does not match
else:
if IDTag not in ColumnSelection.mismatchList:
ColumnSelection.mismatchList.append(IDTag)
# print('Excel IFS mismatch') ### Debugging ###
else:
pass
def write_worksheet_sheetviews(doc, worksheet):
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
selectionAttrs = {}
topLeftCell = worksheet.freeze_panes
if topLeftCell:
colName, row = coordinate_from_string(topLeftCell)
column = column_index_from_string(colName)
pane = 'topRight'
paneAttrs = {}
if column > 1:
paneAttrs['xSplit'] = str(column - 1)
if row > 1:
paneAttrs['ySplit'] = str(row - 1)
pane = 'bottomLeft'
if column > 1:
pane = 'bottomRight'
paneAttrs.update(
dict(topLeftCell=topLeftCell, activePane=pane, state='frozen'))
tag(doc, 'pane', paneAttrs)
selectionAttrs['pane'] = pane
if row > 1 and column > 1:
tag(doc, 'selection', {'pane': 'topRight'})
tag(doc, 'selection', {'pane': 'bottomLeft'})
selectionAttrs.update({
'activeCell': worksheet.active_cell,
'sqref': worksheet.selected_cell
})
tag(doc, 'selection', selectionAttrs)
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
def _get_row(self, element, min_col=1, max_col=None):
"""Return cells from a particular row"""
col_counter = min_col
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
for gap in range(col_counter, column):
# pad row with missing cells
yield ReadOnlyCell(self, row, None, None)
data_type = cell.get('t', 'n')
style_id = int(cell.get('s', 0))
formula = cell.findtext(FORMULA_TAG)
value = cell.find(VALUE_TAG)
if value is not None:
value = value.text
if formula is not None:
if not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=%s" % formula
yield ReadOnlyCell(self, row, column_str, value, data_type,
style_id)
col_counter = column + 1
if max_col is not None:
while col_counter <= max_col:
yield ReadOnlyCell(self, row, None, None)
col_counter += 1
def write_worksheet_cols(doc, worksheet):
"""Write worksheet columns to xml."""
if worksheet.column_dimensions:
start_tag(doc, 'cols')
for column_string, columndimension in \
worksheet.column_dimensions.iteritems():
col_index = column_index_from_string(column_string)
col_def = {}
col_def['collapsed'] = str(columndimension.style_index)
col_def['min'] = str(col_index)
col_def['max'] = str(col_index)
if columndimension.width != \
worksheet.default_column_dimension.width:
col_def['customWidth'] = 'true'
if not columndimension.visible:
col_def['hidden'] = 'true'
if columndimension.outline_level > 0:
col_def['outlineLevel'] = str(columndimension.outline_level)
if columndimension.collapsed:
col_def['collapsed'] = 'true'
if columndimension.auto_size:
col_def['bestFit'] = 'true'
if columndimension.width > 0:
col_def['width'] = str(columndimension.width)
else:
col_def['width'] = '9.10'
tag(doc, 'col', col_def)
end_tag(doc, 'cols')
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source)
for _event, element in p:
if element.tag == '{%s}c' % SHEET_MAIN_NS:
coord = element.get('r')
column_str, row = RE_COORDINATE.match(coord).groups()
row = int(row)
column = column_index_from_string(column_str)
if min_col <= column <= max_col and min_row <= row <= max_row:
data_type = element.get('t', 'n')
style_id = element.get('s')
formula = element.findtext('{%s}f' % SHEET_MAIN_NS)
value = element.findtext('{%s}v' % SHEET_MAIN_NS)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=" + formula
yield RawCell(row, column_str, coord, value, data_type, style_id, None)
# sub-elements of cells should be skipped
if (element.tag == '{%s}v' % SHEET_MAIN_NS
or element.tag == '{%s}f' % SHEET_MAIN_NS):
continue
element.clear()
def append(self, list_or_dict):
"""Appends a group of values at the bottom of the current sheet.
* If it's a list: all values are added in order, starting from the first column
* If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)
:param list_or_dict: list or dict containing values to append
:type list_or_dict: list/tuple or dict
Usage:
* append(['This is A1', 'This is B1', 'This is C1'])
* **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
* **or** append({0 : 'This is A1', 2 : 'This is C1'})
:raise: TypeError when list_or_dict is neither a list/tuple nor a dict
"""
row_idx = len(self.row_dimensions)
if isinstance(list_or_dict, (list, tuple)):
for col_idx, content in enumerate(list_or_dict):
self.cell(row=row_idx, column=col_idx).value = content
elif isinstance(list_or_dict, dict):
for col_idx, content in iteritems(list_or_dict):
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx) - 1
self.cell(row=row_idx, column=col_idx).value = content
else:
raise TypeError("list_or_dict must be a list or a dict")
def create_get_atc_sifra(cfg):
column = cfg['columns']['ATC_naziv']
column_ix = column_index_from_string(column) - 1
def f(row):
return row[column_ix].value[:7]
return f
def create_get_opis(cfg):
column = cfg['opis']
column_ix = column_index_from_string(column) - 1
def f(row):
return row[column_ix].value
return f
def get_range_boundaries(range_string, row_offset=0, column_offset=1):
if ':' in range_string:
min_range, max_range = range_string.split(':')
min_col, min_row = coordinate_from_string(min_range)
max_col, max_row = coordinate_from_string(max_range)
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col) + 1
else:
min_col, min_row = coordinate_from_string(range_string)
min_col = column_index_from_string(min_col)
max_col = min_col + column_offset
max_row = min_row + row_offset
return (min_col, min_row, max_col, max_row)
def get_range_boundaries(range_string, row_offset=0, column_offset=1):
if ':' in range_string:
min_range, max_range = range_string.split(':')
min_col, min_row = coordinate_from_string(min_range)
max_col, max_row = coordinate_from_string(max_range)
min_col = column_index_from_string(min_col)
max_col = column_index_from_string(max_col) + 1
else:
min_col, min_row = coordinate_from_string(range_string)
min_col = column_index_from_string(min_col)
max_col = min_col + column_offset
max_row = min_row + row_offset
return (min_col, min_row, max_col, max_row)
