从Excel导入时如何忽略隐藏数据 [英] How to ignore hidden data when importing from Excel

问题描述

我有一组要导入到 R 中的excel文件.

I have a collection of excel files which I am importing into R.

文件包含我想忽略的隐藏数据-例如，简单地不导入它，或导入带有标志的文件(表明它已隐藏)，以便我可以将其删除.

The files contain hidden data which I would like to disregard -- eg, simply not importing it, or importing it with a flag indicating it was hidden so that I can then drop it.

文件包含两种类型的隐藏数据:

The files contain two types of hidden data:

• 完整工作表被隐藏
工作表中的
• 特定行被隐藏.

• Complete sheets are hidden
• Specific Rows within a sheet are hidden.

有什么方法可以确定excel中的数据何时被隐藏?

现在我正在使用 gdata 软件包，但很高兴使用 XLConnect 或其他软件包

Right now I am using the gdata package, but am happy to use XLConnect or other package

示例代码:

library(gdata)
xlsfile <- "test.xls"

# grab all the sheet names. 
# This is giving me both hidden & non-hidden sheets.  I would like only the latter
sheets <- sheetNames(xlsfile)

# read in the xls file, by sheet 
xlData <- 
  lapply(sheets, function(s) 
      read.xls(xlsfile, sheet=s, stringsAsFactors = FALSE))

如果需要，我可以创建一个虚拟 xls 文件并将其发布.

if needed, I can create a dummy xls file and post it.

推荐答案

XLConnect 有一个很好的函数，叫做 isSheetHidden ，它可以满足您的需求.假设 Sheet2 是隐藏的:

XLConnect has a nice function called isSheetHidden which does what you want. Assuming Sheet2 is hidden:

library(XLConnect)
xlsfile <- "Book1.xls"
wb <- loadWorkbook(xlsfile, create = TRUE)
isSheetHidden(wb, "Sheet1") # FALSE
isSheetHidden(wb, "Sheet2") # TRUE

在 gdata 中，您必须编写自己的函数，该函数调用基础的perl包以访问sheet属性，但是可以的.

In gdata you would have to write your own function that calls the underlying perl package to access the sheet property, but it is possible.

这篇关于从Excel导入时如何忽略隐藏数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！