openxlsx2 (version 0.7)

write_datatable: Write to a worksheet as an Excel table

Description

Write to a worksheet and format as an Excel table

Usage

write_datatable(
  wb,
  sheet,
  x,
  startCol = 1,
  startRow = 1,
  dims = rowcol_to_dims(startRow, startCol),
  colNames = TRUE,
  rowNames = FALSE,
  tableStyle = "TableStyleLight9",
  tableName = NULL,
  withFilter = TRUE,
  sep = ", ",
  firstColumn = FALSE,
  lastColumn = FALSE,
  bandedRows = TRUE,
  bandedCols = FALSE,
  applyCellStyle = TRUE,
  removeCellStyle = FALSE,
  na.strings = na_strings(),
  inline_strings = TRUE
)

Arguments

wb

A Workbook object containing a worksheet.

sheet

The worksheet to write to. Can be the worksheet index or name.

x

A data frame.

startCol

A vector specifying the starting column to write df

startRow

A vector specifying the starting row to write df

dims

Spreadsheet dimensions that will determine startCol and startRow: "A1", "A1:B2", "A:B"

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.

withFilter

If TRUE, columns with have filters in the first row.

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


The below options correspond to Excel table options:

firstColumn

logical. If TRUE, the first column is bold

lastColumn

logical. If TRUE, the last column is bold

bandedRows

logical. If TRUE, rows are color banded

bandedCols

logical. If TRUE, the columns are color banded

applyCellStyle

apply styles when writing on the sheet

removeCellStyle

if writing into existing cells, should the cell style be removed?

na.strings

Value used for replacing NA values from x. Default na_strings() uses the special #N/A value within the workbook.

inline_strings

write characters as inline strings

Details

columns of x with class Date/POSIXt, currency, accounting, hyperlink, percentage are automatically styled as dates, currency, accounting, hyperlinks, percentages respectively. The string "_openxlsx_NA" is reserved for openxlsx2. If the data frame contains this string, the output will be broken.

See Also

wb_add_worksheet()

write_data()

wb_remove_tables()

wb_get_tables()

Examples

## see package vignettes for further examples.

#####################################################################################
## Create Workbook object and add worksheets
wb <- wb_workbook()
wb$add_worksheet("S1")
wb$add_worksheet("S2")
wb$add_worksheet("S3")

#####################################################################################
## -- write data.frame as an Excel table with column filters
## -- default table style is "TableStyleMedium2"

wb$add_data_table("S1", x = iris)

wb$add_data_table("S2",
  x = mtcars, dims = c("B3"), 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")

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

#####################################################################################
## Additional Header Styling and remove column filters

write_datatable(wb,
  sheet = 1,
  x = iris,
  startCol = 7,
  withFilter = FALSE,
  firstColumn = TRUE,
  lastColumn	= TRUE,
  bandedRows = TRUE,
  bandedCols = TRUE
)

#####################################################################################
## Pre-defined table styles gallery

wb <- wb_workbook(paste0("tableStylesGallery.xlsx"))
wb$add_worksheet("Style Samples")
for (i in 1:21) {
  style <- paste0("TableStyleLight", i)
  write_datatable(wb,
    x = data.frame(style), sheet = 1,
    tableStyle = style, startRow = 1, startCol = i * 3 - 2
  )
}

for (i in 1:28) {
  style <- paste0("TableStyleMedium", i)
  write_datatable(wb,
    x = data.frame(style), sheet = 1,
    tableStyle = style, startRow = 4, startCol = i * 3 - 2
  )
}

for (i in 1:11) {
  style <- paste0("TableStyleDark", i)
  write_datatable(wb,
    x = data.frame(style), sheet = 1,
    tableStyle = style, startRow = 7, startCol = i * 3 - 2
  )
}