DEV Community

Cover image for Working with XlsxWriter in Python
≀Paulo Portela
≀Paulo Portela

Posted on

Working with XlsxWriter in Python

Introduction

XlsxWriter is a Python module that allows you to create Excel files with formatting, charts, and more. In this chapter, we will explore various aspects of using XlsxWriter, focusing on creating, formatting, and manipulating Excel documents.

Index

  • Creating a Document
  • Creating a Sheet
  • Adding Data from a Dictionary
  • Adding Data to a Cell
  • Changing Cell Format
  • Adding Formulas
  • Reading Data

Examples

Creating a Document

To start working with XlsxWriter, you first need to create a workbook object. This workbook will act as your Excel file.

import xlsxwriter

workbook = xlsxwriter.Workbook(filename="electronics_data.xlsx")
Enter fullscreen mode Exit fullscreen mode

Creating a Sheet

Once you have a workbook, you can add sheets to it. Each sheet will represent a separate tab in the Excel file.

worksheet = workbook.add_worksheet(name="Circuits")
Enter fullscreen mode Exit fullscreen mode

Adding Data from a Dictionary

You can populate your sheet with data from a dictionary. This is useful for representing tabular data.

data = {"Component": ["Resistor", "Capacitor", "Inductor"],
        "Value": [100, 10, 0.5]}

row = 0
for col, header in enumerate(data.keys()):
    worksheet.write(row, col, header)

for row, (component, value) in enumerate(zip(data["Component"], data["Value"]), start=1):
    worksheet.write(row, 0, component)
    worksheet.write(row, 1, value)
Enter fullscreen mode Exit fullscreen mode

Adding Data to a Cell

You can also add data directly to a specific cell in the worksheet.

worksheet.write("A5", "Voltage")
worksheet.write("B5", 5)
Enter fullscreen mode Exit fullscreen mode

Changing Cell Format

Formatting cells allows you to control how the data appears in the Excel file.

format_bold = workbook.add_format(properties={"bold": True})
worksheet.write("A1", "Component", format_bold)

format_percentage = workbook.add_format(properties={"num_format": "0.0%"})
worksheet.write("B6", 0.1, format_percentage)
Enter fullscreen mode Exit fullscreen mode

Adding Formulas

XlsxWriter supports adding formulas to cells, which can be useful for calculations.

worksheet.write_formula("C2", formula="=B2*2")
Enter fullscreen mode Exit fullscreen mode

Closing the Workbook

Don't forget to close the workbook once you have finished writing to it. This step is essential for saving the changes and finalising the Excel file.

workbook.close()
Enter fullscreen mode Exit fullscreen mode

Conclusion

With XlsxWriter, you can create, format, and manipulate Excel files in Python with ease. By mastering its capabilities, you can generate professional-looking spreadsheets tailored to your specific needs. Experiment with the provided examples and explore further to unlock the full potential of XlsxWriter.

For more detailed information and advanced usage of XlsxWriter, refer to the official documentation at XlsxWriter Documentation.

Top comments (0)