Search code examples
ropenxlsx

Conditional format entire sheet using Openxlsx in R

I have data in R thats currently 645 rows by 94 columns. But for a reproducible data lets just take the "iris" dataset.

I want to save that out as an excel with conditioning formatting where every cell that contains "A" gets highlighted. I know I can do something like this:

wb <- createWorkbook()
addWorksheet(wb, "Data")
       
writeData(wb, "Data", iris)
conditionalFormatting(wb, "Data", cols = 1:5, rows = 1:151, type = "contains", rule = "A")

saveWorkbook(wb, "iris.xlsx", overwrite = TRUE)

But my question is, I had to manually write in:

cols = 1:5, rows = 1:151

But what if I just want it to be every cell? I.e. my data thats 645x94 might be 650x100 the next time I run the script, and I dont want to keep changing that text. How can I just make it default to the whole thing?

Solution

  • Use some basic functions to get the size of your data.

    Try:

    wb <- createWorkbook()
addWorksheet(wb, "Data")
       
writeData(wb, "Data", iris)
col_num <- ncol(iris)
row_num <- nrow(iris)
conditionalFormatting(wb, "Data", cols = 1:col_num, rows = 1:row_num, type = "contains", rule = "A")

saveWorkbook(wb, "iris.xlsx", overwrite = TRUE)