r-Openxlsx-样式有叠加模式吗

Question

我正在尝试将openxlsx的格式选项与addStyle和conditionalFormatting一起使用，就像我在Excel中使用它们一样，即以叠加的方式使用(例如，居中单元格不会修改字体，只是…居中单元格！(。不幸的是，openxlsx似乎还没有这种模式：不能在另一个上添加一层格式化选项，至少以一种简单的方式；新的层简单地替换了前一层。

以下是两个代表(基本上是相同的管理原则(，以便您更好地理解我的意思。

Reprex#1：通过将单元格居中并添加边框，超链接格式(蓝色(消失/被居中格式样式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：只需将条件格式应用于一系列行(替换背景色(，超链接格式(蓝色(就会消失/被条件格式的隐式/默认格式选项所取代：

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)

你知道是否有一个技巧可以像我在Excel中那样以叠加的方式使用openxlsx的格式选项吗？

[EDIT]：Reprex#3添加到下面，作为Reprex#2问题的解决方案，因为conditionalFormatting函数仍然存在问题。此reprex按预期工作。

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)

Answer 1

看看openxlsx::addStyle的参数。默认情况下，有一个参数stack被设置为FALSE，这意味着应用几个样式总是会导致最后一个样式为"；"活动"；。

将此参数设置为TRUE(stack = TRUE(，它应该可以工作。