conditionalFormatting does not take numFmt

[klar-C]

Please see reprex below. When opening the sheet it does not reflect he different number formats.

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

negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE", numFmt = "mmddyy")
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE", numFmt = "0.0%")

writeData(wb, "cellIs", -5:5)
writeData(wb, "cellIs", LETTERS[1:11], startCol = 2)
conditionalFormatting(wb, "cellIs",
                      cols = 1,
                      rows = 1:11, rule = "!=0", style = negStyle
)
conditionalFormatting(wb, "cellIs",
                      cols = 1,
                      rows = 1:11, rule = "==0", style = posStyle
)

openxlsx::openXL(wb)

[JanMarvin]

Hi @klar-C ,

thanks for the report, I can confirm the bug. Though I have taken quite a good amount to time to get rid of the styleObject, therefore I will not waste my time to fix it. It works in openxlsx2 if you fancy:

# slightly modified example, to create something that looks less broken
library(openxlsx2)
wb <- wb_workbook()$
  add_worksheet("cellIs")$
  add_data("cellIs", c(-5:0,openxlsx2::convertToExcelDate(seq(from = Sys.Date(), to = Sys.Date() + 4, by = "days"))))$
  add_data("cellIs", LETTERS[1:11], startCol = 2)

# create and assign styles
negStyle <- create_dxfs_style(font_color = c(rgb = "FF9C0006"), bgFill = c(rgb = "FFFFC7CE"), numFmt = "mmddyy")
posStyle <- create_dxfs_style(font_color = c(rgb = "FF006100"), bgFill = c(rgb = "FFC6EFCE"), numFmt = "0.0%")
wb$styles_mgr$styles$dxfs <- c(wb$styles_mgr$styles$dxfs, c(negStyle, posStyle))

## rule applies to all each cell in range
wb_conditional_formatting(wb, "cellIs", cols = 1, rows = 1:11, rule = ">0", style = negStyle)
wb_conditional_formatting(wb, "cellIs", cols = 1, rows = 1:11, rule = "<=0", style = posStyle)

wb$open()

If you want to try to fix it yourself, negStyle and posStyle should look something like this (not sure what and where openxlsx creates this part of the xml. My fist guess would be the conditionalFormatting function:

<!-- negStyle -->
<dxf>
 <font>
  <color rgb="FF9C0006" />
  <name val="Calibri" />
  <sz val="11" />
 </font>
 <numFmt numFmtId="3" formatCode="mmddyy" />
 <fill>
  <patternFill patternType="solid">
   <bgColor rgb="FFFFC7CE" />
  </patternFill>
 </fill>
</dxf>
<!-- posStyle -->
<dxf>
 <font>
  <color rgb="FF006100" />
  <name val="Calibri" />
  <sz val="11" />
 </font>
 <numFmt numFmtId="3" formatCode="0.0%" />
 <fill>
  <patternFill patternType="solid">
   <bgColor rgb="FFC6EFCE" />
  </patternFill>
 </fill>
</dxf>

[Ljupch0]

Can openxlsx2 be used plug-and-play with openxlsx?

[JanMarvin]

Hi @Ljupch0 , short answer: no. The longer answer is, that it is not a drop in replacement or even interchangeable with openxlsx. It's still in very active development, but you're welcome to have a look for yourself.

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.