R可以识别Excel文件中是否有注释单元格吗? [英] Can R identify if the Excel file has commented cells?
问题描述
我有一个Excel工作表 .xlsx ,其中包含一些注释单元格.将其导入R后,R是否可以通过任何方式识别注释的单元格?
I have an excel sheet .xlsx, which have some commented cells in it. After importing it in R, is there any way by which R can identify the commented cells?
因为我只能对注释的单元格使用一些其他条件.
Because I have to use some if else conditions only to the commented cells.
推荐答案
假设我们有这个文件test.xlsx
:
Let's say we have this file, test.xlsx
:
使用 openxlsx ,我们可以将工作簿作为数据框对象读取,以仅提取数据:
Using openxlsx we can read the workbook as a dataframe object to extract only data:
library(openxlsx)
# read the data
df1 <- read.xlsx("test.xlsx")
df1
# 1 no
# 1 2 yes
# 2 3 no
# 3 5 no
# 4 10 yes
如果需要提取注释,则需要将其作为工作簿对象阅读:
If we need to extract comments, we need to read as workbook object:
# read as workbook object
wb <- loadWorkbook("test.xlsx")
检查对象的结构以查看单元格引用的存储位置:
Check the structure of the object to see where the cell reference is stored:
str(wb$comments)
# List of 3
# $ :List of 2
# ..$ :List of 5
# .. ..$ ref : chr "B2"
# ...
# ... etc
然后子集,循环浏览工作表以及带有注释的子集单元格引用:
Then subset, loop through sheets, and subset cell reference with comments:
lapply(wb$comments, function(sheet)
sapply(sheet, "[[", "ref"))
# [[1]]
# [1] "B2" "B5"
#
# [[2]]
# list()
#
# [[3]]
# list()
这意味着有3张纸,第1张纸在B2和B5处有2条注释,其他2张纸为空白.
This means there are 3 sheets, 1st sheet has 2 comments at B2 and B5, other 2 sheets are blank.
这篇关于R可以识别Excel文件中是否有注释单元格吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!