Spaces:
Sleeping
Sleeping
File size: 3,879 Bytes
210cae4 6621159 210cae4 8a53b2d 210cae4 98ebf2e 1d5d40c 98ebf2e 210cae4 1d5d40c 210cae4 8a53b2d 1d5d40c 210cae4 8a53b2d 210cae4 cb1f896 210cae4 98ebf2e 210cae4 cb1f896 8a53b2d cb1f896 210cae4 8a53b2d 210cae4 cb1f896 210cae4 cb1f896 210cae4 cb1f896 210cae4 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
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()
|