R help - Mar 2009 - read.xls and name of worksheet

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

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.
>