Formatting Reports with Named Regions

Stefan Fleck

2023-03-26

library(tatoo)

Introduction

As of v.1.1.0 tatoo assigns named regions when writing .xlsx files. tatoo can use these named regions to painlessly apply formatting to tables inside Excel workbooks in bulk.

Example

wb <- as_workbook(iris[1:5, ])

a plain table

style_colnames <- openxlsx::createStyle(textDecoration = "bold")

walk_regions(wb, "colnames", openxlsx::addStyle, style_colnames)
walk_regions(wb, "table",    openxlsx::setColWidths, widths = 14)

a stylish table

Named region names

The names of the named regions associated with a table are constructed from the following elements:

Examples:

 show_regions <- function(x){
   unique(regions(as_workbook(x))$region)
 }
show_regions(mash_table(iris, iris))
## [1] "row_mashed_table_o51KddII"          "row_mashed_table_colnames_4OURvnIG"
## [3] "row_mashed_table_body_BCpTnOYg"
show_regions(mash_table(iris, iris, mash_method = "col"))
## [1] "col_mashed_table_lSZtLmwK"          "col_mashed_table_colnames_IMXcjok7"
## [3] "col_mashed_table_body_yvhvD4qT"
show_regions(comp_table(iris, iris))
## [1] "composite_table_multinames_O9I58nfW" "composite_table_vsWIXN6T"           
## [3] "composite_table_colnames_lhjDXbo9"   "composite_table_body_vAFs2MGU"
show_regions(stack_table(iris, iris))
## [1] "stacked_table_GcdgL49h"          "stacked_table_colnames_y3MCLtTr"
## [3] "stacked_table_body_F6kCFNmo"     "stacked_table_vOPekwTu"         
## [5] "stacked_table_colnames_OY8Nh2O0" "stacked_table_body_aa71ordB"
show_regions(tag_table(
  iris, 
  tt_meta(
    table_id = "tab1", 
    title = "a title", 
    footer = "blahblubb")
))
## [1] "tab1_header_TpxD0wol"         "tab1_table_0HCgstSd"         
## [3] "tab1_table_colnames_gCdid0d8" "tab1_table_body_Dauke5Hq"    
## [5] "tab1_footer_ZrXTkYEm"

Formatting parts of tables with walk_regions

walk_regions() is a way to apply formatting to Workbook regions. The syntax is inspired by purrr::walk(). walk_regions() takes the following arguments:

Examples

The following examples show how walk_regions() can be used to format column names, table captions (headers) and the values inside a table (body).

x <- mash_table(
  iris[1:2, ], 
  iris[1:2, ],
  meta = tt_meta(table_id = "iris", title = "example table")
) 
wb <- as_workbook(x)

style_iris     <- openxlsx::createStyle(fgFill = "pink")
style_header   <- openxlsx::createStyle(textDecoration = "italic")
style_colnames <- openxlsx::createStyle(textDecoration = "bold", valign = "top")
style_body     <- openxlsx::createStyle(textRotation = 10)


walk_regions(wb, "iris", openxlsx::addStyle, style = style_iris)
walk_regions(wb, "header", openxlsx::addStyle, style = style_header, stack = TRUE)
walk_regions(wb, "colnames", openxlsx::addStyle, style = style_colnames, stack = TRUE)
walk_regions(wb, "body", openxlsx::addStyle, style = style_body, stack = TRUE)


# You can also use functions that have *either* the rows or cols argument,
# so the following works:
walk_regions(wb, "table", openxlsx::setColWidths, widths = 14)
walk_regions(wb, "colnames", openxlsx::setRowHeights, heights = 34)

a very stylish table