Re: Exporting to Excel spreadsheet with multiple worksheets

["Thompson, Trevor" <tkt2@cdc.gov>]

Thanks for the response.  It appears that the options for exporting data to Excel are more limited than the import options.  I first attempted to do this with exportData but didn't see a way to export to multiple sheets since there was no pageNumber argument like there is in importData.  I thought the openData function would work, but it seems that the pageNumber argument does not work for openType="write".  This seems consistent with the lack of the pageNumber argument in exportData but I can't find any documentation to confirm this.

-----Original Message-----
From: Pravin Jadhav [mailto:pravinj@gmail.com]
Sent: Wednesday, April 13, 2005 9:53 PM
To: Thompson, Trevor
Cc: s-news@lists.biostat.wustl.edu
Subject: Re: [S] Exporting to Excel spreadsheet with multiple worksheets

here are some messages that I received to a similar query.

http://www.biostat.wustl.edu/archives/html/s-news/2004-12/msg00011.html

 

 MESSAGE 1

---------- Forwarded message ----------
From: Austin, Matt <maustin@amgen.com>
Date: Dec 2, 2004 4:55 PM
Subject: RE: [S] Importing a lot of Excel Worksheets into a data frame
To: Pravin Jadhav <pravinj@gmail.com>

Here is a section of code that might be helpful, the first section reads all
the pages into a list of dataframes and adds a column that specifies the
page by number to each dataframe. The the do.call functoin concatenates it
all together.

schoolData <- lapply(1:8, function(x)
                      data.frame(Page=x,

importData("c:/data_mirror/ElementaryMathFall041.xls",
                                            page=x,
                                            stringsAsFactors=FALSE)))

school.df <- do.call('rbind', schoolData)

Matt Austin
Statistician
 

MESSAGE 2 

---------- Forwarded message ----------
From: Biggerstaff, Brad J. <BKB5@cdc.gov>
Date: Dec 2, 2004 5:07 PM
Subject: RE: [S] Importing a lot of Excel Worksheets into a data frame
To: Pravin Jadhav <pravinj@gmail.com>

Perhaps not pretty, but if you know the number of pages, you can use
importData with a for loop:

> x.dat <-
importData(file="c:/TestImport.xls",type="excel",pageNumber=1)
> for(i in 2:2) x.dat <-
rbind(x.dat,importData(file="c:/TestImport.xls",type="excel",pageNumber=
i))

where there are just 2 pages here.

This does assume the worksheets are, as you say, "the same"...as is the
example workbook I include.

Cheers,
Brad

Brad Biggerstaff, Ph.D .
(970) 221-6473 ... BBiggerstaff@cdc.gov

 

MESSAGE 3 

 

---------- Forwarded message ----------
From: Molinari, Luciano <Luciano.Molinari@kispi.unizh.ch >
Date: Dec 3, 2004 4:48 AM
Subject: RE: [S] Importing a lot of Excel Worksheets into a data frame
To: Pravin Jadhav <pravinj@gmail.com>

SPLUS2K under W2K
Select:
File/Import Data/From File/
Then the Option submenu allows to select the page.

Alternatively, working from the command window,  the function import.data
has an undocumented parameter "PageNumber" which allows to input different
Excel work sheets, which can then be combined into a a single data frame.
You can try both ways on the attached Excel  file XY.

From the command window:
import.data(DataFrame="zz1",FileName=" XY.xls",FileT="EXCEL",PageNumber=1)
import.data(DataFrame="zz2",FileName="XY.xls",FileT="EXCEL",PageNumber=2)

Hope this helps,
L. Molinari
 

On 4/13/05, Thompson, Trevor <tkt2@cdc.gov> wrote:

Hi,

I'm trying to write out matrices within a loop to different worksheets
in the same Excel spreadsheet.  I was trying to do this with the
openData (using the pageNumber argument to specify the worksheet) and
writeNextDataRows functions.  When I do this, it only writes to one
worksheet and overwrites the data as the loop increments.  I am able to
read from multiple worksheets ok.  See examples below:

### Creates 3 dataframes from 3 worksheets in Excel file

for(i in 1:3) {

  out.file <- openData("test.xls", type="Excel", openType="read",
pageNumber = i)

  if(i==1) test.df1<-readNextDataRows(out.file)
  if(i==2) test.df2<-readNextDataRows(out.file)
  if(i==3) test.df3<-readNextDataRows(out.file)

  closeData(out.file)

}

#### Does not write to multiple sheets; overwrites 1st sheet

for(i in 1:3) {

  out.file <- openData("test2.xls", type="Excel", openType="write",
pageNumber = i)
  x <- matrix(rnorm(50), ncol=5)
  writeNextDataRows(out.file, x)
  closeData(out.file)
}

Does the pageNumber argument only work for openType="read"?  Is there
any other way to export to multiple worksheets?

Thanks for any advice.  I am using Splus 6.1 on MS Windows 2000.

-Trevor
--------------------------------------------------------------------
This message was distributed by s-news@lists.biostat.wustl.edu.  To
unsubscribe send e-mail to s-news-request@lists.biostat.wustl.edu with
the BODY of the message:  unsubscribe s-news

--
Pravin Jadhav
Graduate Student
Department of Pharmaceutics
MCV/Virginia Commonwealth University
DPE1/OCPB/CDER/Food and Drug Administration
Phone: (301) 594-5652
Fax: (301) 480-3212
/_/_/_/_
If you have an apple and I have an apple and we exchange these apples then you and I will still each have one apple. But if you have an idea and I have an idea and we exchange these ideas, then each of us will have two ideas.
--George Bernard Shaw