Skip to contents

Add a data table to a worksheet

Source: R/class-workbook-wrappers.R
wb_add_data_table.Rd

Add data to a worksheet and format as an Excel table.

Usage

wb_add_data_table(
  wb,
  sheet = current_sheet(),
  x,
  dims = wb_dims(start_row, start_col),
  start_col = 1,
  start_row = 1,
  col_names = TRUE,
  row_names = FALSE,
  table_style = "TableStyleLight9",
  table_name = NULL,
  with_filter = TRUE,
  sep = ", ",
  first_column = FALSE,
  last_column = FALSE,
  banded_rows = TRUE,
  banded_cols = FALSE,
  apply_cell_style = TRUE,
  remove_cell_style = FALSE,
  na.strings = na_strings(),
  inline_strings = TRUE,
  total_row = FALSE,
  ...
)

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

dims

Spreadsheet cell range that will determine start_col and start_row: "A1", "A1:B2", "A:B"

start_col

A vector specifying the starting column to write x to.

start_row

A vector specifying the starting row to write x to.

col_names

If TRUE, column names of x are written.

row_names

If TRUE, the row names of x are written.

table_style

Any table style name or "none" (see vignette("openxlsx2_style_manual"))

table_name

Name of table in workbook. The table name must be unique.

with_filter

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:
Figure: table_options.png

first_column

logical. If TRUE, the first column is bold.

last_column

logical. If TRUE, the last column is bold.

banded_rows

logical. If TRUE, rows are color banded.

banded_cols

logical. If TRUE, the columns are color banded.

apply_cell_style

Should we write cell styles to the workbook

remove_cell_style

keep the cell style?

na.strings

Value used for replacing NA values from x. Default looks if options(openxlsx2.na.strings) is set. Otherwise na_strings() uses the special #N/A value within the workbook.

inline_strings

write characters as inline strings

total_row

logical. With the default FALSE no total row is added.

...

additional arguments

Details

Formulae written using wb_add_formula() to a Workbook object will not get picked up by read_xlsx(). This is because only the formula is written and left to Excel to evaluate the formula when the file is opened in Excel. The string "_openxlsx_NA" is reserved for openxlsx2. If the data frame contains this string, the output will be broken.

Supported classes are data frames, matrices and vectors of various types and everything that can be converted into a data frame with as.data.frame(). Everything else that the user wants to write should either be converted into a vector or data frame or written in vector or data frame segments. This includes base classes such as table, which were coerced internally in the predecessor of this package.

Even vectors and data frames can consist of different classes. Many base classes are covered, though not all and far from all third-party classes. When data of an unknown class is written, it is handled with as.character(). It is not possible to write character nodes beginning with <r> or <r/>. Both are reserved for internal functions. If you need these. You have to wrap the input string in fmt_txt().

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

Functions wb_add_data() and wb_add_data_table() behave quite similar. The distinction is that the latter creates a table in the worksheet that can be used for different kind of formulas and can be sorted independently, though is less flexible than basic cell regions.

Modify total row argument

It is possible to further tweak the total row. In addition to the default FALSE possible values are TRUE (the xlsx file will create column sums each variable).

In addition it is possible to tweak this further using a character string with one of the following functions for each variable: "average", "count", "countNums", "max", "min", "stdDev", "sum", "var". It is possible to leave the cell empty "none" or to create a text input using a named character with name text like: c(text = "Total"). It's also possible to pass other spreadsheet software functions if they return a single value and hence "SUM" would work too.

See also

Other worksheet content functions: col_widths-wb, filter-wb, grouping-wb, named_region-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Examples

wb <- wb_workbook()$add_worksheet()$
  add_data_table(
    x = as.data.frame(USPersonalExpenditure),
    row_names = TRUE,
    total_row = c(text = "Total", "none", "sum", "sum", "sum", "SUM")
  )