mail@pastecode.io avatar
a month ago
2.9 kB
import pandas as pd
import tkinter as tk
from tkinter import filedialog, simpledialog

def process_excel_sheet():
    # Prompt to choose the Excel sheet
    path_to_excel = filedialog.askopenfilename(
        title="Choose Excel Sheet",
        filetypes=[("Excel Files", "*.xlsx;*.xls")]
    # Load data from the Excel sheet
    df = pd.read_excel(path_to_excel)

    # If "Concated" column doesn't exist, create it
    if 'Concated' not in df.columns:
        # Concatenate the required columns to create "Concated" column
        df['Concated'] = df['Requirement(s)'] + '|' + df['Specification(s)']

    # Extract Concated filters from "Concated" column
    concated_filters = df['Concated'].tolist()

    # Create an empty dataframe to store the results
    result_df = pd.DataFrame(columns=['Requirement(s)', 'Specification(s)', 'Concated', 'Software Features'])

    # Iterate through Concated filters
    for concated_filter in concated_filters:
        # Clean the Concated filter to remove non-printable characters
        cleaned_filter = ''.join(char for char in concated_filter if char.isprintable())

        # Split the cleaned Concated filter
        requirement, specification = cleaned_filter.split('|')

        # Filter based on Requirement(s) and Specification(s)
        filter_condition = (df['Requirement(s)'] == requirement) & (df['Specification(s)'] == specification)
        filtered_df = df[filter_condition].copy()

        # Get unique software features for the Concated value
        software_features = ', '.join(filtered_df['Software Features'].unique())

        # Append to the result dataframe
        result_df = pd.concat([result_df, pd.DataFrame({'Requirement(s)': [requirement],
                                                        'Specification(s)': [specification],
                                                        'Concated': [concated_filter],
                                                        'Software Features': [software_features]})], ignore_index=True)

    # Display the resulting dataframe
    result_text.delete('1.0', tk.END)
    result_text.insert(tk.END, result_df.to_string(index=False))

    # Save result to Excel
    result_df.to_excel('output.xlsx', index=False)

# Create the main application window
app = tk.Tk()
app.title("Excel Sheet Processor Utility")

# Create a button to trigger the processing of Excel sheet
process_button = tk.Button(app, text="Process Excel Sheet", command=process_excel_sheet)

# Create a text widget to display the result
result_text = tk.Text(app, height=10, width=80, state=tk.DISABLED)
result_text.pack(padx=10, pady=10)

# Start the main loop
Leave a Comment