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
Right-click and copy it (or use the keyboard shortcut Control + C) Select the entire range where you want to copy the conditional formatting (C2:C11 in this example) Right-click anywhere in the selection. Click on the Paste Special option.
To export a XLSX file click File, mouseover Export and click XLSX File. In the Export XLSX dialog, choose exported documents and other options. After confirmation of the dialog, select a destination for storing the exported XLSX file. Note: In the WebApp, the exported XLSX file is saved in the browser download folder.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules. Select the command you want, such as Between, Equal To Text that Contains, or A Date Occurring. Enter the values you want to use, and then select a format.
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:
#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
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=''))
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With