conditionalFormatting replaces the other applied styles - need a parameter stack = TRUE like with addStyle

[olivier7121]

The function conditionalFormatting replaces the other applied styles (which is not the behaviour expected and observed in Excel). Unlike function addStyle, there is currently no parameter stack = TRUE to prevent that.

Reproducible example (also available on this page of stackoverflow - reprex #2):

library("openxlsx")

OutputFolder <- file.path(".", "Output")
if(!dir.exists(OutputFolder))   dir.create(OutputFolder)

OutputFile <- file.path(OutputFolder, "Reprex_Openxlsx_Is_There_a_Superimpose_Mode.xlsx")

Workbook4Export <- createWorkbook()

addWorksheet(wb = Workbook4Export, sheetName = "Tab_1", zoom = 80, gridLines = FALSE)
addWorksheet(wb = Workbook4Export, sheetName = "Tab_2", zoom = 80, gridLines = FALSE)

ContentsCellsStyle_GreyBackground <- createStyle(bgFill = "#BFBFBF")

conditionalFormatting(wb = Workbook4Export, sheet = "Tab_1", cols = 1:5, rows = 1:10, rule = "MOD(ROW(A1), 2) = 0", style = ContentsCellsStyle_GreyBackground, type = "expression")

for(i in 1:10)
{
    writeFormula(wb = Workbook4Export, sheet = "Tab_1", startRow = i, startCol = 1, x = makeHyperlinkString(sheet = "Tab_2", row = 1, col = 1, text = paste0("Link to 'Tab_2'")))
    for(j in 2:5)
    {
        writeData(wb = Workbook4Export, sheet = "Tab_1", x = "Some text", startRow = i, startCol = j)
    }
}

saveWorkbook(wb = Workbook4Export, file = OutputFile, overwrite = TRUE)

R version 4.2.1
openxlsx version 4.2.5

[JanMarvin]

Hi @olivier7121 , thanks for the report. I assume that you did not want the hyperlink color and font to be modified?

Most likely that's because the dxfs xml string is created with to many arguments.

"<dxf>
  <font>
    <color rgb=\"FF000000\"/>
    <name val=\"Calibri\"/>
    <sz val=\"11\"/>
  </font>
  <fill>
    <patternFill patternType=\"solid\">
      <bgColor rgb=\"FFBFBFBF\"/>
    </patternFill>
  </fill>
</dxf>"

instead of

<dxf>
  <font/>
  <fill>
    <patternFill patternType=\"solid\">
      <bgColor rgb=\"FFBFBFBF\"/>
    </patternFill>
  </fill>
</dxf>

[olivier7121]

Thanks for the quick reply, @JanMarvin.

Your assumption is right: I didn't want the font color to be modified. I wanted exactly the same behaviour as the one we get when doing this with Excel (i.e. 'superimpose' mode and not 'replace' mode).

As I wrote in my first message, it is very similar (if not the same) to the stack = TRUE parameter of the addStyle function of openxlsx.

[JanMarvin]

I have raised an issue with openxlsx2 where I can simply solve this (a workaround is already in the issue). In openxlsx it could be solvable too, but even I have time restrictions 😄 .

[github-actions]

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]

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