Excel data loaded: 23 rows
Loading Word document for 'Yes' forms...
Total tables found in the 'Yes' document: 3
Loading Word document for 'No' forms...
Total tables found in the 'No' document: 3
Unexpected error: '_Cell' object has no attribute 'clear'     import pandas as pd
from docx import Document
from docx.shared import RGBColor

def populate_chambers(excel_path, doc_path_yes, doc_path_no, 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 for 'Yes' forms...")
        doc_yes = Document(doc_path_yes)
        print(f"Total tables found in the 'Yes' document: {len(doc_yes.tables)}")
        
        print("Loading Word document for 'No' forms...")
        doc_no = Document(doc_path_no)
        print(f"Total tables found in the 'No' document: {len(doc_no.tables)}")
        
        # Check if tables exist in both documents
        if not doc_yes.tables or not doc_no.tables:
            print("Error: No tables found in one or both documents.")
            return
        
        # Iterate through each row of the DataFrame
        for i, row in df.iterrows():
            # Determine which document to use based on the Confidential/Publishable column
            if row['Confidential / Publishable '] == 'Yes':
                if i < len(doc_yes.tables):
                    table = doc_yes.tables[i]
                    fill_table_with_data(row, table, True)
                else:
                    print(f"Warning: No more 'Yes' tables available in the document for extra rows in Excel.")
            elif row['Confidential / Publishable '] == 'No':
                if i < len(doc_no.tables):
                    table = doc_no.tables[i]
                    fill_table_with_data(row, table, False)
                else:
                    print(f"Warning: No more 'No' tables available in the document for extra rows in Excel.")
            else:
                print(f"Warning: Unexpected value in the 'Confidential / Publishable ' column: {row['Confidential / Publishable ']}.")
        
        # Save the updated documents
        doc_yes.save(output_path.replace('.docx', '_yes.docx'))
        doc_no.save(output_path.replace('.docx', '_no.docx'))
        print("Documents updated successfully!")
        
    except Exception as e:
        print(f"Unexpected error: {e}")

def fill_table_with_data(row, table, is_confidential):
    try:
        # Ensure that the table has enough rows
        if len(table.rows) >= 15:
            # Fill the cells from the row in the DataFrame
            # Example cells are being populated below, adjust to your specific table structure
            fill_cell(table.cell(4, 0), str(row['Client Name ']), is_confidential)  # Client Name
            fill_cell(table.cell(24, 0), str(row['Description  (in 500 words)']), is_confidential)  # Description
            fill_cell(table.cell(14, 0), str(row['Value ']), is_confidential)  # Matter/Case Overview
            fill_cell(table.cell(20, 0), str(row['Lead partners']), is_confidential)  # Lead Partner
            fill_cell(table.cell(22, 0), str(row['Other Firms/counsels Involved']), is_confidential)  # Other Involved Firms

            print("Table filled with 'Yes' form data.")
        else:
            print(f"Warning: Table has only {len(table.rows)} rows. Expected at least 15.")
    except IndexError as e:
        print(f"Error: Unable to fill the table. {e}")

def fill_cell(cell, text, is_confidential):
    # Clear existing content
    cell.clear()

    # Adding a new paragraph to the cell
    p = cell.add_paragraph()
    run = p.add_run(text)

    if is_confidential:
        run.font.color.rgb = RGBColor(255, 0, 0)  # Set the font color to red

# File paths
excel_path = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx" 
doc_path_yes = r"C:\Users\Administrator\Desktop\1000.docx"
doc_path_no = r"C:\Users\Administrator\Desktop\1000.docx"
output_path = r"C:\Users\Administrator\Desktop\output.docx" 

populate_chambers(excel_path, doc_path_yes, doc_path_no, output_path)