Writing Excel Spreadsheets to Disk with R and Python

code
rtip
python
excel
Author

Steven P. Sanderson II, MPH

Published

June 23, 2024

Introduction

When working with data, exporting your results to an Excel file can be very handy. Today, I’ll show you how to write the iris dataset to an Excel file using R and Python. We will explore three R packages: writexl, openxlsx, and xlsx, and the openpyxl library in Python. Let’s dive in!

Writing Excel Files in R

First, let’s start with R. We’ll use the well-known iris dataset and write it to a temporary file using three different packages.

Using writexl

The writexl package is straightforward and easy to use for writing data frames to Excel files.

# Install and load the writexl package
install.packages("writexl")
library(writexl)

# Write iris dataset to a temporary file
writexl::write_xlsx(iris, tempfile())

The write_xlsx function does exactly what it says: it writes your data frame to an Excel file. The tempfile() function creates a temporary file, which is useful for quick testing without cluttering your directory.

Using openxlsx

The openxlsx package provides more flexibility and additional features compared to writexl.

# Install and load the openxlsx package
install.packages("openxlsx")
library(openxlsx)

# Write the iris dataset to a temporary file
openxlsx::write.xlsx(iris, tempfile())

With openxlsx, you can directly write the data frame to an Excel file using the write.xlsx function, making the process simple and efficient.

Using xlsx

The xlsx package is another option that can be useful, though it requires Java.

# Install and load the xlsx package
install.packages("xlsx")
library(xlsx)

# Write the iris dataset to a temporary file
xlsx::write.xlsx(iris, paste0(tempfile(), ".xlsx"))

write.xlsx from the xlsx package works similarly to the previous functions but requires the .xlsx extension to be explicitly added to the temporary file name.

Writing Excel Files in Python

Now, let’s see how to achieve the same with Python using the openpyxl library.

# Install openpyxl if you haven't already
!pip install openpyxl

import openpyxl

# Load an existing workbook
workbook = openpyxl.load_workbook("example.xlsx")

# Add a new seet
workbook.create_sheet(title = "Sheet1")

sheet_name = "Sheet1"

sheet = workbook[sheet_name]

sheet["A1"] = "Hello, World!"

workbook.save("example.xlsx")

Here is a concise breakdown of what this script does:

  • Installs the openpyxl library (if necessary).
  • Imports the openpyxl library.
  • Loads an existing Excel workbook named example.xlsx.
  • Creates a new sheet titled “Sheet1” in the workbook.
  • Assigns the value “Hello, World!” to cell A1 of the new sheet.
  • Saves the changes back to “example.xlsx”.

Try It Yourself!

Feel free to try these code snippets on your own. Exporting data to Excel is a common task, and knowing different ways to do it can be very useful in your data analysis toolkit. If you want to learn more about integrating Excel with R and Python, consider purchasing the book “Extending Excel with Python and R” for in-depth tutorials and advanced techniques.

Happy coding!