Description Usage Arguments Details See Also Examples
Write to a worksheet and format as an Excel table
1 2 3 4 5 | writeDataTable(wb, sheet, x, startCol = 1, startRow = 1, xy = NULL,
colNames = TRUE, rowNames = FALSE, tableStyle = "TableStyleLight9",
tableName = NULL, headerStyle = NULL, withFilter = TRUE,
keepNA = FALSE, sep = ", ", stack = FALSE, firstColumn = FALSE,
lastColumn = FALSE, bandedRows = TRUE, bandedCols = FALSE)
|
wb |
A Workbook object containing a worksheet. |
sheet |
The worksheet to write to. Can be the worksheet index or name. |
x |
A dataframe. |
startCol |
A vector specifying the starting column to write df |
startRow |
A vector specifying the starting row to write df |
xy |
An alternative to specifying startCol and startRow individually. A vector of the form c(startCol, startRow) |
colNames |
If |
rowNames |
If |
tableStyle |
Any excel table style name or "none" (see "formatting" vignette). |
tableName |
name of table in workbook. The table name must be unique. |
headerStyle |
Custom style to apply to column names. |
withFilter |
If |
keepNA |
If |
sep |
Only applies to list columns. The separator used to collapse list columns to a character vector e.g. sapply(x$list_column, paste, collapse = sep). |
stack |
If |
firstColumn |
logical. If TRUE, the first column is bold |
lastColumn |
logical. If TRUE, the last column is bold |
bandedRows |
logical. If TRUE, rows are colour banded |
bandedCols |
logical. If TRUE, the columns are colour banded |
columns of x with class Date/POSIXt, currency, accounting, hyperlink, percentage are automatically styled as dates, currency, accounting, hyperlinks, percentages respectively.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | ## see package vignettes for further examples.
#####################################################################################
## Create Workbook object and add worksheets
wb <- createWorkbook()
addWorksheet(wb, "S1")
addWorksheet(wb, "S2")
addWorksheet(wb, "S3")
#####################################################################################
## -- write data.frame as an Excel table with column filters
## -- default table style is "TableStyleMedium2"
writeDataTable(wb, "S1", x = iris)
writeDataTable(wb, "S2", x = mtcars, xy = c("B", 3), rowNames = TRUE,
tableStyle = "TableStyleLight9")
df <- data.frame("Date" = Sys.Date()-0:19,
"T" = TRUE, "F" = FALSE,
"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)
## openxlsx will apply default Excel styling for these classes
class(df$Cash) <- c(class(df$Cash), "currency")
class(df$Cash2) <- c(class(df$Cash2), "accounting")
class(df$hLink) <- "hyperlink"
class(df$Percentage) <- c(class(df$Percentage), "percentage")
class(df$TinyNumbers) <- c(class(df$TinyNumbers), "scientific")
writeDataTable(wb, "S3", x = df, startRow = 4, rowNames = TRUE, tableStyle = "TableStyleMedium9")
#####################################################################################
## Additional Header Styling and remove column filters
writeDataTable(wb, sheet = 1, x = iris, startCol = 7, headerStyle = createStyle(textRotation = 45),
withFilter = FALSE)
#####################################################################################
## Save workbook
## Open in excel without saving file: openXL(wb)
saveWorkbook(wb, "writeDataTableExample.xlsx", overwrite = TRUE)
#####################################################################################
## Pre-defined table styles gallery
wb <- createWorkbook(paste0("tableStylesGallery.xlsx"))
addWorksheet(wb, "Style Samples")
for(i in 1:21) {
style <- paste0("TableStyleLight", i)
writeDataTable(wb, x=data.frame(style), sheet=1, tableStyle=style, startRow = 1, startCol = i*3-2)
}
for(i in 1:28) {
style <- paste0("TableStyleMedium", i)
writeDataTable(wb, x=data.frame(style), sheet=1, tableStyle=style, startRow = 4, startCol = i*3-2)
}
for(i in 1:11) {
style <- paste0("TableStyleDark", i)
writeDataTable(wb, x=data.frame(style), sheet=1, tableStyle=style, startRow = 7, startCol = i*3-2)
}
## openXL(wb)
saveWorkbook(wb, file = "tableStylesGallery.xlsx", overwrite = TRUE)
|
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.