`conditionalFormatting` for cells with text doesn't highlight entire row

[phargarten2]

Describe the bug
Thank you for making a package that easily allows me to create complicated Excel sheets from R. The conditionalFormatting() function is particularly powerful, and the examples/vignette was helpful.

I want to highlight an entire row based on finding text in a column.

To Reproduce
For example, from the vignette:

 library(tibble)
library(openxlsx)
fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
t <- tibble(text = purrr::map_chr(1:10, fn), num = 1:10)


wb <- createWorkbook()
addWorksheet(wb, "containsText")
## cells containing text
writeData(wb, "containsText", t)
conditionalFormatting(wb, "containsText", cols = 1, rows = 1:10, type = "contains", rule = "A")
   #`cols` must be 1 for the rule to work, as I need to tell Excel which column to find A.

#I added this code to determine if I can highlight the entire row that has the letter A. 
addWorksheet(wb, "containsText2")
writeData(wb, "containsText2", t)
conditionalFormatting(wb, "containsText2", cols = 1:2, rows = 1:10, type = "expression", rule="FIND(\"A\", A1) > 0")

saveWorkbook(wb, "conditionalFormattingExample.xlsx", TRUE)

packageVersion("openxlsx")
#> [1] '4.2.5.9001'
xfun::session_info("openxlsx")
#> R version 4.2.3 (2023-03-15 ucrt)
#> Platform: x86_64-w64-mingw32/x64 (64-bit)
#> Running under: Windows 10 x64 (build 19045)
#> 
#> Locale:
#>   LC_COLLATE=English_United States.utf8 
#>   LC_CTYPE=English_United States.utf8   
#>   LC_MONETARY=English_United States.utf8
#>   LC_NUMERIC=C                          
#>   LC_TIME=English_United States.utf8    
#> 
#> Package version:
#>   graphics_4.2.3      grDevices_4.2.3     methods_4.2.3      
#>   openxlsx_4.2.5.9001 Rcpp_1.0.10         stats_4.2.3        
#>   stringi_1.7.12      tools_4.2.3         utils_4.2.3        
#>   zip_2.3.0

Created on 2023-06-01 with reprex v2.0.2

Expected behavior
The entire row should be highlighted, as compared to the first column, wherever an "A" is found. In this case, that would be rows 3, 9, and 10 for columns A and B. The cols=1:2 argument above for the containsText2 sheet is ignored.
conditionalFormatting(wb, "containsText2", cols = 1:2, rows = 1:10, type = "expression", rule="FIND(\"A\", A1) > 0")

Screenshots
containsText sheet

contatinsText2 sheet

Additional context
I attempted to use two different types to find text in an Excel sheet and apply a style. The expression type seems to be more flexible than the contains type.

Other minor issues

1. FYI: the introduction vignette contains the deprecated conditionalFormat function.

2. In the documentation for conditionalFormatting, could you add text to roughly describe the default style, as I don't think users will be familiar with hex color codes? (i.e. (Red text overlaid in pink background))? I also figured out that the argument rule is essentially an Excel function with type = "expression" (the default). Could this be more clear under rules==expression in the documentation? Just some thoughts to make the function even better.

Thank you

[github-actions]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions]

This issue was closed because it has been stalled for 7 days with no activity.