import pandas as pd from docx import Document import os from datetime import datetime…
- User
import pandas as pd from docx import Document import os from datetime import datetime
def auto_populate_chambers_form(excel_file, template_file, output_file): """ Auto-populate Chambers submission form (Word document) with data from Excel file
Parameters: excel_file (str): Path to the Excel file containing matter data template_file (str): Path to the Word document template (Chambers.docx) output_file (str): Path where the populated document will be saved """ print(f"Reading data from Excel file: {excel_file}") # Read Excel data and strip whitespace from column names df = pd.read_excel(excel_file) df.columns = df.columns.str.strip() # Strip whitespace from column names # Check columns after stripping print("Columns in DataFrame:", df.columns.tolist()) # Debugging line # Check if the required column is present if 'Confidential / Publishable' not in df.columns: print("Column 'Confidential / Publishable' not found in DataFrame.") return # or handle this issue as suitable # Count how many publishable and confidential matters we have publishable_matters = df[df['Confidential / Publishable'].str.lower() == 'publishable'] confidential_matters = df[df['Confidential / Publishable'].str.lower() == 'confidential'] print(f"Found {len(publishable_matters)} publishable matters and {len(confidential_matters)} confidential matters") # Continue with the rest of your code... # Populate publishable matters (Section D) for i, matter in publishable_matters.iterrows(): if i >= 10: # Max 10 publishable matters allowed print("Warning: More than 10 publishable matters found in Excel. Only the first 10 will be used.") break matter_num = i + 1 section_name = f"Publishable Matter {matter_num}" # Find the section in the document found_section = False for para in doc.paragraphs: if section_name in para.text: found_section = True break if not found_section: print(f"Warning: Could not find section '{section_name}' in the template document.") continue # Populate fields for this matter populate_matter_fields( doc, section_name, "D", matter['Client Name'], matter['Matter/Case Overview'], matter['Value'], matter.get('Cross-border', 'N/A'), matter['Lead partners'], matter['Team Members'], matter.get('Other Firms/counsels Involved', ''), matter.get('Current Status', ''), matter.get('Additional Info', '') ) # Populate confidential matters (Section E) for i, matter in confidential_matters.iterrows(): if i >= 10: # Max 10 confidential matters allowed print("Warning: More than 10 confidential matters found in Excel. Only the first 10 will be used.") break matter_num = i + 1 section_name = f"Confidential Matter {matter_num}" # Find the section in the document found_section = False for para in doc.paragraphs: if section_name in para.text: found_section = True break if not found_section: print(f"Warning: Could not find section '{section_name}' in the template document.") continue # Populate fields for this matter populate_matter_fields( doc, section_name, "E", matter['Client Name'], matter['Matter/Case Overview'], matter['Value'], matter.get('Cross-border', 'N/A'), matter['Lead partners'], matter['Team Members'], matter.get('Other Firms/counsels Involved', ''), matter.get('Current Status', ''), matter.get('Additional Info', '') ) # Populate client lists populate_client_lists(doc, publishable_matters, confidential_matters) # Save the populated document doc.save(output_file) print(f"Document successfully populated and saved as: {output_file}")def populate_matter_fields(doc, section_name, section_prefix, client_name, matter_overview, value, cross_border, lead_partner, team_members, other_firms, status, additional_info): """ Populate the fields for a specific matter in the document """ # Combine matter overview with role, significance, etc. from Excel # (Adjust as needed based on your Excel structure)
# Find and replace fields for i, para in enumerate(doc.paragraphs): # Client name field if f"{section_prefix}1 Name of client" in para.text: # Find the paragraph after this one and set its text if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = client_name # Matter summary field elif f"{section_prefix}2 Summary of matter" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = matter_overview # Matter value field elif f"{section_prefix}3 Matter value" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = str(value) # Cross-border field elif f"{section_prefix}4 Is this a cross-border matter" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = cross_border # Lead partner field elif f"{section_prefix}5 Lead partner" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = lead_partner # Team members field elif f"{section_prefix}6 Other team members" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = team_members # Other firms field elif f"{section_prefix}7 Other firms advising" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = other_firms # Status field elif f"{section_prefix}8 Date of completion" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = status # Additional info field elif f"{section_prefix}9 Other information" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = additional_infodef populate_client_lists(doc, publishable_matters, confidential_matters): """ Populate the publishable and confidential client lists """ # Find the client list tables tables = doc.tables publishable_clients_table = None confidential_clients_table = None
# Locate the tables by checking their content for table in tables: for row in table.rows: for cell in row.cells: if "D -- [PUBLISHABLE] CLIENTS" in cell.text: publishable_clients_table = table elif "E0 -- [CONFIDENTIAL] CLIENTS" in cell.text: confidential_clients_table = table # Populate publishable clients table if publishable_clients_table: for i, matter in publishable_matters.iterrows(): if i >= len(publishable_clients_table.rows) - 1: # -1 for header row break # Row index (skipping header row) row_idx = i + 1 # Set client name if len(publishable_clients_table.rows[row_idx].cells) >= 2: publishable_clients_table.rows[row_idx].cells[1].text = matter['Client Name'] # Set 'New Client' field if available in Excel if 'New Client' in matter and len(publishable_clients_table.rows[row_idx].cells) >= 3: publishable_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '') # Populate confidential clients table if confidential_clients_table: for i, matter in confidential_matters.iterrows(): if i >= len(confidential_clients_table.rows) - 1: # -1 for header row break # Row index (skipping header row) row_idx = i + 1 # Set client name if len(confidential_clients_table.rows[row_idx].cells) >= 2: confidential_clients_table.rows[row_idx].cells[1].text = matter['Client Name'] # Set 'New Client' field if available in Excel if 'New Client' in matter and len(confidential_clients_table.rows[row_idx].cells) >= 3: confidential_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '')if name == "main": # File paths - adjust these to your actual file locations excel_file = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx" # Your Excel file with matter data template_file = r"C:\Users\Administrator\Desktop\Submission Form Templates\Templates\Chambers.docx"# Original Chambers form template
# Create output file with timestamp timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") output_file = f"Chambers_Populated_{timestamp}.docx" # Run the auto-population auto_populate_chambers_form(excel_file, template_file, output_file) this code and this error PS C:\Users\Administrator\Desktop\Bd team> python app.pyReading data from Excel file: C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx Columns in DataFrame: ['S No.', 'Client Name', 'Matter/Case Overview', 'Value', 'Description (in 500 words)', 'Role of S&A', 'The significance, impact and outcome of the case', 'Current Status (ongoing / completed )', 'Lead partners', 'Team Members', 'Other Firms/counsels Involved', 'Confidential / Publishable'] Found 0 publishable matters and 0 confidential matters Traceback (most recent call last): File "C:\Users\Administrator\Desktop\Bd team\app.py", line 239, in auto_populate_chambers_form(excel_file, template_file, output_file)
File "C:\Users\Administrator\Desktop\Bd team\app.py", line 110, in auto_populate_chambers_form populate_client_lists(doc, publishable_matters, confidential_matters)
^^^ NameError: name 'doc' is not defined PS C:\Users\Administrator\Desktop\Bd team> the confidentila matter as yes in last column while oublishable have no in last column - Assistant
The error occurred because the variable
dochas not been defined in yourauto_populate_chambers_formfunction before you attempt to use it in thepopulate_client_listsfunction call.Furthermore, the output shows that you found 0 publishable and confidential matters, which means that your filters for identifying these matters may not be working as expected. Let's fix both of these issues in your code.
Here’s the updated version of your code:
- Add Document Loading: Load the document in the
auto_populate_chambers_formfunction. - Correct Filtering: Ensure proper filtering based on the
'Confidential / Publishable'column.
Here is the updated code:
import pandas as pd from docx import Document import os from datetime import datetime def auto_populate_chambers_form(excel_file, template_file, output_file): """ Auto-populate Chambers submission form (Word document) with data from Excel file Parameters: excel_file (str): Path to the Excel file containing matter data template_file (str): Path to the Word document template (Chambers.docx) output_file (str): Path where the populated document will be saved """ print(f"Reading data from Excel file: {excel_file}") # Read Excel data and strip whitespace from column names df = pd.read_excel(excel_file) df.columns = df.columns.str.strip() # Strip whitespace from column names # Check columns after stripping print("Columns in DataFrame:", df.columns.tolist()) # Debugging line # Check if the required column is present if 'Confidential / Publishable' not in df.columns: print("Column 'Confidential / Publishable' not found in DataFrame.") return # or handle this issue as suitable # Count how many publishable and confidential matters we have publishable_matters = df[df['Confidential / Publishable'].str.lower() == 'no'] # Changed to 'no' confidential_matters = df[df['Confidential / Publishable'].str.lower() == 'yes'] # Changed to 'yes' print(f"Found {len(publishable_matters)} publishable matters and {len(confidential_matters)} confidential matters") # Load the Word document template doc = Document(template_file) # Continue with the rest of your code... # Populate publishable matters (Section D) for i, matter in publishable_matters.iterrows(): if i >= 10: # Max 10 publishable matters allowed print("Warning: More than 10 publishable matters found in Excel. Only the first 10 will be used.") break matter_num = i + 1 section_name = f"Publishable Matter {matter_num}" # Find the section in the document found_section = False for para in doc.paragraphs: if section_name in para.text: found_section = True break if not found_section: print(f"Warning: Could not find section '{section_name}' in the template document.") continue # Populate fields for this matter populate_matter_fields( doc, section_name, "D", matter['Client Name'], matter['Matter/Case Overview'], matter['Value'], matter.get('Cross-border', 'N/A'), matter['Lead partners'], matter['Team Members'], matter.get('Other Firms/counsels Involved', ''), matter.get('Current Status', ''), matter.get('Additional Info', '') ) # Populate confidential matters (Section E) for i, matter in confidential_matters.iterrows(): if i >= 10: # Max 10 confidential matters allowed print("Warning: More than 10 confidential matters found in Excel. Only the first 10 will be used.") break matter_num = i + 1 section_name = f"Confidential Matter {matter_num}" # Find the section in the document found_section = False for para in doc.paragraphs: if section_name in para.text: found_section = True break if not found_section: print(f"Warning: Could not find section '{section_name}' in the template document.") continue # Populate fields for this matter populate_matter_fields( doc, section_name, "E", matter['Client Name'], matter['Matter/Case Overview'], matter['Value'], matter.get('Cross-border', 'N/A'), matter['Lead partners'], matter['Team Members'], matter.get('Other Firms/counsels Involved', ''), matter.get('Current Status', ''), matter.get('Additional Info', '') ) # Populate client lists populate_client_lists(doc, publishable_matters, confidential_matters) # Save the populated document doc.save(output_file) print(f"Document successfully populated and saved as: {output_file}") def populate_matter_fields(doc, section_name, section_prefix, client_name, matter_overview, value, cross_border, lead_partner, team_members, other_firms, status, additional_info): """ Populate the fields for a specific matter in the document """ # Find and replace fields for i, para in enumerate(doc.paragraphs): # Client name field if f"{section_prefix}1 Name of client" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = client_name # Matter summary field elif f"{section_prefix}2 Summary of matter" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = matter_overview # Matter value field elif f"{section_prefix}3 Matter value" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = str(value) # Cross-border field elif f"{section_prefix}4 Is this a cross-border matter" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = cross_border # Lead partner field elif f"{section_prefix}5 Lead partner" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = lead_partner # Team members field elif f"{section_prefix}6 Other team members" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = team_members # Other firms field elif f"{section_prefix}7 Other firms advising" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = other_firms # Status field elif f"{section_prefix}8 Date of completion" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = status # Additional info field elif f"{section_prefix}9 Other information" in para.text: if i+1 < len(doc.paragraphs): doc.paragraphs[i+1].text = additional_info def populate_client_lists(doc, publishable_matters, confidential_matters): """ Populate the publishable and confidential client lists """ # Find the client list tables tables = doc.tables publishable_clients_table = None confidential_clients_table = None # Locate the tables by checking their content for table in tables: for row in table.rows: for cell in row.cells: if "D -- [PUBLISHABLE] CLIENTS" in cell.text: publishable_clients_table = table elif "E0 -- [CONFIDENTIAL] CLIENTS" in cell.text: confidential_clients_table = table # Populate publishable clients table if publishable_clients_table: for i, matter in publishable_matters.iterrows(): if i >= len(publishable_clients_table.rows) - 1: # -1 for header row break # Row index (skipping header row) row_idx = i + 1 # Set client name if len(publishable_clients_table.rows[row_idx].cells) >= 2: publishable_clients_table.rows[row_idx].cells[1].text = matter['Client Name'] # Set 'New Client' field if available in Excel if 'New Client' in matter and len(publishable_clients_table.rows[row_idx].cells) >= 3: publishable_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '') # Populate confidential clients table if confidential_clients_table: for i, matter in confidential_matters.iterrows(): if i >= len(confidential_clients_table.rows) - 1: # -1 for header row break # Row index (skipping header row) row_idx = i + 1 # Set client name if len(confidential_clients_table.rows[row_idx].cells) >= 2: confidential_clients_table.rows[row_idx].cells[1].text = matter['Client Name'] # Set 'New Client' field if available in Excel if 'New Client' in matter and len(confidential_clients_table.rows[row_idx].cells) >= 3: confidential_clients_table.rows[row_idx].cells[2].text = matter.get('New Client', '') if __name__ == "__main__": # File paths - adjust these to your actual file locations excel_file = r"C:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx" # Your Excel file with matter data template_file = r"C:\Users\Administrator\Desktop\Submission Form Templates\Templates\Chambers.docx" # Original Chambers form template # Create output file with timestamp timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") output_file = f"Chambers_Populated_{timestamp}.docx" # Run the auto-population auto_populate_chambers_form(excel_file, template_file, output_file)Key Changes:
- Loaded the Word Document: The
Documentis initialized using the template file within theauto_populate_chambers_formfunction. - Filtering: The filtering of publishable and confidential matters is correctly implemented based on the 'Yes' and 'No' values in the
'Confidential / Publishable'column.
These changes should resolve the issues you encountered and improve the functionality. Be sure to confirm the actual column names from your Excel sheet are matched correctly in your code.
- Add Document Loading: Load the document in the