Introduction

In the previous topic, you learned how to read data from Excel files and use it in Selenium automation. However, automation frameworks often need to perform the opposite task as well—writing data back to Excel.

Writing Excel files is commonly used to store test execution results, generated data, captured values, or application outputs. Instead of manually recording test results, automation scripts can automatically update Excel sheets after execution.

Python provides the openpyxl library for creating, modifying, and saving Microsoft Excel (.xlsx) files. It allows automation frameworks to write new data, update existing cells, create worksheets, and generate reports.

In this tutorial, you’ll learn what writing Excel files means, why it is important, how to write data using openpyxl, and how it can be used in Selenium automation frameworks.

What is Writing Excel Files?

Writing Excel Files is the process of creating or updating an Excel spreadsheet by storing data generated during automation test execution.

Instead of manually recording information, the automation framework writes the required data directly into an Excel file.

For example:

        Selenium Test
              │
              ▼
      Generate Test Result
              │
              ▼
    Write Data using openpyxl
              │
              ▼
       Excel File (.xlsx)

This helps maintain execution records automatically.

Why Write Data to Excel?

Writing data to Excel provides several advantages:

Automatically stores test execution results.
Records pass and fail statuses.
Saves generated application data.
Creates reusable test reports.
Eliminates manual documentation.
Makes result analysis easier.
Supports Data-Driven Testing workflows.
Improves automation framework organization.

Example

from openpyxl import Workbook, load_workbook


def test_writing_excel_files(tmp_path):
    excel_path = tmp_path / "results.xlsx"

    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "Results"

    sheet.append(["case", "status"])
    sheet.append(["simple_form_demo", "passed"])

    workbook.save(excel_path)

    loaded = load_workbook(excel_path)
    sheet = loaded["Results"]

    assert sheet["A2"].value == "simple_form_demo"
    assert sheet["B2"].value == "passed"
    assert excel_path.exists()

Understanding the Code

Import Required Modules

from openpyxl import Workbook, load_workbook

The required classes from the openpyxl library are imported.

Workbook is used to create a new Excel workbook.
load_workbook is used to open an existing Excel file.

Create the Excel File

excel_path = tmp_path / "results.xlsx"

workbook = Workbook()
sheet = workbook.active

A new Excel workbook named results.xlsx is created.

The active worksheet is selected for writing data.

Rename the Worksheet

sheet.title = "Results"

The default worksheet name is changed to Results.

Giving worksheets meaningful names makes the Excel file easier to understand.

Write the Header Row

sheet.append(["case", "status"])

The first row is added as the header.

The worksheet now contains:

Case	Status

These headers describe the information stored in each column.

Write Test Data

sheet.append(["simple_form_demo", "passed"])

A new row is added below the header.

This row stores:

Test Case Name
Test Execution Status

The worksheet now becomes:

Case	Status
simple_form_demo	passed

Save the Workbook

workbook.save(excel_path)

The workbook is saved to the specified file location.

Without saving, the written data would not be stored permanently.

Open the Saved Workbook

loaded = load_workbook(excel_path)
sheet = loaded["Results"]

The Excel file is opened again.

The worksheet named Results is selected for verification.

Verify the Stored Data

assert sheet["A2"].value == "simple_form_demo"

The value stored in cell A2 is verified.

Expected value:

simple_form_demo

assert sheet["B2"].value == "passed"

The value stored in cell B2 is verified.

Expected value:

passed

Both assertions confirm that the data was written successfully.

Verify the File Exists

assert excel_path.exists()

This assertion checks whether the Excel file has been created successfully.

If the file exists, the test passes.

Practical Example

Suppose an e-commerce website has hundreds of automated test cases.

After each test execution, the automation framework writes the following information into an Excel file:

Test Case Name
Execution Time
Pass/Fail Status
Error Message (if any)

The Excel file becomes a simple execution report that testers can review later.

Automation Testing Example

Consider an online banking application.

After validating multiple customer transactions, the automation framework writes the following details into an Excel spreadsheet:

Transaction ID
Customer Name
Transaction Status
Test Result

This allows testers to review execution results without manually recording them.

Real-World Example

Writing Excel files is commonly used in:

Banking Applications
E-commerce Websites
CRM Systems
ERP Applications
Healthcare Portals
Insurance Systems
HR Management Systems
Enterprise Automation Frameworks

Typical information written to Excel includes test results, execution status, generated IDs, transaction details, customer information, reports, and audit records.

Advantages of Writing Excel Files

Automatically stores test results.
Eliminates manual result recording.
Creates reusable execution reports.
Improves result analysis.
Supports Data-Driven Testing.
Stores generated application data.
Makes automation frameworks more organized.
Simplifies reporting and documentation.

Common Mistakes Beginners Make

Forgetting to Save the Workbook

Writing data alone is not enough.

Always call save() after making changes.

Writing Data to the Wrong Worksheet

Always verify that the correct worksheet is selected before writing data.

Overwriting Existing Data

Appending new rows is usually safer than replacing existing values unless updating data intentionally.

Using Incorrect Cell References

Verify row and column positions before reading or writing data.

Best Practices

Use meaningful worksheet names.
Add descriptive column headers.
Save the workbook after every update.
Store execution results in a structured format.
Keep test data separate from test results.
Validate written data whenever necessary.
Organize Excel files inside a dedicated testdata or reports folder.

Conclusion

Writing Excel Files is an important feature in Selenium automation frameworks. Using the openpyxl library, automation scripts can create Excel files, write execution results, store generated data, and maintain test reports automatically. This improves reporting, reduces manual effort, and makes automation frameworks more efficient and maintainable.

Frequently Asked Questions (FAQs)

Why do automation frameworks write data to Excel?

Automation frameworks write data to Excel to store test results, generated values, execution status, and reports automatically.

Which Python library is commonly used to write Excel files?

The openpyxl library is widely used to create, modify, and save Microsoft Excel (.xlsx) files.

Can existing Excel files be updated?

Yes.

openpyxl can open existing Excel workbooks, update cell values, add new rows, create worksheets, and save the changes.

Is writing Excel files used in professional Selenium frameworks?

Yes.

Many Selenium automation frameworks use Excel files to store execution results, reports, and Data-Driven Testing information.

Can automation write both test data and test results to Excel?

Yes.

Excel files can be used both for reading input data and writing execution results.

Key Takeaways

Writing Excel Files stores automation-generated data in Excel spreadsheets.
The openpyxl library is commonly used to create and update Excel files.
Automation frameworks use Excel to save execution results and reports.
Always save the workbook after writing data.
Writing Excel files improves reporting, documentation, and framework maintainability.