Insert charts to excel through xlsx package in R

Question

For a project I have been working on I make use of the package xlsx in R to create and design reports in Excel sheets. So far, any time I wanted to insert a scatter plot/histogram/density plot to my sheet I created a ggplot2 object, saved it as an image and inserted it in to the sheet as an image.

My question is, is there any way to add an excel chart to a sheet using the package xlsx (or any other R package). If, for instance, I have two columns and a scatter plot of one column against the second one, I would like the scatter plot to be an Excel chart such that changing the data in the columns manually would result in a change in the scatter plot. Any suggestions?

Answer 1

I find this code very helpful when I faced similiar problem. It creates a report pasting data frame in one sheet and chart in other. Sadly it does paste chart as a picture. Thus the second part.

In second part we edit the data in Excel without changing any style options or anything else. Now only thing you have to do is manually create Excel chart. It is not the perfect soultion, but it made my life a bit easier. Hope it helps.

library(xlsx)

df <- iris

# create a new workbook for outputs
#++++++++++++++++++++++++++++++++++++
# possible values for type are : "xls" and "xlsx"
wb<-createWorkbook(type="xlsx")
# Define some cell styles
#++++++++++++++++++++++++++++++++++++
# Title and sub title styles
TITLE_STYLE <- CellStyle(wb)+ Font(wb,  heightInPoints=16, 
                                   color="blue", isBold=TRUE, underline=1)
SUB_TITLE_STYLE <- CellStyle(wb) + 
  Font(wb,  heightInPoints=14,
       isItalic=TRUE, isBold=FALSE)
# Styles for the data table row/column names
TABLE_ROWNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE)
TABLE_COLNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE) +
  Alignment(wrapText=TRUE, horizontal="ALIGN_CENTER") +
  Border(color="black", position=c("TOP", "BOTTOM"), 
         pen=c("BORDER_THIN", "BORDER_THICK")) 
# Create a new sheet in the workbook
#++++++++++++++++++++++++++++++++++++
sheet <- createSheet(wb, sheetName="US State Facts")
#++++++++++++++++++++++++
# Helper function to add titles
#++++++++++++++++++++++++
# - sheet : sheet object to contain the title
# - rowIndex : numeric value indicating the row to 
#contain the title
# - title : the text to use as title
# - titleStyle : style object to use for title
xlsx.addTitle<-function(sheet, rowIndex, title, titleStyle){
  rows <-createRow(sheet,rowIndex=rowIndex)
  sheetTitle <-createCell(rows, colIndex=1)
  setCellValue(sheetTitle[[1,1]], title)
  setCellStyle(sheetTitle[[1,1]], titleStyle)
}
# Add title and sub title into a worksheet
#++++++++++++++++++++++++++++++++++++
# Add title
xlsx.addTitle(sheet, rowIndex=1, title="US State Facts",
              titleStyle = TITLE_STYLE)
# Add sub title
xlsx.addTitle(sheet, rowIndex=2, 
              title="Data sets related to the 50 states of USA.",
              titleStyle = SUB_TITLE_STYLE)
# Add a table into a worksheet
#++++++++++++++++++++++++++++++++++++
addDataFrame(df, sheet , startRow=3, startColumn=1, 
             colnamesStyle = TABLE_COLNAMES_STYLE,
             rownamesStyle = TABLE_ROWNAMES_STYLE)
# Change column width
setColumnWidth(sheet, colIndex=c(1:ncol(state.x77)), colWidth=11)
# Add a plot into a worksheet
#++++++++++++++++++++++++++++++++++++
# create a png plot
png("boxplot.png", height=800, width=800, res=250, pointsize=8)
boxplot(count ~ spray, data = InsectSprays,
        col = "blue")
dev.off()
# Create a new sheet to contain the plot
sheet <-createSheet(wb, sheetName = "boxplot")
# Add title
xlsx.addTitle(sheet, rowIndex=1, title="Box plot using InsectSprays data",
              titleStyle = TITLE_STYLE)
# Add the plot created previously
addPicture("boxplot.png", sheet, scale = 1, startRow = 4,
           startColumn = 1)
# remove the plot from the disk
res<-file.remove("boxplot.png")
# Save the workbook to a file...
#++++++++++++++++++++++++++++++++++++
saveWorkbook(wb, "r-xlsx-report-example.xlsx")



# Here you have to restart R; XLConnect overlaps with xlsx --------------------------------------------------

require(XLConnect)

wb <- loadWorkbook("r-xlsx-report-example.xlsx")
imena <- getSheets(wb) #sheetsname
df <-  readWorksheet(wb, 
                     sheet = 1
                     ,startRow = 3)
df$Sepal.Length[df$Sepal.Length < 6] <- "my new data"

writeWorksheetToFile("r-xlsx-report-example.xlsx",df, sheet = imena[1],styleAction = XLC$STYLE_ACTION.NONE,header=T,startRow=3)
#saveWorkbook(wb,"r-xlsx-report-example.xlsx")