import streamlit as st
import pandas as pd

st.title("MultiL Split Excel File by Column Value (4-digit formatting)")

# File uploader
uploaded_file = st.file_uploader("Upload an Excel file", type=["xlsx"])

if uploaded_file:
    # Load Excel file with the header starting at row 3 (Python's 0-index is row 2)
    df = pd.read_excel(uploaded_file, header=2)
    
    # Ensure the column exists
    if "Afsenderkunde løbenumme" in df.columns:
        st.write(f"File uploaded: {uploaded_file.name}")
        unique_values = df["Afsenderkunde løbenumme"].unique()
        st.write(f"Found {len(unique_values)} unique values in 'Afsenderkunde løbenumme'.")
        
        # Iterate through unique values to split the file
        for value in unique_values:
            formatted_value = f"{int(value):04}"  # Format as 4-digit integer
            split_df = df[df["Afsenderkunde løbenumme"] == value]
            output_filename = f"{uploaded_file.name.split('.')[0]} - {formatted_value}.xlsx"
            
            # Convert DataFrame to Excel file for download
            @st.cache_data
            def convert_df_to_excel(dataframe):
                from io import BytesIO
                output = BytesIO()
                with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
                    dataframe.to_excel(writer, index=False, sheet_name="Sheet1")
                output.seek(0)
                return output
            
            excel_data = convert_df_to_excel(split_df)
            
            # Download button for each file
            st.download_button(
                label=f"Download {output_filename}",
                data=excel_data,
                file_name=output_filename,
                mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            )
    else:
        st.error("The column 'Afsenderkunde løbenumme' was not found in the uploaded file.")