openxlsx
openxlsx copied to clipboard
`conditionalFormatting` replaces the other applied styles - need a parameter `stack = TRUE` like with `addStyle`
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
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>
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
.
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 😄 .