Multiple NamedStyle in a single excel sheet - Python openpyxl

Question

I am trying to give styling to multiple cells in a excel worksheet. The sheet has multiple tables in it. The table headers have bold font and normal border whereas the table data has only order.

I have defined 2 NamedStyle and attached to workbook. But when when the style to cells i am getting below error .

book.add_named_style(tableHeaderStyle)
File "C:\Users\bsingh\AppData\Local\Programs\Python\Python36-32\lib\site-packages\openpyxl\workbook\workbook.py", line 303, in add_named_style
    self._named_styles.append(style)
File "C:\Users\bsingh\AppData\Local\Programs\Python\Python36-32\lib\site-packages\openpyxl\styles\named_styles.py", line 194, in append
    raise ValueError("""Style {0} exists already""".format(style.name))
ValueError: Style TableHeaderStyle exists already

Code:

from openpyxl.styles import Border, Side, Color, PatternFill, Font, Alignment, NamedStyle
my_border = Border(left=Side(border_style='thin', color='000000'),
                right=Side(border_style='thin', color='000000'),
                top=Side(border_style='thin', color='000000'),
                bottom=Side(border_style='thin', color='000000'))

normalBorderStyle = NamedStyle(name="NormalBorderStyle",
                           alignment=Alignment(horizontal='center', vertical='center', wrap_text=True), border=my_border)

tableHeaderStyle = NamedStyle(name="TableHeaderStyle", alignment=Alignment(horizontal='center', vertical='center'), border=my_border, \
                               font=Font(bold=True), \
                                fill=PatternFill(patternType='solid', fill_type='solid', fgColor=Color('C4D79B')))

#This function is called for a table in worksheet "ws" with the cell range as the start and end column of table "B3:G7".
def set_border(ws, cell_range):
    flag = 0
    for row in ws.iter_rows(cell_range):
        for cell in row:
            cell.style = "NormalBorderStyle"
            if flag ==0 :
                cell.border = "TableHeaderStyle"
        flag = 1

book = load_workbook(xlsfile)
book.add_named_style(tableHeaderStyle)
book.add_named_style(normalBorderStyle)
ws_active = book.get_sheet_by_name("Summary")
set_border(ws_active, "B3:G7")

Answer 1

It looks very much like the style already exists in the book you're loading because the exception is not related to your function. You might want to add a check using book.named_styles.

Answer 2

You Need to register the style only once, so you can add it using a condition given below.

if 'NormalBorderStyle' not in book.style_names:
    book.add_named_style(normalBorderStyle)

# Directly Use as:
ws['D5'].style = 'NormalBorderStyle'

https://openpyxl.readthedocs.io/en/stable/styles.html

Answer 3

Firstly, to use the cell_range as you have defined it, you would have to use slicing notation, i.e., ws[cell_range]. The documentation states it this way:

Accessing many cells Ranges of cells can be accessed using slicing:

cell_range = ws['A1':'C2']

Also, if you wanted to use rows instead, as per the documentation, the input parameters for iter_rows() must be integers and not the cell ranges as you have defined them.

You can also use the Worksheet.iter_rows() method:

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):

The other thing I noticed in your code is that you were trying to modify the border attribute of the cell with a NamedStyle instead. As @Arpan pointed out, you need to register your named styles with the workbook once it has been loaded. Then you can access them using the value of their name attribute.

Lastly, as of this writing, the get_sheet_by_name method is deprecated, you need to use wb[sheet_name] instead.

With all those highlighted changes, the following (tested on Python 3.6 and openpyl 3.0.6) should work.

import os
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.styles import (
    Border,
    Side,
    Color,
    PatternFill,
    Font,
    Alignment,
    NamedStyle,
)

my_border = Border(
    left=Side(border_style="thin", color="000000"),
    right=Side(border_style="thin", color="000000"),
    top=Side(border_style="thin", color="000000"),
    bottom=Side(border_style="thin", color="000000"),
)

normalBorderStyle = NamedStyle(
    name="NormalBorderStyle",
    alignment=Alignment(horizontal="center", vertical="center", wrap_text=True),
    border=my_border,
)

tableHeaderStyle = NamedStyle(
    name="TableHeaderStyle",
    alignment=Alignment(horizontal="center", vertical="center"),
    border=my_border,
    font=Font(bold=True),
    fill=PatternFill(patternType="solid", fill_type="solid", fgColor=Color("C4D79B")),
)

# This function is called for a table in worksheet "ws" with the cell range as the start and end column of table "B3:G7".
def set_border(ws, cell_range):
    flag = 0
    for row in ws[cell_range]:
        # apply the styles to the cells
        for cell in row:
            cell.style = normalBorderStyle.name
            if flag == 0:
                cell.style = tableHeaderStyle.name
        flag = 1


excelfile_path = "path_to_file"
book = load_workbook(excelfile_path)

# Register the named style cell formats to the workbook if they
# don't exist already
if tableHeaderStyle.name not in book.style_names:
    book.add_named_style(tableHeaderStyle)
if normalBorderStyle.name not in book.style_names:
    book.add_named_style(normalBorderStyle)

ws_active = book["Summary"]
set_border(ws_active, "B3:G7")
book.save(excelfile_path)