1

I'm using openpyxl and i know how to insert a border for every cell. I did a script where i can insert border with a condition but i'd like a way to insert border by range.

import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import , Border, Side
import pandas as pd
import numpy

# Create workbookbook
wb = Workbook()
ws = wb.create_sheet('pandas', index = 0)

#Properties of sheet
ws.sheet_view.showGridLines = False
ws.sheet_view.zoomScale = 85
for column in ["A","B","C","D","E","F"]:
    ws.column_dimensions[column].width = 17

"Create DataFrame"
data = {
    "Product Name": ["ID", "Product 0", "Product 1", "Product 2", "Product 3", "Product 4","Product 5"],
    "Sales Month 1": [30, 30, 30, 20, 20, 20, 10],
    "Sales Month 2": [30, 30, 5, 5, 35, 10, 20],
    "Sales Month 3": [30,30, 5, 10, 20, 10, 20],
    "Sales Month 4": [30, 30, 5, 5, 35, 10, 20],
    "Calification": ['0', '0', '1', '2', '3', '4', '5'],
    "Sales Month 6": [ 20 , 30, 5, 5, 35, 10, 20],
}

df = pd.DataFrame(data)
df = df.set_index(df.columns[0])

"""introduce data to Excel"""
for row in dataframe_to_rows(df, index = False, header=True):
    ws.append(row)

"creating types of Borders"
borde_2_lados_derecha_Izquierda = Border(
                    left=Side(style='medium'),  
                    right=Side(style='medium'), 
                    top=Side(style=None), 
                    bottom=Side(style=None)
                    )
borde_1_lado_abajo = Border(
                        left=Side(style='medium'),  
                        right=Side(style='medium'), 
                        top=Side(style=None), 
                        bottom=Side(style='medium')
                        )
"""insert vertical border """
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
    for cell in row:
        cell.border = borde_2_lados_derecha_Izquierda

"""insert horizontal border to separate ID of products """ 
columna = []
for row in ws.iter_rows(min_row=ws.min_row + 1, 
                        max_row=ws.max_row, 
                        min_col=ws.min_column, 
                        max_col=ws.min_column):
    for cell in row:
        columna.append(cell.value)

conjunto = list(set(columna))[::-1]
numero_filas = [columna.count(i) for i in conjunto]
a = numpy.cumsum(numero_filas)
for i in a:
    celda = i + 1
    for cell in ws[f"{celda}"]:
        cell.border = borde_1_lado_abajo

"""export workbook """
wb.save("Practice.xlsx")

if you see my code, I'm doing vertical border and then horizontal to get many cells with border but I think it's not the good practice, I'd like to know how i can make border of ranges just specifying the range like this border_range(B2:F8)

look the image, I want something like that by using range for border, you can create a function of course.

enter image description here

  • Does this answer your question? [Apply borders to all cells in a range with openpyxl](https://stackoverflow.com/questions/13650059/apply-borders-to-all-cells-in-a-range-with-openpyxl) – Tranbi Sep 23 '21 at 08:04
  • nope, it doesn't solve my answer. i need a function which put a border around a table and not between cells. hope someone answers my question – Joel Barrantes Apr 21 '22 at 20:16

0 Answers0