Add a pivot table to a worksheet

The data must be specified using wb_data() to ensure the function works. The sheet will be empty unless it is opened in spreadsheet software. Find more details in the section about pivot tables in the openxlsx2 book.

Usage

wb_add_pivot_table(
  wb,
  x,
  sheet = next_sheet(),
  dims = "A3",
  filter,
  rows,
  cols,
  data,
  fun,
  params,
  pivot_table,
  slicer,
  timeline
)

Arguments

wb

A Workbook object containing a #' worksheet.

x

A data.frame that inherits the wb_data class.

sheet

A worksheet containing a #'

dims

The worksheet cell where the pivot table is placed

filter

The column name(s) of x used for filter.

rows

The column name(s) of x used as rows

cols

The column names(s) of x used as cols

data

The column name(s) of x used as data

fun

A vector of functions to be used with data. See Details for the list of available options.

params

A list of parameters to modify pivot table creation. See Details for available options.

pivot_table

An optional name for the pivot table

slicer, timeline

Any additional column name(s) of x used as slicer/timeline

Details

The pivot table is not actually written to the worksheet, therefore the cell region has to remain empty. What is written to the workbook is something like a recipe how the spreadsheet software has to construct the pivot table when opening the file.

It is possible to add slicers to the pivot table. For this the pivot table has to be named and the variable used as slicer, must be part of the selected pivot table names (cols, rows, filter, or slicer). If these criteria are matched, a slicer can be added using wb_add_slicer().

Be aware that you should always test on a copy if a param argument works with a pivot table. Not only to check if the desired effect appears, but first and foremost if the file loads. Wildly mixing params might brick the output file and cause spreadsheet software to crash.

fun can be any of AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP.

show_data_as can be any of normal, difference, percent, percentDiff, runTotal, percentOfRow, percentOfCol, percentOfTotal, index.

It is possible to calculate data fields if the formula is assigned as a variable name for the field to calculate. This would look like this: data = c("am", "disp/cyl" = "New")

Possible params arguments are listed below. Pivot tables accepts more parameters, but they were either not tested or misbehaved (probably because we misunderstood how the parameter should be used).

Boolean arguments:

• apply_alignment_formats

• apply_number_formats

• apply_border_formats

• apply_font_formats

• apply_pattern_formats

• apply_width_height_formats

• no_style

• compact

• outline

• compact_data

• row_grand_totals

• col_grand_totals

Table styles accepting character strings:

• auto_format_id: style id as character in the range of 4096 to 4117

• table_style: a predefined (pivot) table style "TableStyleMedium23"

• show_data_as: accepts character strings as listed above

Miscellaneous:

• numfmt: accepts vectors of the form c(formatCode = "0.0%")

• choose: select variables in the form of a named logical vector like c(agegp = 'x > "25-34"') for the esoph dataset.

• sort_item: named list of index or character vectors

See also

wb_data()

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), 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()

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_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Examples

wb <- wb_workbook() %>% wb_add_worksheet() %>% wb_add_data(x = mtcars)

df <- wb_data(wb, sheet = 1)

wb <- wb %>%
  # default pivot table
  wb_add_pivot_table(df, dims = "A3",
    filter = "am", rows = "cyl", cols = "gear", data = "disp"
  ) %>%
  # with parameters
  wb_add_pivot_table(df,
    filter = "am", rows = "cyl", cols = "gear", data = "disp",
    params = list(no_style = TRUE, numfmt = c(formatCode = "##0.0"))
  )