[R] read.xls and name of worksheet

[Gabor Grothendieck]

Here is a program that will get a list of the sheet names and then
read in the indicated name.  Modify to suit.

library(rcom)
xls <- "C:\\test.xls"
oxl <- comCreateObject('Excel.Application')
comSetProperty(oxl, "Visible", TRUE)
owb <- comGetProperty(oxl, "Workbooks")
ob <- comInvoke(owb, "Open", xls)
osheets <- comGetProperty(ob, "Worksheets")
n <- comGetProperty(osheets, "Count")
ithSheetName <- function(i)
	comGetProperty(comGetProperty(osheets, "Item", i), "Name")
sheetNames <- sapply(1:n, ithSheetName)
comInvoke(oxl, "Quit")

library(gdata)
read.xls(xls, match("xyz", sheetNames), na.strings = "na ")


On Thu, Mar 12, 2009 at 10:46 AM, Juergen Rose <rose at rz.uni-potsdam.de> wrote:
> Hi,
>
> I would like to some excel files with some worksheets. I tried this with
> the following R script:
>
> library(gdata)
> i<-1
> rc<-0
> while(rc != "try-error") {
>
> wksh<-try(read.xls("cluster-microarray-FW.xls",sheet=i,verbose=TRUE,perl="perl"))
>  rc<-class(wksh)
>  print(sprintf("------- i=%2d rc=%s ---------------",i,rc))
>  if (rc != "try-error") {
>    print("--- summary(wksh)= -----");  print(summary(wksh))
>    i <- i+1
>  }
> }
>
> At first I found the problem that xls2csv adds one space to the every
> item in the last column.
> The second problem is, that I could not found an a possibility to save
> the name of the worksheet. It is shown on the screen, if I use the last
> script. I see something like
>
> Writing Sheet number 1 ('strenger') to file
> '/tmp/RtmpFpSkBB/file327b23c6.csv'
>
> The word in the single quotes is the name of the worksheet.
> Therefore I tried to redirect the standard output to a file
>
> library(gdata)
> i<-1
> rc<-0
> while(rc != "try-error") {
>  log=paste("text.log",i,sep="")
>  print("log=");  print(log)
>  #con<-file(log,open="wt")
>  #sink(con, type=c("output","message"))
>  #sink(con, type="output")
>  #sink(con, type="message")
>  sink(log)
>
> wksh<-try(read.xls("cluster-microarray-FW.xls",sheet=i,verbose=TRUE,perl="perl"))
>  sink()
>  rc<-class(wksh)
>  print(sprintf("------- i=%2d rc=%s ---------------",i,rc))
>  if (rc != "try-error") {
>    cmd<-paste("sed -n \"s/.*[(](.*)[)].*/\1/p\" ",log," > text.log")
>    system(cmd)
>                                        #unlink(log)
>    print("--- summary(wksh)= -----");  print(summary(wksh))
>    i <- i+1
>  }
> }
>
> But now I don't find the information about Writing Sheet number in
> text.log*. Any hint appreciated.
>
> Regards
>
> --
> Juergen Rose <rose at rz.uni-potsdam.de>
> Uni-Potsdam
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>