Python Write Excel File

To write to an Excel file using Python, you normally make use of libraries such as openpyxl or pandas. Here’s a step-by-step explanation using both approaches:

Using openpyxl

openpyxl is a Python library specifically designed for working with Excel files in the .xlsx format.

Installation

Install the library if you haven’t already:

pip install openpyxl

Steps to Write to an Excel File

1. Import the library:

from openpyxl import Workbook

2. Create a new workbook:

workbook = Workbook()

3. Access the active worksheet:

sheet = workbook.active

4. Write data to the sheet: Use row and column indexing or assign values directly:

sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["C1"] = "City"

# Adding more rows of data
sheet.append(["Alice", 30, "New York"])
sheet.append(["Bob", 25, "Los Angeles"])
sheet.append(["Charlie", 35, "Chicago"])

5. Save the workbook:

workbook.save("example.xlsx")

Using pandas

pandas is a versatile data manipulation library that can handle Excel files when used with openpyxl or xlsxwriter.

Installation

Install pandas and a compatible Excel engine:

pip install pandas openpyxl

Steps to Write to an Excel File

1. Import the library:

import pandas as pd

2. Prepare the data: Create a DataFrame, which is a table-like structure in pandas:

data = {
    "Name": ["Alice", "Bob", "Charlie"],
    "Age": [30, 25, 35],
    "City": ["New York", "Los Angeles", "Chicago"]
}

df = pd.DataFrame(data)

3. Write the DataFrame to an Excel file: Use the to_excel method:

df.to_excel("example_pandas.xlsx", index=False)

• The index=False parameter prevents pandas from writing the default index column.

Comparison

Example Projects

1. With openpyxl

• Writing a formatted table having styles, color and formulas.
• Creating multiple data on multiple sheets.

2. With pandas

• Write Large datasets coming from database/CSV
• Creating Excel reports from analytics script.