export data frames to Excel via xlsx with conditional formatting

Try this out. I changed a few things, including the a slight change to the call to Fill and limiting the cells included for consideration to those with numeric data. I used lapply to apply the conditional formatting.

  cols <- sample(c(1:5), 1) # number of columns to vary to mimic this unknown  label <- rep(paste0("label ", seq(from=1, to=10)))  mydata <- data.frame(label)  for (i in 1:cols) {    mydata[,i+1] <- sample(c(1:10), 10)  }# exporting data.frame to excel is easy with xlsx package  sheetname <- "mysheet"  write.xlsx(mydata, "mydata.xlsx", sheetName=sheetname)  file <- "mydata.xlsx"# but we want to highlight cells if value greater than or equal to 5  wb <- loadWorkbook(file)              # load workbook  fo <- Fill(foregroundColor="yellow")  # create fill object  cs <- CellStyle(wb, fill=fo)          # create cell style  sheets <- getSheets(wb)               # get all sheets  sheet <- sheets[[sheetname]]          # get specific sheet  rows <- getRows(sheet, rowIndex=2:(nrow(mydata)+1)     # get rows                                                         # 1st row is headers  cells <- getCells(rows, colIndex = 3:(cols+3))       # get cells# in the wb I import with loadWorkbook, numeric data starts in column 3# and the first two columns are row number and label number  values <- lapply(cells, getCellValue) # extract the values# find cells meeting conditional criteria   highlight <- "test"  for (i in names(values)) {    x <- as.numeric(values[i])    if (x>=5 & !is.na(x)) {      highlight <- c(highlight, i)    }      }  highlight <- highlight[-1]lapply(names(cells[highlight]),       function(ii)setCellStyle(cells[[ii]],cs))saveWorkbook(wb, file)

Old question, but for people that still research this topic:

In the package openxlsx, there is a function that makes this much easier- conditionalFormatting()

Below is an example:

library(openxlsx)wb <- createWorkbook()addWorksheet(wb, "cellIs")## rule applies to all each cell in rangewriteData(wb, "cellIs", -5:5)writeData(wb, "cellIs", LETTERS[1:11], startCol=2)conditionalFormatting(wb, "cellIs", cols=1, rows=1:11, rule="!=0", style = negStyle)conditionalFormatting(wb, "cellIs", cols=1, rows=1:11, rule="==0", style = posStyle)saveWorkbook(wb, "conditionalFormattingExample.xlsx", TRUE)

you can read about it here and see many other types of conditional highlighting it can do:https://rdrr.io/cran/openxlsx/man/conditionalFormatting.html

It has been a while since I used this feature. Yes it should be possible to save conditional formatting. My (old) code is given below. Hope it helps you.

file.name <- paste('loadings.',state$data,'.xls', sep = "")wb <- loadWorkbook(file.name, create = TRUE)createSheet(wb, name = 'loadings')clearSheet(wb, sheet = 'loadings')Variables <- rownames(df)df.loadings <- cbind(Variables,df)df.loadings[,'Communality'] <- NULLwriteWorksheet(wb,df.loadings[,-1], sheet = 'loadings', rownames = 'Variables', startRow = 1, startCol = 1)max.loading <- createCellStyle(wb)setFillPattern(max.loading, fill = XLC$"FILL.SOLID_FOREGROUND")setFillForegroundColor(max.loading, color = XLC$"COLOR.SKY_BLUE")maxVal <- apply(abs(df.loadings[,-1]),1,max)maxValIndex <- which(abs(df.loadings[,-1]) == maxVal, arr.ind = TRUE)setCellStyle(wb, sheet = "loadings", row = maxValIndex[,'row']+1, col = maxValIndex[,'col']+1, cellstyle = max.loading)df.corr <- data.frame(cor(f.data))df.corr <- cbind(Variables,df.corr)createSheet(wb, name = 'correlations')clearSheet(wb, sheet = 'correlations')writeWorksheet(wb, df.corr, sheet = 'correlations', startRow = 1, startCol = 1)corr <- createCellStyle(wb)setFillPattern(corr, fill = XLC$"FILL.SOLID_FOREGROUND")setFillForegroundColor(corr, color = XLC$"COLOR.SKY_BLUE")corrIndex <- which(abs(df.corr[,-1]) > .3 & df.corr[,-1] != 1 , arr.ind = TRUE)setCellStyle(wb, sheet = "correlations", row = corrIndex[,'row']+1, col = corrIndex[,'col']+1, cellstyle = corr)saveWorkbook(wb)if(.Platform$OS.type == "unix") {    execute(paste("browseURL(\"",getwd(),'/',file.name,"\", browser = '/usr/bin/open')",sep=''))} else {    execute(paste("browseURL(\"",getwd(),'/',file.name,"\", browser = NULL)",sep=''))}