def copy_from_formula(st, en, first, second, shift=0):
for row in cols_from_range(f'{st}:{en}'):
for cell in row:
loca = cell[0] + str(int(cell[1:]) - shift)
second[
loca].value = f"={utils.quote_sheetname(first.title)}!{cell}"
second[loca].number_format = '#,##0.0'
def fill_from_loc_ref1(n, eq, form, loc1, loc2):
for row in cols_from_range(f'B{n}:{projection}{n}'):
for cell in row:
one = ws2[cell].offset(row=loc1).coordinate
two = ws2[cell].offset(row=loc2, column=-1).coordinate
ws2[cell] = f"={one}{eq}{two}-1"
ws2[cell].number_format = form
def get_cell_width(self, cell):
cell_range = self.get_merged_cell(cell)
if cell_range:
width = 0
for col in list(cols_from_range(str(cell_range))):
width += self.ws.column_dimensions[get_column_letter(
self.ws[col[0]].column)].width
else:
return round(width)
else:
return round(self.ws.column_dimensions[get_column_letter(
cell.column)].width)
def get_cell_width_list(self, cell, to_px=False):
ret = []
cell_range = self.get_merged_cell(cell)
if cell_range:
for col in list(cols_from_range(str(cell_range))):
ret.append(self.ws.column_dimensions[get_column_letter(
self.ws[col[0]].column)].width)
else:
ret.append(self.ws.column_dimensions[get_column_letter(
cell.column)].width)
if to_px:
ret = [round(width * 8.08) for width in ret]
return ret
def fill_years_across(a, b, sheet):
c = 0
for row in cols_from_range(f'{a}:{b}'):
for cell in row:
sheet[cell] = year + c
c += 1
first = 'A' + str(beg + 1)
last = 'G' + str(end)
print(first, last)
copy_range(f'{first}:{last}', sheet, ws1)
ws1.move_range("A32:G34", rows=-20)
# Grab the segment numbers from the segment sheet
c = ws1[first].offset(column=1).coordinate
d = ws1[first].offset(column=6, row=(end - beg - 1)).coordinate
print(c, d)
for row in cols_from_range(f'{c}:{d}'):
for cell in row:
ws1[cell].value = f"={utils.quote_sheetname(sheet.title)}!{cell}"
ws1[cell].number_format = '#,##0.0'
# Insert rows between segments, and add growth and percent of revenue
for i in range(end - beg):
ws1.insert_rows(beg + 2 + 3 * i)
ws1.insert_rows(beg + 2 + 3 * i)
for i in range(end - 1 - (beg)):
ws1[f'A{beg+2+i*3}'] = '% Growth'
ws1[f'A{beg+3+i*3}'] = '% of revenue'
ws1[ws1[f'{first}'].offset(row=-1).coordinate] = 'Sources of Revenue'
def findequations(name):
'''Takes all formula's from a excel work book and translates each to the equivalent expression.
Multicell ranges are expanded to a comma separated list. \n
The ordinary operators and the SUM function can be handled. If you need more functions. You have to impelent them in the modelclass.
In the model each cell reference is prefixed by <sheet name>_
Openpyxl is the fastest library and it has a tokenizer
but it can not read all values.
Therefor xlwings is used to read repeated formula's which Openpyxl will show as '='
input:
:name: Location of a excel sheeet
Returns:
:modeldic: A dictionary with formulars keyed by cell reference
'''
outdic = {}
wb = load_workbook(
name, read_only=True, data_only=False
) # to read the spresdsheet first save as xml then write it again
wb2 = xw.Book(name) # the same worksheet in xlwings
allsheets = wb.get_sheet_names()
for wsname in allsheets:
ws = wb[wsname]
ws2 = wb2.sheets(wsname) # the same sheet but in xlwings
formulacell = [
c for row in ws.rows for c in row
if c.value != None and c.data_type == 'f'
]
for cell in formulacell:
cellref = get_column_letter(cell.column) + str(cell.row)
if DEBUG: print('This cell:', cellref, cell.data_type, cell.value)
frml = cell.value if cell.value != '=' else ws2.range(
cellref).formula # To avoid emty repeating formula'rs
tok = Tokenizer(frml)
if DEBUG and False:
print("\n".join("%19s%15s%9s" % (t.value, t.type, t.subtype)
for t in tok.items))
# left hand term is <worksheet>!<column><row>=
lhs = wstrans(wsname) + get_column_letter(cell.column) + str(
cell.row)
out = [lhs + '=']
for t in tok.items:
if t.subtype == "RANGE":
#Find or create the sheetname
sheet0 = t.value.split(
'!')[0] if '!' in t.value else wsname
sheet = wstrans(sheet0)
# print(t.value,'---->')
# Get all the cells in the range columwize
# the nested list comprehension makes the list works for square ranges.
# the split construct drops the sheet name from the range name if any
thisrange = [
sheet + i for subtupler in cols_from_range((
t.value.split('!')[-1])) for i in subtupler
]
# put a ',' between each element i the list
thistext = ','.join(thisrange)
#print(thisrange)
out = out + [thistext]
else:
out.append(t.value)
#create the equation and get rid of the !
equation = ''.join(out).replace('!', '_')
outdic[lhs] = equation
#print(equation)
return outdic