Spaces:
Sleeping
Sleeping
File size: 3,495 Bytes
210cae4 6621159 210cae4 98ebf2e 1d5d40c 98ebf2e 210cae4 1d5d40c 210cae4 1d5d40c 210cae4 cb1f896 210cae4 98ebf2e 210cae4 cb1f896 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 |
import pandas as pd
from openpyxl import load_workbook
import gradio as gr
import os
# 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] * len(input_workbook.sheet_names) # 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()
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
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: {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()
|