src/app.py

import streamlit as st
import pandas as pd
import json
import io
import os
from datetime import datetime
import numpy as np
import base64

# Configure page
st.set_page_config(
    page_title="Excel to JSON Converter",
    page_icon="📊",
    layout="wide"
)

# Debug information
with st.expander("Directory Structure"):
    st.code(f"Current working directory: {os.getcwd()}")
    st.code(f"Directory contents: {os.listdir('.')}")
    
    # Check tmp directory
    if os.path.exists('tmp'):
        st.code(f"tmp directory exists: {os.path.exists('tmp')}")
        st.code(f"tmp directory permissions: {oct(os.stat('tmp').st_mode)[-3:]}")
        st.code(f"tmp directory is writable: {os.access('tmp', os.W_OK)}")
        try:
            st.code(f"tmp directory contents: {os.listdir('tmp')}")
        except Exception as e:
            st.error(f"Error listing tmp directory: {str(e)}")
    else:
        st.error("tmp directory does not exist!")

st.title("Excel to JSON Converter")
st.markdown("""
Upload an Excel file and convert it to a standard JSON format matching your template.
The app will maintain all the original columns and convert values to the appropriate format.
""")

def handle_nan(obj):
    """Convert NaN values to 0.0 to match your JSON format"""
    if isinstance(obj, float) and np.isnan(obj):
        return 0.0
    return obj

def process_excel_data(excel_data, filename, sheet_name=None):
    """Process Excel file data"""
    try:
        # Try to read the Excel file
        if sheet_name:
            df = pd.read_excel(excel_data, sheet_name=sheet_name)
        else:
            df = pd.read_excel(excel_data)
        
        # Show dimensions of the dataframe
        st.info(f"Successfully read {df.shape[0]} rows and {df.shape[1]} columns from the Excel file")
        
        # Fill NaN values
        df = df.fillna(0.0)
        
        # Convert dataframe to list of dictionaries (records)
        result = {"DateStamp": datetime.now().isoformat(), "PriceList": []}
        
        for _, row in df.iterrows():
            record = {}
            for column in df.columns:
                # Convert pandas Timestamp to ISO format string if needed
                if isinstance(row[column], pd.Timestamp):
                    record[column] = row[column].isoformat()
                # Convert float values to match the format in examples
                elif isinstance(row[column], (float, np.float64, np.float32)):
                    record[column] = float(row[column])
                else:
                    record[column] = row[column]
            
            # Add filename if it doesn't exist in the record
            if "FileName" not in record:
                record["FileName"] = filename
                
            result["PriceList"].append(record)
        
        # Convert to JSON string with indentation
        json_str = json.dumps(result, indent=2, default=handle_nan)
        
        return json_str, result
    
    except Exception as e:
        st.error(f"Error: {str(e)}")
        import traceback
        st.code(traceback.format_exc())
        return None, None

def get_download_link(json_str, filename="converted_data.json"):
    """Generate a download link for the JSON file"""
    b64 = base64.b64encode(json_str.encode()).decode()
    href = f'<a href="data:file/json;base64,{b64}" download="{filename}">Download JSON File</a>'
    return href

# Create tabs for different approaches
tab1, tab2 = st.tabs(["File Upload", "Sample Data Demo"])

with tab1:
    st.info("For large files (>5MB), you may need to split them into smaller Excel files first.")
    
    # File uploader
    uploaded_file = st.file_uploader("Upload Excel File (.xlsx, .xls)", type=["xlsx", "xls"])
    
    if uploaded_file is not None:
        # Display success message
        st.success(f"File uploaded: {uploaded_file.name}")
        st.write(f"File size: {uploaded_file.size / 1024:.2f} KB")
        
        try:
            # Read Excel file into memory
            excel_data = io.BytesIO(uploaded_file.getvalue())
            
            try:
                # Try to get sheet names
                xls = pd.ExcelFile(excel_data)
                sheet_names = xls.sheet_names
                st.success(f"Successfully read {len(sheet_names)} sheets")
                
                # Display available sheets
                if len(sheet_names) > 1:
                    selected_sheet = st.selectbox("Select Sheet", options=sheet_names)
                else:
                    selected_sheet = sheet_names[0]
                    st.info(f"Using sheet: {selected_sheet}")
                
                # Process button
                if st.button("Convert to JSON", type="primary"):
                    # Reset the file pointer
                    excel_data = io.BytesIO(uploaded_file.getvalue())
                    
                    # Process the file
                    with st.spinner("Converting..."):
                        json_str, json_data = process_excel_data(excel_data, uploaded_file.name, selected_sheet)
                    
                    if json_str and json_data:
                        st.success("Conversion successful!")
                        
                        # Create tabs for different views
                        json_tab, table_tab, download_tab = st.tabs(["JSON Preview", "Table Preview", "Download"])
                        
                        with json_tab:
                            # For large JSON, show only the first part
                            if len(json_str) > 100000:
                                st.warning("JSON is too large to display fully. Showing first 100,000 characters.")
                                st.code(json_str[:100000] + "...", language="json")
                            else:
                                st.code(json_str, language="json")
                        
                        with table_tab:
                            if "PriceList" in json_data:
                                preview_df = pd.DataFrame(json_data["PriceList"])
                                if len(preview_df) > 1000:
                                    st.write(f"Showing first 1000 rows of {len(json_data['PriceList'])} total")
                                    st.dataframe(preview_df.head(1000), use_container_width=True)
                                else:
                                    st.dataframe(preview_df, use_container_width=True)
                        
                        with download_tab:
                            st.markdown(get_download_link(json_str, f"{os.path.splitext(uploaded_file.name)[0]}.json"), unsafe_allow_html=True)
                            st.info("Click the link above to download the JSON file.")
            
            except Exception as e:
                st.error(f"Error reading Excel file: {str(e)}")
                import traceback
                st.code(traceback.format_exc())
                
                # Fallback: try simple conversion without sheet selection
                if st.button("Try Simple Conversion"):
                    try:
                        # Reset the pointer and try simple conversion
                        excel_data = io.BytesIO(uploaded_file.getvalue())
                        json_str, _ = process_excel_data(excel_data, uploaded_file.name)
                        
                        if json_str:
                            st.success("Simple conversion successful!")
                            # For large JSON, show only the first part
                            if len(json_str) > 100000:
                                st.warning("JSON is too large to display fully. Showing first 100,000 characters.")
                                st.code(json_str[:100000] + "...", language="json")
                            else:
                                st.code(json_str, language="json")
                            st.markdown(get_download_link(json_str, f"{os.path.splitext(uploaded_file.name)[0]}.json"), unsafe_allow_html=True)
                    except Exception as e2:
                        st.error(f"Simple conversion failed: {str(e2)}")
                        st.code(traceback.format_exc())
        
        except Exception as e:
            st.error(f"Error processing file: {str(e)}")
            import traceback
            st.code(traceback.format_exc())

with tab2:
    st.info("This demo uses sample data to show how the converter works.")
    
    # Create sample data
    st.write("### Sample Data")
    
    # Generate sample data that matches your expected format
    sample_data = {
        "Supplier": ["TestCompany", "TestCompany", "TestCompany"],
        "Manufacturer": ["AJA", "AJA", "GRASS VALLEY"],
        "ModelCode": ["TEST-001", "TEST-002", "TEST-003"],
        "ModelDescription": ["Test Product 1", "Test Product 2", "Test Product 3"],
        "T1List": [0.0, 100.0, 200.0],
        "T1Cost": [0.0, 80.0, 160.0],
        "T2List": [150.0, 250.0, 350.0],
        "T2Cost": [120.0, 200.0, 280.0],
        "ISOCurrency": ["EUR", "EUR", "USD"],
        "ValidityDate": ["2025-12-31", "2025-12-31", "2025-12-31"],
        "T1orT2": ["T2", "T2", "T2"],
        "MaterialID": ["MAT-001", "MAT-002", "MAT-003"],
        "SAPNumber": ["SAP-001", "SAP-002", "SAP-003"],
        "ModelDescriptionEnglish": ["Test Product 1 in English", "Test Product 2 in English", "Test Product 3 in English"],
        "QuoteOrPriceList": ["Price List", "Price List", "Price List"],
        "WeightKg": [1.5, 2.0, 3.5],
        "HeightMm": [100.0, 150.0, 200.0],
        "LengthMm": [200.0, 250.0, 300.0],
        "WidthMm": [150.0, 175.0, 225.0],
        "PowerWatts": [50.0, 75.0, 100.0],
        "FileName": ["SampleData.xlsx", "SampleData.xlsx", "SampleData.xlsx"]
    }
    
    # Convert to DataFrame
    sample_df = pd.DataFrame(sample_data)
    
    # Display the sample data
    st.dataframe(sample_df)
    
    # Allow user to edit the sample data
    st.write("### Edit Sample Data (Optional)")
    
    # Let user add a row
    with st.expander("Add or Edit Rows"):
        # Add simple editing capabilities
        new_row = {}
        
        col1, col2 = st.columns(2)
        with col1:
            new_row["Supplier"] = st.text_input("Supplier", "YourCompany")
            new_row["Manufacturer"] = st.text_input("Manufacturer", "YourBrand")
            new_row["ModelCode"] = st.text_input("ModelCode", "CUSTOM-001")
            new_row["ModelDescription"] = st.text_input("ModelDescription", "Custom Product")
        
        with col2:
            new_row["T2List"] = st.number_input("T2List", value=499.99)
            new_row["T2Cost"] = st.number_input("T2Cost", value=399.99)
            new_row["ISOCurrency"] = st.selectbox("ISOCurrency", ["EUR", "USD", "GBP"])
            new_row["ValidityDate"] = st.date_input("ValidityDate")
        
        if st.button("Add Row to Sample Data"):
            # Fill in missing fields with defaults
            for col in sample_df.columns:
                if col not in new_row:
                    if col == "FileName":
                        new_row[col] = "SampleData.xlsx"
                    elif "Date" in col:
                        new_row[col] = "2025-12-31"
                    elif sample_df[col].dtype == float:
                        new_row[col] = 0.0
                    else:
                        new_row[col] = ""
            
            # Append the new row
            sample_df = pd.concat([sample_df, pd.DataFrame([new_row])], ignore_index=True)
            st.success("Row added!")
            st.dataframe(sample_df)
    
    # Convert button
    if st.button("Convert Sample Data to JSON", key="convert2"):
        json_str, json_data = process_excel_data(sample_df, "SampleData.xlsx")
        
        if json_str and json_data:
            st.success("Conversion successful!")
            
            # Create tabs for different views
            json_tab, table_tab, download_tab = st.tabs(["JSON", "Table", "Download"])
            
            with json_tab:
                st.code(json_str, language="json")
            
            with table_tab:
                if "PriceList" in json_data:
                    preview_df = pd.DataFrame(json_data["PriceList"])
                    st.dataframe(preview_df)
            
            with download_tab:
                st.markdown(get_download_link(json_str, "sample_data.json"), unsafe_allow_html=True)
                st.info("Click the link above to download the JSON file.")

# Information about expected format
with st.expander("Expected Excel Format"):
    st.markdown("""
    Your Excel file should contain columns such as:
    
    - Supplier
    - Manufacturer
    - ModelCode
    - ModelDescription
    - T1List
    - T1Cost
    - T2List
    - T2Cost
    - ISOCurrency
    - ValidityDate
    - T1orT2
    - MaterialID
    - SAPNumber
    - ModelDescriptionEnglish
    - ModelDescriptionLanguage2
    - ModelDescriptionLanguage3
    - ModelDescriptionLanguage4
    - QuoteOrPriceList
    - WeightKg
    - HeightMm
    - LengthMm
    - WidthMm
    - PowerWatts
    - FileName
    
    But the app will work with any Excel format, preserving your column structure.
    """)

st.markdown("---")

# Add instructions for local usage
with st.expander("Run This App Locally"):
    st.markdown("""
    ### Instructions for Running Locally
    
    If you're encountering upload issues, you can run this app on your own computer:
    
    1. Install Python if you don't have it already
    2. Install the required packages:
    ```bash
    pip install streamlit pandas openpyxl
    ```
    
    3. Save this app code to a file named `app.py`
    4. Run the app with:
    ```bash
    streamlit run app.py
    ```
    
    5. Access the app in your browser and upload your Excel files locally
    
    ### Alternative: Direct Excel to JSON Conversion Script
    
    You can also use this simple Python script to convert Excel to JSON directly:
    
    ```python
    import pandas as pd
    import json
    from datetime import datetime
    
    # Replace with your Excel file path
    excel_file = "your_file.xlsx"
    
    # Read the Excel file
    df = pd.read_excel(excel_file)
    
    # Fill NaN values
    df = df.fillna(0.0)
    
    # Convert dataframe to list of dictionaries
    result = {"DateStamp": datetime.now().isoformat(), "PriceList": []}
    
    for _, row in df.iterrows():
        record = {}
        for column in df.columns:
            # Convert pandas Timestamp to ISO format string
            if isinstance(row[column], pd.Timestamp):
                record[column] = row[column].isoformat()
            # Convert float values
            elif isinstance(row[column], float):
                record[column] = float(row[column])
            else:
                record[column] = row[column]
        
        # Add filename if it doesn't exist
        if "FileName" not in record:
            record["FileName"] = excel_file
            
        result["PriceList"].append(record)
    
    # Save to JSON file
    with open("output.json", "w") as f:
        json.dump(result, f, indent=2)
    
    print(f"Conversion complete! JSON saved to output.json")
    ```
    """)

# Add footer
st.markdown("---")
st.markdown("Excel to JSON Converter | Created with Streamlit")