I would like to apply a specific format or assign a value to a range of Excel cells without iterating over each cell. I am currently using this script:
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
## With iterations
# Apply style
for i, rowOfCellObjects in enumerate(ws['A1':'C4']):
for n, cellObj in enumerate(rowOfCellObjects):
cellObj.fill = Font(name='Times New Roman')
# Assign singular value to all cells
for i, rowOfCellObjects in enumerate(ws['A1':'C4']):
for n, cellObj in enumerate(rowOfCellObjects):
cellObj.value = 3
wb.save("test.xlsx")
But I am looking for a shorter notation, like this:
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
## Without iterations
# Apply style
ws['A1':'C4'].fill = Font(name='Times New Roman')
# Assign singular value to all cells
ws['A1':'C4'].value = 3
wb.save("test.xlsx")
Does openpyxl
or another module offers something like this?
ps: I'm using Python 3.5
This is not quite exact. OpenPyxel allows to apply styles to columns and rows:
According to: https://openpyxl.readthedocs.io/en/stable/styles.html
Styles can also applied to columns and rows but note that this applies only to cells created (in Excel) after the file is closed. If you want to apply styles to entire rows and columns then you must apply the style to each cell yourself. This is a restriction of the file format:
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")
In Excel styles must be applied to individual cells because this is how the file format works. Because of the way it works, openpyxl does not provide the desired functionality, but it is possible with xlsxwriter.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With