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)