import pandas as pd from docx import Document from datetime import datetime def…
- User
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
- Assistant
To modify your script to temporarily store all generated
.docxfiles and then combine them into a single document at the end, you can use thepython-docxlibrary for creating and manipulating Word documents and theosmodule for handling file paths.You will need to follow these steps:
- Create a temporary directory to store the individual documents.
- Generate your documents and save them as temporary files in this directory.
- At the end, combine all these temporary documents into a single document.
- Cleanup the temporary files.
Here’s how you can implement this:
import os import pandas as pd from docx import Document from datetime import datetime import shutil def populate_docx_template(excel_file, template_file, temp_dir): """ 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 temp_dir (str): Directory where the temporary documents will be stored """ # 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 template) if len(doc.tables) > 0: table = doc.tables[0] # 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 table.cell(3, 0).text = client_name # Matter description matter_text = f"{matter_overview}\n\n{description}" table.cell(5, 0).text = matter_text # Deal value table.cell(6, 1).text = str(value) # Lead partner(s) table.cell(9, 0).text = lead_partners table.cell(10, 0).text = lead_partners # Start date current_date = datetime.now().strftime("%d-%m-%Y") table.cell(16, 3).text = current_date # Create a temporary filename clean_client_name = ''.join(c for c in client_name if c.isalnum() or c in [' ', '_']).replace(' ', '_') temp_filename = os.path.join(temp_dir, f"{clean_client_name}.docx") # Save the populated document to the temporary directory doc.save(temp_filename) print(f"Created temporary document: {temp_filename}") def combine_documents(temp_dir, output_file): """ Combine all temporary documents into a single Word document. Args: temp_dir (str): Directory where temporary documents are stored output_file (str): Path for the combined output document """ # Create a new Document combined_doc = Document() # Loop through each file in the temporary directory and append it to the combined document for filename in os.listdir(temp_dir): if filename.endswith('.docx'): file_path = os.path.join(temp_dir, filename) sub_doc = Document(file_path) # Append the content of the sub-document for element in sub_doc.element.body: combined_doc.element.body.append(element) # Save the combined document combined_doc.save(output_file) print(f"Combined document created: {output_file}") 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 temp_dir = r'c:\Users\Administrator\Documents\temp_docs' # Temporary directory for storing documents output_file = r'c:\Users\Administrator\Desktop\combined_output.docx' # Output filename for combined document # Create temporary directory if it doesn't exist os.makedirs(temp_dir, exist_ok=True) try: # Populate the template with Excel data populate_docx_template(excel_file, template_file, temp_dir) # Combine all temporary documents into one combine_documents(temp_dir, output_file) # Optionally clean up the temporary directory shutil.rmtree(temp_dir) print(f"Document creation and combination completed successfully.") except Exception as e: print(f"Error: {e}") if __name__ == "__main__": main()Note:
- Temporary Directory Management: The script will create a temporary directory to store individual documents. It will be deleted after combining the files to keep the environment clean.
- Document Combining: The
combine_documentsfunction reads each individual document and merges their contents into one final Word document. - File Handling: Ensure that paths are accessible and that the program has necessary permissions for reading/writing files. Adjust paths for your system as needed.
- Efficiency: This method might not be the most efficient for huge documents or a large quantity of files, as the merging process involves copying nodes from each individual document. Adjust as necessary based on your actual requirements.
- User
Created temporary document: c:\Users\Administrator\Documents\temp_docs\Delhi_International_Airport_Limited_DIAL.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Mumbai_International_Airport_Limited_MIAL.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Adani_Vizhinjam_Port_Pvt_Ltd.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Jindal_ITF_Limited.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Parsa_Kente_Collieries_Limited_PKCL.docx
Created temporary document: c:\Users\Administrator\Documents\temp_docs\JITF_Urban_Infrastructure_Limited_JUIL.docx
Created temporary document: c:\Users\Administrator\Documents\temp_docs\Adani_Enterprises_Limited.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Palm_View_Investments_Overseas_Private_Limited.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Shristi_Infrastructure_and_Development_Corporation_Limited.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\ESOPP.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Adani_Ports_and_Special_Economic_Zone_Limited.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Adani_Ennore_Container_Terminal_Pvt_Ltd.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\PNC_Infratech_Limited.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Talabira_Odisha_Mining_Private_Limited.docx
ts\temp_docs\GMR_Hyderabad_Vijayawada_Expressways_Pvt_Ltd.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\Vikram_Solar_Limited.docx Created temporary document: c:\Users\Administrator\Documents\temp_docs\IMR_Metallurgical_Resources_AG_.docx Error: 'float' object is not iterable PS C:\Users\Administrator\Desktop\Bd team>on desktop i dont recive any output docx
- Assistant
The error you are encountering (
'float' object is not iterable) typically occurs when you're trying to iterate over an object that isn't an iterable type like a list, tuple, or string. This can happen if any of the data being fetched from the Excel file containsNaNvalues (which is often the case with pandas when it reads empty cells or non-numeric values in numeric columns).In your code, it's possible that in some rows, one of the values (like
valueorlead_partners) may not be a string or could beNaN, which would lead to this error when you attempt to use it in a context that requires iteration or string interpolation.Steps to Resolve:
-
Check Dataframe Values: Ensure that you're properly handling cases when values are
NaN. You can provide default values or convert them to strings. -
Debugging: Print the row data or the specific value causing issues before it leads to an error, to help identify problem rows.
-
Modify the Code to Handle Faulty or Missing Values: You can update the
populate_docx_templatefunction to check forNaNvalues usingpd.isna()and assign a default value such as an empty string if needed.
Here’s a modified version of your function that includes these adjustments:
import os import pandas as pd from docx import Document from datetime import datetime import shutil def populate_docx_template(excel_file, template_file, temp_dir): """ 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 temp_dir (str): Directory where the temporary documents will be stored """ # 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 with NaN handling client_name = row.get('Client Name ', '') if pd.notna(row.get('Client Name ', '')) else '' matter_overview = row.get('Matter/Case Overview', '') if pd.notna(row.get('Matter/Case Overview', '')) else '' description = row.get('Description (in 500 words)', '') if pd.notna(row.get('Description (in 500 words)', '')) else '' value = row.get('Value ', '') if pd.notna(row.get('Value ', '')) else '' role = row.get('Role of S&A', '') if pd.notna(row.get('Role of S&A', '')) else '' significance = row.get('The significance, impact and outcome of the case', '') if pd.notna(row.get('The significance, impact and outcome of the case', '')) else '' status = row.get('Current Status (ongoing / completed )', '') if pd.notna(row.get('Current Status (ongoing / completed )', '')) else '' lead_partners = row.get('Lead partners', '') if pd.notna(row.get('Lead partners', '')) else '' team_members = row.get('Team Members', '') if pd.notna(row.get('Team Members', '')) else '' other_firms = row.get('Other Firms/counsels Involved', '') if pd.notna(row.get('Other Firms/counsels Involved', '')) else '' publishable = row.get('Confidential / Publishable', '') if pd.notna(row.get('Confidential / Publishable', '')) else '' # Check if the document was created successfully if len(doc.tables) > 0: table = doc.tables[0] # 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 table.cell(3, 0).text = client_name # Matter description matter_text = f"{matter_overview}\n\n{description}" table.cell(5, 0).text = matter_text # Deal value table.cell(6, 1).text = str(value) # Lead partner(s) table.cell(9, 0).text = lead_partners table.cell(10, 0).text = lead_partners # Start date current_date = datetime.now().strftime("%d-%m-%Y") table.cell(16, 3).text = current_date # Create a temporary filename clean_client_name = ''.join(c for c in client_name if c.isalnum() or c in [' ', '_']).replace(' ', '_') temp_filename = os.path.join(temp_dir, f"{clean_client_name}.docx") # Save the populated document to the temporary directory doc.save(temp_filename) print(f"Created temporary document: {temp_filename}") def combine_documents(temp_dir, output_file): """ Combine all temporary documents into a single Word document. Args: temp_dir (str): Directory where temporary documents are stored output_file (str): Path for the combined output document """ # Create a new Document combined_doc = Document() # Loop through each file in the temporary directory and append it to the combined document for filename in os.listdir(temp_dir): if filename.endswith('.docx'): file_path = os.path.join(temp_dir, filename) sub_doc = Document(file_path) # Append the content of the sub-document for element in sub_doc.element.body: combined_doc.element.body.append(element) # Save the combined document combined_doc.save(output_file) print(f"Combined document created: {output_file}") 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 temp_dir = r'c:\Users\Administrator\Documents\temp_docs' # Temporary directory for storing documents output_file = r'c:\Users\Administrator\Desktop\combined_output.docx' # Output filename for combined document # Create temporary directory if it doesn't exist os.makedirs(temp_dir, exist_ok=True) try: # Populate the template with Excel data populate_docx_template(excel_file, template_file, temp_dir) # Combine all temporary documents into one combine_documents(temp_dir, output_file) # Optionally clean up the temporary directory shutil.rmtree(temp_dir) print(f"Document creation and combination completed successfully.") except Exception as e: print(f"Error: {e}") if __name__ == "__main__": main()Key Changes:
- NaN Handling: Each value extracted from the DataFrame is checked for
NaN, and if it's found, it is replaced with an empty string. - Reduction of Errors: This change should help to avoid the float iteration error you were seeing.
Make sure to run this updated code and check if it resolves the issue. The output should now create the combined document you expected on the desktop.
-
- User
it is chnging the color of orginal form not same
- Assistant
When you're working with the
python-docxlibrary to create or manipulate Word documents, it's important to recognize that applying content from one document to another might lead to changes in styles, formatting, or attributes, including font color or other styles.Here are steps to ensure that the original formatting of the template is preserved when populating it with data:
Solution Implementation
-
Utilize the Original Template: Ensure that when you're copying data from the template to the new document, you do not overwrite existing formatting.
-
Directly Append Paragraphs: Instead of directly manipulating table cells, you can create new paragraphs with text and then set their properties (like font color) to match the original.
-
Preserve Styles: When appending or modifying content, ensure that you preserve the formatting by applying the same styles that are in your template.
Here's a modification to handle preserving the document's original formatting while also handling the document population:
from docx import Document from docx.shared import RGBColor from datetime import datetime import os import pandas as pd import shutil def populate_docx_template(excel_file, template_file, temp_dir): # 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 and handle NaN values client_name = row.get('Client Name ', '') if pd.notna(row.get('Client Name ', '')) else '' matter_overview = row.get('Matter/Case Overview', '') if pd.notna(row.get('Matter/Case Overview', '')) else '' description = row.get('Description (in 500 words)', '') if pd.notna(row.get('Description (in 500 words)', '')) else '' value = row.get('Value ', '') if pd.notna(row.get('Value ', '')) else '' role = row.get('Role of S&A', '') if pd.notna(row.get('Role of S&A', '')) else '' significance = row.get('The significance, impact and outcome of the case', '') if pd.notna(row.get('The significance, impact and outcome of the case', '')) else '' status = row.get('Current Status (ongoing / completed)', '') if pd.notna(row.get('Current Status (ongoing / completed)', '')) else '' lead_partners = row.get('Lead partners', '') if pd.notna(row.get('Lead partners', '')) else '' team_members = row.get('Team Members', '') if pd.notna(row.get('Team Members', '')) else '' other_firms = row.get('Other Firms/counsels Involved', '') if pd.notna(row.get('Other Firms/counsels Involved', '')) else '' publishable = row.get('Confidential / Publishable', '') if pd.notna(row.get('Confidential / Publishable', '')) else '' # Check for tables in the document if len(doc.tables) > 0: table = doc.tables[0] # Publishable matter table.cell(0, 1).text = "Publishable" if publishable.lower() == "publishable" else "Confidential" # Client Name table.cell(3, 0).text = client_name # Matter description and ensure line breaks are appropriately handled matter_text = f"{matter_overview}\n\n{description}" table.cell(5, 0).text = matter_text # Deal value table.cell(6, 1).text = str(value) # Lead partner(s) table.cell(9, 0).text = lead_partners table.cell(10, 0).text = lead_partners # Start date current_date = datetime.now().strftime("%d-%m-%Y") table.cell(16, 3).text = current_date # Preserve styles: This assumes your template has already specified styles in the cells. # If you want to explicitly set color or style, assign styles here. # Create a temporary filename clean_client_name = ''.join(c for c in client_name if c.isalnum() or c in [' ', '_']).replace(' ', '_') temp_filename = os.path.join(temp_dir, f"{clean_client_name}.docx") # Save the populated document to the temporary directory doc.save(temp_filename) def combine_documents(temp_dir, output_file): combined_doc = Document() for filename in os.listdir(temp_dir): if filename.endswith('.docx'): file_path = os.path.join(temp_dir, filename) sub_doc = Document(file_path) # Append the content of the sub-document for element in sub_doc.element.body: combined_doc.element.body.append(element) combined_doc.save(output_file) def main(): excel_file = r'c:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx' template_file = r'c:\Users\Administrator\Desktop\legal500.docx' temp_dir = r'c:\Users\Administrator\Documents\temp_docs' output_file = r'c:\Users\Administrator\Desktop\combined_output.docx' os.makedirs(temp_dir, exist_ok=True) try: populate_docx_template(excel_file, template_file, temp_dir) combine_documents(temp_dir, output_file) shutil.rmtree(temp_dir) except Exception as e: print(f"Error: {e}") if __name__ == "__main__": main()Tips for Preserving Formatting:
-
Direct Formatting: If certain parts of your text need explicit styling (like font colors), you'll need to set those properties directly for the relevant paragraphs or table cells.
-
Matching Styles: You can also reference styles directly from your template document to apply to the new content if you have named styles in your document.
-
Review Template Styling: Ensure that the template you are using has been set up correctly with the desired styles so that they apply when copied to another document.
-
Testing Style Attributes: If you're experiencing issues with specific styles (like color changes), you'll want to check those attributes specifically (like
font.color.rgb).
Example for Setting Color:
If you want to set a specific color to a paragraph, you can do it like this:
run = table.cell(3, 0).add_paragraph().add_run(client_name) run.font.color.rgb = RGBColor(0, 0, 0) # Black colorRemember to adjust any specific colors and formatting based on the needs of your document.
-
- User
no jsut retain the color and styling of orginal form
- Assistant
To ensure that you retain the original formatting and styling of the document when populating it with data from Excel using
python-docx, make sure to manipulate the document in such a way that you do not overwrite the existing styles or formatting from the template. The goal is to insert the data into the document while maintaining its original attributes, including font colors, styles, and sizes.Here’s an updated version of the
populate_docx_templatefunction where you manipulate the cells directly without altering their formatting:Updated Code to Preserve Formatting
import os import pandas as pd from docx import Document from datetime import datetime import shutil def populate_docx_template(excel_file, template_file, temp_dir): # 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 the Excel row and handle NaN values client_name = row.get('Client Name ', '') if pd.notna(row.get('Client Name ', '')) else '' matter_overview = row.get('Matter/Case Overview', '') if pd.notna(row.get('Matter/Case Overview', '')) else '' description = row.get('Description (in 500 words)', '') if pd.notna(row.get('Description (in 500 words)', '')) else '' value = row.get('Value ', '') if pd.notna(row.get('Value ', '')) else '' lead_partners = row.get('Lead partners', '') if pd.notna(row.get('Lead partners', '')) else '' publishable = row.get('Confidential / Publishable', '') if pd.notna(row.get('Confidential / Publishable', '')) else '' if len(doc.tables) > 0: table = doc.tables[0] # Publishable matter cell = table.cell(0, 1) cell.text = "Publishable" if publishable.lower() == "publishable" else "Confidential" # Client Name table.cell(3, 0).text = client_name # Matter description matter_text = f"{matter_overview}\n\n{description}" table.cell(5, 0).text = matter_text # Deal value table.cell(6, 1).text = str(value) # Lead partner(s) table.cell(9, 0).text = lead_partners table.cell(10, 0).text = lead_partners # Current date current_date = datetime.now().strftime("%d-%m-%Y") table.cell(16, 3).text = current_date # Create a temporary filename clean_client_name = ''.join(c for c in client_name if c.isalnum() or c in [' ', '_']).replace(' ', '_') temp_filename = os.path.join(temp_dir, f"{clean_client_name}.docx") # Save the populated document to the temporary directory doc.save(temp_filename) print(f"Created temporary document: {temp_filename}") def combine_documents(temp_dir, output_file): combined_doc = Document() for filename in os.listdir(temp_dir): if filename.endswith('.docx'): file_path = os.path.join(temp_dir, filename) sub_doc = Document(file_path) # Loop through each element in the sub-doc and copy it to the combined document for element in sub_doc.element.body: combined_doc.element.body.append(element) combined_doc.save(output_file) print(f"Combined document created: {output_file}") def main(): excel_file = r'c:\Users\Administrator\Documents\Submissions- Arbitration (version 1).xlsb.xlsx' template_file = r'c:\Users\Administrator\Desktop\legal500.docx' temp_dir = r'c:\Users\Administrator\Documents\temp_docs' output_file = r'c:\Users\Administrator\Desktop\combined_output.docx' os.makedirs(temp_dir, exist_ok=True) try: populate_docx_template(excel_file, template_file, temp_dir) combine_documents(temp_dir, output_file) shutil.rmtree(temp_dir) except Exception as e: print(f"Error: {e}") if __name__ == "__main__": main()Key Points:
-
Table Cell Manipulation: The cells in the tables are simply set to the desired text. This keeps any original formatting in those cells intact.
-
Retaining Formatting: By using the
textattribute directly on the cells, the original cell formatting remains. This means that any colors, styles, or formatting already defined in the template are preserved. -
Element Copying: When combining documents, the elements are appended from one document to another without altering their formatting.
Conclusion:
This approach should effectively retain the original styles and formatting of your Word document template while populating it with data from the Excel file. If you find that some specific styles or formatting do change, ensure that the formatting in your original template is set up correctly.
-