Spaces:
Running
Running
import os | |
import re | |
import json | |
from datetime import datetime | |
from babel.dates import format_date | |
from babel import Locale | |
from openpyxl import Workbook, load_workbook | |
from openpyxl.utils import get_column_letter | |
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side | |
from openpyxl.worksheet.worksheet import Worksheet | |
from openpyxl.worksheet.datavalidation import DataValidation | |
class Item: | |
_format_id_spacing = " " * (10 * 10 * 5) | |
_unassigned_id = -1 | |
def parse_id(text: str) -> int | None: | |
match = re.search(r"\[([+-]?\d+)\]$", text.strip()) | |
if not match: | |
raise Exception("Id not found. Must be in format '[int]' at the end of the string") | |
res = int(match.group(1)) | |
return None if res == Item._unassigned_id else res | |
def __init__(self, id: int): | |
self._id = id | |
def format(self) -> str: | |
return f"[{self.id}]" | |
def get_id(self) -> int: | |
return self._id | |
class Branch(Item): | |
def __init__(self, customer_name: str, customer_id: str, branch_name: str, branch_id: int): | |
self._customer_name = customer_name | |
self._branch_name = branch_name | |
self._id = branch_id | |
self._customer_id = customer_id | |
def format(self) -> str: | |
return f"{self._customer_name} - {self._branch_name}" + Branch._format_id_spacing + f"[{self._customer_id}][{self._id}]" | |
def parse(text: str) -> tuple[int, int]: | |
""" | |
Returns a tuple with (customer_id, branch_id) | |
""" | |
res = re.search(r"\[(\d+)\]\[(\d+)\]$", text.strip()) | |
return (int(res.group(1)), int(res.group(2))) | |
class Driver(Item): | |
def __init__(self, id: int, name: str): | |
self._id = id | |
self._name = name | |
def format(self) -> str: | |
return f"{self._name}" + Driver._format_id_spacing + f"[{self._id}]" | |
class Vehicle(Item): | |
def __init__(self, id: int, name: str): | |
self._id = id | |
self._name = name | |
def format(self) -> str: | |
return f"{self._name}" + Vehicle._format_id_spacing + f"[{self._id}]" | |
class Route(Item): | |
def __init__(self, id: int, name: str): | |
self._id = id | |
self._name = name | |
def format(self) -> str: | |
return f"{self._name}" + Route._format_id_spacing + f"[{self._id}]" | |
class RoutePlan: | |
def __init__(self, route: Route, driver: Driver, vehicle: Vehicle, collection_points: list[Branch]): | |
self.route = route | |
self.collection_points = collection_points | |
self.driver = driver | |
self.vehicle = vehicle | |
def append_collection(self, branch: Branch): | |
self.collection_points.append(branch) | |
return self | |
class ParsedRoutePlan: | |
""" | |
collection_points is in format [(customer_id, branch_id)] | |
""" | |
def __init__(self, route_id: int, driver_id: int | None, vehicle_id: int | None, collection_points: list[tuple[int, int]]): | |
self.route_id = route_id | |
self.driver_id = driver_id | |
self.vehicle_id = vehicle_id | |
self.collection_points = collection_points | |
def to_dict(self) -> dict: | |
""" | |
Returns a dictionary with the following format: | |
{ | |
"route_id" : int, | |
"driver_id" : int, | |
"vehicle_id": int, | |
"collection_points": { "customer_id": int, "branch_id": int } | |
} | |
""" | |
return { | |
"route_id" : self.route_id, | |
"driver_id" : self.driver_id, | |
"vehicle_id": self.vehicle_id, | |
"collection_points": [{"customer_id": customer_id, "branch_id": branch_id} for (customer_id, branch_id) in self.collection_points] | |
} | |
def to_json(self) -> str: | |
""" | |
Returns a JSON string with the following format: | |
{ | |
"route_id" : int, | |
"driver_id" : int, | |
"vehicle_id": int, | |
"collection_points": { "customer_id": int, "branch_id": int } | |
} | |
""" | |
return json.dumps(self.to_dict()) | |
def __str__(self) -> str: | |
return f"<ParsedRoutePlan {self.to_json()}>" | |
class Formatter: | |
_locale = Locale("es") | |
_non_spill_alignment = Alignment(horizontal="fill", vertical="top") | |
_branch_column_width = 26 | |
_max_row = 3000 | |
_route_start_column_i = 1 # A | |
_route_start_row = 2 # A2 | |
_metadata_sheet_name = "__metadata__" | |
_metadata_sheet_branch_list_column = 1 # A | |
_metadata_sheet_driver_list_column = 2 # B | |
_metadata_sheet_vehicle_list_column = 3 # C | |
_metadata_sheet_route_metadata_cell = "D1" # { "start_row": 3, "start_column": 4, "end_column": 9 } | |
_COLLECTION_POINT_PROMPT= \ | |
"""Opciones: | |
- Arrastra un punto de recolecci贸n desde otra celda | |
- Selecciona un punto de recolecci贸n de esta lista desplegable | |
""" | |
def _format_route_metadata(start_row: int, start_column_i: int, end_column_i: int): | |
return json.dumps({ "start_row": start_row, "start_column": start_column_i, "end_column": end_column_i }) | |
def _parse_route_metadata(string: str) -> (int, int, int): | |
data = json.loads(string) | |
start_row = data.get("start_row", None) | |
start_column_i = data.get("start_column", None) | |
end_column_i = data.get("end_column", None) | |
if start_row is None or start_column_i is None or end_column_i is None: | |
raise Exception("Failed to parse route medata") | |
return (start_row, start_column_i, end_column_i) | |
def _format_title(dt: datetime) -> str: | |
return f'Rutas {format_date(dt, format="full", locale=Formatter._locale)}' | |
def _set_outer_border(sheet: Worksheet, cell_range_str: str, border_style: str ="medium", border_color: str="000000", omit_top: bool = False): | |
cell_range = cell_range_str.split(":") | |
start_cell, end_cell = cell_range | |
start_col = ord(start_cell[0].upper()) - ord('A') + 1 | |
start_row = int(start_cell[1:]) | |
end_col = ord(end_cell[0].upper()) - ord('A') + 1 | |
end_row = int(end_cell[1:]) | |
side = Side(border_style=border_style, color=border_color) | |
for row in range(start_row, end_row + 1): | |
for col in range(start_col, end_col + 1): | |
cell = sheet.cell(row=row, column=col) | |
# Esquina superior izquierda | |
if row == start_row and col == start_col: | |
if omit_top: | |
cell.border = Border(left=side) | |
else: | |
cell.border = Border(top=side, left=side) | |
# Esquina superior derecha | |
elif row == start_row and col == end_col and omit_top == False: | |
if omit_top: | |
cell.border = Border(right=side) | |
else: | |
cell.border = Border(top=side, right=side) | |
# Esquina inferior izquierda | |
elif row == end_row and col == start_col: | |
cell.border = Border(bottom=side, left=side) | |
# Esquina inferior derecha | |
elif row == end_row and col == end_col: | |
cell.border = Border(bottom=side, right=side) | |
# Lado superior | |
elif row == start_row and omit_top == False: | |
cell.border = Border(top=side) | |
# Lado inferior | |
elif row == end_row: | |
cell.border = Border(bottom=side) | |
# Lado izquierdo | |
elif col == start_col: | |
cell.border = Border(left=side) | |
# Lado derecho | |
elif col == end_col: | |
cell.border = Border(right=side) | |
def format_workbook( | |
date: datetime, | |
routes: list[RoutePlan], | |
branches: list[Branch], | |
vehicles: list[Vehicle], | |
drivers: list[Driver], | |
) -> Workbook: | |
title = Formatter._format_title(date) | |
workbook = Workbook() | |
sheet = workbook.active | |
if sheet is None: | |
raise Exception("Failed to get active sheet") | |
# Ponerle nombre a la hoja | |
sheet.title = f"{date.day}-{date.month}-{date.year}" | |
# Crear hoja escondida de metadatos | |
metadata_sheet = workbook.create_sheet(Formatter._metadata_sheet_name) | |
metadata_sheet.sheet_state = "hidden" | |
########################################################################################## | |
# Validaci贸n de choferes | |
########################################################################################## | |
# Crear lista escondida de validaci贸n de choferes | |
validation_list_row_i_start = 1 | |
validation_list_row_i = validation_list_row_i_start | |
for d in drivers: | |
cell = metadata_sheet.cell(row=validation_list_row_i, column=Formatter._metadata_sheet_driver_list_column, value=d.format()) | |
cell.alignment = Formatter._non_spill_alignment | |
validation_list_row_i += 1 | |
# Crear validaci贸n de choferes | |
driver_validation_list_column_letter = get_column_letter(Formatter._metadata_sheet_driver_list_column) | |
driver_validation = DataValidation( | |
type="list", | |
formula1=f"{Formatter._metadata_sheet_name}!${driver_validation_list_column_letter}${validation_list_row_i_start}:${driver_validation_list_column_letter}${validation_list_row_i}", | |
allowBlank=True, | |
promptTitle="Asignar chofer", | |
prompt="Selecciona un chofer de esta lista desplegable", | |
errorTitle="El chofer no est谩 en la lista", | |
error="Agr茅galo primero en la pesta帽a de choferes.", | |
showDropDown=False, # Est谩 al rev茅z la opci贸n, por alguna raz贸n | |
showInputMessage=True, | |
showErrorMessage=True | |
) | |
sheet.add_data_validation(driver_validation) | |
########################################################################################## | |
# Validaci贸n de veh铆culos | |
########################################################################################## | |
# Crear lista escondida de validaci贸n de veh铆culos | |
validation_list_row_i = validation_list_row_i_start | |
for v in vehicles: | |
cell = metadata_sheet.cell(row=validation_list_row_i, column=Formatter._metadata_sheet_vehicle_list_column, value=v.format()) | |
cell.alignment = Formatter._non_spill_alignment | |
validation_list_row_i += 1 | |
# Crear validaci贸n de veh铆culos | |
vehicle_validation_list_column_letter = get_column_letter(Formatter._metadata_sheet_vehicle_list_column) | |
vehicle_validation = DataValidation( | |
type="list", | |
formula1=f"{Formatter._metadata_sheet_name}!${vehicle_validation_list_column_letter}${validation_list_row_i_start}:${vehicle_validation_list_column_letter}${validation_list_row_i}", | |
allowBlank=True, | |
promptTitle="Asignar veh铆culo", | |
prompt="Selecciona un veh铆culo esta lista desplegable", | |
errorTitle="El veh铆culo no est谩 en la lista", | |
error="Agr茅galo primero en la pesta帽a de veh铆culos.", | |
showDropDown=False, # Est谩 al rev茅z la opci贸n, por alguna raz贸n | |
showInputMessage=True, | |
showErrorMessage=True | |
) | |
sheet.add_data_validation(vehicle_validation) | |
########################################################################################## | |
# Validaci贸n de sucursales | |
########################################################################################## | |
# Crear lista escondida de validaci贸n de puntos de recolecci贸n | |
validation_list_row_i = validation_list_row_i_start | |
for b in branches: | |
cell = metadata_sheet.cell(row=validation_list_row_i, column=Formatter._metadata_sheet_branch_list_column, value=b.format()) | |
cell.alignment = Formatter._non_spill_alignment | |
validation_list_row_i += 1 | |
# Crear validaci贸n de puntos de recolecci贸n | |
collection_point_validation_list_column_letter = get_column_letter(Formatter._metadata_sheet_branch_list_column) | |
collection_point_validation = DataValidation( | |
type="list", | |
formula1=f"{Formatter._metadata_sheet_name}!${collection_point_validation_list_column_letter}${validation_list_row_i_start}:${collection_point_validation_list_column_letter}${validation_list_row_i}", | |
allowBlank=True, | |
promptTitle="Punto de recolecci贸n", | |
prompt=Formatter._COLLECTION_POINT_PROMPT, | |
errorTitle="Punto de recolecci贸n desconocido", | |
error="Agr茅galo primero en la pesta帽a de servicios.", | |
showDropDown=False, # Est谩 al rev茅z la opci贸n, por alguna raz贸n | |
showInputMessage=True, | |
showErrorMessage=True | |
) | |
sheet.add_data_validation(collection_point_validation) | |
########################################################################################## | |
# Metadatos de rutas | |
########################################################################################## | |
metadata_sheet[Formatter._metadata_sheet_route_metadata_cell].value = Formatter._format_route_metadata( | |
start_row=Formatter._route_start_row, | |
start_column_i=Formatter._route_start_column_i, | |
end_column_i=Formatter._route_start_column_i + len(routes) - 1 | |
) | |
# T铆tulo superior de la p谩gina | |
title_cell = sheet.cell(row=1, column=1, value=title) | |
sheet.merge_cells(f"A1:{get_column_letter(len(routes))}1") | |
title_cell.font = Font(size=14, bold=True) | |
title_cell.alignment = Alignment(horizontal="center") | |
# Poner todas las celdas con fondo blanco | |
for c in range(1, len(routes) + 1): | |
for r in range(1, Formatter._max_row + 1): | |
cell = sheet.cell(row=r, column=c) | |
cell.fill = PatternFill(patternType="solid", fgColor="FFFFFF") | |
# Poner bordes a los encabezados | |
Formatter._set_outer_border(sheet=sheet, cell_range_str=f"A1:{get_column_letter(len(routes))}4", border_color="000000", border_style="medium") | |
# Poner bordes a las rutas | |
Formatter._set_outer_border(sheet=sheet, cell_range_str=f"A5:{get_column_letter(len(routes))}{Formatter._max_row}", border_color="000000", border_style="medium", omit_top=True) | |
# Inmovilizar primeras tres filas | |
sheet.freeze_panes = "A5" | |
# Rutas | |
route_col_i = 1 # Empieza en columna A | |
route_row_i = 2 # Empieza en fila 2 | |
driver_row_i = route_row_i + 1 # El chofer va a bajo de la ruta | |
vehicle_row_i = driver_row_i + 1 # El veh铆culo va abajo del chofer | |
for r in routes: | |
column_letter = get_column_letter(route_col_i) | |
# Poner ancho a la columna | |
sheet.column_dimensions[column_letter].width = Formatter._branch_column_width | |
# Escribir nombre de la ruta | |
route_cell = sheet.cell(row=route_row_i, column=route_col_i, value=r.route.format()) | |
route_cell.alignment = Formatter._non_spill_alignment | |
route_cell.font = Font(bold=True) | |
# Escribir nombre de chofer | |
driver_cell = sheet.cell(row=driver_row_i, column=route_col_i, value=r.driver.format()) | |
driver_cell.alignment = Formatter._non_spill_alignment | |
driver_validation.add(driver_cell) | |
# Escribir nombre de veh铆culo | |
vehicle_cell = sheet.cell(row=vehicle_row_i, column=route_col_i, value=r.vehicle.format()) | |
vehicle_cell.alignment = Formatter._non_spill_alignment | |
vehicle_validation.add(vehicle_cell) | |
# Escribir puntos de recolecci贸n | |
collection_point_row_i_start = route_row_i + 3 | |
collection_point_row_i = collection_point_row_i_start | |
for cp in r.collection_points: | |
collection_point_cell = sheet.cell(row=collection_point_row_i, column=route_col_i, value=cp.format()) | |
collection_point_cell.alignment = Formatter._non_spill_alignment | |
collection_point_row_i += 1 | |
# Mover a siguiente columna | |
route_col_i += 1 | |
# Agregar validaci贸n de datos a toda la columna de puntos de recolecci贸n de la ruta a partir del inicio de la lista | |
collection_point_validation.add(f"${column_letter}${collection_point_row_i_start}:${column_letter}${Formatter._max_row}") | |
# Agregar formato a todas las celdas | |
for row_i in range(collection_point_row_i_start, Formatter._max_row + 1): | |
cell = sheet[f"{column_letter}{row_i}"] | |
cell.alignment = Formatter._non_spill_alignment | |
return workbook | |
def parse_workbook_from_path(path: str) -> list[ParsedRoutePlan]: | |
wb = load_workbook(path) | |
return Formatter.parse_workbook(wb) | |
def parse_workbook(wb: Workbook) -> list[ParsedRoutePlan]: | |
sheet = wb.worksheets[0] | |
metadata_sheet = wb[Formatter._metadata_sheet_name] | |
route_start_row_i, route_start_column_i, route_end_column_i = Formatter._parse_route_metadata(metadata_sheet[Formatter._metadata_sheet_route_metadata_cell].value) | |
parsed_route_plans: list[ParsedRoutePlan] = [] | |
for route_col_i in range(route_start_column_i, route_end_column_i + 1): | |
route_id = Item.parse_id(sheet.cell(row=route_start_row_i, column=route_col_i).value) | |
driver_id = Item.parse_id(sheet.cell(row=route_start_row_i + 1, column=route_col_i).value) | |
vehicle_id = Item.parse_id(sheet.cell(row=route_start_row_i + 2, column=route_col_i).value) | |
collection_points: list[tuple[int, int]] = [] | |
for collection_row_i in range(route_start_row_i + 3, Formatter._max_row + 1): | |
value = sheet.cell(row=collection_row_i, column=route_col_i).value | |
if value is not None and len(value) > 0: | |
id_pair = Branch.parse(value) | |
if id_pair is not None: | |
collection_points.append(id_pair) | |
parsed_route_plans.append(ParsedRoutePlan( | |
route_id=route_id, | |
driver_id=driver_id, | |
vehicle_id=vehicle_id, | |
collection_points=collection_points | |
)) | |
return parsed_route_plans | |
if __name__ == "__main__": | |
vehicle_1 = Vehicle(id=1, name="Kenworth") | |
vehicle_2 = Vehicle(id=2, name="Camioneta Nissan") | |
vehicle_3 = Vehicle(id=3, name="Honda 500") | |
vehicle_4 = Vehicle(id=4, name="Freightliner") | |
vehicle_5 = Vehicle(id=5, name="Camioneta Toyota") | |
driver_1 = Driver(id=1, name="Isaac") | |
driver_2 = Driver(id=2, name="Gustavo") | |
driver_3 = Driver(id=3, name="Ra煤l") | |
driver_4 = Driver(id=4, name="Rita") | |
driver_5 = Driver(id=5, name="Pedro") | |
route_1 = Route(id=1, name="Ruta 1") | |
route_2 = Route(id=2, name="Ruta 2") | |
route_3 = Route(id=3, name="Ruta 3") | |
route_4 = Route(id=4, name="Ruta 4") | |
route_5 = Route(id=5, name="Ruta 5") | |
route_6 = Route(id=6, name="Ruta 6") | |
branches = [ | |
Branch(branch_id=1, branch_name="A", customer_id=1, customer_name="Walmart"), | |
Branch(branch_id=2, branch_name="B", customer_id=1, customer_name="Walmart"), | |
Branch(branch_id=3, branch_name="C", customer_id=1, customer_name="Walmart"), | |
Branch(branch_id=4, branch_name="D", customer_id=1, customer_name="Walmart"), | |
Branch(branch_id=5, branch_name="E", customer_id=1, customer_name="Walmart"), | |
Branch(branch_id=6, branch_name="F", customer_id=1, customer_name="Walmart"), | |
Branch(branch_id=7, branch_name="A", customer_id=2, customer_name="Autozone"), | |
Branch(branch_id=8, branch_name="B", customer_id=2, customer_name="Autozone"), | |
Branch(branch_id=9, branch_name="C", customer_id=2, customer_name="Autozone"), | |
Branch(branch_id=10, branch_name="D", customer_id=2, customer_name="Autozone"), | |
Branch(branch_id=11, branch_name="E", customer_id=2, customer_name="Autozone"), | |
Branch(branch_id=12, branch_name="F", customer_id=2, customer_name="Autozone"), | |
Branch(branch_id=13, branch_name="A", customer_id=3, customer_name="Los Tarascos"), | |
Branch(branch_id=14, branch_name="B", customer_id=3, customer_name="Los Tarascos"), | |
Branch(branch_id=15, branch_name="C", customer_id=3, customer_name="Los Tarascos"), | |
Branch(branch_id=16, branch_name="D", customer_id=3, customer_name="Los Tarascos"), | |
Branch(branch_id=17, branch_name="E", customer_id=3, customer_name="Los Tarascos"), | |
Branch(branch_id=18, branch_name="F", customer_id=3, customer_name="Los Tarascos"), | |
Branch(branch_id=31, branch_name="A", customer_id=4, customer_name="Constructora Guti茅rrez"), | |
Branch(branch_id=32, branch_name="B", customer_id=4, customer_name="Constructora Guti茅rrez"), | |
Branch(branch_id=33, branch_name="C", customer_id=4, customer_name="Constructora Guti茅rrez"), | |
Branch(branch_id=45, branch_name="D", customer_id=4, customer_name="Constructora Guti茅rrez"), | |
Branch(branch_id=63, branch_name="E", customer_id=4, customer_name="Constructora Guti茅rrez"), | |
Branch(branch_id=23, branch_name="F", customer_id=4, customer_name="Constructora Guti茅rrez") | |
] | |
print("Formatting Excel") | |
wb = Formatter.format_workbook( | |
date=datetime(2023, 8, 25), | |
branches=branches, | |
vehicles=[ | |
vehicle_1, | |
vehicle_2, | |
vehicle_3, | |
vehicle_4, | |
vehicle_5 | |
], | |
drivers=[ | |
driver_1, | |
driver_2, | |
driver_3, | |
driver_4, | |
driver_5 | |
], | |
routes=[ | |
RoutePlan(route=route_1, driver=driver_1, vehicle=vehicle_1, collection_points=branches[0:6]), | |
RoutePlan(route=route_2, driver=driver_2, vehicle=vehicle_2, collection_points=branches[6:12]), | |
RoutePlan(route=route_3, driver=driver_3, vehicle=vehicle_3, collection_points=branches[12:18]), | |
RoutePlan(route=route_4, driver=driver_4, vehicle=vehicle_4, collection_points=branches[18:24]), | |
], | |
) | |
cwd = os.getcwdb().decode('utf-8') | |
filename = "test.xlsx" | |
path = os.path.join(cwd, filename) | |
print(f"Saving Excel at {path}") | |
wb.save(path) | |
print("Parsing Excel") | |
parsed_routes = Formatter.parse_workbook_from_path(path) | |
for r in parsed_routes: | |
print(r) | |
print("Done!") |