Is it possible to apply a style to a whole sheet with openpyxl?

user_11647805 0
Viewed 1214

I am learning to use the openpyxl module and I am trying to make a standard style in a sheet. I have been searching for an answer in documentation and other people questions but I don't find it.

I have found ways to change the style of single cells and I could loop it to apply to a high amount of cells, but that doesn't solve my problem as I need the whole sheet to fit the style.

So my question is if it's possible to do this and how.

Thank you.

I have read this documentation but it doesn't answer this question: https://openpyxl.readthedocs.io/en/stable/styles.html

from openpyxl.styles import NamedStyle, Font, Border, Side
import openpyxl

workbook = openpyxl.Workbook()
highlight = NamedStyle(name = "highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
sheet = workbook.get_sheet_by_name('Sheet')

sheet['A1'].style = highlight

workbook.save('example.xlsx')

In this example, I can set the format for a single cell but I need it to be applied in the whole sheet.

openpyxl python-3.x python
3 Answers

While you can alter the Normal style, this is probably not the best approach. It is better to create your style and apply it to every cell in the worksheet. This is actually quite quick avoids any unwanted side-effects.

user_23851330

Use:

import openpyxl
from openpyxl.styles import NamedStyle

standard_var = NamedStyle(
    name="thounsed_sep",
    number_format='#,###'
)

filename = r"test.xlsx"
wb = openpyxl.load_workbook(filename)
wb.add_named_style(standard_var)
for ws in wb.worksheets:
    for cells in ws.rows:
        for cell in cells:
            cell.style = "thounsed_sep"
wb.save()

This changes the formatting of the used range of each sheet (i.e. the range that is filled with any text or number contents).

user_139683920

You can get current whole worksheet range via sheet.dimensions:

sheet[ sheet.dimensions ].style = highlight
user_13839420
Related