#!/usr/bin/env python3 """ Link Excel Action Items to Word Documents This script allows you to reference Action No from an Excel spreadsheet and insert the whole row or selected fields into a Word document. Usage: python link_excel_to_word.py --excel actions.xlsx --word minutes.docx --action-no 003 python link_excel_to_word.py --excel actions.xlsx --word minutes.docx --action-no 003 --fields "Action Short Title,Who,Status,Priority" """ import argparse import sys from pathlib import Path from openpyxl import load_workbook from docx import Document from docx.shared import Pt, Inches from docx.enum.text import WD_PARAGRAPH_ALIGNMENT class ActionItemLinker: """Links Excel action items to Word documents.""" def __init__(self, excel_path, word_path=None): """Initialize with paths to Excel and Word files.""" self.excel_path = Path(excel_path) self.word_path = Path(word_path) if word_path else None self.workbook = None self.worksheet = None self.headers = [] self.header_row = None def load_excel(self): """Load Excel workbook and find header row.""" if not self.excel_path.exists(): raise FileNotFoundError(f"Excel file not found: {self.excel_path}") self.workbook = load_workbook(self.excel_path, data_only=True) self.worksheet = self.workbook.active # Find header row (look for "Action N" or "Action No") self.header_row = self._find_header_row() if self.header_row is None: raise ValueError("Could not find header row with 'Action N' or 'Action No' column") # Extract headers self.headers = [str(cell.value).strip() if cell.value else "" for cell in self.worksheet[self.header_row]] print(f"Found headers in row {self.header_row}: {self.headers}") def _find_header_row(self): """Find the row containing headers (looking for Action N column).""" for row_idx in range(1, min(10, self.worksheet.max_row + 1)): row = self.worksheet[row_idx] for cell in row: if cell.value and isinstance(cell.value, str): value = cell.value.strip().lower() if 'action n' in value or 'action no' in value: return row_idx return None def find_action_by_no(self, action_no): """Find row by Action No.""" if self.header_row is None: self.load_excel() # Find Action N column index action_n_idx = None for idx, header in enumerate(self.headers): if header and ('action n' in header.lower() or 'action no' in header.lower()): action_n_idx = idx break if action_n_idx is None: raise ValueError("Could not find 'Action N' column in Excel file") # Search for the action number action_no_str = str(action_no).strip() for row_idx in range(self.header_row + 1, self.worksheet.max_row + 1): cell = self.worksheet.cell(row=row_idx, column=action_n_idx + 1) cell_value = str(cell.value).strip() if cell.value else "" # Try to match with or without leading zeros if cell_value == action_no_str or cell_value.zfill(3) == action_no_str.zfill(3): return self._extract_row_data(row_idx) raise ValueError(f"Action No '{action_no}' not found in Excel file") def _extract_row_data(self, row_idx): """Extract all data from a row as a dictionary.""" row_data = {} row = self.worksheet[row_idx] for idx, header in enumerate(self.headers): if idx < len(row): cell = row[idx] value = cell.value # Handle different cell types if value is None: value = "" elif isinstance(value, (int, float)): value = str(value) else: value = str(value).strip() row_data[header] = value return row_data def insert_into_word(self, action_data, fields=None, placeholder="<>"): """Insert action data into Word document.""" if self.word_path is None: raise ValueError("Word document path not specified") if not self.word_path.exists(): # Create new document doc = Document() doc.add_heading('Action Items', 0) doc.add_paragraph(f"Action No: {placeholder}") else: doc = Document(str(self.word_path)) # Find placeholder and replace with action data replaced = False if fields is None: # Insert all fields fields = list(action_data.keys()) for paragraph in doc.paragraphs: if placeholder in paragraph.text: # Replace placeholder with formatted action data new_text = paragraph.text.replace(placeholder, self._format_action_data(action_data, fields)) paragraph.clear() paragraph.add_run(new_text) replaced = True break if not replaced: # Add action data at the end of document doc.add_paragraph() doc.add_paragraph(self._format_action_data(action_data, fields)) return doc def _format_action_data(self, action_data, fields=None): """Format action data for display.""" if fields is None: fields = list(action_data.keys()) formatted_parts = [] for field in fields: if field in action_data: value = action_data[field] if value: # Only include non-empty values formatted_parts.append(f"{field}: {value}") if not formatted_parts: return "No data available for selected fields." return " | ".join(formatted_parts) def format_as_table(self, action_data, fields=None): """Format action data as a Word table.""" if fields is None: fields = list(action_data.keys()) # Filter to only include fields with values filtered_fields = [f for f in fields if f in action_data and action_data[f]] if not filtered_fields: return None # Create table with 2 columns (Field | Value) table_data = [] for field in filtered_fields: table_data.append([field, action_data[field]]) return table_data def insert_table_into_word(self, action_data, fields=None, placeholder="<>"): """Insert action data as a formatted table in Word document.""" if self.word_path is None: raise ValueError("Word document path not specified") table_data = self.format_as_table(action_data, fields) if table_data is None: return None if not self.word_path.exists(): doc = Document() doc.add_heading('Action Item Details', 0) else: doc = Document(str(self.word_path)) # Find and replace placeholder replaced = False for paragraph in doc.paragraphs: if placeholder in paragraph.text: # Remove placeholder text new_text = paragraph.text.replace(placeholder, "") paragraph.clear() if new_text.strip(): paragraph.add_run(new_text) # Insert table after this paragraph table = doc.add_table(rows=len(table_data), cols=2) table.style = 'Light Grid Accent 1' for i, (field, value) in enumerate(table_data): table.rows[i].cells[0].text = field table.rows[i].cells[1].text = str(value) # Make header row bold if i == 0: for cell in table.rows[i].cells: for paragraph in cell.paragraphs: for run in paragraph.runs: run.bold = True replaced = True break if not replaced: # Add table at the end doc.add_paragraph() doc.add_paragraph(f"Action No: {action_data.get('Action N', 'N/A')}") table = doc.add_table(rows=len(table_data), cols=2) table.style = 'Light Grid Accent 1' for i, (field, value) in enumerate(table_data): table.rows[i].cells[0].text = field table.rows[i].cells[1].text = str(value) return doc def main(): """Main function to run the script.""" parser = argparse.ArgumentParser( description='Link Excel action items to Word documents', formatter_class=argparse.RawDescriptionHelpFormatter, epilog=""" Examples: # Insert full row for Action No 003 python link_excel_to_word.py --excel actions.xlsx --word minutes.docx --action-no 003 # Insert only selected fields python link_excel_to_word.py --excel actions.xlsx --word minutes.docx --action-no 003 \\ --fields "Action Short Title,Who,Status,Priority" # Insert as formatted table python link_excel_to_word.py --excel actions.xlsx --word minutes.docx --action-no 003 \\ --table --fields "Action Short Title,Who,Status,Priority" # Just print the action data (don't modify Word) python link_excel_to_word.py --excel actions.xlsx --action-no 003 """ ) parser.add_argument('--excel', '-e', required=True, help='Path to Excel file (.xlsx)') parser.add_argument('--word', '-w', help='Path to Word document (.docx)') parser.add_argument('--action-no', '-a', required=True, help='Action number to find (e.g., 003, 004)') parser.add_argument('--fields', '-f', help='Comma-separated list of fields to include (default: all fields)') parser.add_argument('--table', '-t', action='store_true', help='Insert data as a formatted table') parser.add_argument('--placeholder', '-p', default='<>', help='Placeholder text to replace in Word doc (default: <>)') parser.add_argument('--output', '-o', help='Output Word document path (default: overwrites input)') args = parser.parse_args() try: # Initialize linker linker = ActionItemLinker(args.excel, args.word) linker.load_excel() # Find action item print(f"Searching for Action No: {args.action_no}") action_data = linker.find_action_by_no(args.action_no) print(f"\nFound Action Item:") print("=" * 60) for key, value in action_data.items(): if value: print(f"{key}: {value}") print("=" * 60) # Parse fields if specified fields = None if args.fields: fields = [f.strip() for f in args.fields.split(',')] print(f"\nUsing selected fields: {', '.join(fields)}") # If Word document specified, insert data if args.word: if args.table: doc = linker.insert_table_into_word(action_data, fields, args.placeholder) else: doc = linker.insert_into_word(action_data, fields, args.placeholder) # Save document output_path = args.output or args.word doc.save(output_path) print(f"\n✓ Word document updated: {output_path}") print(f" Placeholder '{args.placeholder}' has been replaced with action data.") else: print("\n(No Word document specified - data shown above only)") except FileNotFoundError as e: print(f"Error: {e}", file=sys.stderr) sys.exit(1) except ValueError as e: print(f"Error: {e}", file=sys.stderr) sys.exit(1) except Exception as e: print(f"Unexpected error: {e}", file=sys.stderr) import traceback traceback.print_exc() sys.exit(1) if __name__ == '__main__': main()