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.