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