Aug-25-2024, 02:55 AM
Hello everyone,
I started learning Python a couple of weeks ago, and I've been working on a GUI application that performs several tasks. One of the features of my application is a script that allows users to select a folder directory. The script then converts and merges all Excel files within the subfolders into a single PDF file.
The script works perfectly when I run it in Visual Studio Code. However, after converting it into an executable (.exe) file, the Excel-to-PDF conversion script no longer functions correctly. It only manages to create the first folder named "PDF" and then stops without doing anything else.
Below is the script I'm using. Any help or suggestions would be greatly appreciated!
I started learning Python a couple of weeks ago, and I've been working on a GUI application that performs several tasks. One of the features of my application is a script that allows users to select a folder directory. The script then converts and merges all Excel files within the subfolders into a single PDF file.
The script works perfectly when I run it in Visual Studio Code. However, after converting it into an executable (.exe) file, the Excel-to-PDF conversion script no longer functions correctly. It only manages to create the first folder named "PDF" and then stops without doing anything else.
Below is the script I'm using. Any help or suggestions would be greatly appreciated!
def resource_path(self, relative_path):
""" Get the absolute path to the resource, works for both script and executable. """
try:
base_path = sys._MEIPASS # PyInstaller stores files here
except Exception:
base_path = os.path.abspath(".")
return os.path.join(base_path, relative_path)
def select_pdf_directory(self):
# Allow user to select a directory to get subdirectories
folder_path = filedialog.askdirectory(title="Select a Directory Folder")
if folder_path:
subfolders = [f.path for f in os.scandir(folder_path) if f.is_dir()]
for folder in subfolders:
if folder not in self.folders:
self.folders.append(folder)
self.pdf_folder_listbox.insert(tk.END, folder) # Add folder to listbox
self.update_ui()
def update_ui(self):
if self.folders:
self.pdf_convert_button.configure(state=tk.NORMAL)
self.pdf_folder_label.configure(text=f"Selected Folders: {len(self.folders)}")
else:
self.pdf_convert_button.configure(state=tk.DISABLED)
self.pdf_folder_label.configure(text="No folders selected.")
def start_conversion(self):
merged_pdf_paths = [] # List to keep track of merged PDF paths
total_files = sum(len([f for f in os.listdir(folder) if f.lower().endswith(('.xls', '.xlsx'))]) for folder in self.folders)
self.pdf_progressbar.configure(maximum=total_files)
processed_files = 0
for input_dir in self.folders:
# Ensure PDFs directory exists within the input directory
pdfs_dir = os.path.join(input_dir, 'PDFs')
mkdir(pdfs_dir)
# Convert Excel files to PDFs within the subdirectories
processed_files += self.convert_to_pdfs(input_dir, pdfs_dir)
# Always merge PDFs in this folder and store the path
merged_pdf_path = self.merge_pdfs(pdfs_dir)
if merged_pdf_path:
merged_pdf_paths.append(merged_pdf_path)
# Merge all the individual merged PDFs into a master PDF
if merged_pdf_paths:
self.create_master_pdf(merged_pdf_paths, os.path.dirname(self.folders[0]))
messagebox.showinfo("Success", "Conversion, merging, and master PDF creation completed successfully!")
self.pdf_progressbar['value'] = self.pdf_progressbar['maximum'] # Set progress bar to complete
def sanitize_filename(self, filename):
# Remove special characters from filename
return re.sub(r'[<>:"/\\|?*]', '_', filename)
def convert_to_pdfs(self, input_dir, pdfs_dir):
total_files_in_dir = 0
for dirpath, _, filenames in os.walk(input_dir):
excel_files = [os.path.join(dirpath, f) for f in filenames if f.lower().endswith(('.xls', '.xlsx'))]
total_files_in_dir += len(excel_files)
# If no Excel files found in this directory, skip
if not excel_files:
print(f"No Excel files found in {dirpath}") # Debugging output
continue
xl = DispatchEx("Excel.Application")
xl.Visible = False
xl.DisplayAlerts = 0
atexit.register(xl.Quit)
for input_file in tqdm(excel_files, desc=f"Processing files in {dirpath}"):
workbook = None
try:
workbook = xl.Workbooks.Open(input_file)
active_sheet = workbook.ActiveSheet
# Default orientation
orientation = 1 # Portrait
# Determine the orientation based on file name
filename = os.path.basename(input_file)
if "(1.04)" in filename and "IntegrationReverification" in filename:
orientation = 2 # Landscape mode
elif "(1.08)" in filename and "NAX 4 Hr Run" in filename:
orientation = 2 # Landscape mode
elif "(2.07)" in filename and "NAX 4 Hr Run" in filename:
orientation = 2 # Landscape mode
active_sheet.PageSetup.Orientation = orientation
# Sanitize the filename
sanitized_filename = self.sanitize_filename(f"{os.path.splitext(os.path.basename(input_file))[0]}_ActiveSheet.pdf")
output_file = os.path.join(pdfs_dir, sanitized_filename)
print(f"Exporting to: {output_file}") # Debugging output
active_sheet.ExportAsFixedFormat(0, output_file)
# Check if the file is created and its size
if os.path.exists(output_file) and os.path.getsize(output_file) > 0:
print(f"File successfully created: {output_file}")
else:
print(f"PDF file created but is empty: {output_file}")
# Update progress bar and label
self.pdf_progressbar['value'] += 1 # Update progress bar
self.pdf_progress_label.configure(text=f"Converted: {self.pdf_progressbar['value']} / {self.pdf_progressbar['maximum']}")
self.PDF_frame.update_idletasks() # Update the GUI
except Exception as e:
print(f"Error processing file {input_file}: {e}")
finally:
if workbook:
workbook.Close(False)
xl.Quit()
return total_files_in_dir # Return the number of processed files
def merge_pdfs(self, pdfs_dir):
pdf_merger = PdfMerger()
pdf_files = [os.path.join(pdfs_dir, f) for f in os.listdir(pdfs_dir) if f.lower().endswith('.pdf')]
if not pdf_files:
print("No PDF files found to merge.") # Debugging output
messagebox.showwarning("Warning", "No PDF files found to merge.")
return None
for pdf in pdf_files:
if os.path.exists(pdf) and os.path.getsize(pdf) > 0:
try:
pdf_merger.append(pdf)
print(f"Adding to merge: {pdf}") # Debugging output
except Exception as e:
print(f"Error adding PDF to merger: {pdf}, Error: {e}")
merged_file = os.path.join(pdfs_dir, "BuyOff Book Full PDF.pdf") # Set the merged PDF name here
try:
pdf_merger.write(merged_file)
pdf_merger.close()
# Check if the merged file is created and its size
if os.path.exists(merged_file) and os.path.getsize(merged_file) > 0:
print(f"Merged file created: {merged_file}") # Debugging output
self.pdf_progressbar['value'] += 1 # Update progress bar for merging
self.pdf_progress_label.configure(text=f"Merged: {self.pdf_progressbar['value']} / {self.pdf_progressbar['maximum']}")
self.PDF_frame.update_idletasks() # Update the GUI
return merged_file # Return the path of the merged PDF
else:
print(f"Final merged PDF is empty or not created: {merged_file}")
messagebox.showerror("Error", "The final merged PDF is empty or not created.")
return None
except Exception as e:
messagebox.showerror("Error", f"An error occurred while merging PDFs: {e}")
print(f"Error merging PDFs: {e}")
return None
def create_master_pdf(self, merged_pdf_paths, output_dir):
master_pdf_merger = PdfMerger()
for pdf in merged_pdf_paths:
if os.path.exists(pdf) and os.path.getsize(pdf) > 0:
try:
master_pdf_merger.append(pdf)
print(f"Adding to master merge: {pdf}") # Debugging output
except Exception as e:
print(f"Error adding PDF to master merger: {pdf}, Error: {e}")
master_merged_file = os.path.join(output_dir, "Master_Merged_BuyOff_Book.pdf") # Set the master merged PDF name here
try:
master_pdf_merger.write(master_merged_file)
master_pdf_merger.close()
if os.path.exists(master_merged_file) and os.path.getsize(master_merged_file) > 0:
print(f"Master merged file created: {master_merged_file}") # Debugging output
else:
print(f"Final master merged PDF is empty or not created: {master_merged_file}")
messagebox.showerror("Error", "The final master merged PDF is empty or not created.")
except Exception as e:
messagebox.showerror("Error", f"An error occurred while merging master PDFs: {e}")
print(f"Error merging master PDFs: {e}")