r - 如果单元格不为空，则从 R 格式化 Excel 单元格

Question

我正在尝试从 R 格式化 Excel 中特定列中的单元格(使用数据框生成工作簿)。我目前正在使用openxlsx .

这是我当前正在尝试开始工作的代码行:

conditionalFormatting(WorkBook, "Sheet1", cols=17, rows=1:11000, rule='<TODAY(),"<>"&""', style = negStyle)

我也尝试过这个:

conditionalFormatting(WorkBook, "Sheet1", cols=17, rows=1:11000, rule='AND(<TODAY(),"<>"&"")', style = negStyle)

和

conditionalFormatting(WorkBook, "Sheet1", cols=17, rows=1:11000, rule='AND(<TODAY(),<>&"")', style = negStyle)

所以我尝试使用 negStyle 来设置单元格的样式如果列中的日期早于今天的日期。我的代码有什么问题吗？

Answer 1

您的conditionalFormatting Excel 中的公式将类似于:

因此，如果您想使用 openxlsx 应用此格式，您需要执行以下操作:

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "cond")

date_df <- data.frame(id = 1:20,
                      dat_col = as.Date("2019-09-20")-1:20)

writeData(wb, "cond", date_df)

negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
conditionalFormatting(wb, "cond", cols=2, rows=2:21, rule="<TODAY()", style = negStyle)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)

然后你会得到:

编辑

如果您只想在非空单元格上应用格式，则可以通过更新 rule 来完成此操作如:'AND(b2<TODAY(), b2<>"")'

library(openxlsx)

set.seed(111)
date_df <- data.frame(id = 1:20,
                      dat_col = as.Date("2019-09-20")-1:20)

# Shuffle data frame
date_df <- date_df[sample(nrow(date_df)), ]

wb <- createWorkbook()
addWorksheet(wb, "cond")

writeData(wb, "cond", date_df[1:8,], )

writeData(wb, "cond", date_df[9:10, ], startRow = 11, colNames = FALSE )
writeData(wb, "cond", date_df[11:20,], startRow = 15, colNames = FALSE)


negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")

conditionalFormatting(wb, "cond", cols=2, rows=2:24, rule='AND(b2<TODAY(), b2<>"")', style = negStyle)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)