import streamlit as st
import pdfplumber
import pandas as pd
from io import BytesIO

def extract_tables_from_pdf(pdf_file):
    tables = []
    with pdfplumber.open(pdf_file) as pdf:
        for page in pdf.pages:
            extracted_tables = page.extract_tables()
            for table in extracted_tables:
                tables.append(pd.DataFrame(table))
    
    if not tables:
        return None
    
    # Concatenate all tables into one DataFrame
    final_df = pd.concat(tables, ignore_index=True)
    
    # Set first row as column headers (if applicable)
    final_df.columns = final_df.iloc[0]  # First row as headers
    final_df = final_df[1:].reset_index(drop=True)
    
    return final_df

def convert_to_excel(dataframe):
    output = BytesIO()
    with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
        dataframe.to_excel(writer, index=False, sheet_name='Sheet1')
    output.seek(0)
    return output

# Streamlit UI
st.title("PDF to Excel Converter")
st.write("Upload a PDF file with tabular data, and it will be converted into an Excel file.")

uploaded_file = st.file_uploader("Choose a PDF file", type=["pdf"])

if uploaded_file is not None:
    with st.spinner("Extracting tables from PDF..."):
        df = extract_tables_from_pdf(uploaded_file)
        
        if df is not None:
            st.write("### Extracted Table Preview")
            st.dataframe(df)
            
            excel_data = convert_to_excel(df)
            st.download_button(label="Download Excel File", 
                               data=excel_data, 
                               file_name="converted.xlsx", 
                               mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        else:
            st.error("No tables found in the PDF.")