Python - Generate PDF from created Excel workbook - cannot save pdf document

Question

I'm attempting to convert an.xlsx workbook to a PDF file. I'm initially merging data from one spreadsheet into a new one (with some extra data conversion). I then save the new workbook to a temporary path. I then want to open the.xlsx file I just prepared and save it as a PDF.

But when I'm trying to save it, I get this message:

Błąd: (-2147352567, 'Wystąpił wyjątek.', (0, 'Microsoft Excel', 'Nie zapisano dokumentu. Być może jest on otwarty lub przy zapisywaniu napotkano błąd.', 'xlmain11.chm', 0, -2146827284), None)

(it is in Polish) The document was not saved. Perhaps it is open or an error was encountered when saving it

Without code, that is supposed to save a .pdf file, .xlsx file is created correctly.

Code of my program:

import sys
import openpyxl
from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter
from tkinter.filedialog import asksaveasfile
import os
from win32com import client

initialFile = sys.argv[1]
initialCatalog = sys.argv[2]
assetsCatalog = f"{initialCatalog}\\api\\assets"

def codeToPictogram(code):
  for files in os.walk(assetsCatalog):
      if f'{code}.png' in files[2]:
          return 1
      else:
          return 0

try:
    wb = openpyxl.load_workbook(initialFile)
    ws = wb.active
    ws.title = "Stan techniczny nawierzchni"

    for row in ws.iter_cols(min_row=2, min_col=2, max_row=ws.max_row, max_col=ws.max_column):  
        for cell in row:  
            if(cell.value is None):
                continue
            if (codeToPictogram(cell.value) == 1):
                img = Image(f"{assetsCatalog}\\{cell.value}.png")
                img.height = 100
                img.width = 100
                ws.add_image(img, f"{get_column_letter(cell.column)}{cell.row}")
    file = asksaveasfile(defaultextension='.pdf',title = 'Zapisz stan techniczny', filetypes=[("Plik PDF", "*.pdf")])
    if file is not None:
        wb.save(f'{initialCatalog}\\api\\temp\\temp.xlsx')
        # wb.close()
        
        excel = client.Dispatch("Excel.Application")
        sheets = excel.Workbooks.Open(f'{initialCatalog}\\api\\temp\\temp.xlsx')
        work_sheets = sheets.Worksheets[0]
        work_sheets.ExportAsFixedFormat(0, file.name)
        
        print("Gotowe!")
        input("Wciśnij Enter, aby zamknąć...")
except Exception as e:
    print(f"Błąd: {e}")
    input("Wciśnij Enter, aby zamknąć...")

I attempted to use wb.close() but was unable. I searched through the openpyxl instructions, but I was unable to locate a method for first saving a new workbook as an.xlsx file before saving it as a.pdf.

As this is my first try at creating a Python application, there may be something quite trivial that I am unaware of. I'm attempting to switch from C#:)

narikkadan · Answer 1 · Apr 6, 2023

The problem is that temp.xlsx doesn't shut off Excel. Excel is still open and has a lock on the file. Hence, the file is locked when you attempt to write to it.

I'm not sure how to solve this, but instead of storing to a temporary file for this use case, you may try pandas.