openxlsx (version 3.0.0)

writeDataTable: Write to a worksheet and format as a table

Description

Write to a worksheet and format as a table

Usage

writeDataTable(wb, sheet, x, startCol = 1, startRow = 1, xy = NULL, colNames = TRUE, rowNames = FALSE, tableStyle = "TableStyleLight9", tableName = NULL, headerStyle = NULL, withFilter = TRUE, keepNA = 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 withFilters in the first row.

keepNA

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

Details

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

See Also

addWorksheet

writeData

Examples

## see package vignette for further examples.
wb <- createWorkbook()
addWorksheet(wb, "S1")
addWorksheet(wb, "S2")
addWorksheet(wb, "S3")

## write data formatted as excel table with table withFilters
# 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" = "http://cran.r-project.org/",
                 "Percentage" = seq(0, 1, length.out=20),
                 "TinyNumbers" = runif(20) / 1E9,  stringsAsFactors = FALSE)

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

writeDataTable(wb, "S3", x = df, startRow = 4, rowNames=TRUE, tableStyle="TableStyleMedium9")

## Additional headerStyling and remove withFilters
writeDataTable(wb, sheet = 1, x = iris, startCol = 7, headerStyle = createStyle(textRotation = 45),
withFilter = FALSE)

saveWorkbook(wb, "writeDataTableExample.xlsx", overwrite = TRUE)