Alter the style of all cells with openpyxl

Question

I'm using openpyxl 2.0.3 with python2.7.

Is there a way to apply a style to every cell in a worksheet? Lets say I want to change the font of all cells, similar to how I would open an excel document, hit ctrl+a, right click and change the format.

Answer 1

There is no method to do this. At the moment the best approach would probably be to set the style for all the relevant columns or rows

style = Style(…)
for col in 'ABCD':
     ws._styles['A'] = style

I think we'll be working on improving handling styles in coming releases.

Answer 2

The answer changing the default font with 2 or four lines for a single or multiple attributes respectively has rendered this hack obsolete. The references below are useful for other purposes.

---

I understand this question is effectively changing the default sheet (workbook?) font. This is essentially the same as my need to change the default font to match the other excel documents. There are many similar questions about changing font on a cell or column basis:

1. 2011-07-21 - Use openpyxl to edit a Excel2007 file (.xlsx) without changing its own styles?
2. 2011-12-09 - Setting styles in Openpyxl
3. 2014-05-23 - Alter the style of all cells with openpyxl
4. 2017-04-20 - Formatting Fonts and Alignment
5. 2018-03-07 - How to change font size in Python OpenPyXL
6. 2018-05-04 - Python Setting Style and Font Size in Excel

This hack is change the default workbook font. Clearly this is not supported by the API but I am happy to override the package. I started by searching for Calibri, a font I don't use on my computer:

Two lines in ..\openpyxl\styles\__init__.py look relevant:

from .fonts import Font, DEFAULT_FONT
from .named_styles import NamedStyle

Change in fonts.py:

# Hack default font
# DEFAULT_FONT = Font(name="Calibri", sz=11, family=2, b=False, i=False,
DEFAULT_FONT = Font(name="Calibri", sz=10, family=2, b=False, i=False,
                    color=Color(theme=1), scheme="minor")

Other places which appear to be worth looking are: named_styles.py, stylesheet.py, styleable.py, theme.py and workbook.py.

I'd appreciate any advice to tidy up changing the constant in fonts.py called from __init__.py.

Answer 3

The comments on the default font hack show DEFAULT_FONT class can be imported and the properties set directly before saving workbook:

from openpyxl.workbook import Workbook
from openpyxl.styles import DEFAULT_FONT
wb = Workbook()
wb.active['B3'] = "Hello"
DEFAULT_FONT.name = "Arial"
wb.save("DemoDefaultFont.xlsx")

More is needed to set multiple properties simultaneously. Copy the properties from a temporary Font object:

from openpyxl.workbook import Workbook
from openpyxl.styles import DEFAULT_FONT
from openpyxl.styles import Font
wb = Workbook()
wb.active['B3'] = "Hello"
_font = Font(name="Arial", sz=10, b=True)
{k: setattr(DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()}
wb.save("DemoDefaultFont.xlsx")

Further details: https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/fonts.html?highlight=default_font