20

I want to export data frames to Excel and highlight cells according to certain rules. I don't think this answer to a similar question is correct. I think it is possible, and I think I get close using the CellStyle functions of the xlsx package.

Below I outline what I've tried. Most of the ideas come from the package help files. I get all the way to the end and get an error when I try to apply the style I created to the cells that meet the criteria. I get the error: Error in .jcall(cell, "V", "setCellStyle", cellStyle$ref) : RcallMethod: invalid object parameter.

library(xlsx)
# create data 
  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(backgroundColor="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)                # get rows
  cells <- getCells(rows)               # get cells
  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]
# apply style to cells that meet criteria
  if (length(highlight)>0) {            # proceed if any cells meet criteria
    setCellStyle(cells[highlight], cs)  # DOES NOT WORK
  }
# save
  saveWorkbook(wb, file)

Update: I've also tried:

if (length(highlight)>0) {                # proceed if any cells meet criteria
    for (h in 1:length(highlight)) {
      setCellStyle(cells[highlight[h]], cs)  # DOES NOT WORK
    }
  }

But I get the error: Error in .jcall(cell, "V", "setCellStyle", cellStyle$ref) : RcallMethod: cannot determine object class

Community
  • 1
  • 1
Eric Green
  • 7,385
  • 11
  • 56
  • 102

3 Answers3

15

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)
Jota
  • 17,281
  • 7
  • 63
  • 93
  • this is great. I'd like to propose one small change to make it more "reproducible". Since the number of columns will vary (which reflects my underlying use case), I'd suggest changing `rows <- getRows(sheet, rowIndex=2:11)` to `rows <- getRows(sheet, rowIndex=2:(nrow(mydata)+1)`. Other than that, works perfectly. – Eric Green Feb 10 '14 at 16:20
9

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:

#Load needed package
if (!require("pacman")
) install.packages("pacman")
pacman::p_load(
  #add list of libraries here
  openxlsx
)

##Create workbook and write in sample data
wb <- createWorkbook()
addWorksheet(wb, "Moving Row")
writeData(wb, "Moving Row", -5:5)
writeData(wb, "Moving Row", LETTERS[1:11], startCol = 2)

##Define how you want the cells to be formatted
negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")

## highlight row dependent on first cell in row
conditionalFormatting(wb, "Moving Row",
                      cols = 1:2,
                      rows = 1:11, rule = "$A1<0", style = negStyle
)
conditionalFormatting(wb, "Moving Row",
                      cols = 1:2,
                      rows = 1:11, rule = "$A1>0", style = posStyle
)

##Save workbook in default location
saveWorkbook(wb, "conditionalFormattingExample.xlsx", TRUE)

you can read about it here and see many other types of conditional highlighting it can do: https://www.rdocumentation.org/packages/openxlsx/versions/4.2.5/topics/conditionalFormatting

Gingie
  • 129
  • 1
  • 7
  • when I run the function, it works smoothly - but when I open the created workbook, I don't see any of the formatting. any chance you've dealt with this problem before? – Jared Annibale May 08 '20 at 21:20
  • The best answer! – drastega Feb 11 '21 at 19:54
  • Probably don't have this question anymore, but I think the reason the formatting didn't transfer over is because of package updates. You now need to write code to customize the style for the formatting you want. – Gingie Apr 22 '22 at 02:40
3

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'] <- NULL
writeWorksheet(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=''))
}
Vincent
  • 5,063
  • 3
  • 28
  • 39
  • Thanks, @Vincent. It appears that `setCellStyle()` no longer takes the same arguments, just `setCellStyle(cell, cellStyle)`. I'm thinking about how to adapt what you have. Thanks for sharing. – Eric Green Feb 07 '14 at 11:54
  • 1
    Eric: I am sorry but I just realized my code was for [XLConnect](http://cran.r-project.org/web/packages/XLConnect/index.html) and not for the XLSL package. Their syntax is quite similar, hence my confusion. I used this package to achieve conditional highlighting in an xls file. I hadn't used that code in about a year since I moved my app over to [Shiny](http://www.rstudio.com/shiny/). If the above answer might still be useful to you let me know, else I will remove it. – Vincent Feb 10 '14 at 00:34