Spaces:
Sleeping
Sleeping
| import pandas as pd | |
| from openpyxl import load_workbook | |
| import gradio as gr | |
| import os | |
| import warnings | |
| # Suppress openpyxl warnings | |
| warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl") | |
| # Load the constant mapping file (embedded in the app) | |
| def load_mapping(): | |
| mapping_data = { | |
| "PO Output Column": ["Column1", "Column2", "Column3"], # Replace with actual columns | |
| "UVM MMB POLY STICKER Column": ["Sheet1.ColumnA", "Sheet1.ColumnB", "Fixed-Value"] # Replace with mapping logic | |
| } | |
| return pd.DataFrame(mapping_data) | |
| # Function to extract and map data from the input workbook | |
| def transform_data(input_path, mapping_df): | |
| # Load the input workbook | |
| input_workbook = pd.ExcelFile(input_path) | |
| # Initialize a dictionary to store data for output | |
| output_data = {col: [] for col in mapping_df["PO Output Column"] if not pd.isna(col)} | |
| # Iterate through each mapping rule | |
| for _, row in mapping_df.iterrows(): | |
| output_column = row["PO Output Column"] | |
| input_rule = row["UVM MMB POLY STICKER Column"] | |
| if pd.isna(output_column) or pd.isna(input_rule): | |
| continue | |
| # Handle fixed values | |
| if "Fixed" in input_rule: | |
| fixed_value = input_rule.split("-")[0].strip() | |
| output_data[output_column] = [fixed_value] * 10 # Placeholder for rows | |
| # Handle column mapping from input workbook | |
| elif "." in input_rule: | |
| sheet_name, column_name = input_rule.split(".") | |
| if sheet_name in input_workbook.sheet_names: | |
| sheet_data = pd.read_excel(input_path, sheet_name=sheet_name) | |
| if column_name in sheet_data.columns: | |
| output_data[output_column] = sheet_data[column_name].tolist() | |
| # Fill missing columns with empty lists | |
| for key in output_data: | |
| if not output_data[key]: | |
| output_data[key] = ["" for _ in range(10)] | |
| return pd.DataFrame(output_data) | |
| # Main processing function | |
| def process_files(input_workbook): | |
| try: | |
| # Load the constant mapping data | |
| mapping_df = load_mapping() | |
| # Transform the data | |
| transformed_data = transform_data(input_workbook, mapping_df) | |
| # Load the output template (embedded in the app) | |
| output_template_path = "output_template.xlsx" # Replace with the actual template file path | |
| if not os.path.exists(output_template_path): | |
| return "Output template file is missing." | |
| output_workbook = load_workbook(output_template_path) | |
| output_sheet = output_workbook["363040"] | |
| # Write transformed data to the output sheet | |
| for row_idx, row_data in enumerate(transformed_data.itertuples(index=False), start=2): | |
| for col_idx, value in enumerate(row_data, start=1): | |
| output_sheet.cell(row=row_idx, column=col_idx, value=value) | |
| # Save the generated output file | |
| output_file_path = "Generated_Output.xlsx" | |
| output_workbook.save(output_file_path) | |
| return output_file_path | |
| except Exception as e: | |
| return f"An error occurred during file generation: {e}" | |
| # Define the Gradio interface | |
| def generate_excel(input_workbook): | |
| result = process_files(input_workbook.name) | |
| if os.path.exists(result): | |
| return result | |
| else: | |
| return "An error occurred during file generation." | |
| with gr.Blocks() as app: | |
| gr.Markdown("# Excel Sheet Generator") | |
| gr.Markdown("Upload the input workbook to generate the final Excel file.") | |
| with gr.Row(): | |
| input_workbook = gr.File(label="Input Workbook", file_types=[".xlsx"]) | |
| output_file = gr.File(label="Generated Excel File") | |
| generate_button = gr.Button("Generate Excel File") | |
| generate_button.click(generate_excel, inputs=[input_workbook], outputs=[output_file]) | |
| # Launch the app | |
| app.launch() | |