Exporting Multiple Pages to an Excel Workbook from R

Reports exported from R language can become unwieldy as results quickly start to fill up your destination folders. Could an Excel workbook with multiple sheets help to organize your results in a more shareable and user friendly way? Check out this R package to learn more!

You may have used some of the capabilities of the openxlsx package before, as it includes useful functions such as loadWorkbook() - which does a great job maintaining multiline text cells from source files. What we will take a look at today are 5 separate commands:

1. createWorkbook()
2. addWorksheet
3. writeData()
4. insertPlot()
5. saveWorkbook()

You can check out the overview and full documentation for reference, but first let's take a look at how it works. Follow along in R to test this out for yourself!

For our sample data, we will be using the iris dataset that comes with R. Let's load the datasets library as well as tidyverse and openxlsx.

library(datasets)
library(openxlsx)
library(tidyverse)

We are now ready to load the iris data and prepare our empty workbook. The createWorkbook() function allows for you to name an empty workbook object. This workbook resides in your R environment and you will be able to add text, tables, and plots within named workbook pages that you create.

Think of it like a new empty Excel project that has not yet been saved. We will also provide a destination path where our report will be saved later on.

# data from the datasets library
data(iris)


# an empty workbook object, to be added to and then printed to file
iris_wb <- createWorkbook() 


# Where the output will be printed 
outLocation <- "C:/Desktop"

Now that we have our data loaded and our empty workbook waiting, let's add our first page!

The addWorksheet() function adds a blank worksheet to your workbook object, giving it a name of your choice. It works like this: addWorksheet(yourWorkbookName, yourNewWorksheetName). Remember that our workbook is named "iris_wb". We will make a new sheet named "Summary". This name will show up on the tabs when you print your workbook to file.

addWorksheet(iris_wb, "Summary") # Add a blank worksheet and give it a name

Right now our new sheet is empty... so how about we add some content? To save a summary of the iris dataset to our new "Summary" page, we will use the writeData() function. This takes in the name of the workbook, the name of the worksheet, and what you would like to write. The startCol and startRow options let us place the content where we would like it to appear on the worksheet.

# Save a summary table to a variable
sumIris <- summary(iris) 

# Write to the worksheet
writeData(iris_wb, "Summary", sumIris, startRow = 1, startCol = 1) 

Great! Let's take a look at how to add a plot to the workbook. This relies on the plot FIRST being created and displayed within R. The insertPlot() function will save the currently displayed plot.

Recall how we added our first named worksheet. Our second will be added in the same way. We will create a table of summarized petal lengths using tidyverse's summarise(), and then plot the petal lengths. We can add BOTH our table AND the new plot to our new worksheet as long as we use the startCol and startRow options to place them on the page. Test it out!

addWorksheet(iris_wb, "Petal Length")


petalTable <- iris %>% group_by(Species) %>% 
summarise(Ave.Petal.Length = mean(Petal.Length))


# Make a plot
   # The petal length on y axis as a continuous variable
petalPlot <- plot(y = as.numeric(iris$Petal.Length), 

   # The species on x axis as a factor / discrete variable
                  x = as.factor(iris$Species),   

   # 3 of the base colors available in R                  
                  col = 3:6 ,             
    
   # Labels for x and y axis                     
                  xlab = "", ylab = "Petal Length",  

   # Text size              
                  cex.axis = 0.6, 

   # Main title and color                                
                  title(main="Length of Petals", col.main="black") 
)

# Write the table
writeData(iris_wb, "Petal Length", petalTable, startRow = 1, startCol = 1) 


# Insert the plot to the same page
insertPlot(iris_wb, "Petal Length", fileType = "png",
           width = 10, height = 6,  # make the size of the plot
           startCol = 6) # Print to the right of the table by starting at column 6

Good job! All that is left is to save our workbook to file. This is done using the saveWorkbook() function, which takes in the workbook object name, the destination path + file name and extension, and the option to overwrite (useful if you run the same code multiple times or are making updates).

saveWorkbook(iris_wb, file = paste0(outLocation, "/", Sys.Date(), " Fun with Iris Data.xlsx"), overwrite = TRUE)

Congratulations! You just printed a multi-sheet workbook with summary, table, and plot all to one neatly organized file!

Have fun playing around with the options that openxlsx has to offer :-)