Seems like a recipe for disaster, shifting formula references around!
This should move all formulas by a fixed amount (addrows) to cells further down and change the formulas to cells addrows further down.
Is that what you want to do?
What about the data? Do you need to move it?
What about the old formulas? Do you need to delete them?
You can easily make a function to move formulas to the right by a fixed amount too.
import openpyxl
import re
path2XL = '/home/pedro/myPython/openpyxl/xlsx_files/'
XLfile = 'XL_with_formulas.xlsx'
savename = 'changedXL.xlsx'
sF = openpyxl.load_workbook(path2XL + XLfile)
sheets = sF.sheetnames # only 1 sheet here: ['formulas']
def findFormula_coords(ws, startrow, startcol, maxrow, maxcol):
for row in ws.iter_rows(min_row=startrow, min_col=startcol, max_row=maxrow, max_col=maxcol):
yield [cell.coordinate for cell in row if cell.data_type == 'f']
# find all cells in a range which contain formulas
# returns a list of lists
oldcells = [i for i in findFormula_coords(sF['formulas'], 2, 1, 11, 6)]
# don't really need this
##def findFormula(ws, startrow, startcol, maxrow, maxcol):
## for row in ws.iter_rows(min_row=startrow, min_col=startcol, max_row=maxrow, max_col=maxcol):
## yield [cell.value for cell in row if cell.data_type == 'f']
##oldformulas = [i for i in findFormula(sF['formulas'], 2, 1, 11, 6)]
def copyFormula_down(oldcells, addrows):
# oldcells is a list with the coordinates of each cell in a row which contains a formula like ['C2', 'D2', 'E2', 'F2']
for j in range(len(oldcells)):
oldFormula = sF['formulas'][oldcells[j]].value # like: '=A2*B2'
rownum = re.search(r"\d+", oldcells[j]) # if oldcells[j] like 'C2' will find '2'
# increment the row number
newrownum = int(rownum[0]) + addrows
newCell = re.sub('\d+', str(newrownum), oldcells[j])
# find all cells named in the formula, I hope!
cells = re.findall(r"[A-Z]+\d+", oldFormula) # returns a list like ['A2', 'B2']
newcells = []
for c in range(len(cells)):
oldnum = re.search(r"\d+", cells[c]) # find the number in a string like 'A2'
newnum = int(oldnum[0]) + addrows # add the required number of rows
newcell = re.sub('\d+', str(newnum), cells[c]) # change 'A2' to 'A17' or similar
newcells.append(newcell) # make a list of the new cells for formulas
for c in range(len(cells)):
print(f"Old cell coordinates {cells[c]}, new cell coordinates {newcells[c]}") # old cell, new cell coordinates
newFormula = re.sub(cells[c], newcells[c], oldFormula)
# overwrite oldformula or only 1 part will be changed
# the list of cell coordinates in the formula could be long!
oldFormula = newFormula
sF['formulas'][newCell].value=newFormula
# overwrite the old file, or save as a new file
# sF.save(path2XL + savename)
sF.save(path2XL + XLfile)
addrows = 20
for i in range(len(oldcells)):
# oldcells[i] is a list
copyFormula_down(oldcells[i], addrows)
print('Moved all formulas down {addrows} rows. Maybe we need to move the data too? And delete the old formulas?')This copied all formulas to 20 rows further down and changed the formula cell references accordingly.
Sadly, the new references contain no data, so all formulas show zero!
