import pandas as pd
from docx import Document
from datetime import datetime

def populate_docx_template(excel_file, template_file, output_file):
    """
    Populate a Word document template with data from an Excel file using
    precise cell coordinates.
    
    Args:
        excel_file (str): Path to the Excel file containing the data
        template_file (str): Path to the Word document template
        output_file (str): Path where the populated document will be saved
    """
    # Load Excel data
    df = pd.read_excel(excel_file)
    
    # Process each row in the Excel file
    for index, row in df.iterrows():
        # Load the template for each row
        doc = Document(template_file)
        
        # Extract data from Excel row
        client_name = row.get('Client Name ', '')
        matter_overview = row.get('Matter/Case Overview', '')
        description = row.get('Description  (in 500 words)', '')
        value = row.get('Value ', '')
        role = row.get('Role of S&A', '')
        significance = row.get('The significance, impact and outcome of the case', '')
        status = row.get('Current Status (ongoing / completed )', '')
        lead_partners = row.get('Lead partners', '')
        team_members = row.get('Team Members', '')
        other_firms = row.get('Other Firms/counsels Involved', '')
        publishable = row.get('Confidential / Publishable', '')
        
        # Get the first table (Table 1 in the screenshot)
        if len(doc.tables) > 0:
            table = doc.tables[0]
            
            # Direct cell mapping based on the screenshot coordinates
            # Format: table.cell(row, column).text = value
            
            # Publishable matter (choosing "Publishable" or "Confidential")
            if publishable.lower() == "publishable":
                table.cell(0, 1).text = "Publishable"
            else:
                table.cell(0, 1).text = "Confidential"
            
            # Client Name - mapped to multiple cells [2,1], [2,2], [2,3]
            table.cell(3, 0).text = client_name
            
            
            # Matter description - mapped to cells [4,1], [4,2], [4,3]
            matter_text = f"{matter_overview}\n\n{description}"
            table.cell(5, 0).text = matter_text
            
            
            # Role description - mapped to cells [5,1], [5,2], [5,3]
           
            
            # Deal value - mapped to cells [6,1], [6,2], [6,3]
            table.cell(6, 1).text = str(value)
           
            
            # Lead partner(s) - mapped to cells [7,1], [7,2], [7,3]
            table.cell(9, 0).text = lead_partners
            table.cell(10, 0).text = lead_partners
           
            
            
            
            # Team members - mapped to cells [11,1], [11,2], [11,3]
            # Add to the existing "Other key team members" section
            # From the screenshot, these are at indices [11,1], [11,2], [11,3]
            
            
            # Start date - mapped to cells [15,1], [15,2]
            current_date = datetime.now().strftime("%d-%m-%Y")
            
            table.cell(16, 3).text = current_date  # Using same date for end date
        
        # Save the populated document
        clean_client_name = ''.join(c for c in client_name if c.isalnum() or c in [' ', '_']).replace(' ', '_')
        output_filename = f"{output_file.replace('.docx', '')}_{clean_client_name}.docx"
        doc.save(output_filename)
        print(f"Created document: {output_filename}")

def main():
    # Define file paths
    
    excel_file = r'c:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx'   # Update with your Excel file path
    template_file = r'c:\Users\Administrator\Desktop\legal500.docx' # Update with your template file path
    output_file =  r'c:\Users\Administrator\Desktop\output'  # Base name for o
    
    try:
        # Populate the template with Excel data
        populate_docx_template(excel_file, template_file, output_file)
        print(f"Document creation completed successfully.")
    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    main()
   in this instaed of saving all the docx on dsktop ....temproiary store them and in end coombine all docx in a single docx