5.2 Office Document Automation

5.2 Office Document Automation

Office document automation means using Python programs to create, read, modify, organize, and process documents automatically.

Instead of manually opening hundreds of Excel workbooks, updating Word documents, or processing PDF reports, Python can perform these repetitive tasks programmatically.

1. Why Automate Office Documents?

Office automation becomes valuable when the same document operation must be repeated.

• Generate monthly reports.
• Update hundreds of Excel records.
• Create student or employee documents from data.
• Generate invoices and certificates.
• Combine multiple PDF reports.
• Extract information from PDF files.
• Apply standardized formatting to spreadsheets.
• Create Word reports from database records.
• Convert structured data into printable documents.
• Build automated reporting pipelines.

2. Installing Required Libraries

The following packages are commonly used in this lesson.

pip install openpyxl python-docx pypdf pdfplumber reportlab

With Conda:

conda install openpyxl python-docx pypdf pdfplumber reportlab

3. Excel Automation with openpyxl

openpyxl is commonly used to read and modify Excel workbooks in the .xlsx format.

import openpyxl

It can be used for:

• Reading workbook data.
• Creating worksheets.
• Writing cell values.
• Updating existing cells.
• Formatting cells.
• Adding formulas.
• Creating charts.
• Working with merged cells.
• Managing rows and columns.
• Applying data validation.

4. Creating an Excel Workbook

from openpyxl import Workbook

workbook = Workbook()

worksheet = workbook.active

worksheet["A1"] = "Name"
worksheet["B1"] = "Score"

worksheet["A2"] = "Alex"
worksheet["B2"] = 92

workbook.save("students.xlsx")

The workbook is saved as students.xlsx.

5. Workbook vs Worksheet

6. Opening an Existing Excel Workbook

import openpyxl

workbook = openpyxl.load_workbook(
    "students.xlsx"
)

worksheet = workbook.active

print(worksheet["A1"].value)

The value property retrieves the content of a cell.

7. Reading Worksheet Names

print(workbook.sheetnames)

Access a specific worksheet:

worksheet = workbook["Students"]

You can also create a worksheet:

worksheet = workbook.create_sheet(
    "Summary"
)

8. Accessing Excel Cells

Using Cell References

value = worksheet["A1"].value

print(value)

Using Row and Column Numbers

value = worksheet.cell(
    row=2,
    column=1
).value

print(value)

9. Writing Data to Excel

worksheet["A1"] = "Student"
worksheet["B1"] = "Subject"
worksheet["C1"] = "Score"

worksheet["A2"] = "Alex"
worksheet["B2"] = "Python"
worksheet["C2"] = 95

workbook.save("results.xlsx")

10. Writing Multiple Rows

from openpyxl import Workbook

workbook = Workbook()
worksheet = workbook.active

rows = [
    ["Student", "Python", "NumPy"],
    ["Alex", 90, 88],
    ["Jordan", 84, 91],
    ["Taylor", 95, 94]
]

for row in rows:
    worksheet.append(row)

workbook.save(
    "results.xlsx"
)

append() adds a row to the worksheet.

11. Reading Rows from Excel

for row in worksheet.iter_rows(
    values_only=True
):

    print(row)

values_only=True returns cell values rather than cell objects.

12. Finding Worksheet Dimensions

print(
    worksheet.max_row
)

print(
    worksheet.max_column
)

These properties provide the worksheet's currently recognized maximum row and column indices.

13. Formatting Excel Cells

openpyxl supports common cell formatting operations.

from openpyxl.styles import Font

worksheet["A1"].font = Font(
    bold=True
)

Font Size

worksheet["A1"].font = Font(
    bold=True,
    size=14
)

14. Applying Font Formatting

from openpyxl.styles import Font

worksheet["A1"].font = Font(
    name="Calibri",
    size=12,
    bold=True,
    italic=False
)

Font properties can be combined according to the required document design.

15. Cell Background Fill

from openpyxl.styles import PatternFill

header_fill = PatternFill(
    fill_type="solid",
    fgColor="D9EAF7"
)

worksheet["A1"].fill = header_fill

This is useful for visually distinguishing headings and sections.

16. Cell Alignment

from openpyxl.styles import Alignment

worksheet["A1"].alignment = Alignment(
    horizontal="center",
    vertical="center"
)

Common alignment values include:

• left
• center
• right

17. Applying Borders

from openpyxl.styles import Border, Side

thin = Side(
    style="thin"
)

border = Border(
    left=thin,
    right=thin,
    top=thin,
    bottom=thin
)

worksheet["A1"].border = border

18. Adjusting Column Width

worksheet.column_dimensions[
    "A"
].width = 25

worksheet.column_dimensions[
    "B"
].width = 15

This is useful when generating professional spreadsheets.

19. Adjusting Row Height

worksheet.row_dimensions[
    1
].height = 25

20. Merging Cells

worksheet.merge_cells(
    "A1:D1"
)

worksheet["A1"] = (
    "Annual Performance Report"
)

21. Freeze Panes

Freeze panes keep selected rows or columns visible while scrolling.

worksheet.freeze_panes = "A2"

This keeps the first row visible.

22. Adding AutoFilter

worksheet.auto_filter.ref = (
    worksheet.dimensions
)

This enables Excel's filtering controls for the specified range.

23. Writing Excel Formulas

worksheet["C2"] = "=SUM(A2:B2)"

Python writes the formula into the workbook; Excel or another compatible spreadsheet application generally calculates it when the workbook is opened or recalculated.

Average Example

worksheet["D2"] = (
    "=AVERAGE(B2:C2)"
)

24. Reading Formula Results

By default, openpyxl reads the formula expression.

workbook = openpyxl.load_workbook(
    "results.xlsx"
)

print(
    workbook.active["D2"].value
)

To read cached values stored in the workbook:

workbook = openpyxl.load_workbook(
    "results.xlsx",
    data_only=True
)

25. Updating Existing Excel Data

import openpyxl

workbook = openpyxl.load_workbook(
    "students.xlsx"
)

worksheet = workbook["Students"]

worksheet["C2"] = 96

workbook.save(
    "students_updated.xlsx"
)

26. Finding a Record and Updating It

for row in worksheet.iter_rows(
    min_row=2
):

    if row[0].value == "Alex":

        row[2].value = 98

This pattern can be adapted for updating records based on an identifier.

27. Deleting Rows and Columns

worksheet.delete_rows(
    3
)

Delete a column:

worksheet.delete_cols(
    4
)

28. Inserting Rows and Columns

worksheet.insert_rows(
    2
)

worksheet.insert_cols(
    3
)

29. Creating an Excel Table

from openpyxl.worksheet.table import (
    Table,
    TableStyleInfo
)

table = Table(
    displayName="StudentData",
    ref="A1:C4"
)

style = TableStyleInfo(
    name="TableStyleMedium2",
    showFirstColumn=False,
    showLastColumn=False,
    showRowStripes=True,
    showColumnStripes=False
)

table.tableStyleInfo = style

worksheet.add_table(table)

Excel tables provide structured data ranges with built-in filtering and table behavior.

30. Conditional Formatting

Conditional formatting can visually identify values that satisfy particular conditions.

from openpyxl.formatting.rule import (
    CellIsRule
)

worksheet.conditional_formatting.add(
    "C2:C20",
    CellIsRule(
        operator="greaterThan",
        formula=["90"]
    )
)

This is useful for automatically highlighting high scores, thresholds, or exceptions.

31. Excel Data Validation

Data validation can restrict what users enter into selected cells.

from openpyxl.worksheet.datavalidation import (
    DataValidation
)

validation = DataValidation(
    type="whole",
    operator="between",
    formula1="0",
    formula2="100"
)

worksheet.add_data_validation(
    validation
)

validation.add(
    "C2:C50"
)

This example restricts values to whole numbers from 0 to 100.

32. Creating an Excel Chart

from openpyxl.chart import BarChart, Reference

chart = BarChart()

data = Reference(
    worksheet,
    min_col=2,
    min_row=1,
    max_row=5
)

chart.add_data(
    data,
    titles_from_data=True
)

worksheet.add_chart(
    chart,
    "E2"
)

Charts can be generated automatically from structured data.

33. Mini Project — Automated Student Report

Create an Excel workbook containing student scores and calculate a total and average.

from openpyxl import Workbook

workbook = Workbook()
worksheet = workbook.active

worksheet.title = "Results"

worksheet.append([
    "Student",
    "Python",
    "NumPy",
    "Pandas",
    "Total",
    "Average"
])

students = [
    ["Alex", 90, 88, 92],
    ["Jordan", 84, 91, 86],
    ["Taylor", 95, 94, 97]
]

for student in students:

    worksheet.append(
        student + [
            f"=SUM(B{worksheet.max_row + 1}:D{worksheet.max_row + 1})",
            f"=AVERAGE(B{worksheet.max_row + 1}:D{worksheet.max_row + 1})"
        ]
    )

workbook.save(
    "student_report.xlsx"
)

34. Word Document Automation

The python-docx package can create and modify Microsoft Word .docx documents.

from docx import Document

Typical tasks include:

• Create Word documents.
• Add headings and paragraphs.
• Insert tables.
• Apply basic formatting.
• Add images.
• Insert page breaks.
• Modify existing documents.
• Generate standardized reports.

35. Creating a Word Document

from docx import Document

document = Document()

document.add_heading(
    "Annual Report",
    level=1
)

document.add_paragraph(
    "This report was generated automatically."
)

document.save(
    "annual_report.docx"
)

36. Adding Headings and Paragraphs

document.add_heading(
    "Data Analysis",
    level=2
)

document.add_paragraph(
    "The dataset contains "
    "500 records."
)

Heading levels range from 1 through 9, corresponding to Word's heading hierarchy.

37. Formatting Text with Runs

paragraph = document.add_paragraph()

run = paragraph.add_run(
    "Important:"
)

run.bold = True

paragraph.add_run(
    " This section contains "
    "key findings."
)

A run represents a portion of text within a paragraph that can have its own formatting.

38. Font Formatting in Word

paragraph = document.add_paragraph()

run = paragraph.add_run(
    "Python Automation"
)

run.bold = True
run.italic = True
run.font.size = 140000

39. Creating a Word Table

table = document.add_table(
    rows=1,
    cols=3
)

header = table.rows[0].cells

header[0].text = "Student"
header[1].text = "Score"
header[2].text = "Grade"

row = table.add_row().cells

row[0].text = "Alex"
row[1].text = "95"
row[2].text = "A"

document.save(
    "results.docx"
)

40. Creating a Word Report from Python Data

from docx import Document

document = Document()

document.add_heading(
    "Student Results",
    level=1
)

students = [
    ("Alex", 95, "A"),
    ("Jordan", 88, "B+"),
    ("Taylor", 92, "A")
]

table = document.add_table(
    rows=1,
    cols=3
)

headers = table.rows[0].cells

headers[0].text = "Student"
headers[1].text = "Score"
headers[2].text = "Grade"

for name, score, grade in students:

    cells = table.add_row().cells

    cells[0].text = name
    cells[1].text = str(score)
    cells[2].text = grade

document.save(
    "student_results.docx"
)

41. Adding a Page Break

document.add_page_break()

document.add_heading(
    "Appendix",
    level=1
)

42. Adding an Image to Word

document.add_picture(
    "chart.png"
)

A width can be specified using a measurement object.

from docx.shared import Inches

document.add_picture(
    "chart.png",
    width=Inches(5)
)

43. Opening an Existing Word Document

from docx import Document

document = Document(
    "existing_report.docx"
)

for paragraph in document.paragraphs:

    print(
        paragraph.text
    )

44. Updating Word Document Content

for paragraph in document.paragraphs:

    if "PLACEHOLDER" in paragraph.text:

        for run in paragraph.runs:

            run.text = run.text.replace(
                "PLACEHOLDER",
                "Completed"
            )

document.save(
    "updated_report.docx"
)

45. Template-Based Word Automation

A common real-world approach is to create a standard Word template containing placeholders such as:

Name: {{name}}
Department: {{department}}
Score: {{score}}

Python can then populate the document using data from Excel, databases, or APIs.

46. PDF Automation

PDF automation involves reading, extracting, creating, merging, splitting, rotating, and processing PDF documents.

Different libraries solve different PDF problems.

47. Reading a PDF with pypdf

from pypdf import PdfReader

reader = PdfReader(
    "report.pdf"
)

print(
    len(reader.pages)
)

The number of pages can be obtained from the reader.

48. Extracting Text from a PDF

from pypdf import PdfReader

reader = PdfReader(
    "report.pdf"
)

for page in reader.pages:

    text = page.extract_text()

    print(text)

49. Extracting PDF Text with pdfplumber

import pdfplumber

with pdfplumber.open(
    "report.pdf"
) as pdf:

    for page in pdf.pages:

        text = page.extract_text()

        print(text)

pdfplumber is particularly useful when you need more detailed inspection of page content and tabular information.

50. Merging PDF Files

from pypdf import PdfWriter

writer = PdfWriter()

writer.append(
    "report_1.pdf"
)

writer.append(
    "report_2.pdf"
)

with open(
    "combined_report.pdf",
    "wb"
) as output:

    writer.write(output)

This combines the supplied PDF files into one output PDF.

51. Splitting a PDF

from pypdf import PdfReader, PdfWriter

reader = PdfReader(
    "report.pdf"
)

writer = PdfWriter()

writer.add_page(
    reader.pages[0]
)

with open(
    "page_1.pdf",
    "wb"
) as output:

    writer.write(output)

The same pattern can be extended to create separate files for multiple pages.

52. Rotating a PDF Page

from pypdf import PdfReader, PdfWriter

reader = PdfReader(
    "report.pdf"
)

writer = PdfWriter()

page = reader.pages[0]

page.rotate(90)

writer.add_page(page)

with open(
    "rotated.pdf",
    "wb"
) as output:

    writer.write(output)

53. Reading PDF Metadata

from pypdf import PdfReader

reader = PdfReader(
    "report.pdf"
)

print(
    reader.metadata
)

Metadata may include properties such as title, author, subject, creator, and producer when present.

54. Creating a PDF with reportlab

from reportlab.pdfgen import canvas

pdf = canvas.Canvas(
    "report.pdf"
)

pdf.drawString(
    100,
    750,
    "Python Automation Report"
)

pdf.save()

This creates a basic PDF document.

55. Structured PDF Reports with Platypus

ReportLab's Platypus framework is useful for structured documents containing paragraphs, headings, tables, and other flowable elements.

from reportlab.lib.pagesizes import A4
from reportlab.platypus import (
    SimpleDocTemplate,
    Paragraph
)
from reportlab.lib.styles import getSampleStyleSheet

document = SimpleDocTemplate(
    "report.pdf",
    pagesize=A4
)

styles = getSampleStyleSheet()

content = [
    Paragraph(
        "Annual Report",
        styles["Title"]
    ),
    Paragraph(
        "This report was generated "
        "using Python.",
        styles["BodyText"]
    )
]

document.build(content)

56. Creating a PDF Table

from reportlab.platypus import (
    SimpleDocTemplate,
    Table
)

document = SimpleDocTemplate(
    "results.pdf"
)

data = [
    ["Student", "Score"],
    ["Alex", 95],
    ["Jordan", 88],
    ["Taylor", 92]
]

table = Table(data)

document.build([
    table
])

57. Excel → Word → PDF Automation Pipeline

A powerful real-world workflow is to combine multiple document formats.

For example, a Python application could:

1. Read structured records from Excel.
2. Calculate statistics.
3. Create a Word report.
4. Insert tables and charts.
5. Produce a PDF version.
6. Store the final documents in an archive folder.

58. Real-World Automated Reporting Workflow

Raw Data
    ↓
Read Excel / CSV
    ↓
Validate Data
    ↓
Calculate Metrics
    ↓
Generate Excel Summary
    ↓
Generate Word Report
    ↓
Generate PDF
    ↓
Archive Documents

This pattern is common in business intelligence, administration, education, finance, operations, and data science workflows.

59. Standardized Document Naming

Automated document systems should use predictable file names.

sales_report_2026_08.xlsx
sales_report_2026_08.docx
sales_report_2026_08.pdf

Consistent naming makes automated archiving and retrieval easier.

60. Automatically Create Output Folders

from pathlib import Path

output = Path("reports/2026/08")

output.mkdir(
    parents=True,
    exist_ok=True
)

Generated files can then be saved inside the controlled output location.

61. Logging Document Automation

import logging

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s - %(levelname)s - %(message)s"
)

logging.info(
    "Report generation started."
)

logging.info(
    "Excel workbook created."
)

logging.info(
    "PDF generated."
)

Logging provides an audit trail for automated processes.

62. Error Handling in Office Automation

from pathlib import Path

file = Path(
    "input/report.xlsx"
)

try:

    if not file.exists():
        raise FileNotFoundError(
            file
        )

    print(
        "Processing:",
        file
    )

except FileNotFoundError as error:

    print(
        "Input file not found:",
        error
    )

except PermissionError:

    print(
        "Permission denied."
    )

except OSError as error:

    print(
        "File-system error:",
        error
    )

Robust automation should identify failures clearly instead of silently producing incomplete output.

63. Backup Before Modifying Important Documents

from pathlib import Path
import shutil

source = Path(
    "important.xlsx"
)

backup = Path(
    "important_backup.xlsx"
)

shutil.copy2(
    source,
    backup
)

64. Security Considerations

• Validate input file paths.
• Avoid processing arbitrary files from untrusted sources without appropriate controls.
• Do not expose sensitive documents unnecessarily.
• Restrict output directories where appropriate.
• Protect documents containing confidential information.
• Keep credentials and API keys outside source code.
• Maintain backups before destructive operations.
• Log important document-processing actions.

65. Project — Automated Excel Performance Report

Build an automated workbook containing scores, averages, and formatting.

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment


students = [
    ["Alex", 90, 88, 92],
    ["Jordan", 84, 91, 86],
    ["Taylor", 95, 94, 97],
    ["Morgan", 78, 85, 82]
]


workbook = Workbook()

worksheet = workbook.active

worksheet.title = "Performance"


headers = [
    "Student",
    "Python",
    "NumPy",
    "Pandas",
    "Average"
]

worksheet.append(headers)


for student in students:

    row_number = (
        worksheet.max_row + 1
    )

    worksheet.append(
        student + [
            f"=AVERAGE(B{row_number}:D{row_number})"
        ]
    )


for cell in worksheet[1]:

    cell.font = Font(
        bold=True
    )

    cell.alignment = Alignment(
        horizontal="center"
    )


worksheet.freeze_panes = "A2"

worksheet.column_dimensions[
    "A"
].width = 20

for column in ["B", "C", "D", "E"]:

    worksheet.column_dimensions[
        column
    ].width = 15


workbook.save(
    "performance_report.xlsx"
)

66. Project — Automated Word Performance Report

from docx import Document


students = [
    ("Alex", 90, 88, 92),
    ("Jordan", 84, 91, 86),
    ("Taylor", 95, 94, 97)
]


document = Document()

document.add_heading(
    "Performance Report",
    level=1
)

document.add_paragraph(
    "Generated automatically using Python."
)


table = document.add_table(
    rows=1,
    cols=5
)


headers = table.rows[0].cells

headers[0].text = "Student"
headers[1].text = "Python"
headers[2].text = "NumPy"
headers[3].text = "Pandas"
headers[4].text = "Average"


for name, python, numpy, pandas in students:

    average = (
        python + numpy + pandas
    ) / 3

    cells = table.add_row().cells

    cells[0].text = name
    cells[1].text = str(python)
    cells[2].text = str(numpy)
    cells[3].text = str(pandas)
    cells[4].text = f"{average:.2f}"


document.save(
    "performance_report.docx"
)

67. Project — Automated PDF Summary

from reportlab.lib.pagesizes import A4
from reportlab.platypus import (
    SimpleDocTemplate,
    Paragraph,
    Spacer,
    Table
)
from reportlab.lib.styles import (
    getSampleStyleSheet
)


students = [
    ["Alex", 90],
    ["Jordan", 88],
    ["Taylor", 95]
]


document = SimpleDocTemplate(
    "summary.pdf",
    pagesize=A4
)


styles = getSampleStyleSheet()

content = []

content.append(
    Paragraph(
        "Performance Summary",
        styles["Title"]
    )
)

content.append(
    Spacer(1, 20)
)


table_data = [
    ["Student", "Score"]
] + students


content.append(
    Table(table_data)
)


document.build(content)

68. Office Automation Interview Questions

openpyxl.load_workbook(
    "file.xlsx"
)

workbook.save(
    "output.xlsx"
)

69. Examination Questions — MCQs

70. Practical Examination Questions

71. Advanced Challenge — Automated Report Generator

Build a complete reporting application with the following workflow:

1. Read student or business data from Excel.
2. Validate the input data.
3. Calculate totals, averages, and performance indicators.
4. Create a formatted Excel summary.
5. Generate a Word report containing the findings.
6. Generate a PDF version of the report.
7. Store all output documents in a dated folder.
8. Record processing events in a log.
9. Handle missing files and invalid data gracefully.

72. Quick Reference Cheat Sheet

73. Final Takeaways

• openpyxl is a key tool for automating .xlsx workbooks.
• Excel automation can include data entry, updates, formulas, formatting, tables, charts, filtering, and validation.
• python-docx enables programmatic creation and modification of Word documents.
• Word automation is especially useful for template-driven reports.
• pypdf is useful for common PDF manipulation tasks.
• pdfplumber can assist with PDF text and page content extraction.
• reportlab can generate PDF documents and structured reports.
• Office automation becomes significantly more powerful when combined with pathlib, pandas, logging, validation, and error handling.
• Automated document generation should use standardized file names, output directories, backups, and validation.
• Important or destructive document operations should never be performed without appropriate safeguards.

74. Self-Assessment Checklist

Before moving to the next lesson, make sure you can:

• ☐ Create an Excel workbook with openpyxl.
• ☐ Read and update Excel cells.
• ☐ Add rows and columns.
• ☐ Apply basic Excel formatting.
• ☐ Add formulas to worksheets.
• ☐ Create Excel tables and charts.
• ☐ Apply data validation.
• ☐ Create a Word document with Python.
• ☐ Add headings, paragraphs, tables, and images.
• ☐ Modify an existing DOCX document.
• ☐ Read text from PDF files.
• ☐ Merge and split PDFs.
• ☐ Rotate PDF pages.
• ☐ Generate a basic PDF.
• ☐ Build a multi-format automated reporting workflow.
• ☐ Add error handling and logging.
• ☐ Protect important source documents through backups.