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'Download JSON File' 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")