温馨提示:本文翻译自stackoverflow.com,查看原文请点击:r - export data frames to Excel via xlsx with conditional formatting

r - 使用条件格式通过xlsx将数据框导出到Excel

发布于 2020-03-27 11:42:14

我想将数据框导出到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

查看更多

查看更多

提问者
Eric Green
被浏览
42
Jota 2014-02-11 08:07

试试看 我做了一些更改,包括对调用的细微更改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)