import pandas as pd
from docx import Document

def populate_chambers(excel_path, doc_path, output_path):
    try:
        print("Loading Excel data...")
        df = pd.read_excel(excel_path)
        print(f"Excel data loaded: {len(df)} rows")
        
        print("Loading Word document...")
        print("Available columns:", df.columns.tolist())

        doc = Document(doc_path)
        tables = doc.tables
        print(f"Total tables found in document: {len(tables)}")
        
        if not tables:
            print("Error: No tables found in document.")
            return
        
        publishable_index = 0
        confidential_index = 0
        
        # Populate Publishable Matters
        for i, row in df.iterrows():
            if i < len(tables):
                table = tables[i]  # Assume publishable tables appear first
                if len(table.rows) >= 18:
                    try:
                        table.cell(2, 0).text = f" {row['Client Name ']}"
                       # table.cell(0, 0).text = f" {row['Description  (in 500 words)']}"
                        table.cell(4, 0).text = f" {row['Value ']}"
                        table.cell(10, 0).text = f" {row['Lead partners']}"
                        table.cell(12, 0).text = f" {row['Team Members']}"
                        print(f"Publishable matter {i} populated successfully.")
                    except IndexError as e:
                        print(f"Error: Table {i} has fewer rows than expected. Skipping entry. {e}")
                else:
                    print(f"Warning: Table {i} has only {len(table.rows)} rows. Expected at least 6.")
                publishable_index += 1
        
        # Populate Confidential Matters
        for i, row in df.iterrows():
            if row.get('Confidential', 'No') == "Yes":  # Check confidentiality column
                table_index = confidential_index + publishable_index
                if table_index < len(tables):
                    table = tables[table_index]
                    if len(table.rows) >= 6:
                        try:
                            table.cell(1, 0).text = f"E1 Name of client: {row['Client Name']}"
                            table.cell(2, 0).text = f"E2 Summary: {row['Description (in500 words)']}"
                            table.cell(3, 0).text = f"E3 Matter value: {row['Value']}"
                            print(f"Confidential matter {i} populated successfully.")
                        except IndexError as e:
                            print(f"Error: Confidential Table {table_index} has fewer rows than expected. Skipping entry. {e}")
                    else:
                        print(f"Warning: Confidential Table {table_index} has only {len(table.rows)} rows. Expected at least 6.")
                    confidential_index += 1
        
        doc.save(output_path)
        print("Document updated successfully!")
    except Exception as e:
        print(f"Unexpected error: {e}")


# File paths
excel_path = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx" 
doc_path = r"C:\Users\Administrator\Desktop\Submission Form Templates\Templates\Chambers.docx" 
output_path = r"C:\Users\Administrator\Desktop\Chambersss.docx" 
 

populate_chambers(excel_path, doc_path, output_path)
    in this when i define cells it is not going to right place everytime is it possible to make chnges in this code where we dervibe cell like place name from xcel to the cell below the cell havig text Name of client in the excel ...for row 1 populate matter 1 and index 1 +and row +1