CODEWITHSUNDEEP
pythonopenpyxlxlsxxls
Outlaw
Outlaw

Reputation: 359

Formatting Excel files in openpyxl

In my project I create .xlsx file and fill it with data using ws.append([list]). Like that:

for line in inf:
    current_line = line.strip().split(';')
    ws.append(current_line)

Header row is also added using .append() method.

The next thing I need to do is to apply one style for a header row (bold font), and another style for the entire table (each cell should have simple borders).

I have tried different methods to do so (primarily on openpyxl.readthedocs.io, and Googled), but none of them worked for me.

Is there a way to apply style for the first row, and apply borders for all of the existing cells in the file? The difficulty is that I have different amount of columns in each row, and unknown amount of rows (a lot of them). The borders should be applied accordingly to the width of the longest row, like at the pic.

enter image description here

Some of the methods I tried:

col = ws.column_dimensions['A']
col.border =  = Border(left=Side(border_style='thin', color='FF000000'),
             right=Side(border_style='thin', color='FF000000'),
             top=Side(border_style='thin', color='FF000000'),
             bottom=Side(border_style='thin', color='FF000000')
    )

row = ws.row_dimensions[1]
row.border =  = Border(left=Side(border_style='thin', color='FF000000'),
             right=Side(border_style='thin', color='FF000000'),
             top=Side(border_style='thin', color='FF000000'),
             bottom=Side(border_style='thin', color='FF000000')
    )

These don't even work for a single row/column (1/'A').

UPD: tried this

row = 1
for line in inf:
    curr_line = line.strip().split(';')
    n_cols = len(curr_line)
    ws.append(curr_line)
    for col in range(1, n_cols + 1):
        cell = ws.cell(row, col)
        cell.border = cell_border
        if row == 1:        # Header Style
            cell.font = Font(bold=True)
    row += 1

The result of that. The border distribution is somehow not uniform. Some rows are short, some are long, and it looks not satisfying. Besides that, some cells don't have one of the borders or don't have them at all. enter image description here

Upvotes: 1

Views: 3040

Answers (1)

Basu_C
Basu_C

Reputation: 410

I assume you are trying to apply Cell Style to 'list' type, rather than 'openpyxl.cell.cell.Cell' type.

Below is the snippet to add styles using openpyxl under assumptions:

  • current_line : List of something.
  • Header is Row 1 only.
  • version: Python 3.8.1
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, Font

wb = load_workbook(filename="sample.xlsx", read_only=False)
ws = wb.active

data = [["H1", "H2", "H3", "H4", "H5", "H6"],[1,2,3,4,5,6,7],[11,12,13],[21,22,23,24,25,26,27],[31,32],[41,42,43,44,45],[51,52]]

cell_border = Border(left=Side(border_style='thin', color='FF000000'),
                     right=Side(border_style='thin', color='FF000000'),
                     top=Side(border_style='thin', color='FF000000'),
                     bottom=Side(border_style='thin', color='FF000000')
)

n_rows = len(data)
for row in range(1, n_rows + 1):
    n_cols = len(data[row-1])
    ws.append(data[row-1])
    for col in range(1, n_cols + 1):
        cell = ws.cell(row, col)
        cell.border = cell_border
        if row == 1:        # Header Style
            cell.font = Font(bold=True)
wb.save("sample.xlsx")

You can modify to suit your exact requirement. Hope it helps.

Update:

max_rows = 0
max_cols = 0

for line in inf:
    current_line = line.strip().split(';')
    ws.append(current_line)
    max_rows += 1
    row_size = len(current_line)
    if row_size > max_cols:
        max_cols = row_size

for row in range(1, max_rows + 1):
    for col in range(1, max_cols + 1):
        cell = ws.cell(row, col)
        cell.border = cell_border
        if row == 1:        # Header Style
            cell.font = Font(bold=True)

More details on openpyxl cell formatting here.

Upvotes: 1

Related Questions