2.6 Hierarchies in formatting

Different kinds of formatting might also represent different levels of a hierarchy, e.g.

formatting	interpretation
none	good
italic	satisfactory
bold	poor
bold & italic	fail

When each kind of formatting relates to a different level of one hierarchy, import the different kinds of formatting into different columns, and then combine them into a third column, perhaps using paste(), or case_when().

# Step 1: import the table taking only cell values and ignoring the formatting
x <- read_excel(path, sheet = "highlight-hierarchy")
x

## # A tibble: 4 x 2
##   Name     Score
##   <chr>    <dbl>
## 1 Matilda      7
## 2 Nicholas     5
## 3 Olivia       3
## 4 Paul         1

# Step 2: import one kind of formatting of one column of the table

# `formats` is a pallette of fill colours that can be indexed by the
# `local_format_id` of a given cell to get the fill colour of that cell
bold <- xlsx_formats(path)$local$font$bold
italic <- xlsx_formats(path)$local$font$italic

# Import all the cells, filter out the header row, filter for the first column,
# and create a new column `fill` of the fill colours, by looking up the
# local_format_id of each cell in the `formats` pallette.
formats <-
  xlsx_cells(path, sheet = "highlight-hierarchy") %>%
  dplyr::filter(row >= 2, col == 1) %>% # Omit the header row
  mutate(bold = bold[local_format_id],
         italic = italic[local_format_id]) %>%
  mutate(grade = case_when(bold & italic ~ "fail",
                           bold ~ "poor",
                           italic ~ "satisfactory",
                           TRUE ~ "good")) %>%
  select(bold, italic, grade)

# Step 3: append the `fill` column to the rest of the data
bind_cols(x, formats)

## # A tibble: 4 x 5
##   Name     Score bold  italic grade       
##   <chr>    <dbl> <lgl> <lgl>  <chr>       
## 1 Matilda      7 FALSE FALSE  good        
## 2 Nicholas     5 FALSE TRUE   satisfactory
## 3 Olivia       3 TRUE  FALSE  poor        
## 4 Paul         1 TRUE  TRUE   fail

Here it is again, using only tidyxl and unpivotr.

bold <- xlsx_formats(path)$local$font$bold
italic <- xlsx_formats(path)$local$font$italic

xlsx_cells(path, sheet = "highlight-hierarchy") %>%
  mutate(bold = bold[local_format_id],
         italic = italic[local_format_id]) %>%
  mutate(grade = case_when(bold & italic ~ "fail",
                           bold ~ "poor",
                           italic ~ "satisfactory",
                           TRUE ~ "good")) %>%
  select(row, col, data_type, character, numeric, bold, italic, grade) %>%
  behead("up", header) %>%
  select(-col) %>%
  spatter(header)

## # A tibble: 4 x 6
##     row bold  italic grade        Name     Score
##   <int> <lgl> <lgl>  <chr>        <chr>    <dbl>
## 1     2 FALSE FALSE  good         Matilda      7
## 2     3 FALSE TRUE   satisfactory Nicholas     5
## 3     4 TRUE  FALSE  poor         Olivia       3
## 4     5 TRUE  TRUE   fail         Paul         1