Previous chapter
Write Excel FilesStyling with openxlsx
Next chapter

Global Styling Options

openxlsx supports global styling options which can be set as

options("openxlsx.borderColour" = "#4F80BD")
options("openxlsx.borderStyle" = "thin")
options("openxlsx.dateFormat" = "mm/dd/yyyy")
options("openxlsx.datetimeFormat" = "yyyy-mm-dd hh:mm:ss")
options("openxlsx.numFmt" = NULL) ## For default style rounding of numeric columns

Let’s write a data.frame with global options set/unset and compare the output:

df <- data.frame("Date" = Sys.Date()-0:19, "LogicalT" = TRUE,
"Time" = Sys.time()-0:19*60*60,
"Cash" = paste("$",1:20), "Cash2" = 31:50,
"hLink" = "https://CRAN.R-project.org/",
"Percentage" = seq(0, 1, length.out=20),
"TinyNumbers" = runif(20) / 1E9, stringsAsFactors = FALSE)

write.xlsx(df, "writeXLSX1.xlsx")
write.xlsx(df, file = "writeXLSXTable1.xlsx", asTable = TRUE)

See also vignette("Introduction", package = "openxlsx").

Setting Options using class

Additionally, we can set the output styling using class.

class(df$Cash) <- "currency"
class(df$Cash2) <- "accounting"
class(df$hLink) <- "hyperlink"
class(df$Percentage) <- "percentage"
class(df$TinyNumbers) <- "scientific"

write.xlsx(df, "writeXLSX2.xlsx")
write.xlsx(df, file = "writeXLSXTable2.xlsx", asTable = TRUE)

See also vignette("Introduction", package = "openxlsx").

Additional styling

We Define a style for column headers

hs <- createStyle(fontColour = "#ffffff", fgFill = "#4F80BD",
halign = "center", valign = "center", textDecoration = "Bold",
border = "TopBottomLeftRight", textRotation = 45)
write.xlsx(iris, file = "writeXLSX3.xlsx", borders = "rows", headerStyle = hs)
write.xlsx(iris, file = "writeXLSX4.xlsx", borders = "columns", headerStyle = hs)

Include Plots

library((ggplot2)

wb <- createWorkbook()

## Add a worksheet
addWorksheet(wb, "Sheet 1", gridLines = FALSE) 

## create plot objects
p1 <- qplot(mpg, data=mtcars, geom="density",
  fill=as.factor(gear), alpha=I(.5), main="Distribution of Gas Mileage")
p2 <- qplot(age, circumference,
  data = Orange, geom = c("point", "line"), colour = Tree)

## Insert currently displayed plot to sheet 1, row 1, column 1
print(p1) #plot needs to be showing
insertPlot(wb, 1, width = 5, height = 3.5, fileType = "png", units = "in")

## Insert plot 2
print(p2)
insertPlot(wb, 1, xy = c("J", 2), width = 16, height = 10,  fileType = "png", units = "cm")

## Save workbook
saveWorkbook(wb, "insertPlotExample.xlsx", overwrite = TRUE)

Example from help page, see also ?insertPlot.

Basic Workbook

library(ggplot2)
## set default border Colour and style
wb <- createWorkbook()
options("openxlsx.borderColour" = "#4F80BD")
options("openxlsx.borderStyle" = "thin")
modifyBaseFont(wb, fontSize = 10, fontName = "Arial Narrow")
addWorksheet(wb, sheetName = "Motor Trend Car Road Tests", gridLines = FALSE)
addWorksheet(wb, sheetName = "Iris", gridLines = FALSE)
## sheet 1
freezePane(wb, sheet = 1, firstRow = TRUE, firstCol = TRUE) ## freeze first row and column
writeDataTable(wb, sheet = 1, x = mtcars,colNames = TRUE, rowNames = TRUE, tableStyle = "TableStyleLight9")
setColWidths(wb, sheet = 1, cols = "A", widths = 18)
## write iris data.frame as excel table
writeDataTable(wb, sheet = 2, iris, startCol = "K", startRow = 2)
qplot(data=iris, x = Sepal.Length, y= Sepal.Width, colour = Species)
insertPlot(wb, 2, xy=c("B", 16)) ## insert plot at cell B16
means <- aggregate(x = iris[,-5], by = list(iris$Species), FUN = mean)
vars <- aggregate(x = iris[,-5], by = list(iris$Species), FUN = var)
## write group means
headSty <- createStyle(fgFill="#DCE6F1", halign="center", border = "TopBottomLeftRight")
writeData(wb, 2, x = "Iris dataset group means", startCol = 2, startRow = 2)
writeData(wb, 2, x = means, startCol = "B", startRow=3, borders="rows", headerStyle = headSty)
## write group variances
writeData(wb, 2, x = "Iris dataset group variances", startCol = 2, startRow = 9)
writeData(wb, 2, x= vars, startCol = "B", startRow=10, borders="columns", headerStyle = headSty)
setColWidths(wb, 2, cols=2:6, widths = 12) ## width is recycled for each col
setColWidths(wb, 2, cols=11:15, widths = 15)
# style mean & variance table headers
s1 <- createStyle(fontSize=14, textDecoration=c("bold", "italic"))
addStyle(wb, 2, style = s1, rows=c(2,9), cols=c(2,2))
saveWorkbook(wb, "basics.xlsx", overwrite = TRUE) ## save to working directory

See also vignette("Introduction", package = "openxlsx").