Create XLSX Files With R – Part 2: Solutions

####################
#                  #
#    Exercise 1    #
#                  #
####################
library(openxlsx)

wb <- createWorkbook()

####################
#                  #
#    Exercise 2    #
#                  #
####################
options("openxlsx.numFmt" = "0.00")

####################
#                  #
#    Exercise 3    #
#                  #
####################
addWorksheet(wb, sheetName = 'Iris')

####################
#                  #
#    Exercise 4    #
#                  #
####################
writeDataTable(wb, sheet = 'Iris', iris, startCol = 2)

####################
#                  #
#    Exercise 5    #
#                  #
####################
freezePane(wb, sheet = 'Iris', firstRow = T)

####################
#                  #
#    Exercise 6    #
#                  #
####################
green_style <- createStyle(bgFill = "#C6EFCE")
conditionalFormatting(wb, sheet = 'Iris', cols = 'B', rows = 2:151, rule = ">5", style = green_style)

####################
#                  #
#    Exercise 7    #
#                  #
####################
setColWidths(wb, sheet = 'Iris', cols = 2:(1+ncol(iris)), widths = 20)

####################
#                  #
#    Exercise 8    #
#                  #
####################
addWorksheet(wb, sheetName = 'Iris_plot', gridLines = F)

library(ggplot2)
ggplot(iris, aes(Sepal.Width, fill = Species)) +
  geom_density()

insertPlot(wb, sheet = 'Iris_plot', startRow = 2)

####################
#                  #
#    Exercise 9    #
#                  #
####################
hyperlink <- 'https://en.wikipedia.org/wiki/Iris_flower_data_set'

class(hyperlink) <- "hyperlink"
writeData(wb, sheet = 'Iris_plot', hyperlink)


####################
#                  #
#    Exercise 10   #
#                  #
####################
saveWorkbook(wb, "iris.xlsx", overwrite = TRUE)