Openxlsx - Is there a superimpose mode for styles?

Viewed 44

I'm trying to use the formatting options of openxlsx with addStyle and conditionalFormatting like I would use them within Excel, i.e. in a superimpose way (e.g. centering a cell doesn't modify the font but just... centers the cell!). Unfortunately it seems openxlsx doesn't have this mode (yet): one cannot add a layer of formatting options on another one, at least in a simple way; the new layer simply replaces the former one.

Below are 2 reprexes (it is basically the same governing principle) so that you better understand what I mean.

Reprex #1: By just centering the cell and adding borders to it, the hyperlinks format (blue color) disappears/is replaced by the implicit/default formatting options of the centering format style ContentsCellsStyle_Centered:

library("openxlsx")

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

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

Workbook4Export <- createWorkbook()

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

writeFormula(wb = Workbook4Export, sheet = "Tab_1", startRow = 1, startCol = 1, x = makeHyperlinkString(sheet = "Tab_2", row = 1, col = 1, text = paste0("Link to 'Tab_2'")))
writeFormula(wb = Workbook4Export, sheet = "Tab_1", startRow = 1, startCol = 2, x = makeHyperlinkString(sheet = "Tab_3", row = 1, col = 1, text = paste0("Link to 'Tab_3'")))
writeFormula(wb = Workbook4Export, sheet = "Tab_1", startRow = 1, startCol = 3, x = makeHyperlinkString(sheet = "Tab_4", row = 1, col = 1, text = paste0("Link to 'Tab_4'")))

ContentsCellsStyle_Centered <- createStyle(halign = "center", valign = "center", border = "TopBottomLeftRight")
addStyle(wb = Workbook4Export, sheet = "Tab_1", style = ContentsCellsStyle_Centered, rows = 1, cols = 3)

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

Reprex #2: By just applying a conditional format to a series of rows (alternate background colors), the hyperlinks format (blue color) disappears/is replaced by the implicit/default formatting options of the conditional format:

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)

Do you know whether there is a trick to use the formatting options of openxlsx in a superimpose way like I would from within Excel?

[EDIT]: Reprex #3 added below as a solution to the issue from reprex #2 as there is still an issue with the conditionalFormatting function. This reprex works as intended.

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(fgFill = "#BFBFBF")

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

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)
    }
    if(i%%2 == 0)   addStyle(wb = Workbook4Export, sheet = "Tab_1", style = ContentsCellsStyle_GreyBackground, rows = i, cols = 1:5, stack = TRUE)
}

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

Have a look at the arguments of openxlsx::addStyle. There is an argument stack that is set to FALSE per default, which means that applying several styles will always lead to only the last one being "active".

Set this argument to TRUE (stack = TRUE) and it should work.

Related