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 57formats <- 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 FALSE FALSE FALSE TRUE
## [16] FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [31] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [46] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE## # 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 FALSEA quick way to see what formatting definitions exist is to use str(). (Scroll
past this for now – you don’t need to memorise it).
## 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## [1] FALSE TRUE TRUE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE
## [16] FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [31] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [46] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE## Normal Explanatory Text
## FALSE FALSE## # 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 TRUEMost 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.