5.1 An example formatting lookup

This example shows how to look up whether a cell is bold.

path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
cells <-
  xlsx_cells(path, sheet = "formatting") %>%
  select(row, col, character, style_format, local_format_id)
cells
## # A tibble: 14 x 5
##      row   col character          style_format     local_format_id
##    <int> <int> <chr>              <chr>                      <int>
##  1     1     1 bold               Normal                         6
##  2     2     1 italic             Normal                         8
##  3     3     1 underline          Normal                        51
##  4     4     1 strikethrough      Normal                        52
##  5     5     1 red text           Normal                        12
##  6     6     1 font size 14       Normal                        53
##  7     7     1 font arial         Normal                        54
##  8     8     1 yellow fill        Normal                        11
##  9     9     1 black border       Normal                        43
## 10    10     1 thick border       Normal                        55
## 11    11     1 dashed border      Normal                        56
## 12    12     1 row height 30      Normal                         1
## 13    13     2 column width 16.76 Normal                         1
## 14    14     1 Bad' style         Explanatory Text              57
formats <- xlsx_formats(path)
bold <- formats$local$font$bold # The list of lists of lists of vectors
bold
##  [1] FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE
## [12] FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE
## [23] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [34] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [45] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [56] FALSE FALSE FALSE  TRUE
mutate(cells, bold = bold[local_format_id])
## # A tibble: 14 x 6
##      row   col character          style_format     local_format_id bold 
##    <int> <int> <chr>              <chr>                      <int> <lgl>
##  1     1     1 bold               Normal                         6 TRUE 
##  2     2     1 italic             Normal                         8 FALSE
##  3     3     1 underline          Normal                        51 FALSE
##  4     4     1 strikethrough      Normal                        52 FALSE
##  5     5     1 red text           Normal                        12 FALSE
##  6     6     1 font size 14       Normal                        53 FALSE
##  7     7     1 font arial         Normal                        54 FALSE
##  8     8     1 yellow fill        Normal                        11 FALSE
##  9     9     1 black border       Normal                        43 FALSE
## 10    10     1 thick border       Normal                        55 FALSE
## 11    11     1 dashed border      Normal                        56 FALSE
## 12    12     1 row height 30      Normal                         1 FALSE
## 13    13     2 column width 16.76 Normal                         1 FALSE
## 14    14     1 Bad' style         Explanatory Text              57 FALSE

A quick way to see what formatting definitions exist is to use str(). (Scroll past this for now – you don’t need to memorise it).

formats <- xlsx_formats(path)
str(formats)
## List of 2
##  $ local:List of 6
##   ..$ numFmt    : chr [1:59] "General" "General" "General" "General" ...
##   ..$ font      :List of 10
##   .. ..$ bold     : logi [1:59] FALSE TRUE TRUE FALSE FALSE TRUE ...
##   .. ..$ italic   : logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ underline: chr [1:59] NA NA NA NA ...
##   .. ..$ strike   : logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ vertAlign: chr [1:59] NA NA NA NA ...
##   .. ..$ size     : num [1:59] 11 11 11 11 11 11 11 11 11 11 ...
##   .. ..$ color    :List of 4
##   .. .. ..$ rgb    : chr [1:59] "FF000000" "FF000000" "FF000000" "FF000000" ...
##   .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ name     : chr [1:59] "Calibri" "Calibri" "Calibri" "Calibri" ...
##   .. ..$ family   : int [1:59] 2 2 2 2 2 2 2 2 2 2 ...
##   .. ..$ scheme   : chr [1:59] NA NA NA NA ...
##   ..$ fill      :List of 2
##   .. ..$ patternFill :List of 3
##   .. .. ..$ fgColor    :List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ bgColor    :List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ patternType: chr [1:59] NA NA NA NA ...
##   .. ..$ gradientFill:List of 8
##   .. .. ..$ type  : chr [1:59] NA NA NA NA ...
##   .. .. ..$ degree: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ left  : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ right : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ top   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ bottom: num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ stop1 :List of 2
##   .. .. .. ..$ position: num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ color   :List of 4
##   .. .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. ..$ stop2 :List of 2
##   .. .. .. ..$ position: num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ color   :List of 4
##   .. .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   ..$ border    :List of 12
##   .. ..$ diagonalDown: logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ diagonalUp  : logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ outline     : logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ left        :List of 2
##   .. .. ..$ style: chr [1:59] NA "thin" NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA "FF000000" NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ right       :List of 2
##   .. .. ..$ style: chr [1:59] NA "thin" NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA "FF000000" NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ start       :List of 2
##   .. .. ..$ style: chr [1:59] NA NA NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ end         :List of 2
##   .. .. ..$ style: chr [1:59] NA NA NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ top         :List of 2
##   .. .. ..$ style: chr [1:59] NA "thin" NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA "FF000000" NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ bottom      :List of 2
##   .. .. ..$ style: chr [1:59] NA NA "thin" NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA "FF000000" NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ diagonal    :List of 2
##   .. .. ..$ style: chr [1:59] NA NA NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ vertical    :List of 2
##   .. .. ..$ style: chr [1:59] NA NA NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. ..$ horizontal  :List of 2
##   .. .. ..$ style: chr [1:59] NA NA NA NA ...
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ theme  : chr [1:59] NA NA NA NA ...
##   .. .. .. ..$ indexed: int [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   .. .. .. ..$ tint   : num [1:59] NA NA NA NA NA NA NA NA NA NA ...
##   ..$ alignment :List of 8
##   .. ..$ horizontal     : chr [1:59] "general" "center" "general" "general" ...
##   .. ..$ vertical       : chr [1:59] "bottom" "bottom" "bottom" "bottom" ...
##   .. ..$ wrapText       : logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ readingOrder   : chr [1:59] "context" "context" "context" "context" ...
##   .. ..$ indent         : int [1:59] 0 0 0 0 0 0 0 0 0 0 ...
##   .. ..$ justifyLastLine: logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ shrinkToFit    : logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##   .. ..$ textRotation   : int [1:59] 0 0 0 0 0 0 0 0 0 0 ...
##   ..$ protection:List of 2
##   .. ..$ locked: logi [1:59] TRUE TRUE TRUE TRUE TRUE TRUE ...
##   .. ..$ hidden: logi [1:59] FALSE FALSE FALSE FALSE FALSE FALSE ...
##  $ style:List of 6
##   ..$ numFmt    : Named chr [1:2] "General" "General"
##   .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   ..$ font      :List of 10
##   .. ..$ bold     : Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ italic   : Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ underline: Named chr [1:2] NA NA
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ strike   : Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ vertAlign: Named chr [1:2] NA NA
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ size     : Named num [1:2] 11 11
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ color    :List of 4
##   .. .. ..$ rgb    : Named chr [1:2] "FF000000" "FF9C0006"
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ name     : Named chr [1:2] "Calibri" "Calibri"
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ family   : Named int [1:2] 2 2
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ scheme   : Named chr [1:2] NA NA
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   ..$ fill      :List of 2
##   .. ..$ patternFill :List of 3
##   .. .. ..$ fgColor    :List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA "FFFFC7CE"
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ bgColor    :List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA "FFCCCCFF"
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ patternType: Named chr [1:2] NA "solid"
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ gradientFill:List of 8
##   .. .. ..$ type  : Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ degree: Named int [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ left  : Named num [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ right : Named num [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ top   : Named num [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ bottom: Named num [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ stop1 :List of 2
##   .. .. .. ..$ position: Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ color   :List of 4
##   .. .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ stop2 :List of 2
##   .. .. .. ..$ position: Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ color   :List of 4
##   .. .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   ..$ border    :List of 12
##   .. ..$ diagonalDown: Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ diagonalUp  : Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ outline     : Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ left        :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ right       :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ start       :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ end         :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ top         :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ bottom      :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ diagonal    :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ vertical    :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ horizontal  :List of 2
##   .. .. ..$ style: Named chr [1:2] NA NA
##   .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. ..$ color:List of 4
##   .. .. .. ..$ rgb    : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ theme  : Named chr [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ indexed: Named int [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. .. .. ..$ tint   : Named num [1:2] NA NA
##   .. .. .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   ..$ alignment :List of 8
##   .. ..$ horizontal     : Named chr [1:2] "general" "general"
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ vertical       : Named chr [1:2] "bottom" "bottom"
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ wrapText       : Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ readingOrder   : Named chr [1:2] "context" "context"
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ indent         : Named int [1:2] 0 0
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ justifyLastLine: Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ shrinkToFit    : Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ textRotation   : Named int [1:2] 0 0
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   ..$ protection:List of 2
##   .. ..$ locked: Named logi [1:2] TRUE TRUE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"
##   .. ..$ hidden: Named logi [1:2] FALSE FALSE
##   .. .. ..- attr(*, "names")= chr [1:2] "Normal" "Explanatory Text"

Why is this so complicated? For one thing, there are too many types of formatting available to include in the data frame given by xlsx_cells().

Consider borders: each cell can have a border on each of its four sides, as well as through the middle of the cell horizontally, vertically, diagonally up and diagonally down. Each border can have its own colour and linetype. Colour can be expressed as an RGB value, a theme number with or without a tint, or an index number.

To express that in a data frame would take (4 sides + 4 through the middle) * (4 ways to express colour + 1 linetype) = 40 columns. Just for borders.

Instead, Excel dynamically defines combinations of formatting, as they occur, and gives ID numbers to those combinations. Each cell has a formatting ID, which is used to look up its particular combination of formats. Note that this means two cells that are both bold can have different formatting IDs, e.g. if one is also italic.

There is also a hierarchy of formatting. The first formatting to be applied is the ‘style’. Every cell has a style, which by default is the ‘normal’ style. You can reformat all cells of the ‘normal’ style at once by updating the ‘normal’ style. Style formats are available under xlsx_formats()$style

When you modify the format of a particular cell, then that modification is local to that cell. The cell’s local formatting is available under xlsx_formats()$local. Both $style and $local have the same structure, so it’s easy to switch from checking a cell’s style-level formatting to its local formatting.

Here’s an example of looking up both the local bold formatting and the style-level bold formatting of a cell.

path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
cells <-
  xlsx_cells(path, sheet = "formatting") %>%
  select(row, col, character, style_format, local_format_id) %>%
  dplyr::filter(row == 1, col == 1)
cells
## # A tibble: 1 x 5
##     row   col character style_format local_format_id
##   <int> <int> <chr>     <chr>                  <int>
## 1     1     1 bold      Normal                     6
formats <- xlsx_formats(path)

local_bold <- formats$local$font$bold
local_bold
##  [1] FALSE  TRUE  TRUE FALSE FALSE  TRUE  TRUE FALSE FALSE FALSE FALSE
## [12] FALSE FALSE FALSE  TRUE FALSE  TRUE FALSE FALSE FALSE FALSE FALSE
## [23] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [34] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [45] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [56] FALSE FALSE FALSE  TRUE
style_bold <- formats$style$font$bold
style_bold
##           Normal Explanatory Text 
##            FALSE            FALSE
mutate(cells,
       style_bold = style_bold[style_format],
       local_bold = local_bold[local_format_id])
## # A tibble: 1 x 7
##     row   col character style_format local_format_id style_bold local_bold
##   <int> <int> <chr>     <chr>                  <int> <lgl>      <lgl>     
## 1     1     1 bold      Normal                     6 FALSE      TRUE

Most of the time you will use the local formatting. You only need to check the style formatting when styles have been used in the spreadsheet (rare) and you want to ignore any local modifications of that style for particular cells.

Conditional formatting is an obvious omission. It isn’t supported by tidyxl because it doesn’t encode any new information; it’s responds to cell values, which you already have. If you think you need it, feel free to open an issue.