How to Write Data to Excel Spreadsheets Using Python: Openpyxl vs. Pandas?

How to Write Data to an Excel Spreadsheet with Python

Many developers encounter the need to export data from their programs to Excel spreadsheets. This guide will explore the different methods and packages available in Python for accomplishing this task.

Choosing an Approach

When selecting an approach, consider the specific requirements of your project. Factors to keep in mind include the availability of Office on target computers and the length and structure of your data.

Using Openpyxl

Openpyxl is a popular Python package for reading and writing to Excel spreadsheets. It offers flexibility and in-depth control over cell formatting and styling. However, keep in mind that Openpyxl requires Office to be installed on the target system, which may not always be feasible.

Utilizing Pandas

For cases where you don't have Office installed or your data is complex, Pandas emerges as an excellent option. Pandas allows you to manipulate and represent data in versatile data structures. By converting your data into a DataFrame and utilizing the to_excel method, you can effortlessly save it to an Excel file.

Example Use Case

Consider a scenario where you have two lists of values and three string variables. You need to create an Excel file with a specific layout, as illustrated in the image below:

Using Openpyxl, you can create this layout as follows:

import openpyxl

data = {
    "Display": [1, 2, 3],
    "Dominance": [2.34, 4.346, 4.234],
    "Test": [2.3, 3.2, 1.7]
}

workbook = openpyxl.Workbook()
sheet = workbook.active

# Set column widths
sheet.column_dimensions["A"].width = 10
sheet.column_dimensions["B"].width = 15

# Insert headings
sheet["A1"] = "Category"
sheet["B1"] = "Values"

# Iterate over keys and values
for key, values in data.items():
    sheet[f"A{sheet.max_row + 1}"] = key
    for i, value in enumerate(values, 2):
        sheet[f"B{sheet.max_row + i}"] = value

workbook.save("output.xlsx")

Alternatively, using Pandas:

import pandas as pd

data = {
    "display": [1, 2, 3],
    "dominance": [2.34, 4.346, 4.234],
    "test": [2.3, 3.2, 1.7]
}

df = pd.DataFrame(data)
df.to_excel("output.xlsx", index=False)

Formatting Cells

To format specific cells as scientific or number with precision, you can utilize the style method in Pandas:

df['dominance'] = df['dominance'].apply(lambda x: "%.10f" % x)
df.to_excel("output.xlsx", index=False)

This will preserve the full precision of your values while formatting them as scientific notation.

The above is the detailed content of How to Write Data to Excel Spreadsheets Using Python: Openpyxl vs. Pandas?. For more information, please follow other related articles on the PHP Chinese website!