Openpyxl_adding a thick border around outside of page

[pcsailor]

Hi,

Does anyone have any clues on how to add a thick border around only the outside of a page coded with openpyxl?

Thanks,
phil

[Charlie Clark]

Am .10.2018, 05:08 Uhr, schrieb pcsailor <philcu...@gmail.com>:

> Does anyone have any clues on how to add a thick border around only the
> outside of a page coded with openpyxl?

I have really no idea what you mean by this: openpyxl has no idea of pages.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[pcsailor]

Hi Charlie,

Yes, I can see the confusing. I'm thinking in Excel and not coding terms

Let's say I have a SS that is 4 columns across by 40 rows down. It fits on one A4 page when printed. I've got thin borders on every cell on all sides.

What I want to add is a thick border around the outside cells of the printed page.
So in column 1(A), each cell would have a thick border on the left side, and a thin borders on the top, bottom, and right sides.
In column 4(D), each cell would have a thick border on the right side, and a thin borders on the top, bottom, and left sides.
In the top row (1), the top 4 cells (columns 1 to 4) would have a thick border on the top side, and a thin borders on the bottom, left and right sides, with the exception of the two corner cells (columns 1 & 4) which have also have the outside cells thick as noted above.
And the bottom row is opposite of the top row.
I suppose now that Ive written this out like this, I will try to hand code it myself. I'm wondering if there's any feature in openpyxl to do this or advice on the most efficient way in Python to do this.

Thank you,
Phil

[Charlie Clark]

Two things:

* if there is an easy way to do this in Excel then it *might* be possible
to do something similar in Excel.
* the code in the MergedCellRange could be used to identity and format
the edges.

[pcsailor]

Just to solve my own problem, from a while ago, here's the code to add thin borders to all cells and a thick outer border to an entire (printable) worksheet.  I'm guessing there's a more elegant way to write this using less code, which I'd be curious to see.  I'm a novice coder and this is how I got it to work.

Phil

#! python

'''

Border options:

'dashed', 'dotted', 'double', 'hair', 'thin', 'thick', 'mediumDashDot', 'slantDashDot', 'medium', 'mediumDashDotDot', 'dashDot', 'dashDotDot', 'mediumDashed'

'''

# Imports

import os #

import openpyxl #

from openpyxl.styles import Border, Side# Alignment

print('cwd = ', os.getcwd())

# os.chdir ('c:\\users\pc\desktop') # CHANGE DIRECTORY FOR FILE LOCATION

wb = openpyxl.Workbook()

print('  Created a new Excel Spreadsheet')

print ("New spreadsheet type = ", type (wb)) #

print('all sheetnames = ', wb.sheetnames)

sheet = wb.active

print('sheet = ', sheet)

borderThick = Border(left=Side(style='thick'),

                    right=Side(style='thick'),

                    top=Side(style='thick'),

                    bottom=Side(style='thick')) #

borderThin = Border(left=Side(style='thin'),

                   right=Side(style='thin'),

                   top=Side(style='thin'),

                   bottom=Side(style='thin')) #

borderTop = Border(top=Side(style='thick'),

                   left=Side(style='thin'),

                   right=Side(style='thin'),

                    bottom=Side(style='thin'))

borderBottom = Border(bottom=Side(style='thick'),

                   left=Side(style='thin'),

                   right=Side(style='thin'),

                   top=Side(style='thin'))

borderLeft = Border(left=Side(style='thick'),

                   right=Side(style='thin'),

                   top=Side(style='thin'),

                   bottom=Side(style='thin')) #

                   

borderRight = Border(right=Side(style='thick'),

                   left=Side(style='thin'),

                   top=Side(style='thin'),

                   bottom=Side(style='thin')) #

borderTopLeft = Border(left=Side(style='thick'),

                   right=Side(style='thin'),

                   top=Side(style='thick'),

                   bottom=Side(style='thin')) #

borderTopRight = Border(left=Side(style='thin'),

                   right=Side(style='thick'),

                   top=Side(style='thick'),

                   bottom=Side(style='thin')) #

borderBottomLeft = Border(left=Side(style='thick'),

                   right=Side(style='thin'),

                   top=Side(style='thin'),

                   bottom=Side(style='thick')) #

borderBottomRight = Border(left=Side(style='thin'),

                   right=Side(style='thick'),

                   top=Side(style='thin'),

                   bottom=Side(style='thick')) #

borderSide = Side(border_style='thick')  # applies to sides of each cell

# Set thin borders on entire sheet

rows = range(1, 46)

rowTop = int(1)

rowBot = int(45)

colLeft = int(1)

colRight = int(10)

columns = range(1, 11)

for row in rows:

   for col in columns:

       sheet.cell(row, col).border = borderThin

# Set thick outer borders

for row in rows:

   for col in columns:

       sheet.cell(rowTop, col).border = borderTop

       sheet.cell(rowBot, col).border = borderBottom

       sheet.cell(row, colLeft).border = borderLeft

       sheet.cell(row, colRight).border = borderRight

       sheet['A1'].border = borderTopLeft

       sheet['J1'].border = borderTopRight

       sheet['A45'].border = borderBottomLeft

       sheet['J45'].border = borderBottomRight

# Saving an Excel Spreadsheet

print('  save the empty file')

wb.save('openpyxl_styles_cellBorders.xlsx') #