Support for Operations on Ranges
Sorry that I don't have time to turn this into a proper PR/patch and from what I can tell this does not already exist. I believe OpenPYXL would benefit from a flexible way of implementing the operations on rectangular ranges of cells. This is currently a monkey patch but it could be pretty simply adapted into the native classes.
import openpyxl
cl = openpyxl.utils.get_column_letter
def rc(r1,c1,r2=None,c2=None):
return f'{cl(c1)}{r1}:{cl(c2)}{r2}' if r2 is not None else f'{cl(c1)}{r1}'
class RangeObject:
_cells = None
def __init__(self, cells):
self._cells = cells
def __setattr__(self, __name: str, __value) -> None:
if __name=='_cells':
self.__dict__[__name] = __value
return
n = len(self._cells)*len(self._cells[0])
if(isinstance(__value, tuple) or isinstance(__value, list) or 'ndarray' in str(type(__value))):
i=0
for row in self._cells:
if(isinstance(__value[i], tuple) or isinstance(__value[i], list) or 'ndarray' in str(type(__value[i]))):
j=0
for cell in row:
cell.__setattr__(__name,__value[i][j])
j+=1
i+=1
else:
for cell in row:
cell.__setattr__(__name,__value[i])
i+=1
else:
for row in self._cells:
for cell in row:
cell.__setattr__(__name,__value)
def __getattr__(self, __name: str):
return [[cell.__getattribute__(__name) for cell in row] for row in self._cells]
class Sheet():
_sh = None
def __init__(self, sh):
self._sh = sh
def __setattr__(self, name, value):
return self._sh.__dict__[name]==value
self.__setattr__ = __setattr__
def __getattr__(self, name):
return self._sh.__dict__[name]
def __getitem__(self, key):
if ':' in key:
return RangeObject(self._sh[key])
else:
return self._sh[key]
Example of use:
wb = openpyxl.Workbook()
sh = Sheet(wb.create_sheet('block ops'))
sh[rc(1,1,2,10)].value = range(20)*5000 #The range can be assigned to either scalar values,
#1D arrays/iterators (matching the total number of cells)
#or 2D arrays/iterators (in the shape of the RC dimensions)
sh[rc(1,1,1,10)].number_format = '#,##0.00'
sh[rc(1,1,2,5)].font = Font(bold=True)
wb.save('text sheet')