Buon pomeriggio a tutti,
Ho creato questa piccola applicazione che svolge i seguenti compiti:
- funzione update_promo: Dato un Database e un Layout Promo contenente i numeri articolo (index number) popola il file Layout Promo con tutti i dati relativi ad ogni codice articolo, per poi salvare il tutto in un nuovo file.
-
- funzione update_database_from_totallist: Dato un Database e una Lista di articoli, aggiorna prezzi e quantità del Database.
import tkinter as tk
from tkinter import filedialog, messagebox
import openpyxl
from openpyxl.drawing.image import Image as OpenpyxlImage
from PIL import Image as PilImage
import os
# Define constants for column indices
CODICE_ARTICOLO_COL = 3
PRICE_COL = 16
FREE_STOCK_COL = 17
# Define global variables for file paths
db_file = ""
promo_file = ""
total_list_file = ""
# Function for loading the database file
def load_database():
global db_file
db_file = filedialog.askopenfilename(
title="Select Database File", filetypes=[("Excel files", "*.xlsx")]
)
if db_file:
db_label.config(text=os.path.basename(db_file))
entry_database.delete(0, tk.END)
entry_database.insert(0, db_file)
enable_update_buttons()
# Function for loading the promo file
def load_promo():
global promo_file
promo_file = filedialog.askopenfilename(
title="Select Promo File", filetypes=[("Excel files", "*.xlsx")]
)
if promo_file:
promo_label.config(text=os.path.basename(promo_file))
enable_update_buttons()
# Function for loading the total list file
def select_total_list_file():
global total_list_file
total_list_file = filedialog.askopenfilename(
title="Select Total List File", filetypes=[("Excel files", "*.xlsx")]
)
if total_list_file:
entry_total_list.delete(0, tk.END)
entry_total_list.insert(0, total_list_file)
enable_update_buttons()
# Function to enable buttons if all files are selected
def enable_update_buttons():
if db_file and promo_file:
update_promo_button.config(state=tk.NORMAL)
if db_file and total_list_file:
update_database_button.config(state=tk.NORMAL)
# Function to copy images from the database worksheet to the promo worksheet
def copy_images(src_ws, dest_ws, codice_articolo, dest_row):
for image in src_ws._images:
cell = image.anchor._from
if cell.col in [0, 1]: # Columns 1 and 2
codice_cell = src_ws.cell(row=cell.row + 1, column=3)
if codice_cell.value == codice_articolo:
img = OpenpyxlImage(image.ref)
cell_col = chr(65 + cell.col)
cell_pos = f'{cell_col}{dest_row}'
max_width = dest_ws.column_dimensions[cell_col].width * 7.2
max_height = dest_ws.row_dimensions[dest_row].height or 15
pil_img = PilImage.open(image.ref)
orig_width, orig_height = pil_img.size
scale = min(max_width / orig_width, max_height / orig_height)
img.width = int(orig_width * scale)
img.height = int(orig_height * scale)
img.anchor = cell_pos
dest_ws.add_image(img)
print(f"Copied image from column {cell.col + 1} for row {dest_row}")
# Function to update the promo file with data from the database file
def update_promo():
if db_file and promo_file:
try:
# Load the database and promo workbooks
db_wb = openpyxl.load_workbook(db_file, data_only=True)
promo_wb = openpyxl.load_workbook(promo_file)
db_ws = db_wb.active
promo_ws = promo_wb.active
# Get the number of rows in each worksheet
db_max_row = db_ws.max_row
promo_max_row = promo_ws.max_row
# Create a dictionary for database data
database_data = {}
for row in db_ws.iter_rows(min_row=2, max_row=db_max_row, values_only=True):
codice_articolo = row[CODICE_ARTICOLO_COL - 1]
database_data[codice_articolo] = row
print("Database Data:", database_data)
# Update promo worksheet with database data
columns_to_update = list(range(3, 19))
for row in promo_ws.iter_rows(min_row=2, max_row=promo_max_row):
codice_articolo = row[2].value
if codice_articolo in database_data:
data = database_data[codice_articolo]
for i, col_index in enumerate(columns_to_update, start=3):
promo_ws.cell(row=row[0].row, column=col_index, value=data[i])
print(f"Updated row {row[0].row} with data: {data}")
copy_images(db_ws, promo_ws, codice_articolo, row[0].row)
else:
print(f"Codice articolo {codice_articolo} not found in database")
# Save the updated promo workbook
promo_wb.save("Promo_aggiornato.xlsx")
result_label.config(text="Promo file updated and saved as Promo_aggiornato.xlsx")
except Exception as e:
result_label.config(text=f"Error: {e}")
print(f"Error: {e}")
finally:
db_wb.close()
promo_wb.close()
else:
result_label.config(text="Please select both files")
# Function to update the database file with data from the total list file
def update_database_from_totallist():
global db_file, total_list_file
if db_file and total_list_file:
try:
# Load the database and total list workbooks
database_wb = openpyxl.load_workbook(db_file)
database_ws = database_wb.active
totallist_wb = openpyxl.load_workbook(total_list_file)
totallist_ws = totallist_wb.active
# Get the number of rows in each worksheet
db_max_row = database_ws.max_row
totallist_max_row = totallist_ws.max_row
# Create a dictionary for total list data
totallist_data = {}
for row in totallist_ws.iter_rows(min_row=2, max_row=totallist_max_row, values_only=True):
codice_articolo = row[0] # Adjust index if needed
price = row[21] # Adjust index if needed
free_stock = row[23] # Adjust index if needed
totallist_data[codice_articolo] = (price, free_stock)
# Update the database worksheet with total list data
for db_row in database_ws.iter_rows(min_row=2, max_row=db_max_row):
codice_articolo_db = str(db_row[CODICE_ARTICOLO_COL - 1].value)
if codice_articolo_db in totallist_data:
price, free_stock = totallist_data[codice_articolo_db]
db_row[PRICE_COL - 1].value = price
db_row[FREE_STOCK_COL - 1].value = free_stock
# Save the updated database workbook
database_wb.save(db_file)
result_label.config(text=f'Database updated and saved as {db_file}')
print(f'Updated database saved to {db_file}')
except Exception as e:
result_label.config(text=f"Error: {e}")
print(f"Error: {e}")
finally:
database_wb.close()
totallist_wb.close()
else:
result_label.config(text="Please select both the database file and the total list file")
# Create the main application window
app = tk.Tk()
app.title("Update Files")
# Widgets for selecting and updating the promo file
tk.Label(app, text="Select Database File:").grid(row=0, column=0, padx=10, pady=10)
tk.Button(app, text="Browse", command=load_database).grid(row=0, column=1, padx=10, pady=10)
db_label = tk.Label(app, text="")
db_label.grid(row=0, column=2, padx=10, pady=10)
tk.Label(app, text="Select Promo File:").grid(row=1, column=0, padx=10, pady=10)
tk.Button(app, text="Browse", command=load_promo).grid(row=1, column=1, padx=10, pady=10)
promo_label = tk.Label(app, text="")
promo_label.grid(row=1, column=2, padx=10, pady=10)
update_promo_button = tk.Button(app, text="Update Promo File", command=update_promo, state=tk.DISABLED)
update_promo_button.grid(row=2, column=0, columnspan=3, padx=10, pady=10)
tk.Label(app, text="File Total List Italy:").grid(row=4, column=0, padx=10, pady=10)
entry_total_list = tk.Entry(app, width=50)
entry_total_list.grid(row=4, column=1, padx=10, pady=10)
tk.Button(app, text="Browse", command=select_total_list_file).grid(row=4, column=2, padx=10, pady=10)
update_database_button = tk.Button(app, text="Update Database", command=update_database_from_totallist, state=tk.DISABLED)
update_database_button.grid(row=5, columnspan=3, pady=20)
result_label = tk.Label(app, text="")
result_label.grid(row=6, column=0, columnspan=3, padx=10, pady=10)
app.mainloop()
La funzione update_database_from_totallist funziona correttamente.
La funzione update_promo invece, fino a ieri funzionava con il seguente codice, oggi invece, una voltacliccato il bottone per lanciare la funzione mi viene dato questo errore:
Error: I/O operation on closed file.
Non riesco proprio a capire da dove derivi questo errore, sicuramente è qualcosa relativo ad uno dei tre file utilizzati durante la funzione.
Aspetto qualcuno che ne capisca più di me per riuscire a risolvere il dilemma!