Skip to content

read.xls and name of worksheet

2 messages · Juergen Rose, Gabor Grothendieck

#
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
#
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: