Only one databar works per workbook

rdisalv2 commented 7 years ago

Expected Behavior

Be able to run the databar example in the dev openxlsx package and produce an excel file that opens in MS Excel. Code copied from ?openxlsx::conditionalFormatting

library(openxlsx)

#########################################################################
## Databar Example

wb <- createWorkbook()
addWorksheet(wb, "databar")

## Databars
writeData(wb, "databar", -5:5, startCol = 1)
conditionalFormatting(wb, "databar", cols = 1, rows = 1:11, type = "databar") ## Defaults

writeData(wb, "databar", -5:5, startCol = 3)
conditionalFormatting(wb, "databar", cols = 3, rows = 1:11, type = "databar", border = FALSE)

writeData(wb, "databar", -5:5, startCol = 5)
conditionalFormatting(wb, "databar", cols = 5, rows = 1:11, 
                      type = "databar", style = c("#a6a6a6"), showValue = FALSE) 

writeData(wb, "databar", -5:5, startCol = 7)
conditionalFormatting(wb, "databar", cols = 7, rows = 1:11, 
                      type = "databar", style = c("#a6a6a6"), showValue = FALSE, gradient = FALSE) 

writeData(wb, "databar", -5:5, startCol = 9)
conditionalFormatting(wb, "databar", cols = 9, rows = 1:11, 
                      type = "databar", style = c("#a6a6a6", "#a6a6a6"), showValue = FALSE, gradient = FALSE)

saveWorkbook(wb, file = "databarExample.xlsx", overwrite = TRUE)

Actual Behavior

databarExample.xlsx cannot be opened in MS Excel. I attached the xlsx output, and screenshots of the errors.

Steps to Reproduce the Problem

Run the databar example from ?openxlsx::conditionalFormatting. But, in production, here's where I ran into this problem: add two databars to a worksheet (using two different calls to conditionalFormatting() -- trying to get different scales). One conditionalFormatting call (even for multiple columns) is OK -- it only breaks on two different columns.
Export worksheet to xlsx
Try to open in MS Excel

I attached the output excel file, which I cannot open in MS Excel. Also attached a screenshot of Excel's errors.

sessionInfo()

sessionInfo() R version 3.4.0 (2017-04-21) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

locale: [1] LC_COLLATE=English_United States.1252 [2] LC_CTYPE=English_United States.1252
[3] LC_MONETARY=English_United States.1252 [4] LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

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.0 tools_3.4.0 Rcpp_0.12.10

Version of openxlsx: 4.0.17
Version of R: 3.4.0

Thanks!

Also -- the problem seems to be excel specific. A workaround I've found is to open the file in LibreOffice then Save As to xlsx. This doesn't really solve the problem because libreoffice seems to strip a bunch of other stuff in conversion. I attached that xlsx file, too.

Also 2 -- reverting via versions::install.versions('openxlsx',c('4.0.0')) did not seem to help (obviously, have to remove the gradient = FALSE etc. etc. for that, but still I can only seem to get one databar to work.

databarExample.xlsx databarExample_Saved_From_LibreOffice.xlsx

TundraXYZ commented 6 years ago

I faced a same problem about conditionalFormatting(). Then, I found the conditionalFormat() does not make error if using twice. However, this function is marked as DEPRECATED in manual page. https://rdrr.io/cran/openxlsx/man/conditionalFormat.html

wb <- createWorkbook()
addWorksheet(wb, 'S1')
writeData(wb, sheet = 'S1', x = iris)

n<-nrow(iris)
rng <- (1:n)+1
conditionalFormat(wb, "S1", cols=1, rows=2:4, type='databar')
conditionalFormat(wb, "S1", cols=3, rows=5:6, type='databar')

saveWorkbook(wb, "out(DataBar-3).xlsx", overwrite = T)

ーーーーーーーーーーーーーーーーーーーーーー openxlsx ver 4.0.17

sessioninfo::platform_info() setting value
version R version 3.4.1 (2017-06-30) os Windows >= 8 x64
system x86_64, mingw32
ui RStudio
language (EN)

mauritzvdworm commented 4 years ago

I can confirm that it databars work with conditionalFormat, but gives errors in the xml file when using conditionalFormatting.

awalker89 / openxlsx