r - 如何编写R来循环设置目录中每个文件的每个工作表

Question

我在这里有一个脚本，它可以很好地在某个列中获取一个数字。现在我不仅要收集目录中每个文件的第一张纸，还要收集每个文件的每张纸。

现在 R 写入的 .csv 文件显示 2 列，A 列是文件名，B 是 R 抓取的数字。

我应该在下面的脚本中添加什么修改以使 csv 输出显示 3 列，A 是文件名，B 是工作表名，C 是数字？

require(xlsx)
#setwd
setwd("D:\\Transferred Files\\")
files <- (Sys.glob("*.xls"))
f<-length(files)

DF <- data.frame(txt=rep("", f),num=rep(NA, f),stringsAsFactors=FALSE)

# files loop
for(i in 1:f)
{
  A<-read.xlsx(file=files[i],1,startColumn=1, endColumn=20, startRow=1, endRow=60)
  #Find price
  B<-as.data.frame.matrix(A)
  P<-B[which(apply(B, 1, function(x) any(grepl("P", x)))),which(apply(B, 2, function(x) any(grepl("P", 

x))))+6]

  #fill price DF
  DF[i, ] <-c(files[i],P)
}
write.csv(DF, "prices.csv", row.names=FALSE)

我尝试了 XLconnet，但不能真正让它发挥作用。

Answer 1

您有一个好的开始，但您正在询问如何将文件中的工作表添加到循环中。如果您阅读 ?read.xlsx ，你会在你的代码中看到你正在掩盖的两个参数(好吧，使用一个，忽略另一个):

Usage:

     read.xlsx(file, sheetIndex, sheetName=NULL, rowIndex=NULL,
       startRow=NULL, endRow=NULL, colIndex=NULL,
       as.data.frame=TRUE, header=TRUE, colClasses=NA,
       keepFormulas=FALSE, encoding="unknown", ...)

Arguments:

    file: the path to the file to read.

sheetIndex: a number representing the sheet index in the workbook.

sheetName: a character string with the sheet name.

您应该只需要提供两者之一。

您可能会问“我怎么知道工作表中有多少张工作表？” (对于 sheetIndex )甚至“工作表名称是什么？” (对于 sheetName)。 ?getSheets救援:

Usage:

     getSheets(wb)

Arguments:

      wb: a workbook object as returned by 'createWorksheet' or
          'loadWorksheet'.

Value:

     'getSheets' returns a list of java object references each pointing
     to an worksheet.  The list is named with the sheet names.

您需要使用 loadWorkbook(file)而不是 read.xlsx为了获得工作表名称，但稍微阅读手册将为您提供切换所需的信息。 (你可以使用类似 getSheets(loadWorkbook(file)) 的东西，但根据我的经验，我尽量避免在同一个脚本中多次打开同一个文件，不管自动关闭。)

作为替代方案，Hadley 的 readxl 包在其简单性、速度和稳定性方面显示出希望。它有 excel_sheets()和 read_excel()那应该可以满足您的需求。 (事实上​​，这就是它的全部......简单是“一件好事(tm)”。)

编辑 :

library(XLConnect)
## Loading required package: XLConnectJars
## XLConnect 0.2-11 by Mirai Solutions GmbH [aut],
##   Martin Studer [cre],
##   The Apache Software Foundation [ctb, cph] (Apache POI, Apache Commons
##     Codec),
##   Stephen Colebourne [ctb, cph] (Joda-Time Java library)
## http://www.mirai-solutions.com ,
## http://miraisolutions.wordpress.com
## Attaching package: 'XLConnect'
## The following objects are masked from 'package:xlsx':
##     createFreezePane, createSheet, createSplitPane, getCellStyle, getSheets, loadWorkbook, removeSheet, saveWorkbook, setCellStyle, setColumnWidth, setRowHeight

wb1 <- loadWorkbook('Book1.xlsx')
shts1 <- getSheets(wb1)
shts1
## [1] "Orig"   "Sheet2" "Sheet8" "Sheet3" "Sheet4" "Sheet5" "Sheet6" "Sheet7"
for (ws in shts1) {
    message(ws)                              # just announcing myself
    dat <- readWorksheet(wb1, ws)
    message(paste(dim(dat), collapse=' x ')) # do something meaningful, not this
}
## Orig
## 128 x 11
## Sheet2
## 128 x 11
## Sheet8
## 128 x 19
## Sheet3
## 17 x 11
## Sheet4
## 128 x 11
## Sheet5
## 128 x 11
## Sheet6
## 128 x 11
## Sheet7
## 128 x 11

编辑#2 :

作为更详细的迭代示例:

library(XLConnect)
for (fn in list.files(pattern="*.xlsx")) {
    message('Opening: ', fn)
    wb <- loadWorkbook(fn)
    shts <- getSheets(wb)
    message(sprintf('    %d Sheets: %s', length(shts),
                    paste(shts, collapse=', ')))
    for (sh in shts) {
        dat <- readWorksheet(wb, sh)
        ## do something meaningful with the data
    }        
}

我不太确定你在用你的代码做什么(因为你从来没有说过任何电子表格中包含的内容)，但是另一种方法(我将使用它来代替前面的双 - for 示例)是将所有内容包含在列表中:

dat <- sapply(list.files(pattern='*.xlsx'), function(fn) {
    wb <- loadWorkbook(fn)
    sapply(getSheets(wb), function(sh) readWorksheet(wb, sh))
})

str(dat, list.len=2)
## List of 4
##  $ Book1.xlsx:List of 8
##   ..$ Orig  :'data.frame':   128 obs. of  11 variables:
##   .. ..$ i   : num [1:128] 1 2 3 4 5 6 7 8 9 10 ...
##   .. ..$ x   : num [1:128] 1606527 7484 437881 1601729 1341668 ...
##   .. .. [list output truncated]
##   ..$ Sheet2:'data.frame':   128 obs. of  11 variables:
##   .. ..$ i   : num [1:128] 1 2 3 4 5 6 7 8 9 10 ...
##   .. ..$ x   : num [1:128] 1606527 7484 437881 1601729 1341668 ...
##   .. .. [list output truncated]
##   .. [list output truncated]
##  $ Book2.xlsx:List of 8
##   ..$ Orig  :'data.frame':   128 obs. of  11 variables:
##   .. ..$ i   : num [1:128] 1 2 3 4 5 6 7 8 9 10 ...
##   .. ..$ x   : num [1:128] 1606527 7484 437881 1601729 1341668 ...
##   .. .. [list output truncated]
##   ..$ Sheet2:'data.frame':   128 obs. of  11 variables:
##   .. ..$ i   : num [1:128] 1 2 3 4 5 6 7 8 9 10 ...
##   .. ..$ x   : num [1:128] 1606527 7484 437881 1601729 1341668 ...
##   .. .. [list output truncated]
##   .. [list output truncated]
##   [list output truncated]

如果您不关心区分特定工作表来自哪个工作簿 - 并随后简化数据处理 - 那么您可以将嵌套列表“展平”为单个列表:

flatdat <- unlist(dat, recur=FALSE)
str(flatdat, list.len=3)
## List of 555
##  $ Book1.xlsx.Orig  :'data.frame':   128 obs. of  11 variables:
##   ..$ i   : num [1:128] 1 2 3 4 5 6 7 8 9 10 ...
##   ..$ x   : num [1:128] 1606527 7484 437881 1601729 1341668 ...
##   ..$ c1  : num [1:128] 1 1 1 1 1 1 1 1 1 1 ...
##   .. [list output truncated]
##  $ Book1.xlsx.Sheet2:'data.frame':   128 obs. of  11 variables:
##   ..$ i   : num [1:128] 1 2 3 4 5 6 7 8 9 10 ...
##   ..$ x   : num [1:128] 1606527 7484 437881 1601729 1341668 ...
##   ..$ c1  : num [1:128] 1 1 1 1 1 1 1 1 1 1 ...
##   .. [list output truncated]
##  $ Book1.xlsx.Sheet8:'data.frame':   128 obs. of  19 variables:
##   ..$ i    : num [1:128] 1 2 3 4 5 6 7 8 9 10 ...
##   ..$ x    : num [1:128] 1606527 7484 437881 1601729 1341668 ...
##   ..$ c1   : num [1:128] 1 1 1 1 1 1 1 1 1 1 ...
##   .. [list output truncated]
##   [list output truncated]

现在，处理您的数据可能更简单。您用于查找“P”的代码有点缺陷，因为您将 data.frame 分配给另一个 data.frame 中的单元格，通常不赞成。

这可能会变成您的另一个问题。为此，我强烈建议您提供 better detailed问题，包括示例工作表的外观以及您希望输出的外观。