Use R to create chart in Excel sheet

Question

In order to create excel files from my R output, I use the openXLSX program. I haven't been able to find a way to include Excel charts in an Excel workbook. I discovered that Python provides an Excel file creation module with a class for including Excel charts.

Is R capable of accomplishing this?

Answer 1

Here is a solution utilizing the XLConnect package. Just a quick reminder that it creates new files rather than adding sheets or charts to already existing files and relies on chart templates that you must build in advance.

It is comprised of two stages:

1. Preparing Excel templates for the type of charts you would like to use.
2. Updating the template files with the data from R as required each time.

First step: Create Excel templates for the different types of charts you'll need. All of the templates can be found in either one file (in various sheets) or numerous ones. Include the types of charts you'll need in the template, but use "named ranges" rather than specific cells when referencing them.

Second step: use an adaptation of the following code such that it suits your needs. Mostly use your own data frame and update the reference in the createName function.

library(XLConnect) # load library
wb1 <- loadWorkbook(filename = "edit_chart_via_R_to_excel.xlsx") 
new.df <- data.frame(Type = c("Ford", "Hyundai", "BMW", "Other"),
          Number = c(45, 35, 25, 15)) # sample data
writeWorksheet(wb1, data = new.df, sheet = "Sheet1", 
               startRow = 1, startCol = 1, header = TRUE)
# update named ranges for the chart's use.
# Note that 
# "Sheet1!$A$2:$A$5" and "Sheet1!$B$2:$B$5" 
# should change according to the data you are updating
createName(wb1, "bar_names", "Sheet1!$A$2:$A$5", overwrite = TRUE) 
createName(wb1, "values", "Sheet1!$B$2:$B$5", overwrite = TRUE)
saveWorkbook(wb1)