PDF Automation with Unique ID + Logs in xls File

[pollos]

Hello,

I created a code base to generate PDFs from a JPG file, automatically adding a unique ID to each PDF. Additionally, these IDs are linked to emails, and all this information is logged in an XLSX file. I also generated an EXE from this code to make it more user-friendly (with a front prompt asking for the email and the number of PDFs to generate). Everything works perfectly.

My issue: I wanted to customize the log file with some macros, so the output file is now an XLSM.

I've reviewed the code from A to Z, mentioned this format multiple times, and specified the import of xlwings, but despite everything, the program continues to log in the XLSX format.

Here's the code:

import os
import sys
from PIL import Image, ImageDraw, ImageFont
from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas
import uuid
import xlwings as xw
from datetime import datetime
import tempfile

# Les fonctions generate_unique_id, add_id_to_image, et create_pdf_with_image restent inchangées

def add_to_excel(excel_path, email, unique_id, pdf_path, creation_date, creation_time):
    print(f"Tentative d'ajout au fichier Excel : {excel_path}")

    if not excel_path.endswith('.xlsm'):
        excel_path = os.path.splitext(excel_path)[0] + '.xlsm'
    print(f"Chemin du fichier Excel : {excel_path}")

    try:
        if os.path.exists(excel_path):
            print(f"Ouverture du fichier Excel existant : {excel_path}")
            wb = xw.Book(excel_path)
        else:
            raise FileNotFoundError(f"Le fichier {excel_path} n'existe pas.")

        ws = wb.sheets[0]
        ws.append([email, unique_id, pdf_path, creation_date, creation_time])
        print(f"Nouvelle ligne ajoutée")

        wb.save(excel_path)
        print(f"Fichier Excel sauvegardé avec succès : {excel_path}")
        wb.close()
        return None
    except Exception as e:
        print(f"Erreur lors de l'écriture dans le fichier Excel: {e}")
        temp_file = tempfile.NamedTemporaryFile(mode='w+', delete=False, suffix='.xlsm')
        wb = xw.Book(excel_path)
        ws = wb.sheets[0]
        ws.append([email, unique_id, pdf_path, creation_date, creation_time])
        wb.save(temp_file.name)
        print(f"Les données ont été sauvegardées dans un fichier temporaire : {temp_file.name}")
        wb.close()
        return temp_file.name

def main():
    image_path = "invitation.jpg"

    if not os.path.exists(image_path):
        print(f"Erreur : L'image '{image_path}' n'a pas été trouvée dans le dossier du script.")
        print("Assurez-vous que l'image d'invitation est nommée 'invitation.jpg' et se trouve dans le même dossier que ce programme.")
        input("Appuyez sur Entrée pour quitter...")
        return

    email = input("Entrez l'adresse e-mail du destinataire: ")
    quantity = int(input("Entrez le nombre d'invitations à générer: "))

    output_folder = "invitations_generees"
    if not os.path.exists(output_folder):
        os.makedirs(output_folder)

    script_dir = os.path.dirname(os.path.abspath(sys.argv[0]))
    excel_path = os.path.join(script_dir, "invitations_info.xlsm")

    if not os.path.exists(excel_path):
        print(f"Erreur : Le fichier Excel '{excel_path}' n'existe pas.")
        print("Assurez-vous que le fichier 'invitations_info.xlsm' est présent dans le même dossier que ce programme.")
        input("Appuyez sur Entrée pour quitter...")
        return

    print(f"Chemin du script : {script_dir}")
    print(f"Chemin du fichier Excel : {excel_path}")

    temp_files = []

    for count in range(1, quantity + 1):
        unique_id = generate_unique_id()
        output_image_path = os.path.join(output_folder, f"invitation_with_id_{unique_id}_{count}.jpg")
        pdf_path = os.path.join(output_folder, f"invitation_{unique_id}_{count}.pdf")

        now = datetime.now()
        creation_date = now.strftime("%d-%m-%Y")
        creation_time = now.strftime("%H:%M")

        add_id_to_image(image_path, output_image_path, unique_id, count)
        create_pdf_with_image(output_image_path, pdf_path)
        print(f"PDF généré: {pdf_path}")

        temp_file = add_to_excel(excel_path, email, unique_id, pdf_path, creation_date, creation_time)
        if temp_file:
            temp_files.append(temp_file)

        os.remove(output_image_path)

    print(f"\nTous les PDFs ont été générés dans le dossier: {output_folder}")

    if temp_files:
        print("\nCertaines données n'ont pas pu être ajoutées au fichier Excel principal.")
        print("Tentative de fusion des fichiers temporaires...")

        try:
            main_wb = xw.Book(excel_path)
            main_ws = main_wb.sheets[0]

            for temp_file in temp_files:
                temp_wb = xw.Book(temp_file)
                temp_ws = temp_wb.sheets[0]
                for row in temp_ws.range(2, temp_ws.used_range.last_cell.row).value:
                    main_ws.append(row)
                temp_wb.close()
                os.remove(temp_file)

            main_wb.save(excel_path)
            print("Fusion des fichiers temporaires terminée avec succès")
        except Exception as e:
            print(f"Erreur lors de la fusion des fichiers temporaires : {e}")

    print(f"Processus terminé. Fichier Excel final : {excel_path}")
    print(f"Le fichier Excel existe à la fin : {os.path.exists(excel_path)}")
    print(f"Les informations de corrélation ont été sauvegardées dans: {excel_path}")
    input("Appuyez sur Entrée pour quitter...")

if __name__ == "__main__":
    main()

No error just an issue with this xlsx instead of xlsm.

Could you please provide some verification tips?

Thank you in advance!

Larz60+ write Jul-09-2024, 08:21 AM:
Rather than pasting links, please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[Pedroski55]

Most probably, xlwings cannot save .xlsm files and can only save files as .xlsx.

Microsoft probably will not give away the code necessary for saving as .xlsm, so xlwings will not / can not save as .xlsm.

Microsoft is definitely not shareware!

Have a look here.

But this link is from 2015.