使用xlsx格式进行Excel单元格着色

Question

初始代码：

假设我们正在使用以下命令创建虚拟数据：

Data <- data.frame(
    X = paste(c(sample(1:10),sample(1:10)), collapse=";"),
    Y = sample(c("yes", "no"), 10, replace = TRUE)
)

输出:

                                           X   Y
1  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
2  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
3  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
4  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
5  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
6  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
7  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7  no
8  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
9  10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes
10 10;7;4;3;8;6;5;2;9;1;3;5;10;2;9;6;8;4;1;7 yes

问题:

使用xlsx包，我可以将X列数据输出到一个着色的Excel文件中。

是否有一种方法，我可以将大于5的值标记为红色，小于5的值标记为蓝色，并将它们放在同一个单元格中。基本上，我只是将这个表写入Excel，但是其中一些值是着色的。

提前致谢，

- alap

---

我想在R中生成一个文件，而不是事后有条件地格式化它。 - alap

为什么不创建一个Excel工作簿“模板”，并使用xlsx或XLConnect包直接将数据写入单元格呢？ - Carl Witthoft

你能具体说明一下“模板”的含义吗？目前我有一个包含10列的Excel文件，其中2列是空的。在其中一列中，我按照之前指定的方式输出了列“X”。如果要输出到新列，则需要对该列的每个单元格进行着色。我面临的问题是，在一个单元格中有多个数字。我需要对某些数字进行着色，而对其他数字不进行着色。我该如何实现这一点？ - alap

一个单元格中只能有一个数字。因此，如果您想将多个值写入单个单元格，则必须将它们转换为字符字符串。希望您不要这样做，因为这样做非常糟糕。现在，如果您正确地将单个值写入每个单元格，请首先打开Excel工作簿，并设置列“X”要进入的条件格式为您想要的颜色与值条件。然后当您从R写入工作簿时，着色将发生。 - Carl Witthoft

仔细再看了一下你的评论，我觉得答案可能就在条件格式设置里。请把它作为一个答案发出来！我本来想用R来完成所有操作，但是只有在Excel中才能实现。 - alap

Answer 1

我基本上是从这个问题和我的答案中复制代码，并对其进行了一些调整以适应此用例。我不确定在这方面的礼节，但我只是想表明这是可行的！如果我在重用链接问题中的代码时做了什么不该做的事情，请让我知道。如果现在看到它是重复的，因为其他问题已经得到解答，那我也没关系。我只是想帮忙！

首先，稍微重新格式化数据。

# split the X column so there will be one numeric entry per cell 
d <- matrix(as.numeric(unlist(strsplit(as.character(Data$X), ";"))), 
           ncol = 20, byrow = TRUE)

d <- data.frame(d, Data$Y)
cols <- length(d[1, ]) # number of columns, we'll use this later

其次，我们可以使用 xlsx 中的函数创建工作簿，然后获取单元格的值。

library(xlsx)

# exporting data.frame to excel is easy with xlsx package
sheetname <- "mysheet"
write.xlsx(d, "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
fo1 <- Fill(foregroundColor="blue")   # create fill object # 1
cs1 <- CellStyle(wb, fill=fo1)        # create cell style # 1
fo2 <- Fill(foregroundColor="red")    # create fill object # 2
cs2 <- CellStyle(wb, fill=fo2)        # create cell style # 2 
sheets <- getSheets(wb)               # get all sheets
sheet <- sheets[[sheetname]]          # get specific sheet
rows <- getRows(sheet, rowIndex=2:(nrow(d)+1))     # get rows
                                                   # 1st row is headers
cells <- getCells(rows, colIndex = 2:cols)         # get cells

# in the wb I import with loadWorkbook, numeric data starts in column 2
# The first column is row numbers.  The last column is "yes" and "no" entries, so
# we do not include them, thus we use colIndex = 2:cols

values <- lapply(cells, getCellValue) # extract the cell values

接下来我们需要找到需要根据条件进行格式化的单元格。

# find cells meeting conditional criteria > 5
highlightblue <- NULL
for (i in names(values)) {
  x <- as.numeric(values[i])
  if (x > 5 && !is.na(x)) {
    highlightblue <- c(highlightblue, i)
  }    
}

# find cells meeting conditional criteria < 5
highlightred <- NULL
for (i in names(values)) {
  x <- as.numeric(values[i])
  if (x < 5 && !is.na(x)) {
    highlightred <- c(highlightred, i)
  }    
}

最后，应用格式并保存工作簿。

lapply(names(cells[highlightblue]),
       function(ii) setCellStyle(cells[[ii]], cs1))

lapply(names(cells[highlightred]),
       function(ii) setCellStyle(cells[[ii]], cs2))

saveWorkbook(wb, file)

- Jota

---

似乎您已经应用了着色和高亮显示，但这不是条件格式，Excel中的条件格式是动态设置，如果单元格值发生更改，则会自动更改。 - Carl Witthoft

我对条件格式并不感兴趣，我只想着色一些东西。但是还是谢谢大家的回答！ - alap

我用你的脚本成功地完成了我的填充。非常完美。谢谢。 - Shicheng Guo

功能完美，但在大数据集上需要相当长的时间，请记住这一点。谢谢！ - Olympia

Answer 2

虽然这是一个老问题，但对于仍在研究此主题的人来说：

在openxlsx包中，有一个函数可以让此过程变得更加容易-conditionalFormatting()

下面是一个例子：

#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)

您可以在此处阅读有关条件格式的详细信息，并查看其它许多类型的条件高亮显示： https://www.rdocumentation.org/packages/openxlsx/versions/4.2.5/topics/conditionalFormatting

- Gingie

Answer 3

我怀疑直接从R更改Excel的条件格式可能是不可能的。因此，首先打开Excel工作簿并设置列“X”的条件格式，使其符合您想要的颜色与值的条件。然后，当您从R写入工作簿时，着色将发生。

- Carl Witthoft