5.3 In-cell formatting

The previous section was about formatting applied at the level of cells. What about when multiple formats are applied within a single cell? A single word in a string might be a different colour, to stand out.

Unlike cell-level formatting, in-cell formatting is very limited, so it can be provided as a data frame with the following columns.

• bold
• italic
• underline
• strike
• vertAlign
• size
• color_rgb
• color_theme
• color_indexed
• color_tint
• font
• family
• scheme

There is one of these data frames for each cell, and they are kept in a list-column called character_formatted.

path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
xlsx_cells(path, sheet = "in-cell formatting") %>%
  select(address, character_formatted)

## # A tibble: 9 x 2
##   address character_formatted
##   <chr>   <list>             
## 1 A1      <tibble [9 × 14]>  
## 2 A3      <tibble [1 × 14]>  
## 3 B3      <tibble [1 × 14]>  
## 4 A4      <tibble [1 × 14]>  
## 5 B4      <NULL>             
## 6 A5      <tibble [2 × 14]>  
## 7 B5      <NULL>             
## 8 A6      <tibble [1 × 14]>  
## 9 B6      <NULL>

The way to access these data frames is via tidyr::unnest(). In this example, a single cell has a long string of words, where each word is formatted differently.

xlsx_cells(path, sheet = "in-cell formatting") %>%
  dplyr::filter(address == "A1") %>%
  select(address, character_formatted) %>%
  unnest()

## Warning: `cols` is now required.
## Please use `cols = c(character_formatted)`

## # A tibble: 9 x 15
##   address character bold  italic underline strike vertAlign  size color_rgb color_theme
##   <chr>   <chr>     <lgl> <lgl>  <chr>     <lgl>  <chr>     <dbl> <chr>           <int>
## 1 A1      in-cell:  FALSE FALSE  <NA>      FALSE  <NA>          0 FF000000           NA
## 2 A1      bold,     TRUE  FALSE  <NA>      FALSE  <NA>          0 FF000000           NA
## 3 A1      italic,   FALSE TRUE   <NA>      FALSE  <NA>          0 FF000000           NA
## 4 A1      underlin… FALSE FALSE  single    FALSE  <NA>          0 FF000000           NA
## 5 A1      striketh… FALSE FALSE  <NA>      TRUE   <NA>          0 FF000000           NA
## 6 A1      superscr… FALSE FALSE  <NA>      FALSE  superscr…     0 FF000000           NA
## 7 A1      red,      FALSE FALSE  <NA>      FALSE  <NA>          0 FFFF0000           NA
## 8 A1      arial,    FALSE FALSE  <NA>      FALSE  <NA>          0 <NA>               NA
## 9 A1      size 14   FALSE FALSE  <NA>      FALSE  <NA>          0 <NA>               NA
## # … with 5 more variables: color_indexed <int>, color_tint <dbl>, font <chr>, family <int>,
## #   scheme <chr>

It’s hard to think of a plausible example, so what follows is an implausible one that nevertheless occurred in real life.