Document Parsing: Domain Explainer#

What is Document Parsing?#

Document parsing is the process of reading, interpreting, and extracting structured data from document files. In the context of this research, we focus on Office document formats (Excel, Word, PowerPoint) and PDF files - the most common document types in business and enterprise environments.

Core Concepts#

Parsing vs Generation:

• Parsing (Reading): Extract data from existing documents (user uploads, automated imports)
• Generation (Writing): Create new documents programmatically (reports, exports, templates)

Most production systems need both: parse uploaded data, generate standardized reports.

Structured vs Unstructured:

• Structured: Excel spreadsheets with rows/columns, Word tables, forms with fixed fields
• Unstructured: Free-form Word documents, PDFs with varied layouts, scanned images

Parsing structured data is straightforward; unstructured requires pattern matching, layout analysis, or AI/ML.

Why This Research Matters#

Business Context#

Document processing is universal:

• 80% of enterprise data exists in documents (not databases)
• Common workflows: invoice processing, contract generation, report automation, data imports
• Manual processing: expensive ($25-50/hour), error-prone (5-15% error rate), slow
• Automated parsing: 100x faster, 99%+ accuracy, scales to millions of documents

Cost impact:

• Processing 10k invoices/month manually: $10k-20k/month (20 min each @ $30/hour)
• Automated parsing: $100-500/month (cloud services or server costs)
• ROI: 20-200x depending on volume

Technical Context#

Why not just use Excel/Word directly?

• Can’t automate: Opening Excel.exe is manual, not scriptable
• No server deployment: Office apps require GUI, don’t run on servers
• Licensing costs: Office 365 per-user licenses don’t cover automation
• Cross-platform: Office is Windows-only (mostly)

Python libraries solve this:

• Server-side automation (no GUI needed)
• Free open-source (MIT/BSD licenses)
• Cross-platform (Windows, Linux, macOS)
• Programmable (validation, transformation, integration with databases/APIs)

Domain Fundamentals#

Office Open XML (OOXML) Format Family#

Modern Office formats (.xlsx, .docx, .pptx) are ZIP archives containing XML:

document.docx (unzipped)
├── [Content_Types].xml        # MIME types
├── _rels/                      # Relationships between parts
│   └── .rels
├── word/
│   ├── document.xml           # Main content
│   ├── styles.xml             # Formatting
│   ├── numbering.xml          # Lists
│   ├── media/                 # Images
│   └── _rels/
└── docProps/                   # Metadata (author, date)
    ├── core.xml
    └── app.xml

Key implications:

1. Human-readable: Unzip any .docx file and read the XML
2. Parseable: Standard XML tools work
3. Modifiable: Can edit XML directly (risky but powerful)
4. Larger files: XML is verbose (mitigated by ZIP compression)

Example: Excel cell with formula

<c r="A1">
  <f>SUM(B1:B10)</f>
  <v>150</v>
</c>

• <c r="A1">: Cell at A1
• <f>: Formula
• <v>: Calculated value (last saved)

PDF Format#

PDFs are NOT XML - they use a custom format designed for print fidelity:

%PDF-1.7
1 0 obj
<< /Type /Catalog /Pages 2 0 R >>
endobj

2 0 obj
<< /Type /Pages /Kids [3 0 R] /Count 1 >>
endobj

3 0 obj
<< /Type /Page /MediaBox [0 0 612 792] /Contents 4 0 R >>
endobj

PDF structure:

• Objects: Self-contained units (pages, fonts, images)
• Streams: Compressed binary data
• Cross-reference table: Index to find objects
• Incremental updates: Appended changes (not in-place edits)

Why PDF extraction is hard:

1. No semantic structure: Text is positioned by X/Y coordinates, not paragraphs
2. Tables aren’t tables: Just text at specific positions
3. Fonts can be embedded: May need font files to extract text
4. Complex rendering: Graphics operators for drawing

Common Use Cases#

1. Data Import/ETL#

Scenario: Users upload Excel files with budget data; system validates and loads into database

Challenges:

• Variable formats (users create files manually)
• Validation (check column names, data types, ranges)
• Error reporting (tell user exactly what’s wrong: “Row 15, Column ‘Amount’: must be a number”)

Solution pattern:

1. Validate structure (sheets exist, columns present)
2. Validate data (types, ranges, business rules)
3. Transform (normalize dates, currencies)
4. Load (insert into database with transaction)
5. Report (success count, errors with row numbers)

2. Report Generation#

Scenario: Generate monthly sales reports for 50 regions, each with 10k+ rows, charts, formatting

Challenges:

• Large data volume (500k+ total rows)
• Memory constraints (can’t load everything at once)
• Performance (must complete in <10 minutes)
• Rich formatting (colors, charts, conditional formatting)

Solution pattern:

1. Stream data from database (chunk by region)
2. Write Excel in streaming mode (constant memory)
3. Add formatting progressively (don't store all cells)
4. Generate charts from formulas (not raw data)
5. Save incrementally (flush to disk as you go)

3. Template Population#

Scenario: Generate 100+ contracts from Word template by replacing placeholders with customer data

Challenges:

• Complex formatting (bold, colors, tables)
• Placeholders in multiple locations (body, headers, footers, tables)
• Preserving formatting (can’t just find/replace text)
• Conditional sections (add paragraph only if condition met)

Solution pattern:

1. Load template document
2. Find placeholders at RUN level (not paragraph - preserves formatting)
3. Replace text in each run
4. Add optional sections via document API
5. Save per customer

4. Invoice Data Extraction#

Scenario: Extract vendor, amount, date, line items from PDF invoices (varied layouts)

Challenges:

• No standard format (every vendor different)
• Mix of scanned and digital PDFs
• Tables may span multiple pages
• Currency symbols, date formats vary

Solution pattern:

1. Detect if scanned (no text layer → OCR needed)
2. Extract text from PDF
3. Use regex patterns for key fields (invoice #, total, date)
4. Extract tables with layout analysis
5. Validate extracted data (total matches sum of line items)
6. Return structured JSON

5. Batch Document Conversion#

Scenario: Convert 50k legacy .xls files to modern .xlsx format

Challenges:

• Legacy binary format (complex to parse)
• Volume (must process efficiently)
• Preservation (maintain formatting, formulas)
• Error handling (some files may be corrupted)

Solution pattern:

1. Detect file format (MIME type check)
2. Use LibreOffice CLI for conversion (handles edge cases better than libraries)
3. Parallel processing (10-50 workers)
4. Validate output (compare row counts, spot-check values)
5. Log failures for manual review

Technology Landscape#

Python Libraries (Open Source)#

Excel:

• openpyxl: Read/write .xlsx with full formatting support (MIT license, 8.5k stars)
• xlsxwriter: Write-only, optimized for speed and memory (BSD, 3.5k stars)
• pandas: High-level data manipulation, Excel I/O (BSD, 42k stars)
• xlrd: Legacy .xls reader (BSD, maintenance mode)

Word:

• python-docx: Read/write .docx files (MIT, 4k stars)
• docx2txt: Fast text-only extraction
• python-docx-template: Jinja2 templating for .docx

PowerPoint:

• python-pptx: Read/write .pptx files (MIT, 2.3k stars)

PDF:

• pdfplumber: Advanced extraction with table/layout analysis (MIT, 5.8k stars)
• PyPDF2: Basic operations (split, merge, extract text) (BSD, 7.5k stars)
• PyMuPDF (fitz): Fast text extraction, rendering (AGPL, 4k stars)

Cloud Services (Paid)#

OCR & Form Extraction:

• AWS Textract: Form fields, tables, handwriting ($1.50/1k pages)
• Azure Form Recognizer: Pre-trained for invoices, receipts ($1.00/1k pages)
• Google Document AI: General documents, custom models ($1.50/1k pages)

When to use cloud:

• Scanned documents (require OCR)
• Handwritten forms
• Complex layouts (multi-column, tables across pages)
• High accuracy needed (>95%)

Commercial Libraries#

Windows-only:

• win32com (pywin32): Automate Excel.exe via COM (requires Office installed)
• xlwings: Similar to win32com but nicer API

Cross-platform (paid):

• Aspose.Cells/Words: Full Office feature support ($999-2999/year)
• Syncfusion: Excel/Word/PDF libraries ($995-2995/year)

Trade-offs:

• Python libraries: Free, but limited features (no VBA macros, some formatting unsupported)
• Cloud services: Pay per use, great for OCR, but vendor lock-in
• Commercial: Full features, but expensive, licensing per developer/server

Key Technical Challenges#

1. Memory Management#

Problem: Excel files can be huge (1M rows × 50 columns = 50M cells)

Memory consumption:

• openpyxl standard mode: ~2 KB per cell with formatting = 100 GB (impossible)
• Reality: Shared strings reduce to ~200 MB for typical data
• Still problematic: Running out of memory on large files

Solutions:

• Streaming read (read_only=True): Process row-by-row, ~50 MB constant memory
• Streaming write (constant_memory=True): Write directly to file, ~30 MB
• Chunking: Process 10k rows at a time with pandas
• Format conversion: Convert Excel → Parquet for repeated access (5x faster, 50% less memory)

2. Security#

Attack vectors:

Formula injection:

Cell A1: =cmd|'/c calc.exe'!A1

When exported to CSV and opened in Excel, executes calculator. Mitigation: Prefix dangerous characters with '.

ZIP bomb:

# Malicious .xlsx that expands to 10 GB in memory
compressed_size: 1 MB
uncompressed_size: 10 GB
ratio: 10,000:1  # Suspicious!

Mitigation: Check ratio before loading.

XXE (XML External Entity):

<!DOCTYPE foo [
  <!ENTITY xxe SYSTEM "file:///etc/passwd">
]>
<w:document>
  <w:t>&xxe;</w:t>
</w:document>

Reads local files. Mitigation: Python’s xml.etree disables this by default (since 3.7.1).

PDF JavaScript: PDFs can execute JavaScript, potentially malicious. Mitigation: Use sandboxed environment, disable JavaScript rendering.

3. Format Variations#

Problem: Same format, different interpretations

Excel dates:

• Stored as float (days since 1900-01-01)
• But: 1900 was not a leap year, Excel thinks it was (bug)
• Different date systems: 1900 vs 1904 (Mac)

Word paragraph splits:

• Word may split “{{PLACEHOLDER}}” across multiple runs (invisible to user)
• Simple find/replace breaks formatting
• Need run-level replacement logic

PDF encodings:

• Text may be in custom encodings (not UTF-8)
• Fonts can be subsetted (only used characters)
• Need font file to extract full text

4. Performance at Scale#

Problem: Processing 100k documents takes too long

Approaches:

Sequential (baseline):

100k docs × 5 sec each = 500k seconds = 139 hours (5.8 days)

Parallel (10 workers):

100k docs / 10 workers = 10k each × 5 sec = 50k sec = 14 hours

Distributed (50 workers, cloud auto-scaling):

100k docs / 50 workers = 2k each × 5 sec = 10k sec = 2.8 hours

Optimization techniques:

1. Batch processing: Process multiple files per worker (reduce overhead)
2. Format conversion: Excel → Parquet (5x faster subsequent reads)
3. Caching: Hash-based cache for repeated files
4. Early rejection: Validate structure before full processing
5. Async I/O: Don’t wait for file writes

Decision Framework#

When to Use Python Libraries#

Choose open-source libraries when:

• ✅ Modern formats (.xlsx, .docx, .pptx)
• ✅ Digital documents (not scanned)
• ✅ On-premise deployment (no cloud allowed)
• ✅ Low-medium volume (<100k/month)
• ✅ Full control needed (custom validation, transformation)
• ✅ Cost-sensitive (free vs cloud fees)

When to Use Cloud Services#

Choose cloud services when:

• ✅ Scanned documents (require OCR)
• ✅ Handwritten text
• ✅ Complex layouts (tables across pages, multi-column)
• ✅ High volume (>100k/month) - cloud scales elastically
• ✅ Multiple languages (pre-trained models)
• ✅ Faster time to market (no ML training)

When to Use Commercial Libraries#

Choose commercial when:

• ✅ Need 100% Office compatibility (VBA macros, pivot tables, SmartArt)
• ✅ Budget for licensing ($1k-10k/year)
• ✅ Business-critical (need vendor support)
• ✅ Legacy formats required (.doc, .ppt pre-2007)

Success Patterns#

1. Validate Early#

Pattern:

1. Check structure (files exist, sheets present, columns correct)
2. If structure invalid → reject immediately (don't waste time on data)
3. Check data (types, ranges, business rules)
4. If data invalid → return detailed errors (row/column location)
5. Process data (transform, load)

Why: Failing fast saves compute and gives better user feedback.

2. Stream Large Files#

Pattern:

# BAD: Load entire file
df = pd.read_excel('huge.xlsx')  # 500 MB memory
process(df)

# GOOD: Stream chunks
for chunk in pd.read_excel('huge.xlsx', chunksize=10000):  # 50 MB memory
    process(chunk)

Why: Prevents out-of-memory errors, scales to arbitrary file sizes.

3. Normalize Formats Early#

Pattern:

# Convert all inputs to DataFrame immediately
if file.endswith('.xlsx'):
    df = pd.read_excel(file)
elif file.endswith('.csv'):
    df = pd.read_csv(file)
elif file.endswith('.json'):
    df = pd.read_json(file)

# Rest of application works on DataFrame only
total = df['amount'].sum()

Why: Application logic is format-independent, easy to add new formats.

4. Centralize Common Logic#

Pattern:

# Single processing service used by all apps
class DocumentService:
    def process(self, file):
        self.validate_security(file)  # Shared security checks
        self.detect_format(file)      # Shared format detection
        self.extract_data(file)       # Format-specific extraction
        self.audit_log(file)          # Shared audit trail

Why: DRY (don’t repeat yourself), consistent security, easier to maintain.

5. Test with Real Data#

Pattern:

# Include actual user uploads in test suite
def test_invoice_extraction():
    # Real invoice from vendor A (complex layout)
    result = extract_invoice('tests/fixtures/vendor_a_invoice.pdf')
    assert result['total'] == Decimal('1250.00')

    # Real invoice from vendor B (different layout)
    result = extract_invoice('tests/fixtures/vendor_b_invoice.pdf')
    assert result['total'] == Decimal('850.50')

Why: Catches edge cases that synthetic test data misses.

Maturity Assessment#

Library Maturity#

Mature (Production-Ready):

• openpyxl, pandas, python-docx, python-pptx
• 5+ years active development
• 1k+ GitHub stars
• Used by Fortune 500 companies
• Breaking changes rare

Stable (Good for Most Uses):

• pdfplumber, PyPDF2
• 3+ years active
• Some edge cases remain
• Active maintenance

Emerging (Use with Caution):

• AI/ML-based parsing (GPT-4, Claude for documents)
• Experimental (2025)
• Accuracy improving rapidly
• Not production-ready yet (but watch this space)

Format Maturity#

Stable (Unlikely to Change):

• Office Open XML (.xlsx, .docx, .pptx)
• ISO standard (ISO/IEC 29500)
• Supported by Microsoft, LibreOffice, Google Docs
• Safe to build on

Legacy (Deprecate by 2027):

• Binary Office formats (.xls, .doc, .ppt)
• Pre-2007, complex proprietary format
• Microsoft no longer developing
• Plan migration path

PDF (Special Case):

• Format stable, but interpretation varies
• PDF 1.7 is ISO standard
• New features (PDF 2.0) not widely supported
• Extraction remains hard (layout-based, not semantic)

5-Year Outlook#

Predictions (2025-2030):

1. LLMs improve document parsing

   • GPT-4/Claude can already extract structured data from documents
   • By 2027: Production-ready for invoices, forms, contracts
   • By 2030: Better than rule-based parsing for most use cases

2. Cloud services dominate OCR

   • Accuracy: 95% (2025) → 99%+ (2027)
   • Cost: $1.50/1k pages (2025) → $0.50/1k pages (2030)
   • Speed: 1-2 seconds/page → <0.5 seconds/page

3. Legacy format support fades

   • .xls/.doc support dropped by major libraries (2027-2028)
   • Microsoft ends .xls support in Office 365
   • Migrate now or pay for legacy converter services

4. WebAssembly enables browser-side parsing

   • Python libraries compiled to WASM
   • Parse documents client-side (privacy, speed)
   • But: Large file support still limited

5. Office formats remain dominant

   • Google Sheets adoption grows, but enterprises still use Excel
   • OOXML remains standard (no major changes expected)
   • Parsing libraries mature further (100% feature parity with Office)

Strategic recommendations:

• Invest in cloud integrations (OCR/AI services)
• Monitor LLM parsing (experimental → production by 2027)
• Deprecate legacy formats by 2027
• Build abstraction layers (don’t couple to specific libraries)
• Stay current (update libraries quarterly)

Summary#

Document parsing is a high-ROI automation opportunity (20-200x cost savings) for any business processing documents at scale.

Key takeaways:

1. Modern formats are parseable: OOXML is XML, Python libraries work well
2. Security matters: Validate structure, sanitize inputs, limit file sizes
3. Stream large files: Don’t load everything into memory
4. Start with libraries: Free, flexible, good for 80% of use cases
5. Add cloud for OCR: When scanned documents appear
6. Plan for scale: Async processing, distributed workers for >10k docs/day

This research provides:

• S1: Library recommendations (openpyxl, python-docx, pdfplumber)
• S2: Technical deep dive (formats, performance, security)
• S3: Production use cases (invoices, reports, templates, validation)
• S4: Architecture patterns (centralized service, async processing, governance)

Target audience: Developers building document automation, technical leads making technology decisions, architects designing scalable systems.

Domain: Document Processing / Office Automation Technologies: Python, Excel, Word, PowerPoint, PDF, Cloud OCR Methodology: 4PS (Four-Phase Survey) Date: 2026-02-04 Research ID: 1.102

S1 RAPID DISCOVERY: Python Document Parsing Libraries (Office Formats)#

Executive Summary#

TLDR: Use openpyxl for Excel, python-docx for Word, and python-pptx for PowerPoint. For data-heavy Excel work, pandas provides a higher-level API. All are MIT-licensed, actively maintained, and production-ready.

Top 8 Document Parsing Libraries (2025)#

1. 🏆 openpyxl - Excel (.xlsx) Champion#

• Format: Excel 2010+ (.xlsx, .xlsm)
• Capability: Read/write with full formatting, formulas, charts, images
• License: MIT
• Performance: ~1000 rows/sec read, ~500 rows/sec write
• Use When: Need full Excel feature support, formatting matters
• Caveat: Memory-intensive for large files (loads entire workbook)

from openpyxl import load_workbook, Workbook

# Read
wb = load_workbook('data.xlsx')
ws = wb.active
value = ws['A1'].value

# Write
wb = Workbook()
ws = wb.active
ws['A1'] = 'Hello'
ws['A1'].font = Font(bold=True)
wb.save('output.xlsx')

2. 🏆 python-docx - Word (.docx) Standard#

• Format: Word 2007+ (.docx)
• Capability: Read/write paragraphs, tables, styles, images
• License: MIT
• Performance: ~500 paragraphs/sec
• Use When: Creating reports, contracts, templates
• Caveat: No support for legacy .doc format

from docx import Document

# Read
doc = Document('input.docx')
for para in doc.paragraphs:
    print(para.text)

# Write
doc = Document()
doc.add_heading('Report Title', level=1)
doc.add_paragraph('Content here')
doc.save('output.docx')

3. 🏆 python-pptx - PowerPoint (.pptx) Go-To#

• Format: PowerPoint 2007+ (.pptx)
• Capability: Read/write slides, shapes, text, images, charts
• License: MIT
• Performance: ~100 slides/sec
• Use When: Automated slide generation, template population
• Caveat: Complex animations/transitions not fully supported

from pptx import Presentation

# Read
prs = Presentation('input.pptx')
for slide in prs.slides:
    for shape in slide.shapes:
        if hasattr(shape, 'text'):
            print(shape.text)

# Write
prs = Presentation()
slide = prs.slides.add_slide(prs.slide_layouts[1])
slide.shapes.title.text = 'Title'
prs.save('output.pptx')

4. pandas - Excel Data Analysis#

• Format: Excel (.xlsx, .xls), CSV, JSON, SQL
• Capability: High-level data manipulation, Excel I/O as DataFrame
• License: BSD
• Performance: ~10,000 rows/sec read (via openpyxl/xlrd engine)
• Use When: Data analysis, ETL pipelines, numeric processing
• Strength: Built-in data transformation, statistics, aggregation

import pandas as pd

# Read
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df.head())

# Write
df.to_excel('output.xlsx', index=False)

5. xlsxwriter - Fast Excel Writing#

• Format: Excel (.xlsx) - write-only
• Capability: Create Excel files with formatting, formulas, charts
• License: BSD
• Performance: ~2000 rows/sec (2x faster than openpyxl write)
• Use When: Generating large reports, write-only workflows
• Caveat: Cannot read or modify existing files

import xlsxwriter

workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

bold = workbook.add_format({'bold': True})
worksheet.write('A1', 'Header', bold)
worksheet.write('A2', 123)
workbook.close()

6. PyPDF2 - PDF Reading (Basic)#

• Format: PDF (.pdf)
• Capability: Read text, split, merge, rotate pages
• License: BSD
• Performance: ~50 pages/sec
• Use When: Simple PDF text extraction, merging PDFs
• Caveat: Struggles with complex layouts, OCR needed for scanned PDFs

from PyPDF2 import PdfReader

reader = PdfReader('document.pdf')
for page in reader.pages:
    print(page.extract_text())

7. pdfplumber - Advanced PDF Parsing#

• Format: PDF (.pdf)
• Capability: Table extraction, layout analysis, text positioning
• License: MIT
• Performance: ~20 pages/sec (slower but more accurate)
• Use When: Extracting tables from PDFs, preserving layout
• Strength: Best-in-class table detection

import pdfplumber

with pdfplumber.open('document.pdf') as pdf:
    for page in pdf.pages:
        tables = page.extract_tables()
        for table in tables:
            print(table)

8. xlrd - Legacy Excel (.xls) Reader#

• Format: Excel 97-2003 (.xls) - read-only
• License: BSD
• Status: Maintenance mode (xlsx support dropped in 2020)
• Use When: Must read legacy .xls files
• Modern Alternative: Use pandas with xlrd engine for .xls

import xlrd

book = xlrd.open_workbook('legacy.xls')
sheet = book.sheet_by_index(0)
value = sheet.cell_value(0, 0)

Performance Benchmarks (Single-threaded, 2025)#

Excel Reading/Writing#

Document Formats#

Quick Decision Framework#

Excel Files#

✅ Use openpyxl if:

• Need to preserve existing formatting/formulas
• Working with moderate-sized files (<100k rows)
• Need full Excel feature support (charts, images, conditional formatting)
• Modifying existing workbooks

✅ Use xlsxwriter if:

• Generating new reports/exports
• Write-only workflow
• Need maximum write performance
• Large file generation (>100k rows)

✅ Use pandas if:

• Primarily working with tabular data
• Need data analysis/transformation
• Reading CSV/SQL/JSON alongside Excel
• Statistical operations, aggregations

✅ Use xlrd if:

• Must read legacy .xls files (Excel 97-2003)
• Cannot convert to .xlsx format

Word Documents#

✅ Use python-docx if:

• Creating/editing .docx files
• Need styling, tables, headers/footers
• Template population
• Report generation

❌ Avoid if:

• Need legacy .doc format (use docx2txt for reading only)
• Complex SmartArt/diagrams (not supported)

PowerPoint#

✅ Use python-pptx if:

• Automated slide generation
• Bulk updates to presentations
• Template-based slide creation

❌ Avoid if:

• Need legacy .ppt format
• Heavy animations/transitions required

PDFs#

✅ Use PyPDF2 if:

• Simple text extraction
• Splitting/merging PDFs
• Rotating pages
• Low overhead needed

✅ Use pdfplumber if:

• Extracting tables from PDFs
• Need layout preservation
• Complex PDF structure

❌ Avoid PDFs if:

• Need to edit content (use Word/Excel source instead)
• Scanned PDFs (need OCR: tesseract, EasyOCR)

Common Use Cases & Recommendations#

Data ETL Pipeline#

Recommendation: pandas + openpyxl/xlrd

import pandas as pd

# Read Excel with pandas (uses openpyxl for .xlsx)
df = pd.read_excel('input.xlsx', engine='openpyxl')

# Transform
df['new_col'] = df['col1'] * 2

# Write
df.to_excel('output.xlsx', index=False, engine='openpyxl')

Report Generation (Excel)#

Recommendation: xlsxwriter for performance

import xlsxwriter

workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet()

# Add formatting
header_format = workbook.add_format({'bold': True, 'bg_color': '#D3D3D3'})

# Write data with formatting
worksheet.write_row('A1', ['Name', 'Value', 'Date'], header_format)
worksheet.write_column('A2', data)

# Add chart
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': '=Sheet1!$B$2:$B$10'})
worksheet.insert_chart('D2', chart)

workbook.close()

Document Template Population (Word)#

Recommendation: python-docx

from docx import Document

# Load template
doc = Document('template.docx')

# Replace placeholders
for para in doc.paragraphs:
    if '{{name}}' in para.text:
        para.text = para.text.replace('{{name}}', 'John Doe')

# Modify tables
table = doc.tables[0]
table.cell(1, 0).text = 'New Value'

doc.save('filled_template.docx')

PDF Data Extraction#

Recommendation: pdfplumber for tables, PyPDF2 for simple text

import pdfplumber
import pandas as pd

with pdfplumber.open('invoice.pdf') as pdf:
    page = pdf.pages[0]

    # Extract tables
    tables = page.extract_tables()
    df = pd.DataFrame(tables[0][1:], columns=tables[0][0])

    # Extract all text
    text = page.extract_text()

Bulk PowerPoint Updates#

Recommendation: python-pptx

from pptx import Presentation

prs = Presentation('template.pptx')

# Update all slides
for slide in prs.slides:
    for shape in slide.shapes:
        if hasattr(shape, 'text') and '{{company}}' in shape.text:
            shape.text = shape.text.replace('{{company}}', 'Acme Corp')

prs.save('updated.pptx')

2025 Best Practices#

Memory Management#

Large Excel Files (>100k rows):

• Use pandas chunking for reading:

  for chunk in pd.read_excel('large.xlsx', chunksize=10000):
      process(chunk)

• Use openpyxl read_only mode:

  wb = load_workbook('large.xlsx', read_only=True)

• Use xlsxwriter for writing large files (lower memory footprint)

Large PDFs:

• Process page-by-page with pdfplumber to avoid loading entire file:

  with pdfplumber.open('large.pdf') as pdf:
      for i, page in enumerate(pdf.pages):
          process(page)  # Process one page at a time

Error Handling#

Always validate file formats:

from openpyxl.utils.exceptions import InvalidFileException

try:
    wb = load_workbook('file.xlsx')
except InvalidFileException:
    print("Invalid Excel file format")

Handle missing sheets gracefully:

# pandas
try:
    df = pd.read_excel('file.xlsx', sheet_name='NonExistent')
except ValueError:
    df = pd.DataFrame()  # Empty DataFrame fallback

Security Considerations#

Untrusted Excel Files:

• Set data_only=True in openpyxl to disable formula evaluation
• Use read_only=True to prevent macro execution
• Validate cell values before processing:

  wb = load_workbook('untrusted.xlsx', data_only=True, read_only=True)

PDF Parsing:

• PDFs can contain malicious JavaScript - use sandboxed environment
• Limit extraction to specific page ranges to prevent resource exhaustion

Word Documents:

• Disable external content loading
• Sanitize extracted text before display (XSS risk)

Unicode Handling#

All modern libraries (python-docx, openpyxl, python-pptx) handle Unicode correctly:

# Works with CJK, emoji, RTL scripts
doc = Document()
doc.add_paragraph('Hello 世界 🌍')
doc.save('unicode.docx')

Migration Patterns#

Excel: xlrd (.xls) → openpyxl (.xlsx)#

Step 1: Convert files

import xlrd
from openpyxl import Workbook

# Read .xls with xlrd
old_wb = xlrd.open_workbook('legacy.xls')
old_sheet = old_wb.sheet_by_index(0)

# Write to .xlsx with openpyxl
new_wb = Workbook()
new_ws = new_wb.active

for row_idx in range(old_sheet.nrows):
    for col_idx in range(old_sheet.ncols):
        new_ws.cell(row_idx + 1, col_idx + 1).value = \
            old_sheet.cell_value(row_idx, col_idx)

new_wb.save('converted.xlsx')

Step 2: Update code

# OLD: xlrd
import xlrd
book = xlrd.open_workbook('file.xls')
sheet = book.sheet_by_index(0)
value = sheet.cell_value(0, 0)

# NEW: openpyxl (after converting to .xlsx)
from openpyxl import load_workbook
wb = load_workbook('file.xlsx')
ws = wb.active
value = ws['A1'].value

PDF: PyPDF2 → pdfplumber (for table extraction)#

# OLD: PyPDF2 (unreliable table extraction)
from PyPDF2 import PdfReader
reader = PdfReader('document.pdf')
text = reader.pages[0].extract_text()
# Manual parsing of table from text

# NEW: pdfplumber (automatic table detection)
import pdfplumber
with pdfplumber.open('document.pdf') as pdf:
    tables = pdf.pages[0].extract_tables()
    # Returns structured list of lists

Libraries to Avoid in 2025#

❌ Outdated/Deprecated#

• xlwt/xlutils: Legacy .xls writing (use openpyxl/xlsxwriter)
• docx2python: Unmaintained, use python-docx
• pdfrw: Low-level, unmaintained (use PyPDF2 or pdfplumber)
• xlrd for .xlsx: Support dropped in 2020 (use openpyxl or pandas)

❌ Commercial-Only Full Features#

• win32com/pywin32: Windows-only, requires Office installation
• aspose-words/aspose-cells: Paid licenses for full features

Ecosystem Integration#

Combining Libraries#

Excel + pandas workflow:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

# Read with pandas for data manipulation
df = pd.read_excel('input.xlsx')
df['new_col'] = df['col1'] * 2

# Write with pandas
df.to_excel('output.xlsx', index=False)

# Add formatting with openpyxl
wb = load_workbook('output.xlsx')
ws = wb.active
for cell in ws[1]:  # Header row
    cell.font = Font(bold=True)
wb.save('output.xlsx')

Multi-format pipeline:

import pandas as pd
from docx import Document

# Extract data from Excel
df = pd.read_excel('data.xlsx')

# Generate Word report
doc = Document()
doc.add_heading('Data Report', level=1)

# Add table from DataFrame
table = doc.add_table(df.shape[0] + 1, df.shape[1])
# Header
for i, col in enumerate(df.columns):
    table.cell(0, i).text = col
# Data
for i, row in enumerate(df.values):
    for j, val in enumerate(row):
        table.cell(i + 1, j).text = str(val)

doc.save('report.docx')

Final Recommendation#

For 95% of office document workflows:

• Excel: Start with pandas for data work, openpyxl for formatting needs, xlsxwriter for large write-only reports
• Word: Use python-docx (only production-ready option)
• PowerPoint: Use python-pptx (only production-ready option)
• PDF: Use pdfplumber for table extraction, PyPDF2 for simple text/merge operations

All recommended libraries are MIT/BSD licensed, actively maintained, and have strong community support.

Advanced considerations:

• For .doc/.ppt/.xls legacy formats: Consider batch-converting to modern formats with LibreOffice CLI
• For very large Excel files (>1M rows): Consider switching to CSV/Parquet + pandas
• For PDF generation: Consider ReportLab (covered in separate research 1.103)

Date compiled: 2025-12-10 (estimated based on context) Research Focus: Production-ready Python libraries for Office document I/O Next Steps: S2 will cover file format internals, performance optimization, and edge cases

S1 Rapid Discovery: Approach#

Methodology#

This rapid discovery phase surveys the Python ecosystem for document parsing libraries across Office formats (Excel, Word, PowerPoint, PDF). The goal is to identify the top 3-5 libraries per format with clear recommendations for 95% of use cases.

Research Questions#

1. What libraries exist for each format (.xlsx, .docx, .pptx, .pdf)?
2. Which are production-ready (active maintenance, stable APIs, community adoption)?
3. What are the key trade-offs (speed, memory, features, licensing)?
4. When to use each library (decision framework)?

Evaluation Criteria#

For each library, we assess:

Technical:

• Performance: Speed (rows/sec, pages/sec), memory usage
• Features: Read/write capabilities, formatting support, special features
• API quality: Ease of use, documentation, examples

Operational:

• License: MIT/BSD preferred (Apache/GPL noted)
• Maintenance: Last release, update frequency, issue response time
• Community: GitHub stars, usage in production, Stack Overflow presence

Practical:

• Use cases: When to choose this library
• Limitations: Known issues, format restrictions, performance boundaries

Information Sources#

1. GitHub: Stars, forks, issues, last commit
2. PyPI: Download stats, version history
3. Documentation: Official docs, tutorials, API reference
4. Stack Overflow: Question volume, answer quality
5. Real-world usage: Production stories, benchmarks, case studies

Scope#

Included:

• Python 3.7+ libraries (current Python versions)
• Modern formats (.xlsx, .docx, .pptx, .pdf post-2007)
• Cross-platform libraries (Windows, Linux, macOS)

Excluded:

• Legacy formats as primary focus (.xls, .doc) - noted but not recommended
• Windows-only solutions (win32com, xlwings) - mentioned as alternatives
• Commercial libraries (Aspose, Syncfusion) - out of scope for open-source research
• Language-specific parsers (Java POI, C# EPPlus) - Python-only focus

Deliverables#

1. Library profiles: Top 3-5 libraries per format with key characteristics
2. Performance benchmarks: Speed and memory measurements for common operations
3. Decision framework: When to use each library (flowchart/table)
4. Quick recommendations: TLDR for developers who need fast answers
5. Migration guides: Moving from old libraries to recommended ones

Time Budget#

• Excel libraries: 30 minutes (openpyxl, xlsxwriter, pandas, xlrd)
• Word libraries: 20 minutes (python-docx, docx2txt)
• PowerPoint libraries: 15 minutes (python-pptx)
• PDF libraries: 30 minutes (PyPDF2, pdfplumber, PyMuPDF)
• Documentation: 25 minutes (write-up, decision framework, recommendations)

Total: ~2 hours for rapid discovery

Success Criteria#

At the end of S1, developers should be able to:

• ✅ Choose the right library for their use case in <5 minutes
• ✅ Understand trade-offs (speed vs features vs memory)
• ✅ Start coding with recommended library immediately
• ✅ Know when to consider alternatives (edge cases, special requirements)

Library Profile: openpyxl#

Overview#

openpyxl is the primary library for reading and writing Excel 2010+ (.xlsx, .xlsm) files with full formatting support.

Quick Facts:

• License: MIT
• Format: Excel 2010+ (.xlsx, .xlsm)
• Python: 3.7+
• GitHub: 8.5k stars
• Maintainer: openpyxl developers (community-driven)
• Status: Mature, stable (10+ years)

Capabilities#

Reading#

• ✅ Cell values (text, numbers, dates, formulas)
• ✅ Formatting (fonts, colors, borders, alignment)
• ✅ Charts (read-only - can preserve but not create)
• ✅ Images (read embedded images)
• ✅ Merged cells
• ✅ Data validation rules
• ✅ Conditional formatting

Writing#

• ✅ All formatting (fonts, fills, borders, alignment)
• ✅ Formulas (write as text, Excel calculates on open)
• ✅ Charts (basic types: line, bar, pie, scatter)
• ✅ Images (insert PNG, JPG)
• ✅ Data validation
• ✅ Conditional formatting
• ✅ Protection (password-protect sheets)

Not Supported#

• ❌ VBA macros (can preserve in .xlsm but not execute/modify)
• ❌ Pivot tables (can preserve but not create/modify)
• ❌ SmartArt/diagrams
• ❌ Legacy .xls files (use xlrd instead)

Performance#

Benchmarks (100k rows × 10 columns, modern laptop):

Speed: ~1000 rows/sec read, ~500 rows/sec write

Memory:

• Standard: ~2 KB per cell with formatting
• Read-only: ~50% reduction (no style objects)
• Shared strings: Deduplicates repeated text (saves 50-80% for typical data)

Code Examples#

Basic Read/Write#

from openpyxl import load_workbook, Workbook

# Read existing file
wb = load_workbook('data.xlsx')
ws = wb.active

# Read cell value
value = ws['A1'].value
print(f"A1: {value}")

# Read range
for row in ws['A1:C10']:
    for cell in row:
        print(cell.value)

# Write new file
wb = Workbook()
ws = wb.active

ws['A1'] = 'Hello'
ws['B1'] = 123
ws['C1'] = datetime.now()

wb.save('output.xlsx')

Formatting#

from openpyxl.styles import Font, Fill, Border, Side, Alignment

# Font styling
ws['A1'].font = Font(name='Arial', size=14, bold=True, color='FF0000')

# Background color
ws['A1'].fill = Fill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# Borders
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
ws['A1'].border = thin_border

# Alignment
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')

Large File Handling (Read-Only Mode)#

from openpyxl import load_workbook

# Stream large files (constant memory)
wb = load_workbook('large.xlsx', read_only=True)
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    # row is tuple of values (not Cell objects)
    # Memory stays constant at ~50 MB
    process_row(row)

wb.close()

Formula Handling#

# Write formula
ws['D1'] = '=SUM(A1:C1)'

# Read formula vs calculated value
wb = load_workbook('formulas.xlsx')
print(ws['D1'].value)  # '=SUM(A1:C1)' (formula string)

wb = load_workbook('formulas.xlsx', data_only=True)
print(ws['D1'].value)  # 150 (last calculated value)

Use Cases#

✅ Choose openpyxl when:

• Reading/writing .xlsx files with formatting
• Modifying existing workbooks (preserve formatting/formulas)
• Creating reports with styling (bold headers, colored cells)
• File size <100k rows (standard mode)
• Need full Excel feature support

❌ Avoid openpyxl when:

• Writing large files (>100k rows) - use xlsxwriter instead
• Data analysis primary goal - use pandas instead
• Legacy .xls format - use xlrd instead
• Simple CSV-like data - use pandas or csv module

Limitations & Gotchas#

1. Memory Intensive (Standard Mode)#

Problem: Loading large files consumes GB of RAM

# BAD: Standard mode on 1M row file
wb = load_workbook('huge.xlsx')  # 2 GB memory!

Solution: Use read_only mode

wb = load_workbook('huge.xlsx', read_only=True)  # 50 MB memory

2. Formulas Returned as Strings#

Problem: Reading formula cells returns formula text, not calculated value

value = ws['A1'].value  # '=SUM(B1:B10)' not 150

Solution: Use data_only=True to get last calculated values

wb = load_workbook('file.xlsx', data_only=True)
value = ws['A1'].value  # 150

Caveat: Only works if file was saved with calculated values (requires Excel to have opened it)

3. Date Formatting Ambiguity#

Problem: Excel stores dates as floats (days since 1900-01-01). openpyxl must guess if a float is a date.

# Excel cell formatted as date
ws['A1'] = 44927.0  # 2023-01-15 in Excel

# openpyxl reads as...
value = ws['A1'].value  # datetime(2023, 1, 15) OR 44927.0 (depends on format)

Solution: Check cell number format

from openpyxl.utils import is_date_format

if is_date_format(ws['A1'].number_format):
    date = ws['A1'].value  # datetime object
else:
    number = ws['A1'].value  # float

4. Merged Cells Edge Case#

Problem: Only top-left cell of merged range has a value

# Merge A1:C1
ws.merge_cells('A1:C1')
ws['A1'] = 'Header'

print(ws['A1'].value)  # 'Header'
print(ws['B1'].value)  # None (merged cell, no value)

Solution: Check if cell is part of merged range

for merged_range in ws.merged_cells.ranges:
    if 'B1' in merged_range:
        # Get value from top-left cell
        top_left = merged_range.start_cell
        value = ws[top_left].value

Best Practices#

1. Use read_only for large files:

   wb = load_workbook('large.xlsx', read_only=True)

2. Close workbooks explicitly:

   wb = load_workbook('file.xlsx')
   try:
       process(wb)
   finally:
       wb.close()

3. Reuse format objects (don’t create new Font() for every cell):

   header_font = Font(bold=True, size=14)  # Create once
   for cell in ws[1]:  # Apply to all header cells
       cell.font = header_font

4. Use write_only mode for large exports:

   from openpyxl import Workbook
   wb = Workbook(write_only=True)
   ws = wb.create_sheet()
   
   for row_data in large_dataset:
       ws.append(row_data)  # Streaming write
   
   wb.save('output.xlsx')

5. Validate file structure before full load:

   import zipfile
   
   try:
       with zipfile.ZipFile('file.xlsx', 'r') as zf:
           if '[Content_Types].xml' not in zf.namelist():
               raise ValueError("Invalid Excel file")
   except zipfile.BadZipFile:
       raise ValueError("Not a valid .xlsx file")

Alternatives & When to Switch#

Switch to xlsxwriter if:

• Write-only workflow (don’t need to read)
• Generating large reports (>100k rows)
• Speed critical (xlsxwriter 2x faster for writing)

Switch to pandas if:

• Data analysis primary goal (groupby, pivot, statistics)
• Reading CSV/SQL alongside Excel
• Numeric computations

Switch to xlrd if:

• Must read legacy .xls files
• Python 2 compatibility needed (xlrd supports it)

Resources#

• Documentation: https://openpyxl.readthedocs.io/
• GitHub: https://github.com/theorchard/openpyxl
• PyPI: https://pypi.org/project/openpyxl/
• Tutorial: https://realpython.com/openpyxl-excel-spreadsheets-python/

Version Notes#

• Current: 3.1.2 (as of 2025-12)
• Python: 3.7+ required
• Breaking changes: Rare (semantic versioning)
• Deprecated: Python 2 support dropped in 3.0

Verdict#

⭐ Recommended for 90% of Excel automation needs.

Strengths:

• Full formatting support
• Active development
• MIT license
• Large community
• Read/write capable

Weaknesses:

• Memory intensive on large files
• Slower than write-only alternatives
• No VBA/pivot table support

Bottom line: Start with openpyxl for Excel work. Switch to xlsxwriter only if write performance critical.

Library Profile: pdfplumber#

Overview#

pdfplumber is an advanced PDF extraction library with sophisticated table detection and layout analysis.

Quick Facts:

• License: MIT
• Format: PDF (.pdf)
• Python: 3.7+
• GitHub: 5.8k stars
• Maintainer: BuzzFeed News (Jeremy Singer-Vine)
• Status: Mature, actively maintained

Key Differentiator#

Best-in-class table extraction from PDFs. Can detect and parse tables with complex layouts where other libraries fail.

Capabilities#

Extraction#

• ✅ Text with position coordinates
• ✅ Tables (automatic detection + parsing)
• ✅ Layout analysis (columns, text flow)
• ✅ Bounding boxes for all objects
• ✅ Images (metadata, positions)
• ✅ Lines and curves (visual elements)
• ✅ Character-level details (font, size)

Analysis#

• ✅ Detect table structure automatically
• ✅ Filter text by region/coordinates
• ✅ Search for patterns
• ✅ Extract by layout (columns)

Not Supported#

• ❌ PDF creation/editing (read-only)
• ❌ OCR (scanned PDFs need tesseract preprocessing)
• ❌ Form filling
• ❌ Merging/splitting PDFs (use PyPDF2)

Performance#

Benchmarks (100 pages, text-heavy PDF):

Speed: ~20 pages/sec (vs 50 for PyPDF2)

Memory: ~5 MB per page (stores character bounding boxes)

Trade-off: Slower but more accurate than PyPDF2

Code Examples#

Basic Text Extraction#

import pdfplumber

with pdfplumber.open('document.pdf') as pdf:
    # Extract text from all pages
    for page in pdf.pages:
        text = page.extract_text()
        print(text)

    # Or just first page
    first_page = pdf.pages[0]
    text = first_page.extract_text()

Table Extraction#

import pdfplumber

with pdfplumber.open('invoice.pdf') as pdf:
    page = pdf.pages[0]

    # Automatic table detection
    tables = page.extract_tables()

    for table in tables:
        # table is list of lists (rows × columns)
        for row in table:
            print(row)

    # Convert to pandas DataFrame
    import pandas as pd
    if tables:
        df = pd.DataFrame(tables[0][1:], columns=tables[0][0])
        print(df)

Region-Based Extraction#

# Extract text from specific region (coordinates in points)
page = pdf.pages[0]

# Define bounding box (x0, top, x1, bottom)
bbox = (100, 100, 400, 300)

# Crop to region
cropped = page.within_bbox(bbox)
text = cropped.extract_text()

# Or use crop() method
cropped = page.crop((100, 100, 400, 300))
text = cropped.extract_text()

Custom Table Settings#

# Fine-tune table detection
table_settings = {
    "vertical_strategy": "lines",  # or "text"
    "horizontal_strategy": "lines",
    "intersection_tolerance": 3,
}

tables = page.extract_tables(table_settings=table_settings)

Search for Patterns#

import re

# Find all email addresses
page = pdf.pages[0]
text = page.extract_text()
emails = re.findall(r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b', text)

# Or search for specific phrase
if 'Invoice Number' in text:
    # Extract invoice number
    match = re.search(r'Invoice Number:\s*([A-Z0-9-]+)', text)
    if match:
        invoice_num = match.group(1)

Use Cases#

✅ Choose pdfplumber when:

• Extracting tables from PDFs (best tool for this)
• Need layout-aware extraction (preserve structure)
• Working with invoices, forms, reports (structured data)
• Need character-level details (fonts, positions)
• Complex multi-column layouts

❌ Avoid pdfplumber when:

• Simple text extraction (PyPDF2 is 3x faster)
• Scanned PDFs (need OCR first - use tesseract)
• Need to merge/split PDFs (use PyPDF2)
• Speed critical (pdfplumber slower due to analysis)

Limitations & Gotchas#

1. No OCR for Scanned PDFs#

Problem: Scanned PDFs have no text layer

with pdfplumber.open('scanned.pdf') as pdf:
    text = pdf.pages[0].extract_text()
    print(text)  # Empty or minimal text

Solution: Preprocess with OCR

from pdf2image import convert_from_path
import pytesseract

# Convert PDF to images
images = convert_from_path('scanned.pdf')

# OCR each page
for image in images:
    text = pytesseract.image_to_string(image)
    print(text)

2. Table Detection Not Perfect#

Problem: Complex tables may be missed or incorrectly parsed

tables = page.extract_tables()
# May miss:
# - Tables without borders (text-only alignment)
# - Nested tables
# - Tables spanning multiple pages

Solution: Adjust table settings or manual extraction

# Try different strategies
settings = {
    "vertical_strategy": "text",  # Use text alignment instead of lines
    "horizontal_strategy": "text",
}
tables = page.extract_tables(table_settings=settings)

# Or extract manually using coordinates

3. Memory Intensive for Large PDFs#

Problem: Loading entire PDF consumes memory

pdf = pdfplumber.open('1000-page.pdf')  # Loads all pages into memory

Solution: Process one page at a time

with pdfplumber.open('large.pdf') as pdf:
    for i, page in enumerate(pdf.pages):
        extract_and_process(page)  # Process immediately
        # Page object released after iteration

4. Coordinates Are in Points (Not Pixels)#

Problem: Coordinate system uses PDF points (72 points per inch)

# bbox coordinates in points, not pixels
bbox = (100, 100, 400, 300)  # This is ~1.4 inches from top-left

Conversion: 1 inch = 72 points

Best Practices#

1. Use context manager:

   with pdfplumber.open('file.pdf') as pdf:
       process(pdf)
   # Automatically closes

2. Process pages individually for large files:

   with pdfplumber.open('large.pdf') as pdf:
       for page in pdf.pages:
           data = extract_data(page)
           save_to_db(data)  # Save incrementally

3. Check for empty tables before processing:

   tables = page.extract_tables()
   if tables and len(tables[0]) > 1:  # At least header + 1 row
       process_table(tables[0])

4. Validate extracted data:

   text = page.extract_text()
   if not text or len(text) < 50:  # Suspiciously short
       # Might be scanned PDF or extraction failed
       logger.warning(f"Page {page.page_number}: minimal text")

5. Use pandas for table processing:

   import pandas as pd
   
   tables = page.extract_tables()
   if tables:
       df = pd.DataFrame(tables[0][1:], columns=tables[0][0])
       # Now use pandas for cleaning, validation
       df = df.dropna(how='all')  # Remove empty rows
       df['Amount'] = df['Amount'].str.replace('$', '').astype(float)

Advanced Features#

Custom Table Detection#

# Define explicit table boundaries
table = page.extract_table({
    "vertical_strategy": "explicit",
    "horizontal_strategy": "explicit",
    "explicit_vertical_lines": [100, 200, 300, 400],
    "explicit_horizontal_lines": [100, 150, 200, 250],
})

Character-Level Analysis#

# Get all characters with positions
chars = page.chars

for char in chars:
    print(f"Char: {char['text']}")
    print(f"Position: ({char['x0']}, {char['top']})")
    print(f"Font: {char['fontname']}, Size: {char['size']}")

Line Detection#

# Extract lines (visual elements)
lines = page.lines

for line in lines:
    print(f"Line from ({line['x0']}, {line['top']}) to ({line['x1']}, {line['bottom']})")

Image Extraction#

# Get image metadata
images = page.images

for img in images:
    print(f"Image: {img['width']}x{img['height']} at ({img['x0']}, {img['top']})")

Comparison with PyPDF2#

When to use each:

• pdfplumber: Tables, forms, invoices, complex layouts
• PyPDF2: Simple text extraction, PDF merging/splitting

Alternatives#

PyMuPDF (fitz) - Faster extraction:

import fitz  # PyMuPDF

doc = fitz.open('document.pdf')
page = doc[0]
text = page.get_text()  # 10x faster than pdfplumber
# But: No table detection, less accurate layout

Camelot - Table extraction specialist:

import camelot

tables = camelot.read_pdf('document.pdf', pages='1')
df = tables[0].df  # Returns pandas DataFrame directly
# But: Requires ghostscript, complex installation

Tabula-py - Java-based table extraction:

import tabula

df = tabula.read_pdf('document.pdf', pages='all')
# But: Requires Java runtime

Recommendation: Start with pdfplumber (pure Python, no dependencies). Switch to Camelot/Tabula only if pdfplumber table detection insufficient.

Resources#

• Documentation: https://github.com/jsvine/pdfplumber
• GitHub: https://github.com/jsvine/pdfplumber
• PyPI: https://pypi.org/project/pdfplumber/
• Examples: https://github.com/jsvine/pdfplumber/tree/stable/examples

Version Notes#

• Current: 0.10.3 (as of 2025-12)
• Python: 3.7+ required
• Dependencies: pdfminer.six (PDF parsing engine)

Verdict#

⭐ Highly Recommended for PDF table extraction.

Strengths:

• Best table detection in Python
• Layout-aware extraction
• Character-level access
• Pure Python (no external deps)
• MIT license

Weaknesses:

• Slower than PyPDF2 (but more accurate)
• No OCR (need tesseract preprocessing)
• Higher memory usage
• Table detection not 100% perfect

Bottom line: Use pdfplumber for extracting structured data from PDFs (tables, forms, invoices). For simple text extraction where speed matters, use PyPDF2. For scanned PDFs, add tesseract OCR preprocessing.

Library Profile: python-docx#

Overview#

python-docx is THE standard library for reading and writing Word 2007+ (.docx) files in Python.

Quick Facts:

• License: MIT
• Format: Word 2007+ (.docx)
• Python: 3.7+
• GitHub: 4k stars
• Maintainer: Steve Canny + community
• Status: Mature, stable (10+ years)

Key Differentiator#

Only production-ready open-source library for .docx read/write. No real alternatives.

Capabilities#

Reading#

• ✅ Paragraphs (text, formatting)
• ✅ Tables (cells, rows, columns)
• ✅ Styles (paragraph/character styles)
• ✅ Headers and footers
• ✅ Images (read metadata, extract files)
• ✅ Lists (numbered and bulleted)
• ✅ Sections (page setup, orientation)

Writing#

• ✅ Paragraphs with formatting (font, size, color, bold/italic)
• ✅ Tables (create, populate, style)
• ✅ Headers and footers
• ✅ Images (insert PNG, JPG)
• ✅ Page breaks
• ✅ Styles (apply built-in or custom)
• ✅ Lists (add numbered/bulleted)

Not Supported#

• ❌ Legacy .doc files (use docx2txt for text-only extraction)
• ❌ SmartArt/diagrams
• ❌ Complex fields (TOC, cross-references partially supported)
• ❌ VBA macros
• ❌ Track changes/comments (can preserve but not modify)
• ❌ Equations (MathML)

Performance#

Benchmarks (1000 paragraphs, modern laptop):

Speed: ~500 paragraphs/sec

Memory: ~1 KB per paragraph with formatting

Code Examples#

Basic Read/Write#

from docx import Document

# Read existing document
doc = Document('input.docx')

# Iterate paragraphs
for para in doc.paragraphs:
    print(para.text)

# Read tables
for table in doc.tables:
    for row in table.rows:
        for cell in row.cells:
            print(cell.text)

# Create new document
doc = Document()
doc.add_heading('Document Title', level=1)
doc.add_paragraph('First paragraph text.')
doc.add_page_break()
doc.save('output.docx')

Text Formatting#

from docx.shared import Pt, RGBColor

# Add formatted paragraph
para = doc.add_paragraph()
run = para.add_run('Bold text')
run.bold = True
run.font.size = Pt(14)
run.font.color.rgb = RGBColor(255, 0, 0)  # Red

# Multiple runs in same paragraph
para = doc.add_paragraph()
para.add_run('Regular ').bold = False
para.add_run('bold ').bold = True
para.add_run('and ').bold = False
para.add_run('italic').italic = True

Tables#

# Create table
table = doc.add_table(rows=3, cols=3)
table.style = 'Light Grid Accent 1'

# Populate table
table.cell(0, 0).text = 'Header 1'
table.cell(0, 1).text = 'Header 2'
table.cell(1, 0).text = 'Data 1'
table.cell(1, 1).text = 'Data 2'

# Add row dynamically
row = table.add_row()
row.cells[0].text = 'New data'

Headers/Footers#

from docx.enum.text import WD_PARAGRAPH_ALIGNMENT

# Add header to first section
section = doc.sections[0]
header = section.header
para = header.paragraphs[0]
para.text = 'Company Name'
para.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER

# Add footer with page number
footer = section.footer
para = footer.paragraphs[0]
para.text = 'Page '
run = para.add_run()
run.add_field('PAGE')  # Inserts page number field

Images#

from docx.shared import Inches

# Add image
doc.add_picture('logo.png', width=Inches(2))

# Insert inline image in paragraph
para = doc.add_paragraph('Text before ')
run = para.add_run()
run.add_picture('icon.png', width=Inches(0.5))
para.add_run(' text after')

Use Cases#

✅ Choose python-docx when:

• Creating/editing .docx files
• Report generation (formatted text, tables, images)
• Template population (replace placeholders)
• Contract/document automation
• Any Word document task (it’s the only option!)

❌ Avoid python-docx when:

• Need legacy .doc format (use docx2txt for read-only)
• Complex layouts (SmartArt, equations) - not supported
• High-volume text extraction (use docx2txt - faster)

Limitations & Gotchas#

1. Placeholders Split Across Runs#

Problem: Word may split “{{PLACEHOLDER}}” across multiple runs invisibly

# Template has {{name}} but Word stored it as:
# Run 1: "{{na"
# Run 2: "me}}"

# Simple replace won't find it
for para in doc.paragraphs:
    if '{{name}}' in para.text:  # FALSE! Not found
        para.text = para.text.replace('{{name}}', 'John')

Solution: Replace at run level or use python-docx-template library

# Option 1: Concatenate runs, find/replace, rebuild
full_text = ''.join([r.text for r in para.runs])
if '{{name}}' in full_text:
    # Complex: rebuild runs while preserving formatting
    # Better: use python-docx-template library

# Option 2: Use python-docx-template (Jinja2 syntax)
from docxtpl import DocxTemplate

doc = DocxTemplate('template.docx')
doc.render({'name': 'John', 'date': datetime.now()})
doc.save('filled.docx')

2. Cannot Read All Formatting#

Problem: Some formatting not exposed in API

# Can read: bold, italic, underline, font name/size/color
# Cannot read: strikethrough, small caps, highlight color (some)

Workaround: Access XML directly (advanced)

from docx.oxml import parse_xml

# Access underlying XML for unsupported features
run_xml = para.runs[0]._element
# Parse XML manually

3. No Layout Engine#

Problem: Can’t measure text, predict page breaks, or calculate table widths

# IMPOSSIBLE: "Will this text fit on one page?"
# IMPOSSIBLE: "How many pages is this document?"
# IMPOSSIBLE: "Make this column 30% of page width"

Reality: Word calculates layout on open. python-docx just writes structure.

4. Legacy .doc Not Supported#

Problem: Cannot read .doc files directly

doc = Document('old.doc')  # FAILS: not a .docx file

Solution: Convert with LibreOffice or use docx2txt

# Convert .doc to .docx with LibreOffice CLI
soffice --headless --convert-to docx file.doc

# Or read text-only with docx2txt
import docx2txt
text = docx2txt.process('old.doc')  # Works with .doc files!

Best Practices#

1. Use context manager for error handling:

   try:
       doc = Document('input.docx')
       # Process document
       doc.save('output.docx')
   except Exception as e:
       print(f"Error: {e}")

2. Iterate over runs for formatting:

   for para in doc.paragraphs:
       for run in para.runs:
           if run.bold:
               # Process bold text
               pass

3. Check for empty paragraphs:

   for para in doc.paragraphs:
       if para.text.strip():  # Skip empty paragraphs
           process(para.text)

4. Use styles for consistent formatting:

   doc.add_paragraph('Heading', style='Heading 1')
   doc.add_paragraph('Body text', style='Normal')

5. Handle headers/footers per section:

   for section in doc.sections:
       section.header.paragraphs[0].text = 'Header'

Advanced Features#

Custom Styles#

from docx.shared import Pt

# Create custom paragraph style
styles = doc.styles
style = styles.add_style('CustomStyle', WD_STYLE_TYPE.PARAGRAPH)
style.font.name = 'Arial'
style.font.size = Pt(12)
style.paragraph_format.space_before = Pt(6)

# Apply custom style
doc.add_paragraph('Text', style='CustomStyle')

Section Management#

# Add new section with different orientation
section = doc.add_section()
section.orientation = WD_SECTION.LANDSCAPE
section.page_width = Inches(11)
section.page_height = Inches(8.5)

Hyperlinks#

# Add hyperlink (requires XML manipulation)
from docx.oxml.shared import OxmlElement

def add_hyperlink(paragraph, url, text):
    part = paragraph.part
    r_id = part.relate_to(url, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink', is_external=True)
    hyperlink = OxmlElement('w:hyperlink')
    hyperlink.set(qn('r:id'), r_id)
    new_run = OxmlElement('w:r')
    rPr = OxmlElement('w:rPr')
    # Add run properties for hyperlink styling
    new_run.append(rPr)
    new_run.text = text
    hyperlink.append(new_run)
    paragraph._p.append(hyperlink)
    return hyperlink

para = doc.add_paragraph()
add_hyperlink(para, 'https://example.com', 'Click here')

Alternatives#

docx2txt (read-only, text extraction):

import docx2txt

# 10x faster for text-only extraction
text = docx2txt.process('document.docx')

python-docx-template (Jinja2 templating):

from docxtpl import DocxTemplate

doc = DocxTemplate('template.docx')
doc.render({
    'name': 'John Doe',
    'date': datetime.now(),
    'items': [
        {'product': 'Widget', 'price': 10},
        {'product': 'Gadget', 'price': 20},
    ]
})
doc.save('output.docx')

When to use each:

• python-docx: Full read/write, formatting control
• docx2txt: Fast text extraction only
• python-docx-template: Complex templating with loops/conditions

Resources#

• Documentation: https://python-docx.readthedocs.io/
• GitHub: https://github.com/python-openxml/python-docx
• PyPI: https://pypi.org/project/python-docx/
• Cookbook: https://python-docx.readthedocs.io/en/latest/user/documents.html

Version Notes#

• Current: 1.0.1 (as of 2025-12)
• Python: 3.7+ required
• Breaking changes: Rare (stable API)

Verdict#

⭐ Recommended (only real choice for .docx).

Strengths:

• Only production-ready open-source option
• Clean API
• Good documentation
• MIT license
• Handles most common use cases well

Weaknesses:

• No .doc support
• Limited advanced features (equations, SmartArt)
• Placeholder replacement tricky (use python-docx-template)
• Cannot measure/layout text

Bottom line: Use python-docx for all Word automation. For complex templating, add python-docx-template. For text extraction only, use docx2txt (faster).

S1 Rapid Discovery: Recommendations#

Executive Summary#

For 95% of document parsing needs, use these libraries:

Decision Flowcharts#

Excel: Which Library?#

Start: Need Excel functionality
│
├─ Read only?
│  ├─ YES → Read existing file?
│  │  ├─ YES → Data analysis needed?
│  │  │  ├─ YES → pandas (10x faster for data)
│  │  │  └─ NO  → openpyxl (preserves formatting)
│  │  └─ NO → (no reading needed, skip to write)
│  │
│  └─ NO → Must write
│     ├─ Modify existing file?
│     │  └─ YES → openpyxl (read + write)
│     │
│     └─ NO → Create new file
│        ├─ Large file (>100k rows)?
│        │  └─ YES → xlsxwriter (2x faster, 50% less memory)
│        └─ NO  → openpyxl or xlsxwriter (either works)

RECOMMENDATION:
- Default: openpyxl (most flexible)
- Large reports: xlsxwriter (performance)
- Data processing: pandas (high-level API)

PDF: Which Library?#

Start: Need PDF extraction
│
├─ Scanned document (no text layer)?
│  └─ YES → pdfplumber + tesseract OCR
│
├─ Need tables?
│  ├─ YES → pdfplumber (best table detection)
│  └─ NO  → Text extraction only
│     ├─ Layout matters?
│     │  ├─ YES → pdfplumber (layout-aware)
│     │  └─ NO  → PyPDF2 (3x faster, simple)
│     │
│     └─ Need to manipulate PDF?
│        └─ YES → PyPDF2 (merge, split, rotate)

RECOMMENDATION:
- Tables/forms: pdfplumber
- Simple text: PyPDF2
- Scanned: pdfplumber + tesseract

Library Recommendations by Use Case#

Use Case: Data Import (User Uploads Excel)#

Scenario: Users upload budget spreadsheets; system validates and imports to database.

Recommended Stack:

import pandas as pd
from openpyxl import load_workbook

# Step 1: Quick validation with openpyxl
wb = load_workbook('upload.xlsx', read_only=True, data_only=True)
ws = wb.active

# Check structure
if ws.max_row < 2:
    raise ValueError("File is empty")

# Step 2: Read data with pandas (faster)
df = pd.read_excel('upload.xlsx')

# Step 3: Validate and transform
df['Amount'] = df['Amount'].astype(float)
df = df.dropna()

# Step 4: Load to database
df.to_sql('budget', con=db_engine, if_exists='append')

Why this stack:

• openpyxl: Quick structure check without loading all data
• pandas: Fast data manipulation, validation, database integration

Use Case: Report Generation (Monthly Sales Reports)#

Scenario: Generate 50 regional reports, each 10k+ rows, with charts and formatting.

Recommended Stack:

import xlsxwriter

workbook = xlsxwriter.Workbook('report.xlsx', {'constant_memory': True})
worksheet = workbook.add_worksheet()

# Add formatted data (streaming mode)
header_format = workbook.add_format({'bold': True, 'bg_color': '#D3D3D3'})
worksheet.write_row(0, 0, ['Region', 'Sales', 'Growth'], header_format)

for i, row in enumerate(sales_data, start=1):
    worksheet.write_row(i, 0, row)

# Add chart
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': '=Sheet1!$B$2:$B$100'})
worksheet.insert_chart('E2', chart)

workbook.close()

Why xlsxwriter:

• 2x faster than openpyxl for writing
• constant_memory mode: handles 100k+ rows easily
• Full chart support

Use Case: Contract Generation (Word Templates)#

Scenario: Generate 100 contracts from template by replacing {{placeholders}}.

Recommended Stack:

from docxtpl import DocxTemplate  # python-docx-template
from datetime import datetime

doc = DocxTemplate('template.docx')

context = {
    'customer_name': 'Acme Corp',
    'contract_date': datetime.now().strftime('%B %d, %Y'),
    'monthly_fee': '$2,500.00',
    'services': [
        {'name': 'Cloud Hosting', 'price': '$1,500'},
        {'name': 'Support', 'price': '$1,000'},
    ]
}

doc.render(context)
doc.save('contract.docx')

Why python-docx-template:

• Handles split placeholders (Word quirk)
• Supports loops, conditions (Jinja2)
• Preserves all formatting
• 10x less code than raw python-docx

Fallback to python-docx if:

• Simple placeholder replacement (no loops/conditions)
• Can’t add external dependency

Use Case: Invoice Data Extraction (PDFs)#

Scenario: Extract vendor, amount, date, line items from varied invoice layouts.

Recommended Stack:

import pdfplumber
import re
from decimal import Decimal

with pdfplumber.open('invoice.pdf') as pdf:
    page = pdf.pages[0]
    text = page.extract_text()

    # Extract fields with regex
    invoice_num = re.search(r'Invoice #:\s*([A-Z0-9-]+)', text).group(1)
    total = re.search(r'Total:\s*\$?([\d,]+\.\d{2})', text).group(1)
    total = Decimal(total.replace(',', ''))

    # Extract table
    tables = page.extract_tables()
    line_items = []
    if tables:
        for row in tables[0][1:]:  # Skip header
            line_items.append({
                'description': row[0],
                'quantity': int(row[1]),
                'price': Decimal(row[2].replace('$', ''))
            })

    return {
        'invoice_number': invoice_num,
        'total': total,
        'line_items': line_items
    }

Why pdfplumber:

• Best table detection
• Layout-aware text extraction
• Pure Python (no dependencies)

Use Case: Simple Text Extraction (PDF Reports)#

Scenario: Extract text from 1000 PDF reports for search indexing.

Recommended Stack:

from PyPDF2 import PdfReader
import concurrent.futures

def extract_text(pdf_path):
    reader = PdfReader(pdf_path)
    return '\n'.join([page.extract_text() for page in reader.pages])

# Parallel processing
with concurrent.futures.ProcessPoolExecutor(max_workers=8) as executor:
    texts = executor.map(extract_text, pdf_files)

Why PyPDF2:

• 3x faster than pdfplumber for simple text
• Lower memory (1 MB vs 5 MB per page)
• Good enough for search indexing

Performance Comparison#

Excel (100k rows × 10 columns)#

Decision rules:

• Read >100k rows: pandas or openpyxl read_only
• Write >100k rows: xlsxwriter
• Read + Write: openpyxl (only option)
• Data analysis: pandas

PDF (100 pages, text-heavy)#

Decision rules:

• Simple text: PyPDF2 (3x faster)
• Tables/forms: pdfplumber (only good option)
• Scanned PDFs: pdfplumber + tesseract

Common Pitfalls & Solutions#

Pitfall 1: Memory Errors on Large Excel Files#

Problem:

df = pd.read_excel('huge.xlsx')  # OutOfMemoryError!

Solution:

# Option 1: Chunked read
for chunk in pd.read_excel('huge.xlsx', chunksize=10000):
    process(chunk)

# Option 2: read_only mode
from openpyxl import load_workbook
wb = load_workbook('huge.xlsx', read_only=True)
for row in wb.active.iter_rows(values_only=True):
    process(row)

# Option 3: Convert to Parquet (one-time cost, 5x faster after)
df = pd.read_excel('huge.xlsx')
df.to_parquet('data.parquet')  # Future reads: pd.read_parquet()

Pitfall 2: Word Placeholder Replacement Breaks#

Problem:

# Doesn't work if {{name}} split across runs
doc = Document('template.docx')
for para in doc.paragraphs:
    if '{{name}}' in para.text:
        para.text = para.text.replace('{{name}}', 'John')

Solution:

# Use python-docx-template library
from docxtpl import DocxTemplate

doc = DocxTemplate('template.docx')
doc.render({'name': 'John'})
doc.save('output.docx')

Pitfall 3: PDF Table Extraction Fails#

Problem:

tables = page.extract_tables()  # Empty list!

Solution:

# Try different table detection strategies
settings = {
    "vertical_strategy": "text",  # Use text alignment, not lines
    "horizontal_strategy": "text",
}
tables = page.extract_tables(table_settings=settings)

# Or manual extraction using coordinates
bbox = (100, 100, 500, 400)
cropped = page.within_bbox(bbox)
text = cropped.extract_text()
# Parse text manually

Migration Guide#

From xlrd (legacy .xls) to openpyxl (.xlsx)#

Step 1: Convert files

# Batch convert with LibreOffice CLI
find . -name "*.xls" -exec soffice --headless --convert-to xlsx {} \;

Step 2: Update code

# OLD: xlrd
import xlrd
book = xlrd.open_workbook('file.xls')
sheet = book.sheet_by_index(0)
value = sheet.cell_value(0, 0)

# NEW: openpyxl (after converting to .xlsx)
from openpyxl import load_workbook
wb = load_workbook('file.xlsx')
ws = wb.active
value = ws['A1'].value

From docx (unmaintained) to python-docx#

# OLD: docx library (deprecated)
import docx
document = docx.Document('file.docx')

# NEW: python-docx (drop-in replacement)
from docx import Document
document = Document('file.docx')
# Same API!

Library Maturity Matrix#

All recommended libraries are safe for production use.

Final Recommendations#

Quick Start Matrix#

Default Stack for Full Office Suite#

# requirements.txt
openpyxl>=3.1.2      # Excel read/write
xlsxwriter>=3.1.9    # Excel write-only (large files)
pandas>=2.1.0        # Excel data analysis
python-docx>=1.0.1   # Word
python-pptx>=0.6.23  # PowerPoint
pdfplumber>=0.10.3   # PDF tables/forms
PyPDF2>=3.0.1        # PDF simple operations

This stack covers 95% of document automation needs.

When to Consider Alternatives#

Cloud services (AWS Textract, Azure Form Recognizer):

• ✅ When: Scanned documents, handwriting, >100k docs/month
• ❌ When: On-premise only, cost-sensitive, <10k docs/month

Commercial libraries (Aspose, Syncfusion):

• ✅ When: Need 100% Office compatibility (VBA, pivot tables)
• ❌ When: Open-source requirement, budget <$1k/year

LLM parsing (GPT-4, Claude):

• ✅ When: Unstructured layouts, experimental projects
• ❌ When: Production-ready, deterministic output required (2025)
• ⏳ Watch: May become viable by 2027

Action Items#

For developers starting new project:

1. ✅ Install: pip install openpyxl pandas python-docx pdfplumber
2. ✅ Use openpyxl for Excel (default), switch to xlsxwriter if large writes
3. ✅ Use python-docx for Word, add python-docx-template if complex templating
4. ✅ Use pdfplumber for PDFs with tables, PyPDF2 for simple text

For teams with existing code:

1. ✅ Audit: Are you using deprecated libraries? (xlrd for .xlsx, old docx)
2. ✅ Migrate: xlrd → openpyxl, old docx → python-docx
3. ✅ Optimize: Large file writes → xlsxwriter, data processing → pandas
4. ✅ Test: Validate with real user files (edge cases common)

For technical leads making decisions:

1. ✅ Security: Add validation (file size limits, MIME check, sanitize inputs)
2. ✅ Performance: Profile before optimizing, streaming for >100k rows
3. ✅ Scale: Async processing for >10k docs/day, consider cloud for >100k/month
4. ✅ Governance: Establish library approval process, security policies

S1 Rapid Discovery Complete: You now have clear recommendations for 95% of document parsing needs. Proceed to S2 for technical deep dives, S3 for production use cases, S4 for architecture patterns.

Library Profile: xlsxwriter#

Overview#

xlsxwriter is a write-only Excel library optimized for generating large, formatted reports with charts and formulas.

Quick Facts:

• License: BSD
• Format: Excel 2007+ (.xlsx) - write-only
• Python: 3.6+
• GitHub: 3.5k stars
• Maintainer: John McNamara (single maintainer, very responsive)
• Status: Mature, stable (12+ years)

Key Differentiator#

Write-only = 2x faster + 50% less memory than openpyxl for report generation.

Cannot read or modify existing files - only create new ones.

Capabilities#

Writing#

• ✅ All formatting (fonts, fills, borders, number formats)
• ✅ Formulas (Excel calculates on open)
• ✅ Charts (26 types: line, bar, pie, scatter, stock, etc.)
• ✅ Conditional formatting (all types)
• ✅ Data validation
• ✅ Images (PNG, JPEG, BMP, WMF, EMF)
• ✅ Tables (structured references)
• ✅ Sparklines (inline charts)
• ✅ Protection (password-protect sheets/workbooks)
• ✅ VBA macros (add pre-written macros from .xlsm files)

Not Supported#

• ❌ Reading existing files
• ❌ Modifying existing files
• ❌ Creating from template (can’t read template)

Performance#

Benchmarks (100k rows × 10 columns, modern laptop):

Speed: ~2000 rows/sec (vs 500 for openpyxl)

Memory: Constant ~30 MB with constant_memory option

Code Examples#

Basic Writing#

import xlsxwriter

# Create workbook
workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet('Sheet1')

# Write data
worksheet.write('A1', 'Hello')
worksheet.write('B1', 123)
worksheet.write('C1', 45.67)

# Alternative: write_row, write_column
worksheet.write_row('A2', ['Apple', 100, 1.50])
worksheet.write_column('A3', ['Banana', 50, 0.75])

workbook.close()  # Must close to finalize file

Formatting#

# Create reusable formats
header_format = workbook.add_format({
    'bold': True,
    'font_color': 'white',
    'bg_color': '#4472C4',
    'border': 1,
    'align': 'center',
    'valign': 'vcenter'
})

currency_format = workbook.add_format({'num_format': '$#,##0.00'})

# Apply formats
worksheet.write('A1', 'Product', header_format)
worksheet.write('B1', 'Price', header_format)
worksheet.write('B2', 19.99, currency_format)

Charts#

# Create chart
chart = workbook.add_chart({'type': 'column'})

# Add data series
chart.add_series({
    'name': 'Sales',
    'categories': '=Sheet1!$A$2:$A$10',
    'values': '=Sheet1!$B$2:$B$10',
})

# Configure chart
chart.set_title({'name': 'Monthly Sales'})
chart.set_x_axis({'name': 'Month'})
chart.set_y_axis({'name': 'Revenue ($)'})

# Insert into worksheet
worksheet.insert_chart('D2', chart, {'x_scale': 2, 'y_scale': 1.5})

Large File Generation (Constant Memory)#

workbook = xlsxwriter.Workbook('large.xlsx', {'constant_memory': True})
worksheet = workbook.add_worksheet()

# Stream 1 million rows (memory stays at 30 MB)
for i in range(1000000):
    worksheet.write_row(i, 0, [f'Item {i}', i * 10, i * 0.5])

workbook.close()

Conditional Formatting#

# Highlight cells > 100 in green
worksheet.conditional_format('B2:B100', {
    'type': 'cell',
    'criteria': '>',
    'value': 100,
    'format': workbook.add_format({'bg_color': '#C6EFCE'})
})

# Data bars
worksheet.conditional_format('C2:C100', {
    'type': 'data_bar',
    'bar_color': '#638EC6'
})

Use Cases#

✅ Choose xlsxwriter when:

• Generating new reports/exports (not editing existing)
• Large files (>100k rows) - memory efficiency critical
• Write performance matters (2x faster than openpyxl)
• Need charts, conditional formatting, data validation
• Creating Excel-based dashboards

❌ Avoid xlsxwriter when:

• Need to read/modify existing files
• Working from template (must read template first)
• Interactive workflows (read user’s file, make changes)

Limitations & Gotchas#

1. Cannot Read Files#

Problem: Want to add sheet to existing workbook

# This is IMPOSSIBLE with xlsxwriter
wb = xlsxwriter.Workbook('existing.xlsx')  # Overwrites file!

Solution: Use openpyxl for read/modify, or separate files

# Workaround: Read with openpyxl, write new file with xlsxwriter
import openpyxl
import xlsxwriter

# Read existing
old_wb = openpyxl.load_workbook('old.xlsx')
old_data = [[cell.value for cell in row] for row in old_wb.active.rows]

# Write to new file with xlsxwriter
new_wb = xlsxwriter.Workbook('new.xlsx')
ws = new_wb.add_worksheet()
for i, row in enumerate(old_data):
    ws.write_row(i, 0, row)
new_wb.close()

2. Must Close Workbook#

Problem: Forgetting to close() leaves corrupted file

wb = xlsxwriter.Workbook('output.xlsx')
ws = wb.add_worksheet()
ws.write('A1', 'Data')
# Forgot wb.close() - file is incomplete/corrupted!

Solution: Use context manager

with xlsxwriter.Workbook('output.xlsx') as wb:
    ws = wb.add_worksheet()
    ws.write('A1', 'Data')
# Automatically closed

3. Cannot Modify After Close#

Problem: Writing after close() fails silently

wb = xlsxwriter.Workbook('output.xlsx')
ws = wb.add_worksheet()
wb.close()

ws.write('A1', 'Data')  # SILENTLY IGNORED!

Solution: Write all data before closing

4. Chart Data Must Use Excel Formulas#

Problem: Can’t pass Python arrays to chart

# WRONG: This doesn't work
chart.add_series({
    'categories': ['Jan', 'Feb', 'Mar'],  # Python list
    'values': [10, 20, 30],               # Python list
})

Solution: Use Excel cell references

# Write data to cells first
worksheet.write_column('A1', ['Jan', 'Feb', 'Mar'])
worksheet.write_column('B1', [10, 20, 30])

# Reference cells in chart
chart.add_series({
    'categories': '=Sheet1!$A$1:$A$3',
    'values': '=Sheet1!$B$1:$B$3',
})

Best Practices#

1. Use constant_memory for large files:

   workbook = xlsxwriter.Workbook('output.xlsx', {'constant_memory': True})

2. Reuse format objects:

   # BAD: Creating format for every cell (slow)
   for i in range(1000):
       worksheet.write(i, 0, 'Data', workbook.add_format({'bold': True}))
   
   # GOOD: Create once, reuse
   bold_format = workbook.add_format({'bold': True})
   for i in range(1000):
       worksheet.write(i, 0, 'Data', bold_format)

3. Set column widths for readability:

   worksheet.set_column('A:A', 20)  # Width in characters
   worksheet.set_column('B:D', 12)

4. Use write_row/write_column for speed:

   # SLOW: Individual writes
   for i, val in enumerate(data):
       worksheet.write(i, 0, val)
   
   # FAST: Batch write
   worksheet.write_column(0, 0, data)

5. Freeze panes for navigation:

   worksheet.freeze_panes(1, 0)  # Freeze first row

Alternatives & When to Switch#

Switch to openpyxl if:

• Need to read/modify existing files
• Want to preserve existing formatting/charts
• Interactive workflows (edit user’s file)

Switch to pandas if:

• Data comes from DataFrame
• Need data transformations before writing
• Writing simple data (no complex formatting)

Use xlsxwriter + pandas together:

import pandas as pd

df = pd.DataFrame(data)

# Use xlsxwriter as pandas Excel engine
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False, sheet_name='Data')

# Access xlsxwriter objects for formatting
workbook = writer.book
worksheet = writer.sheets['Data']

# Add formatting
header_format = workbook.add_format({'bold': True, 'bg_color': '#D3D3D3'})
for col_num, col_name in enumerate(df.columns):
    worksheet.write(0, col_num, col_name, header_format)

writer.close()

Advanced Features#

Data Validation#

worksheet.data_validation('A2:A100', {
    'validate': 'list',
    'source': ['Apple', 'Banana', 'Cherry']
})

Sparklines#

worksheet.add_sparkline('F2', {'range': 'A2:E2', 'type': 'line'})

Autofilter#

worksheet.autofilter('A1:D100')

Cell Comments#

worksheet.write_comment('A1', 'This is a comment')

Resources#

• Documentation: https://xlsxwriter.readthedocs.io/
• GitHub: https://github.com/jmcnamara/XlsxWriter
• PyPI: https://pypi.org/project/XlsxWriter/
• Examples: https://xlsxwriter.readthedocs.io/examples.html (100+ examples)

Version Notes#

• Current: 3.1.9 (as of 2025-12)
• Python: 3.6+ required
• Breaking changes: Rare (stable API for 10+ years)

Verdict#

⭐ Highly Recommended for report generation.

Strengths:

• 2x faster than openpyxl for writing
• 50% less memory
• Excellent documentation (100+ examples)
• All Excel features supported
• Single maintainer = fast responses

Weaknesses:

• Write-only (cannot read)
• Must write data before charts (can’t pass arrays)
• Must close workbook explicitly

Bottom line: Use xlsxwriter for all write-only workflows, especially large reports. Its performance and memory efficiency make it the clear choice when you don’t need to modify existing files.

S2 COMPREHENSIVE DISCOVERY: Document Parsing Deep Dive#

File Format Internals#

Office Open XML (OOXML) Format Structure#

Modern Office formats (.docx, .xlsx, .pptx) are ZIP archives containing XML:

# Unzip any modern Office file to see structure
unzip document.docx -d docx_contents/

# Structure:
docx_contents/
├── [Content_Types].xml        # MIME types for all parts
├── _rels/                      # Relationships (links between parts)
├── word/                       # Main document content
│   ├── document.xml           # Actual document text/structure
│   ├── styles.xml             # Paragraph/character styles
│   ├── numbering.xml          # List numbering definitions
│   ├── media/                 # Embedded images
│   └── _rels/                 # Relationships for this part
└── docProps/                   # Document metadata
    ├── core.xml               # Author, created date, etc.
    └── app.xml                # Application properties

Excel (.xlsx):

xl/
├── workbook.xml               # Workbook structure, sheet list
├── worksheets/
│   ├── sheet1.xml            # Cell data, formulas, formats
│   └── sheet2.xml
├── sharedStrings.xml          # String pool (all text values)
├── styles.xml                 # Cell formats, fonts, fills
├── calcChain.xml              # Formula calculation order
└── charts/                    # Chart definitions

PowerPoint (.pptx):

ppt/
├── presentation.xml           # Slide order, master slides
├── slides/
│   ├── slide1.xml            # Slide content, layout
│   └── slide2.xml
├── slideLayouts/              # Layout templates
├── slideMasters/              # Master slide designs
└── media/                     # Images, videos

Key Insight: Office Open XML is XML-based, making it:

• Human-readable when unzipped
• Parsable with standard XML tools
• Modifiable programmatically (but error-prone)
• Larger file size than binary formats (mitigated by ZIP compression)

Legacy Binary Formats (.doc, .xls, .ppt)#

Compound File Binary Format (CFBF):

• Structured storage (FAT-like filesystem inside file)
• Complex, proprietary format
• Reading requires reverse-engineered libraries (xlrd, python-oletools)
• Writing support largely abandoned
• Recommendation: Convert to modern formats with LibreOffice CLI

PDF Internals#

PDF structure:

%PDF-1.7
1 0 obj
<<
  /Type /Catalog
  /Pages 2 0 R
>>
endobj

2 0 obj
<<
  /Type /Pages
  /Kids [3 0 R]
  /Count 1
>>
endobj

3 0 obj
<<
  /Type /Page
  /Parent 2 0 R
  /MediaBox [0 0 612 792]
  /Contents 4 0 R
>>
endobj

• Cross-reference table: Byte offsets to all objects
• Objects: Self-contained units (pages, images, fonts)
• Streams: Compressed binary data (text, images)
• Incremental updates: Appended changes (not in-place edits)

Why PDF extraction is hard:

• Text is positioned by coordinates, not flow
• Tables have no semantic structure
• Fonts can be embedded/subsetted
• Complex rendering model (graphics operators)

Performance Characteristics#

Memory Usage Analysis#

openpyxl (Excel):

# Memory profile for 100k rows:
# - XML parsing: ~50 MB (holds entire workbook in memory)
# - Cell objects: ~2 KB per cell with formatting
# - String caching: Shares identical strings

# Problem: 100k rows × 50 cols = 5M cells × 2KB = 10 GB (worst case)
# Reality: Shared strings reduce this to ~500 MB for typical data

# Solution for large files:
from openpyxl import load_workbook

# Read-only mode (iterates, doesn't store all cells)
wb = load_workbook('large.xlsx', read_only=True)
for row in wb.active.iter_rows():
    process(row)  # Row is tuple of values, not Cell objects

pandas (Excel):

# Memory profile:
# - Reads entire sheet into DataFrame (columnar storage)
# - More memory-efficient than openpyxl (no style info)
# - Categorical types reduce memory for repeated values

import pandas as pd

# Standard read (loads all data)
df = pd.read_excel('data.xlsx')  # ~100 MB for 100k rows

# Chunked read (iterator, ~10 MB at a time)
for chunk in pd.read_excel('data.xlsx', chunksize=10000):
    process(chunk)

xlsxwriter (Excel):

# Write-only mode: ~30 MB for 100k rows
# - Streams directly to file (doesn't hold in memory)
# - Closes workbook when done (frees memory)
# - No cell objects retained

import xlsxwriter
workbook = xlsxwriter.Workbook('out.xlsx')
worksheet = workbook.add_worksheet()

for i in range(100000):
    worksheet.write_row(i, 0, [f'val{i}', i * 2])
    # Memory stays constant (streaming mode)

workbook.close()

python-docx (Word):

# Memory scales with:
# - Number of paragraphs (~1 KB each with formatting)
# - Embedded images (stored in memory)
# - Tables (each cell is like a paragraph)

# 1000 paragraphs = ~1 MB
# Large document (10k paragraphs + images) = ~50 MB

pdfplumber (PDF):

# Memory scales with page complexity:
# - Text-heavy page: ~500 KB
# - Image-heavy page: ~5 MB (stores decoded images)
# - Table extraction: 2-3x page memory (layout analysis)

# Recommendation: Process one page at a time
with pdfplumber.open('large.pdf') as pdf:
    for page in pdf.pages:
        extract_and_process(page)  # ~5 MB per page max

Speed Benchmarks (Real-World Data)#

Excel Reading (100k rows × 10 columns):

Excel Writing (100k rows × 10 columns):

Word Processing (1000 paragraphs):

PDF Extraction (100 pages):

Optimization Strategies#

Excel Optimization#

Problem: Reading 1M rows × 50 cols (50M cells)

Strategy 1: Streaming read (openpyxl)

from openpyxl import load_workbook

wb = load_workbook('huge.xlsx', read_only=True, data_only=True)
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    # row is tuple of values (no Cell objects)
    # Memory constant at ~50 MB
    process_row(row)

Strategy 2: Chunked read (pandas)

import pandas as pd

chunk_size = 10000
for chunk in pd.read_excel('huge.xlsx', chunksize=chunk_size):
    # Process 10k rows at a time
    # Memory stays at ~50 MB
    result = process_chunk(chunk)

Strategy 3: Filter columns at read time

# Only read 5 of 50 columns
df = pd.read_excel('huge.xlsx', usecols=['Name', 'Value', 'Date', 'Status', 'Amount'])
# Memory: 10% of full read

Strategy 4: Convert to CSV/Parquet for repeated access

# One-time conversion
df = pd.read_excel('huge.xlsx')
df.to_parquet('data.parquet')  # ~10x faster reads after this

# All subsequent reads
df = pd.read_parquet('data.parquet')  # 5x faster, 50% less memory

Word Document Optimization#

Problem: Extracting text from 10k paragraph document

Strategy: Text-only extraction (fast path)

from docx import Document

# Slow: Retains all formatting (3s for 10k paragraphs)
doc = Document('large.docx')
text = '\n'.join([p.text for p in doc.paragraphs])

# Fast: Use docx2txt (pure text extraction, no python-docx overhead)
import docx2txt
text = docx2txt.process('large.docx')  # 0.5s for 10k paragraphs

PDF Optimization#

Problem: Extracting text from 1000-page PDF

Strategy 1: Parallel processing

from concurrent.futures import ProcessPoolExecutor
import pdfplumber

def extract_page(pdf_path, page_num):
    with pdfplumber.open(pdf_path) as pdf:
        return pdf.pages[page_num].extract_text()

# Process 8 pages at a time
with ProcessPoolExecutor(max_workers=8) as executor:
    texts = executor.map(extract_page, ['doc.pdf'] * 1000, range(1000))