conditional formatting does not apply numeric formatting

[nassuphis]

rm(list=ls())
require(data.table)
require(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Sheet1", gridLines = FALSE)
writeData(
  wb=wb,
  sheet="Sheet1",
  x=data.table(x=1:10+1e6),
  startRow = 1,
  startCol = 3
)

conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1<0.5,C2>5)",
style=createStyle(numFmt = "€ #,##0",fontColour = "#006100", bgFill = "#C6EFCE")
)
conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1<0.5,C2<5)",
style=createStyle(numFmt = "€ #,##0",fontColour = "#9C0006", bgFill = "#FFC7CE")
)
conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1>0.5,C2>5)",
style=createStyle(numFmt = "0",fontColour = "#006100", bgFill = "#C6EFCE")
)
conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1>0.5,C2<5)",
style=createStyle(numFmt = "0",fontColour = "#9C0006", bgFill = "#FFC7CE")
)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)

Expected Behavior

changing cell A1 changes the number format, i made this by hand on excel

Actual Behavior

but it makes no difference in the generated sheet:

Steps to Reproduce the Problem

(please attach an example xlsx file if possible) the generated file test.xlsx

sessionInfo()

sessionInfo() R version 3.4.3 (2017-11-30) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale: [1] LC_COLLATE=English_United Kingdom.1252 LC_CTYPE=English_United Kingdom.1252 LC_MONETARY=English_United Kingdom.1252 [4] LC_NUMERIC=C LC_TIME=English_United Kingdom.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] openxlsx_4.1.0 scales_0.5.0 FRAPO_0.4-1 timeSeries_3042.102 timeDate_3043.102 Rglpk_0.6-3 slam_0.1-43
[8] cccp_0.2-4 Rblpapi_0.3.8 magrittr_1.5 ggplot2_2.2.1.9000 readxl_1.1.0 Matrix_1.2-14 data.table_1.11.2
[15] stringi_1.1.7 gsubfn_0.7 proto_1.0.0 R.cache_0.13.0

loaded via a namespace (and not attached): [1] zip_1.0.0 Rcpp_0.12.18 cellranger_1.1.0 compiler_3.4.3 pillar_1.2.2 plyr_1.8.4 bindr_0.1.1 R.methodsS3_1.7.1 [9] R.utils_2.6.0 tools_3.4.3 digest_0.6.15 tibble_1.4.2 gtable_0.2.0 lattice_0.20-35 pkgconfig_2.0.1 rlang_0.2.0
[17] yaml_2.1.19 bindrcpp_0.2.2 withr_2.1.2 dplyr_0.7.5 grid_3.4.3 tidyselect_0.2.4 glue_1.2.0 R6_2.2.2
[25] tcltk_3.4.3 purrr_0.2.4 codetools_0.2-15 assertthat_0.2.0 colorspace_1.3-2 lazyeval_0.2.1 munsell_0.4.3 R.oo_1.22.0

[nassuphis]

I think the addDXFS method does not add any conditional formatting coming from the conditionalFormatting style argument:

 wb$addDXFS
Class method definition for method addDXFS()
function (style) 
{
    dxf <- "<dxf>"
    dxf <- paste0(dxf, createFontNode(style))
    fillNode <- NULL
    if (!is.null(style$fill$fillFg) | !is.null(style$fill$fillBg)) 
        dxf <- paste0(dxf, createFillNode(style))
    if (any(!is.null(c(style$borderLeft, style$borderRight, style$borderTop, 
        style$borderBottom, style$borderDiagonal)))) 
        dxf <- paste0(dxf, createBorderNode(style))
    dxf <- paste(dxf, "</dxf>")
    if (dxf %in% styles$dxfs) 
        return(which(styles$dxfs == dxf) - 1L)
    dxfId <- length(styles$dxfs)
    styles$dxfs <<- c(styles$dxfs, dxf)
    return(dxfId)
}
<environment: 0x000000002d3460d0>

Methods used: 
     "createBorderNode", "createFillNode", "createFontNode

[nassuphis]

note that the color part of the conditional formatting style works fine, its just the number formatting thats missing:

[nassuphis]

but excel can actually do this, its just that numFmt is left at default and I have no way to set it because that part of the style thing is not used..

[nassuphis]

and this is probably better example of the problem

rm(list=ls())

require(openxlsx)

wb <- createWorkbook()

addWorksheet(wb, "Sheet1", gridLines = FALSE)

writeData(
  wb=wb,
  sheet="Sheet1",
  x=data.table(x=seq(0,1e6,length.out=10)),
  startRow = 1,
  startCol = 3
)
addStyle(wb=wb, sheet="Sheet1", style=createStyle(numFmt = "€ #,##0"), rows=2:5, cols=3, gridExpand = FALSE, stack = FALSE)
addStyle(wb=wb, sheet="Sheet1", style=createStyle(numFmt = "0"), rows=6:11, cols=3, gridExpand = FALSE, stack = FALSE)

conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1<0.5,C2>500000)",
style=createStyle(numFmt = "€ #,##0",fontColour = "#006100", bgFill = "#C6EFCE")
)
conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1<0.5,C2<500000)",
style=createStyle(numFmt = "€ #,##0",fontColour = "#9C0006", bgFill = "#FFC7CE")
)
conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1>0.5,C2>500000)",
style=createStyle(numFmt = "0",fontColour = "#006100", bgFill = "#C6EFCE")
)
conditionalFormatting(  
wb=wb,
sheet="Sheet1",
cols=3,
rows=2:11,
rule="AND($A$1>0.5,C2<500000)",
style=createStyle(numFmt = "0",fontColour = "#9C0006", bgFill = "#FFC7CE")
)

saveWorkbook(wb, "test.xlsx", overwrite = TRUE)

[nassuphis]

setting cell A1 to a falue does not change the number formatting, as one can do by setting rules manually in excel: