Write a gt table into a worksheet

Lays a gt table out as cells: the heading, the column spanners, the column labels, the stub, the row groups, the body, any summary rows, the footnotes and the source notes, one after another in a single rectangular block starting at dims.

Usage

wb_add_gt(
  wb,
  x,
  sheet = current_sheet(),
  dims = "A1",
  numeric = TRUE,
  convert_date = TRUE,
  col_widths = "auto",
  row_heights = NULL,
  ignore_errors = TRUE,
  gap = 1L,
  features = TRUE,
  freeze = FALSE,
  ...
)

Arguments

wb: A wbWorkbook object, as returned by openxlsx2::wb_workbook().
x: A gt_tbl object, or a gt_group as returned by gt::gt_group() or gt::gt_split(). A group is written one table after another down the sheet.
sheet: The worksheet to write to. Defaults to the current sheet.
dims: Cell reference of the top left corner of the table, for example "B2".
numeric: Write numbers as numbers where the displayed format can be reproduced. Set to FALSE to write every number as text.
convert_date: Write dates and times as spreadsheet dates. A column is converted when its text is exactly what one of gt's date or time styles gives for the stored value, with the matching spreadsheet format; month and weekday names then follow the language of the spreadsheet software. With FALSE, or where no style reproduces the text, the printed text is written as it is.
col_widths: "auto" measures the rendered text and sizes the columns to fit it, a numeric vector sets the widths directly, and NULL leaves them alone. Widths set with gt::cols_width() always win.
row_heights: NULL, the default, leaves the spreadsheet software to size the rows. "gt" sets each row from the padding gt would have used, and a numeric vector sets the heights directly. Both also center the text vertically, since spreadsheet software aligns to the bottom of a cell and gt pads evenly. Rows with wrapped text keep the software's own sizing, which a fixed height would clip. Footnotes and source notes are merged across the table, and spreadsheet software does not fit the height of merged cells, so their height is estimated from the table width.
ignore_errors: Mark text cells whose content looks like a number or a date, so spreadsheet software stops flagging them.
gap: Blank rows left between the tables of a gt_group. Ignored for a single table.
features: What to write besides the values. TRUE, the default, is all of them; FALSE writes values only. Otherwise a character vector of any of "font", "fill", "border", "numfmt", "merge" and "link", so a table that goes wrong in one respect can still be written in every other.
freeze: Freeze panes so the heading and the stub stay in view while scrolling. TRUE freezes below the heading and beside the stub, a length-two vector c(row, col) freezes at a cell of your choosing, and FALSE, the default, leaves the sheet alone.
...: Currently unused.

Value

The workbook, invisibly. The input workbook is not modified; a clone is returned, as elsewhere in openxlsx2.

Details

Everything gt applies before rendering is already in place when the cells are written, because gtxlsx reads the table gt has built rather than repeating the work: every fmt_*() and sub_*(), the cols_merge_*() family, text_transform(), data_color(), summary_rows() and the footnote marks. Styling set with gt::tab_style() and gt::tab_options() becomes fonts, fills, alignment and borders; markup inside a cell (bold, italic, superscripts, line breaks) becomes rich text.

Anything gt draws as a picture cannot be written to a cell. gt::fmt_image() and gt::cols_nanoplot() leave the cell empty, gt::fmt_icon() and gt::fmt_flag() fall back to their label text.

Row striping

A striped table gets a fill on every body row, the striping color on one and table.background.color on the next. Spreadsheet software leaves an unfilled cell transparent, so filling only half the rows would show the banding as detached blocks rather than a continuous column.

The color comes from gt, and gt's default is white. On a worksheet with a colored background that white will cover the tint under the table. Set table.background.color to match, or turn striping off, if that matters.

Links

fmt_url() and fmt_email() leave an anchor in the cell, and that becomes a hyperlink on the cell. The text shown is whatever gt put there.

Numbers versus text

With numeric = TRUE a column is written as numbers whenever a spreadsheet number format can reproduce exactly what gt displays. $1,234.50 becomes the value 1234.5 with the format "$"#,##0.00, so the sheet stays usable for arithmetic. Columns gt has scaled or suffixed (1.2K for 1200) cannot be reproduced that way and stay text; those cells are marked so spreadsheet software does not flag them as numbers stored as text. The decision is made per column: if one cell cannot be reproduced, for example a cell with a footnote mark or one replaced with a dash by text_transform(), the whole column is written as text. Missing values shown with sub_missing() are the exception and leave the numbers alone.

Examples

library(gt)
library(openxlsx2)

tbl <- gt(data.frame(item = c("Cash", "Debt"), amount = c(1204.5, -3910)))
tbl <- fmt_currency(tbl, columns = "amount", decimals = 2)
tbl <- tab_header(tbl, title = "Balance")

wb <- wb_workbook()$add_worksheet()
wb <- wb_add_gt(wb, tbl, dims = "B2")

wb_to_df(wb, col_names = FALSE)
#>         B      C
#> 2 Balance   <NA>
#> 3    item amount
#> 4    Cash 1204.5
#> 5    Debt  -3910