Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
3.2 kB
2
Indexable
Never
import pandas as pd
import tkinter as tk
from tkinter import filedialog, messagebox
from openpyxl import Workbook
from openpyxl.styles import PatternFill

def browse_files():
    file_paths = filedialog.askopenfilenames(filetypes=[("Excel Files", "*.xlsx")])
    if len(file_paths) < 2:
        messagebox.showerror("Error", "Please select at least two Excel files.")
        return
    process_files(file_paths)

def process_files(file_paths):
    # Read the output Excel files
    try:
        dfs = [pd.read_excel(file_path) for file_path in file_paths]

        # Ensure all DataFrames have the same columns
        if not all(df.columns.tolist() == dfs[0].columns.tolist() for df in dfs):
            messagebox.showerror("Error", "Not all files have the same columns.")
            return

        # Choose a unique identifier column(s) to match rows
        unique_identifier = ['Requirement(s)', 'Specification(s)']  # Update with your actual column name(s)

        # Merge the DataFrames based on the unique identifier(s)
        merged_df = pd.merge(*dfs, on=unique_identifier, how='outer', suffixes=tuple(f"_file{i+1}" for i in range(len(dfs))))

        # Identify discrepancies
        discrepancy_df = merged_df[merged_df.apply(lambda x: not x[::2].equals(x[1::2]), axis=1)]

        # Export discrepancies to Excel and highlight differing cells
        with pd.ExcelWriter('discrepancies.xlsx', engine='openpyxl') as writer:
            discrepancy_df.to_excel(writer, sheet_name='Discrepancies', index=False)

            # Highlight differing cells
            wb = writer.book
            ws = wb['Discrepancies']
            red_fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')

            for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
                for cell in row:
                    if cell.column_letter.endswith('_file1') and cell.value != cell.offset(column=1).value:
                        cell.fill = red_fill
                    elif cell.column_letter.endswith('_file2') and cell.value != cell.offset(column=-1).value:
                        cell.fill = red_fill

        messagebox.showinfo("Success", "Discrepancies exported to 'discrepancies.xlsx'.")
    except Exception as e:
        messagebox.showerror("Error", str(e))

def find_unique_values(row):
    col1 = str(row['Column1']).split(', ') if pd.notna(row['Column1']) else []
    col2 = str(row['Column2']).split(', ') if pd.notna(row['Column2']) else []
    
    set1 = sorted(set(col1))
    set2 = sorted(set(col2))
    
    unique_to_col1 = ', '.join(set1) if set1 else ''
    unique_to_col2 = ', '.join(set2) if set2 else ''
    
    return pd.Series([unique_to_col1, unique_to_col2], index=['Column3', 'Column4'])


# Create the main application window
app = tk.Tk()
app.title("Excel Discrepancy Checker")

# Create a button to browse files
browse_button = tk.Button(app, text="Browse Files", command=browse_files)
browse_button.pack(padx=20, pady=10)

# Run the Tkinter event loop
app.mainloop()
Leave a Comment