Package {gtxlsx}

gtxlsx: Write 'gt' and HTML Tables into 'openxlsx2' Workbooks

Description

Turns a 'gt' table into a range of cells in an 'openxlsx2' workbook, keeping the heading, column spanners, row groups, stub, summary rows, footnotes and the styling set through 'gt'. Numbers stay numbers wherever a spreadsheet number format can reproduce what 'gt' shows. A second entry point does the same for a plain HTML table, so output from other table packages can be written to a worksheet as well; that path needs nothing beyond 'openxlsx2'.

Author(s)

Maintainer: Jan Marvin Garbuszus jan.garbuszus@ruhr-uni-bochum.de

Authors:

• Jan Marvin Garbuszus jan.garbuszus@ruhr-uni-bochum.de

See Also

Useful links:

• https://janmarvin.github.io/gtxlsx/

• https://github.com/JanMarvin/gtxlsx

• Report bugs at https://github.com/JanMarvin/gtxlsx/issues

Look at the pieces of a built gt table

Description

Runs gt's own build step and hands back the result as plain data frames and lists: the rendered body, the column definitions, the stub, the row groups, the spanners, the styles, the footnotes and the table options.

Usage

gtxlsx_extract(x, context = "html")

Arguments

Details

This is the input wb_add_gt() works from. It is exported mainly so you can see why a table came out the way it did, or check what a gt feature leaves behind before it reaches the worksheet.

Value

A named list with the elements body, data, boxhead, stub, groups_rows, row_groups, spanners, heading, stubhead, styles, footnotes, source_notes, summary and options.

Examples

library(gt)

tbl <- gt(data.frame(a = 1:2, b = c(1.5, 2.5)))
tbl <- fmt_number(tbl, columns = "b", decimals = 1)

g <- gtxlsx_extract(tbl)
g$body
g$boxhead$var

Write a gt table into a worksheet

Description

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,
  col_widths = "auto",
  row_heights = NULL,
  ignore_errors = TRUE,
  gap = 1L,
  features = TRUE,
  freeze = FALSE,
  ...
)

Arguments

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, and gt::fmt_url() keeps the link text but not the hyperlink.

Value

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

Row striping

A striped table gets a fill on every body row, the striping colour 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 colour comes from gt, and gt's default is white. On a worksheet with a coloured 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.

See Also

wb_add_html() for tables that are already HTML, and gtxlsx_extract() to see the pieces wb_add_gt() works from.

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)

Write an HTML table into a worksheet

Description

Reads the first (or any) ⁠<table>⁠ of an HTML fragment or document and writes it as cells. colspan and rowspan become merged ranges, ⁠<style>⁠ rules and ⁠style=⁠ attributes become fills, fonts, alignment and borders, and markup inside a cell becomes rich text. Titles and notes that sit beside the table rather than inside it are picked up as well.

Usage

wb_add_html(
  wb,
  x,
  sheet = current_sheet(),
  dims = "A1",
  which = 1L,
  numeric = TRUE,
  col_widths = "auto",
  ignore_errors = TRUE,
  context = TRUE,
  features = TRUE,
  freeze = FALSE,
  ...
)

Arguments

Details

This is the general path for tables that are already HTML, whatever produced them. openxlsx2 is the only thing it needs; gt is a suggestion and nothing on this path uses it.

Old fashioned presentational markup is understood too: bgcolor, align, valign, width, nowrap and ⁠<table border>⁠.

Value

The workbook, invisibly.

How much CSS is understood

Enough for tables, not enough to call it a browser. A selector is matched by walking its components against the cell and the elements above it, so ⁠table.report td.total⁠, ⁠thead td⁠ and div > td all mean what they say. Nested rules, ⁠:is()⁠, custom properties and !important are handled, as are the positional pseudo-classes ⁠:first-child⁠, ⁠:last-child⁠, ⁠:only-child⁠ and ⁠:nth-child()⁠.

An ⁠<a href>⁠ inside a cell becomes a hyperlink on that cell, and the whole cell is what becomes clickable: a spreadsheet has no way to link part of a cell's text. The first usable anchor is taken, since a cell holds one target, and a link that only points at a fragment of the source page is skipped. Either of those produces a warning naming how many were dropped.

What is not: sibling combinators (+, ~), state pseudo-classes and ⁠::before⁠ cause a rule to be skipped rather than guessed at, attribute selectors match on the tag alone, ⁠@media⁠ conditions are ignored, and stylesheets pulled in with ⁠<link>⁠ are not fetched.

Properties with no spreadsheet equivalent, such as gradients, letter spacing and rounded corners, are dropped. ⁠<img>⁠ and ⁠<svg>⁠ leave an empty cell, and ⁠<a href>⁠ keeps its text but not the link.

See Also

wb_add_gt(), which goes straight from a gt object and keeps more of the structure.

Examples

library(openxlsx2)

html <- paste0(
  "<style>th { background-color: #204060; color: white; }</style>",
  "<table><tr><th>Account</th><th>Change</th></tr>",
  "<tr><td>Cash</td><td>1,204.50</td></tr></table>"
)

wb <- wb_workbook()$add_worksheet()
wb <- wb_add_html(wb, html, dims = "A1")

wb_to_df(wb, col_names = FALSE)

Write an lt table into a worksheet

Description

The lt package builds its HTML in JavaScript when the page is viewed, so there is no table to read on the R side. This helper asks lt to bake the table to static HTML first and then hands the result to wb_add_html().

Usage

wb_add_lt(wb, x, sheet = current_sheet(), dims = "A1", method = "auto", ...)

Arguments

Details

Baking needs Node.js or a Chromium based browser on the machine.

Value

The workbook, invisibly.

Examples

# needs the lt package and a Node.js or browser install
## Not run: 
library(openxlsx2)

tbl <- lt::lt(data.frame(a = c("x", "y"), n = c(1234.5, 67.89)))
tbl <- lt::lt_format(tbl, ~ n, decimals = 2, big_mark = ",")

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

## End(Not run)

Turn a worksheet range back into a gt table (experimental)

Description

Reads a range of cells and builds a gt object from it: full width merged rows at the top become the heading, partly merged rows above the labels become spanners, full width merged rows at the bottom become source notes, and per-cell fills, fonts and alignment are translated into gt::tab_style() calls.

Usage

wb_to_gt(
  wb,
  sheet = current_sheet(),
  dims = NULL,
  styles = TRUE,
  structure = TRUE,
  ...
)

Arguments

Value

A gt_tbl object.

Please read this before using it

This function is a development toy, not a finished feature. It exists because the reverse direction was interesting to try, and it has had only light testing: a handful of sheets, no round trip guarantees. Treat its output as a starting point you will edit, not as a faithful copy, and expect the details to change or the function to be withdrawn.

A worksheet simply does not record most of what a gt table knows. Row groups, the stub, footnote marks and number formats do not come back: groups arrive as ordinary rows, the stub as a column named after its letter, footnote marks glued to the text they mark, and ⁠$115,900⁠ as the bare number 115900 with no fmt_currency() behind it. Column names are made unique, so repeated labels gain a suffix.

Examples

library(openxlsx2)

wb <- wb_workbook()$add_worksheet()
wb$add_data(x = data.frame(a = c("x", "y"), b = c(1, 2)))

tbl <- wb_to_gt(wb, dims = "A1:B3", styles = FALSE)
class(tbl)