unable to find "setCellStyle" function

[Fortunat]

Hello, I'm using the setCellStyle function, openxlsx package is already installed and loaded, but I'm getting an error message: unable to find "setCellStyle" function. I also tried with the readxl package, but no success. Does anyone have a solution to this problem, please?

[prubin]

The xlsx library has a setCellStyle function. I don't think openxlsx does.

[Fortunat]

Thank you Prubin
It seems that xlsx requires Java. loading it unfortunately gives an error message.

[prubin]

Yes, xlsx requires Java (which is pretty ubiquitous these days). It also requires the rJava R package.

[Fortunat]

Hello here's the data exercice.pdf (19.5 KB) I'm using to try and format the last column (scale_color): the idea is to put green if the scale column contains FL and red, when it contains MF.
I've already installed and loaded xlsx and its Java and rJava dependent packages. when I use this script :

mfs2 <- read.xlsx("./inputs/exercice.xlsx")

wb <- createWorkbook()

addWorksheet(wb, "reshape")

Écrire les données dans la feuille

writeData(wb, "reshape", mfs2)

Créer un style de cellule personnalisé pour chaque condition

style1 <- createStyle(fgFill = "#FF0000") # Rouge pour une condition

style2 <- createStyle(fgFill = "#00FF00") # Vert pour une autre condition

Appliquer le style en fonction des conditions

rows <- nrow(mfs2)

for (i in 1:rows) {

if (mfs2$echelle[i] =="MF") {

setCellStyle(wb, sheet = "reshape", row = i+1, col = 4, cellStyle = style1)

} else if (mfs2$echelle[i] == "FL") {

setCellStyle(wb, sheet = "reshape", row = i+1, col = 4, cellStyle = style2)

}

}

Sauvegarder le workbook

saveWorkbook(wb, " formatting file.xlsx", overwrite = TRUE)

I get this error message that I don't understand the origin of the error:
Error in setCellStyle(wb, sheet = "reshape", row = i + 1, col = 4, cellStyle = style1) : **
** unused arguments (sheet = "reshape", row = i + 1, col = 4)

[vedoa]

Hi @Fortunat ,

i would stick to openxlsx

library(openxlsx)
## your file 
df <- read.xlsx(xlsxFile = "exercise.xlsx")

## https://rdrr.io/cran/openxlsx/man/addStyle.html

## Create a new workbook
wb <- createWorkbook("Fortunat")

## Add a worksheets
addWorksheet(wb, "reshape")

## write data to worksheet reshape
writeData(wb, sheet = "reshape", df, rowNames = FALSE)

## your style
style1 <- createStyle(fgFill = "#FF0000") # Rouge pour une condition
style2 <- createStyle(fgFill = "#00FF00")

## add style 
addStyle(wb, sheet = "reshape", style1, rows = which(df$couleur_echell == "rouge") + 1, cols = 4, gridExpand = FALSE)
addStyle(wb, sheet = "reshape", style2, rows = which(df$couleur_echell == "vert") + 1, cols = 4, gridExpand = FALSE)

## write to file
saveWorkbook(wb, "addStyleExample.xlsx", overwrite = TRUE)

[Fortunat]

Thanks @vedoa for your support, your code worked well and produced the desired result.