sheet styles

[gunjangala]

Hello, How can you identify styles using getStyles() for multiple sheets in workbook? I have 11 sheets in my workbook and there are 22 sublists generated and I do not know which style corresponds to which sheet in a particular workbook. Is there a way to know that? You can find the input file here.

> library(openxlsx)
> wb     <- loadWorkbook("test.xlsx")
> names(wb)
 [1] "fold.difference"               "Top 10th percentile in D or R"
 [3] "Gains"                         "Losses"                       
 [5] "Promoter gains"                "Promoter losses"              
 [7] "gene body gains"               "Gene body losses"             
 [9] "Intergenic gains"              "Intergenic losses"            
[11] "Statistics"                   
> getStyles(wb)
[[1]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #FFFF00 
 Cell fill background:  indexed: 64 
[[2]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #FFFF00 
 Cell fill background:  indexed: 64 
[[3]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #92D050 
 Cell fill background:  indexed: 64 
[[4]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #FFFF00 
 Cell fill background:  indexed: 64 
[[5]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #92D050 
 Cell fill background:  indexed: 64 
[[6]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #FFFF00 
 Cell fill background:  indexed: 64 
[[7]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #92D050 
 Cell fill background:  indexed: 64 
[[8]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #FFFF00 
 Cell fill background:  indexed: 64 
[[9]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #92D050 
 Cell fill background:  indexed: 64 
[[10]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #FFFF00 
 Cell fill background:  indexed: 64 
[[11]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #92D050 
 Cell fill background:  indexed: 64 
[[12]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin, Bottom: thin, Left: thin 
 Cell border colours: 64, 64, 64 
[[13]]
A custom cell style. 
 Cell formatting: GENERAL 
[[14]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin, Bottom: thin, Left: thin, Right: thin 
 Cell border colours: 64, 64, 64, 64 
[[15]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin, Bottom: thin 
 Cell border colours: 64, 64 
[[16]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin, Bottom: thin, Right: thin 
 Cell border colours: 64, 64, 64 
[[17]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin, Left: thin 
 Cell border colours: 64, 64 
[[18]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin 
 Cell border colours: 64 
[[19]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin, Right: thin 
 Cell border colours: 64, 64 
[[20]]
A custom cell style.
 Cell formatting: GENERAL 
 Cell borders: Left: thin 
 Cell border colours: 64 
[[21]]
A custom cell style. 
 Cell formatting: GENERAL 
 Cell borders: Top: thin, Bottom: thin, Left: thin, Right: thin 
 Cell border colours: 64, 64, 64, 64 
[[22]]
A custom cell style. 
 Cell formatting: PERCENTAGE 
 Cell borders: Top: thin, Bottom: thin, Left: thin, Right: thin 
 Cell border colours: 64, 64, 64, 64 

> sessionInfo()
R version 3.4.3 (2017-11-30)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS Sierra 10.12.6
Matrix products: default
BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/3.4/Resources/lib/libRlapack.dylib
locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     
other attached packages:
[1] openxlsx_4.0.17
loaded via a namespace (and not attached):
[1] compiler_3.4.3 tools_3.4.3    yaml_2.1.14    Rcpp_0.12.13  

Thanks, Gunjan

[kainhofer]

The list of styles returned by getStyles does NOT include the information about the sheet any more. Instead, you have to directly access the workbook's styleObjects list:

> wb$styleObjects
[[1]]
[[1]]$style
A custom cell style. 

 Cell formatting: GENERAL 
 Cell fill foreground:  rgb: #FFFF00 
 Cell fill background:  indexed: 64 

[[1]]$sheet
[1] "fold.difference"

[[1]]$rows
   [1]  100  100  100  100  100  100  100  100  100  100  100  100  100  100  100  100
  [17]  100  100  100  100  100  100  102  102  102  102  102  102  102  102  102  102
.....