通过带条件格式的xlsx将数据框导出到Excel [英] export data frames to Excel via xlsx with conditional formatting
问题描述
xlsx
包的 CellStyle
函数来关闭它。 下面我概述了我所尝试过的。大部分想法都来自软件包的帮助文件。当我尝试将我创建的样式应用到满足条件的单元格时,我会一路走到最后,并出现错误。我得到错误: .jcall(cell,V,setCellStyle,cellStyle $ ref)中的错误:RcallMethod:无效的对象参数
。
$ b $
library(xlsx)
#创建数据
cols < - sample(c(1:5),1)#number (paste0(label,seq(from = 1,to = 10)))
mydata< - data.frame(label)
(i in 1:cols){
mydata [,i + 1]< - sample(c(1:10),10)
}
#导出数据使用xlsx软件包
sheetname< - mysheet
write.xlsx(mydata,mydata.xlsx,sheetName = sheetname)
file< - mydata .xlsx
#但是,如果值大于或等于5
wb< - loadWorkbook(file)#加载工作簿
fo< - 填充(backgroundColor =黄色)#创建填充对象
cs< - CellStyle(wb,fill = fo)#create cell style
sheets < - getSheets(wb)#get all sheets
shee t< - sheets [[sheetname]]#获取特定工作表
rows< - getRows(sheet)#get rows
cells< - getCells(rows)#get cells
values< ; - lapply(cells,getCellValue)#提取值
#找到满足条件条件的单元格
highlight< - test
for(i in names(values)){
x< - as.numeric(values [i])
if(x> = 5& !is.na(x)){
突出显示< - c(突出显示,i)
}
}
突出显示< - 突出显示[-1]
#将样式应用到满足条件的单元格
if(length(highlight)> 0){#如果任何单元格符合条件,则继续$ b $ setCellStyle(单元格[highlight],cs)#不工作
}
#save
saveWorkbook(wb,file)
更新: 但是我得到错误: 试试看。我改变了一些东西,包括对 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 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: Update:
I've also tried: But I get the error: Try this out. I changed a few things, including the a slight change to the call to
这篇关于通过带条件格式的xlsx将数据框导出到Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
我也试过了:
$ p $ if(length(highlight)> 0) {#如果任何单元格满足条件
,则进入(h in 1:length(highlight)){
setCellStyle(cells [highlight [h]],cs)#不工作
}
.jcall单元格V,setCellStyle,cellStyle $ ref):RcallMethod:无法确定对象类
Fill
调用的细微变化,并将包含在内的单元格限制为数字数据。我使用 lapply
来应用条件格式。
cols< - 样本(c(1:5),1)#要改变的列数以模仿这个未知的
标签< - rep(paste0(label,seq(from = 1,to = 10)))$ (i(1:cols)){
mydata [,i + 1]< - sample(c(1:10),10 )
}
#使用xlsx包将数据输出到excel很容易
sheetname< - mysheet
write.xlsx(mydata,mydata.xlsx,sheetName = sheetname)
file < - mydata.xlsx
#但是如果值大于或等于5,我们想要突出显示单元格
wb < - loadWorkbook(file)#load workbook
fo < - Fill(foregroundColor =yellow)#create fill object
cs< - CellStyle(wb,fill = fo)#create cell style
sheets< - getSheets( wb)#获取所有表单
表单< - sheets [[sheetname]]#获取具体表单
行< - getRows(sheet,rowIndex = 2:(n行(mydata)+1)#获得行
#第一行是标题
单元格< - getCells(rows,colIndex = 3:(cols + 3))#get cells
#in我用loadWorkbook导入的wb,数字数据从第3列开始
#,前两列是行号和标签号
values< - lapply(cells,getCellValue)#extract
#查找符合条件标准的单元格
突出显示< - test
for(i in names(values)){
x< - as.numeric (数值[i])
if(x> = 5& !is.na(x)){
突出显示< - c(突出显示,i)
}
}
突出显示< - 突出显示[-1]
lapply(names(cells [highlight]),
function(ii)setCellStyle(cells [[ii]],cs))
saveWorkbook(wb,file)
CellStyle
functions of the xlsx
package.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
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)