r - 使用 R 过滤/保留 Excel 文件中的彩色单元格

这是一个很长的答案，而且有点笨拙，但它确实有效。

这篇文章Using R to read out excel-colorinfo展示了如何将颜色信息作为向量提取，但我们希望将此向量与我们的数据框对齐，然后创建一个索引来过滤我们的单元格。

首先读入DataSet一个数据框（我们稍后将使用它）和同一个工作簿wb，我们可以使用xlsx::loadWorkbook它来提取颜色信息（按照上面的链接）。

library(openxlsx)
library(xlsx)
library(tidyr)

DataSet<-openxlsx::read.xlsx("SpeciesByYear_Colored.xlsx")
wb<-xlsx::getSheets(xlsx::loadWorkbook("SpeciesByYear_Colored.xlsx"))[[1]]

然后从上面的 SO 链接中，我们将颜色信息作为向量提取出来：

rows<-getRows(wb)
cells<-getCells(rows)
styles <- sapply(cells, getCellStyle)

cellColor <- function(style) 
{
  fg  <- style$getFillForegroundXSSFColor()
  rgb <- tryCatch(fg$getRgb(), error = function(e) NULL)
  rgb <- paste(rgb, collapse = "")
  return(rgb)
}
colors<-sapply(styles, cellColor)

现在让我们看看向量

> head(colors,n=100)
     1.1      1.2      1.3      1.4      1.5      1.6      1.7      1.8 
      ""       ""       ""       ""       ""       ""       ""       "" 
     1.9     1.10     1.11     1.12     1.13     1.14     1.15     1.16 
      ""       ""       ""       ""       ""       ""       ""       "" 
    1.17     1.18     1.19     1.20     1.21     1.22     1.23     1.24 
      ""       ""       ""       ""       ""       ""       ""       "" 

    ...

    2.32     2.33     2.34     2.35     2.36     2.37     2.38     2.39 
      ""       ""       ""       ""       ""       ""       ""       "" 
    2.40     2.41     2.42     2.43     2.44     2.45     2.46     2.47 
      ""       ""       ""       "" "70ad47" "70ad47"       "" "70ad47" 
    2.48     2.49      3.1      3.2 
"70ad47" "70ad47"       ""       ""

此命名向量中的第一个数字是行号，第二个（在之后.）是列号。我们需要提取这些信息。引号内是颜色代码。所以我们把它变成一个数据框，并用正则表达式（特别是反向引用）添加行和列信息：

dat<-as.data.frame(colors)
dat$rows<-as.numeric(gsub(pattern = "([0-9]+).([0-9]+)",replacement ="\\1", row.names(dat)))
dat$cols<-as.numeric(gsub(pattern = "([0-9]+).([0-9]+)",replacement ="\\2", row.names(dat)))

> head(dat)
    colors rows cols
1.1           1    1
1.2           1    2
1.3           1    3
1.4           1    4
1.5           1    5
1.6           1    6

现在我们可以检查尺寸是否有意义：

> dim(dat)
[1] 3332    3

有 3332 个颜色值，几乎对应于我们的DataSet尺寸：

> dim(DataSet)
[1] 67 49

> 67*49
[1] 3283

为什么这不匹配？颜色值包括标题行（年份），而在中DataSet，它们位于数据框“外部”并包含在列名中，因此我们需要在计算中添加一行以获取要匹配的值：

> (67+1)*49
[1] 3332

记住这一点很重要，我们很快就会看到。

这里我只有两种颜色"70ad47" == green，"" == no fill 所以我将创建一个blanks包含要删除的单元格的对象。

blanks<-dat[dat$colors!="70ad47",]  ## OR: blanks<-dat[dat$colors=="",]

然后我删除第一列，这是一个有价值的索引 (col 1) == Species。第一行也是如此，其中包括标题 ( Year)。我们不想删除这些，这就是为什么我们将它们从删除列表中删除（有点复杂）。

blanks<-blanks[which(blanks$cols!=1),] 
blanks<-blanks[which(blanks$rows!=1),]

现在这是诀窍，我们现在必须将索引向下移动一个，因为DataSet从数据的第 1 行开始，行 = 1，而在另一个名为的数据集中dat，标题行是第 1 行，第一行数据是第 2 行。

blanks$rows<-blanks$rows-1

DataSet现在我们将没有填充颜色的每个观察值都设置为 -1（或我们以后可以过滤的任何其他有用值）

for(i in 1:dim(blanks)[1]){
  DataSet[blanks$rows[i],blanks$cols[i]]<- -1 
}

然后我们可以转向长格式来轻松删除我们不感兴趣的单元格

DataSet.Long<-DataSet %>% pivot_longer(cols=`1967`:`2019`,names_to = "Year")
> DataSet.Long
# A tibble: 3,216 x 3
   Species  Year  value
   <chr>    <chr> <dbl>
 1 Albacore 1967     -1
 2 Albacore 1972     -1
 3 Albacore 1973     -1
 4 Albacore 1974     -1
 5 Albacore 1975     -1
 6 Albacore 1977     -1
 7 Albacore 1978     -1
 8 Albacore 1979     -1
 9 Albacore 1980     -1
10 Albacore 1981     -1
# ... with 3,206 more rows

现在只保持高于 -1 的值

DataSet.Truncated<-DataSet.Long[DataSet.Long$value>-1,] 


> DataSet.Truncated
# A tibble: 134 x 3
   Species    Year  value
   <chr>      <chr> <dbl>
 1 Albacore   2014     38
 2 Albacore   2015     30
 3 Albacore   2017     24
 4 Albacore   2018     49
 5 Albacore   2019     18
 6 Blue Shark 1999      1
 7 Blue Shark 2000      9
 8 Blue Shark 2002     18
 9 Blue Shark 2003      2
10 Blue Shark 2006     11
# ... with 124 more rows

r - 使用 R 过滤/保留 Excel 文件中的彩色单元格

问题描述

解决方案

推荐阅读