我想将数据框导出到Excel并根据某些规则突出显示单元格。我认为类似问题的答案不正确。我认为这是有可能的,并且我认为我使用软件包的CellStyle
功能已经很接近了xlsx
。
下面我概述了我尝试过的方法。大多数想法来自软件包帮助文件。当我尝试将我创建的样式应用于满足条件的单元格时,我一直走到最后,并得到一个错误。我收到错误: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)
更新: 我也尝试过:
if (length(highlight)>0) { # proceed if any cells meet criteria
for (h in 1:length(highlight)) {
setCellStyle(cells[highlight[h]], cs) # DOES NOT WORK
}
}
但是我得到了错误: Error in .jcall(cell, "V", "setCellStyle", cellStyle$ref) : RcallMethod: cannot determine object class
试试看 我做了一些更改,包括对调用的细微更改Fill
以及将要考虑的单元格限制为包含数字数据的单元格。我曾经lapply
应用过条件格式。
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)
这很棒。我想提出一个小的更改,使其更具“可复制性”。由于列数会有所不同(这反映了我的基本用例),因此建议更改
rows <- getRows(sheet, rowIndex=2:11)
为rows <- getRows(sheet, rowIndex=2:(nrow(mydata)+1)
。除此之外,还可以完美地工作。