Spark Online Training by Edureka

writeDataTable: Write to a worksheet as an Excel table

Description Usage Arguments Details Author(s) See Also Examples

Description

Write to a worksheet and format as an Excel table

Usage

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)

Arguments

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 specifiying the starting column to write df

startRow

A vector specifiying 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 TRUE, column names of x are written.

rowNames

If TRUE, row names of x are written.

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 TRUE, columns with have filters in the first row.

keepNA

If TRUE, NA values are converted to #N/A in Excel else NA cells will be empty.

sep

Only applies to list columns. The seperator used to collapse list columns to a character vector e.g. sapply(x$list_column, paste, collapse = sep).


The below options correspond to Excel table options:
Figure: table\_options.png

stack

If TRUE the new style is merged with any existing cell styles. If FALSE, any existing style is replaced by the new style.

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

Details

columns of x with class Date/POSIXt, currency, accounting, hyperlink, percentage are automatically styled as dates, currency, accounting, hyperlinks, percentages respectively.

Author(s)

Alexander Walker

See Also

addWorksheet

writeData

removeTable

getTables

Examples

 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
## 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)


Search within the openxlsx package
Search all R packages, documentation and source code

Questions? Problems? Suggestions? or email at ian@mutexlabs.com.

Please suggest features or report bugs with the GitHub issue tracker.

All documentation is copyright its authors; we didn't write any of that.