通过带有条件格式的 xlsx 将数据框导出到 Excel

本文介绍了通过带有条件格式的 xlsx 将数据框导出到 Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想将数据框导出到 Excel 并根据某些规则突出显示单元格.我不认为 这个答案 对类似问题是正确的.我认为这是可能的，并且我认为我可以使用 xlsx 包的 CellStyle 函数来接近.

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.

下面我概述了我尝试过的内容.大多数想法来自软件包帮助文件.当我尝试将我创建的样式应用于符合条件的单元格时，我一直走到最后并得到一个错误.我得到错误:Error in .jcall(cell, "V", "setCellStyle", cellStyle$ref) : RcallMethod: invalid object parameter.

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)

更新:我也试过:

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

但我收到错误:.jcall(cell, "V", "setCellStyle", cellStyle$ref) 中的错误:RcallMethod: cannot determine object class

推荐答案

试试这个.我更改了一些内容，包括对 Fill 调用的细微更改，以及将包含的单元格限制为包含数字数据的单元格.我使用 lapply 来应用条件格式.

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)