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")

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")

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)

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)

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)

Adding Formulas

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

worksheet.write_formula("C2", formula="=B2*2")

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()

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.