Formatting Excel files in openpyxl

Question

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.

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.

Could you add some of the code you've tried please? – D Malan Mar 04 '20 at 11:20 — D Malan, Mar 04 '20 at 11:20

Basu_C · Accepted Answer · 2020-03-05T11:18:53.177

1

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.

edited Mar 05 '20 at 11:18

answered Mar 04 '20 at 17:21

Basu_C

380
1
5

The thing is I don't know the data length, cause I read data from a file, and it can be really large. Now I'm trying to suit your solution to my project, so thank you for the advice. – Outlaw Mar 05 '20 at 06:57
Ok, so I adapted you solution to my case. But, unfortunately, it doesn't work that well, as I would like it to do. I've updated the question with your solution and the result. – Outlaw Mar 05 '20 at 07:14
Is there a way to add borders to all cells after I fill the file with data? – Outlaw Mar 05 '20 at 07:24
1

The objective here is to add style to Cell using openpyxl lib. The handling of files, flow of logic etc., are fully yours to make.And, yes, you can add borders to all cells after filling the file with all the data, and you'll need two iteration for that, once to fill the data, another to add the style. I have updated the answer to include that. – Basu_C Mar 05 '20 at 11:19
oh, my, this works just perfect! Thank you very much! By the way, how fast does it work? Look, I'm having a txt file of 845 rows and the longest of them comes to 'Z' column (in Excel). And it executes for 6-10 seconds. – Outlaw Mar 06 '20 at 06:57
Oh, yeah, and for some reason the very last row doesn't get the lower border. So I just increased the right border by 1 in: for row in range(1, max_rows + 2) – Outlaw Mar 06 '20 at 07:00

Formatting Excel files in openpyxl

1 Answers1