1--- 2title: "Formating with xlsx" 3author: "Alexander Walker, Philipp Schauberger" 4date: "`r Sys.Date()`" 5output: rmarkdown::html_vignette 6vignette: > 7 %\VignetteIndexEntry{Formating with xlsx} 8 %\VignetteEngine{knitr::rmarkdown} 9 %\VignetteEncoding{UTF-8} 10--- 11 12 13## Formatting with writeData and writeDataTable 14 15 16```{r include=TRUE,tidy=TRUE, eval = FALSE,highlight=TRUE} 17 18## data.frame to write 19df <- data.frame("Date" = Sys.Date()-0:4, 20 "Logical" = c(TRUE, FALSE, TRUE, TRUE, FALSE), 21 "Currency" = paste("$",-2:2), 22 "Accounting" = -2:2, 23 "hLink" = "https://CRAN.R-project.org/", 24 "Percentage" = seq(-1, 1, length.out=5), 25 "TinyNumber" = runif(5) / 1E9, stringsAsFactors = FALSE) 26 27class(df$Currency) <- "currency" 28class(df$Accounting) <- "accounting" 29class(df$hLink) <- "hyperlink" 30class(df$Percentage) <- "percentage" 31class(df$TinyNumber) <- "scientific" 32 33## Formatting can be applied simply through the write functions 34## global options can be set to further simplify things 35options("openxlsx.borderStyle" = "thin") 36options("openxlsx.borderColour" = "#4F81BD") 37 38## create a workbook and add a worksheet 39wb <- createWorkbook() 40addWorksheet(wb, "writeData auto-formatting") 41 42writeData(wb, 1, df, startRow = 2, startCol = 2) 43writeData(wb, 1, df, startRow = 9, startCol = 2, borders = "surrounding") 44writeData(wb, 1, df, startRow = 16, startCol = 2, borders = "rows") 45writeData(wb, 1, df, startRow = 23, startCol = 2, borders ="columns") 46writeData(wb, 1, df, startRow = 30, startCol = 2, borders ="all") 47 48## headerStyles 49hs1 <- createStyle(fgFill = "#4F81BD", halign = "CENTER", textDecoration = "Bold", 50 border = "Bottom", fontColour = "white") 51 52writeData(wb, 1, df, startRow = 16, startCol = 10, headerStyle = hs1, 53 borders = "rows", borderStyle = "medium") 54 55## to change the display text for a hyperlink column just write over those cells 56writeData(wb, sheet = 1, x = paste("Hyperlink", 1:5), startRow = 17, startCol = 14) 57 58 59## writing as an Excel Table 60 61addWorksheet(wb, "writeDataTable") 62writeDataTable(wb, 2, df, startRow = 2, startCol = 2) 63writeDataTable(wb, 2, df, startRow = 9, startCol = 2, tableStyle = "TableStyleLight9") 64writeDataTable(wb, 2, df, startRow = 16, startCol = 2, tableStyle = "TableStyleLight2") 65writeDataTable(wb, 2, df, startRow = 23, startCol = 2, tableStyle = "TableStyleMedium21") 66 67openXL(wb) ## opens a temp version 68``` 69 70 71## Use of pre-defined table styles 72 73The 'tableStyle' argument in writeDataTable can be any of the predefined tableStyles in Excel. 74 75![](tableStyles.PNG) 76 77## Date Formatting 78 79 80```{r include=TRUE,tidy=TRUE, eval = FALSE,highlight=TRUE} 81 82# data.frame of dates 83dates <- data.frame("d1" = Sys.Date() - 0:4) 84for(i in 1:3) dates <- cbind(dates, dates) 85names(dates) <- paste0("d", 1:8) 86 87## Date Formatting 88wb <- createWorkbook() 89addWorksheet(wb, "Date Formatting", gridLines = FALSE) 90writeData(wb, 1, dates) ## write without styling 91 92## openxlsx converts columns of class "Date" to Excel dates with the format given by 93getOption("openxlsx.dateFormat", "mm/dd/yyyy") 94 95## this can be set via (for example) 96options("openxlsx.dateFormat" = "yyyy/mm/dd") 97## custom date formats can be made up of any combination of: 98## d, dd, ddd, dddd, m, mm, mmm, mmmm, mmmmm, yy, yyyy 99 100## numFmt == "DATE" will use the date format specified by the above 101addStyle(wb, 1, style = createStyle(numFmt = "DATE"), rows = 2:11, cols = 1, gridExpand = TRUE) 102 103## some custom date format examples 104sty <- createStyle(numFmt = "yyyy/mm/dd") 105addStyle(wb, 1, style = sty, rows = 2:11, cols = 2, gridExpand = TRUE) 106 107sty <- createStyle(numFmt = "yyyy/mmm/dd") 108addStyle(wb, 1, style = sty, rows = 2:11, cols = 3, gridExpand = TRUE) 109 110sty <- createStyle(numFmt = "yy / mmmm / dd") 111addStyle(wb, 1, style = sty, rows = 2:11, cols = 4, gridExpand = TRUE) 112 113sty <- createStyle(numFmt = "ddddd") 114addStyle(wb, 1, style = sty, rows = 2:11, cols = 5, gridExpand = TRUE) 115 116sty <- createStyle(numFmt = "yyyy-mmm-dd") 117addStyle(wb, 1, style = sty, rows = 2:11, cols = 6, gridExpand = TRUE) 118 119sty <- createStyle(numFmt = "mm/ dd yyyy") 120addStyle(wb, 1, style = sty, rows = 2:11, cols = 7, gridExpand = TRUE) 121 122sty <- createStyle(numFmt = "mm/dd/yy") 123addStyle(wb, 1, style = sty, rows = 2:11, cols = 8, gridExpand = TRUE) 124 125setColWidths(wb, 1, cols = 1:10, widths = 23) 126 127## The default date format used in writeData and writeDataTable can be set with: 128options("openxlsx.dateFormat" = "dd/mm/yyyy") 129writeData(wb, "Date Formatting", dates, startRow = 8, borders = "rows") 130options("openxlsx.dateFormat" = "yyyy-mm-dd") 131writeData(wb, "Date Formatting", dates, startRow = 15) 132 133saveWorkbook(wb, "Date Formatting.xlsx", overwrite = TRUE) 134 135``` 136 137## DateTime Formatting 138 139The conversion from POSIX to Excel datetimes is dependent on the timezone you are in. 140If POSIX values are being written incorrectly, try setting the timezone with (for example) 141 142 143```{r include=TRUE,tidy=TRUE, eval = FALSE,highlight=TRUE} 144 145Sys.setenv(TZ = "Australia/Sydney") 146 147dateTimes <- data.frame("d1" = Sys.time() - 0:4*10000) 148for(i in 1:2) dateTimes <- cbind(dateTimes, dateTimes) 149names(dateTimes) <- paste0("d", 1:4) 150 151## POSIX Formatting 152wb <- createWorkbook() 153addWorksheet(wb, "DateTime Formatting", gridLines = FALSE) 154writeData(wb, 1, dateTimes) ## write without styling 155 156## openxlsx converts columns of class "POSIxt" to Excel datetimes with the format given by 157getOption("openxlsx.datetimeFormat", "yyyy/mm/dd hh:mm:ss") 158 159## this can be set via (for example) 160options("openxlsx.datetimeFormat" = "yyyy-mm-dd hh:mm:ss") 161## custom datetime formats can be made up of any combination of: 162## d, dd, ddd, dddd, m, mm, mmm, mmmm, mmmmm, yy, yyyy, h, hh, m, mm, s, ss, AM/PM 163 164## numFmt == "LONGDATE" will use the date format specified by the above 165long_date_style <- createStyle(numFmt = "LONGDATE") 166addStyle(wb, 1, style = long_date_style, rows = 2:11, cols = 1, gridExpand = TRUE) 167 168## some custom date format examples 169sty <- createStyle(numFmt = "yyyy/mm/dd hh:mm:ss AM/PM") 170addStyle(wb, 1, style = sty, rows = 2:11, cols = 2, gridExpand = TRUE) 171 172sty <- createStyle(numFmt = "hh:mm:ss AM/PM") 173addStyle(wb, 1, style = sty, rows = 2:11, cols = 3, gridExpand = TRUE) 174 175sty <- createStyle(numFmt = "hh:mm:ss") 176addStyle(wb, 1, style = sty, rows = 2:11, cols = 4, gridExpand = TRUE) 177 178setColWidths(wb, 1, cols = 1:4, widths = 30) 179 180## The default date format used in writeData and writeDataTable can be set with: 181options("openxlsx.datetimeFormat" = "yyyy/mm/dd hh:mm:ss") 182writeData(wb, "DateTime Formatting", dateTimes, startRow = 8, borders = "rows") 183 184options("openxlsx.datetimeFormat" = "hh:mm:ss AM/PM") 185writeDataTable(wb, "DateTime Formatting", dateTimes, startRow = 15) 186 187saveWorkbook(wb, "DateTime Formatting.xlsx", overwrite = TRUE) 188openXL("DateTime Formatting.xlsx") 189 190``` 191 192## Conditional Formatting 193 194```{r include=TRUE,tidy=TRUE, eval = FALSE,highlight=TRUE} 195 196wb <- createWorkbook() 197addWorksheet(wb, "cellIs") 198addWorksheet(wb, "Moving Row") 199addWorksheet(wb, "Moving Col") 200addWorksheet(wb, "Dependent on 1") 201addWorksheet(wb, "Duplicates") 202addWorksheet(wb, "containsText") 203addWorksheet(wb, "colourScale", zoom = 30) 204addWorksheet(wb, "databar") 205 206negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE") 207posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE") 208 209## rule applies to all each cell in range 210writeData(wb, "cellIs", -5:5) 211writeData(wb, "cellIs", LETTERS[1:11], startCol=2) 212conditionalFormatting(wb, "cellIs", cols=1, rows=1:11, rule="!=0", style = negStyle) 213conditionalFormatting(wb, "cellIs", cols=1, rows=1:11, rule="==0", style = posStyle) 214 215## highlight row dependent on first cell in row 216writeData(wb, "Moving Row", -5:5) 217writeData(wb, "Moving Row", LETTERS[1:11], startCol=2) 218conditionalFormatting(wb, "Moving Row", cols=1:2, rows=1:11, rule="$A1<0", style = negStyle) 219conditionalFormatting(wb, "Moving Row", cols=1:2, rows=1:11, rule="$A1>0", style = posStyle) 220 221## highlight column dependent on first cell in column 222writeData(wb, "Moving Col", -5:5) 223writeData(wb, "Moving Col", LETTERS[1:11], startCol=2) 224conditionalFormatting(wb, "Moving Col", cols=1:2, rows=1:11, rule="A$1<0", style = negStyle) 225conditionalFormatting(wb, "Moving Col", cols=1:2, rows=1:11, rule="A$1>0", style = posStyle) 226 227## highlight entire range cols X rows dependent only on cell A1 228writeData(wb, "Dependent on 1", -5:5) 229writeData(wb, "Dependent on 1", LETTERS[1:11], startCol=2) 230conditionalFormatting(wb, "Dependent on 1", cols=1:2, rows=1:11, rule="$A$1<0", style = negStyle) 231conditionalFormatting(wb, "Dependent on 1", cols=1:2, rows=1:11, rule="$A$1>0", style = posStyle) 232 233## highlight duplicates using default style 234writeData(wb, "Duplicates", sample(LETTERS[1:15], size = 10, replace = TRUE)) 235conditionalFormatting(wb, "Duplicates", cols = 1, rows = 1:10, type = "duplicates") 236 237## cells containing text 238fn <- function(x) paste(sample(LETTERS, 10), collapse = "-") 239writeData(wb, "containsText", sapply(1:10, fn)) 240conditionalFormatting(wb, "containsText", cols = 1, rows = 1:10, type = "contains", rule = "A") 241 242## colourscale colours cells based on cell value 243df <- read.xlsx(system.file("readTest.xlsx", package = "openxlsx"), sheet = 4) 244writeData(wb, "colourScale", df, colNames=FALSE) ## write data.frame 245 246## rule is a vector or colours of length 2 or 3 (any hex colour or any of colours()) 247## If rule is NULL, min and max of cells is used. Rule must be the same length as style or NULL. 248conditionalFormatting(wb, "colourScale", cols=1:ncol(df), rows=1:nrow(df), 249 style = c("black", "white"), 250 rule = c(0, 255), 251 type = "colourScale") 252 253setColWidths(wb, "colourScale", cols = 1:ncol(df), widths = 1.07) 254setRowHeights(wb, "colourScale", rows = 1:nrow(df), heights = 7.5) 255 256## Databars 257writeData(wb, "databar", -5:5) 258conditionalFormatting(wb, "databar", cols = 1, rows = 1:12, type = "databar") ## Default colours 259 260saveWorkbook(wb, "conditionalFormattingExample.xlsx", TRUE) 261 262openXL(wb) 263 264``` 265 266 267 268## Numeric Formatting 269 270 271numeric columns styling can be set using the numFmt parameter in createStyle or a default can be 272set with, for example, options("openxlsx.numFmt" = "#,#0.00") 273 274 275```{r include=TRUE,tidy=TRUE, eval = FALSE,highlight=TRUE} 276 277options("openxlsx.numFmt" = NULL) 278wb <- createWorkbook() 279addWorksheet(wb, "Sheet 1") 280df <- data.frame(matrix(12.987654321, ncol = 7, nrow = 5)) ## data.frame to write 281df[ ,6:7] <- df[ ,6:7]*1E6 282 283## Set column 1 class to "comma" to get comma separated thousands 284class(df$X1) <- "comma" 285 286writeData(wb, 1, df) 287s <- createStyle(numFmt = "0.0") 288addStyle(wb, 1, style = s, rows = 2:6, cols = 2, gridExpand = TRUE) 289 290s <- createStyle(numFmt = "0.00") 291addStyle(wb, 1, style = s, rows = 2:6, cols = 3, gridExpand = TRUE) 292 293s <- createStyle(numFmt = "0.000") 294addStyle(wb, 1, style = s, rows = 2:6, cols = 4, gridExpand = TRUE) 295 296s <- createStyle(numFmt = "#,##0") 297addStyle(wb, 1, style = s, rows = 2:6, cols = 5, gridExpand = TRUE) 298 299s <- createStyle(numFmt = "#,##0.00") 300addStyle(wb, 1, style = s, rows = 2:6, cols = 6, gridExpand = TRUE) 301 302s <- createStyle(numFmt = "$ #,##0.00") 303addStyle(wb, 1, style = s, rows = 2:6, cols = 7, gridExpand = TRUE) 304 305## set a default number format for numeric columns of data.frames 306options("openxlsx.numFmt" = "$* #,#0.00") 307writeData(wb, 1, x = data.frame("Using Default Options" = rep(2345.1235, 5)), startCol = 9) 308 309setColWidths(wb, 1, cols = 1:10, widths = 15) 310 311## Using default numFmt to round to 2 dp (Any numeric column will be affected) 312addWorksheet(wb, "Sheet 2") 313df <- iris; df[, 1:4] <- df[1:4] + runif(1) 314writeDataTable(wb, sheet = 2, x = df) 315writeData(wb, sheet = 2, x = df, startCol = 7) 316writeData(wb, sheet = 2, x = df, startCol = 13, borders = "rows") 317 318## To stop auto-formatting numerics set 319options("openxlsx.numFmt" = NULL) 320addWorksheet(wb, "Sheet 3") 321writeDataTable(wb, sheet = 3, x = df) 322 323openXL(wb) 324``` 325 326 327