R help - Jul 2005 - Is it possible to create highly customized report in *.xlsformat by using R/S+?

Your surest bet is to look into S+, not R, since the former does
'integrate' with Microsoft applications. To what extent, I don'
know.
This is a desirable feature (IMO) and a reason to use S+ rather then R
(already discussed in different thread).

Try the S+ newsgroup.

> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch 
> [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Wensui Liu
> Sent: Wednesday, July 20, 2005 10:56 AM
> To: Greg Snow
> Cc: r-help at stat.math.ethz.ch
> Subject: Re: [R] Is it possible to create highly customized 
> report in *.xlsformat by using R/S+?
> 
> I appreciate your reply and understand your point completely. 
> But at times we can't change the rule, the only choice is to 
> follow the rule.
> Most deliverables in my work are in excel format.
> 
> On 7/20/05, Greg Snow <greg.snow at ihc.com> wrote:
> > See:
> > 
> > http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html
> > and
> > http://www.stat.uiowa.edu/~jcryer/JSMTalk2001.pdf
> > 
> > Greg Snow, Ph.D.
> > Statistical Data Center, LDS Hospital
> > Intermountain Health Care
> > greg.snow at ihc.com
> > (801) 408-8111
> > 
> > >>> Wensui Liu <liuwensui at gmail.com> 07/19/05
03:22PM >>>
> > I remember in one slide of Prof. Ripley's presentation overhead,
he
> > said the most popular data analysis software is excel.
> > 
> > So is there any resource or tutorial on this topic?
> > 
> > Thank you so much!
> > 
> > ______________________________________________
> > R-help at stat.math.ethz.ch mailing list
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide!
> > http://www.R-project.org/posting-guide.html
> > 
> > 
> 
> 
> --
> WenSui Liu, MS MA
> Senior Decision Support Analyst
> Division of Health Policy and Clinical Effectiveness 
> Cincinnati Children Hospital Medical Center
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
> --------------------------------------------------------------
> --------------
> This e-mail and any attachments may be confidential or\ > ...{{dropped}}

I have a package which I use to create excel files from R.

I have not been able to produce a configure script general enough for me
to post it to cran, but I will send it to you if you like.

I use it for production jobs on our linux servers.  You may have to
tinker a bit to get it to compile on windows.

Have a look at the examples below.  If it suits your needs, I will send
it to you.

Cheers,
Whit


Here are the examples from the help page:

     nc <- 4
     nr <- 20
     x <- matrix(rnorm(nc*nr),ncol=nc,nrow=nr)

     rownames(x) <- rep(letters,length=nr)
     colnames(x) <- rep(letters,length=nc)

     # write a matrix
     write.xls(x,"matrixFromR.xls","matrix")
    
write.xls(x,"matrixFromR.no.colnms.xls","matrix",writeColNms=FALSE)
    
write.xls(x,"matrix.no.rownms.xls","matrix",writeRowNms=FALSE)
 
write.xls(x,"matrix.no.nms.xls","matrix",writeColNms=FALSE,writeRowNms=F
ALSE)

     # add some formats
 
write.xls(x,"matrixFromR_formatted.xls","matrix",formats=rep("0.0",nc))
 
write.xls(x,"matrixFromR_formatted2.xls","matrix",formats=rep(c("0.0","0
"),nc/2))
 
write.xls(x,"matrixFromR_formatted3.xls","matrix",formats=rep("#,##0.0;[
Red]#-##0.0;\"\"",nc))

     write.xls(x[,1],"vectorFromR.xls","vector")
 
write.xls(x[,1],"vectorFromR.no.colnms.xls","vector",writeColNms=FALSE)
 
write.xls(x[,1],"vectorFromR.no.rownms.xls","vector",writeRowNms=FALSE)
 
write.xls(x[,1],"vectorFromR.no.nms.xls","vector",writeColNms=FALSE,writ
eRowNms=FALSE)

     # char data test
     y <- matrix(rep(letters,each=26),ncol=26)
     rownames(y) <- rep(letters,length=26)
     colnames(y) <- rep(letters,length=26)
     write.xls(y,"char.data.xls","alphabet")

     # logical data test
     z <- matrix(as.logical(round(runif(nc*nr),0)),ncol=nc)
     rownames(z) <- rep(letters,length=nr)
     colnames(z) <- rep(letters,length=nc)

     write.xls(z,"logical.data.xls","myLogic")
    
write.xls(z[,1],"logical.data.vector.xls","myLogicVector")



-----Original Message-----
From: r-help-bounces at stat.math.ethz.ch
[mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Wensui Liu
Sent: Wednesday, July 20, 2005 10:56 AM
To: Greg Snow
Cc: r-help at stat.math.ethz.ch
Subject: Re: [R] Is it possible to create highly customized report in
*.xlsformat by using R/S+?

I appreciate your reply and understand your point completely. But at
times we can't change the rule, the only choice is to follow the rule.
Most deliverables in my work are in excel format.

On 7/20/05, Greg Snow <greg.snow at ihc.com>
wrote:
> See:
> 
> http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html
> and
> http://www.stat.uiowa.edu/~jcryer/JSMTalk2001.pdf
> 
> Greg Snow, Ph.D.
> Statistical Data Center, LDS Hospital
> Intermountain Health Care
> greg.snow at ihc.com
> (801) 408-8111
> 
> >>> Wensui Liu <liuwensui at gmail.com> 07/19/05 03:22PM
>>>
> I remember in one slide of Prof. Ripley's presentation overhead, he 
> said the most popular data analysis software is excel.
> 
> So is there any resource or tutorial on this topic?
> 
> Thank you so much!
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
> 
> 

--
WenSui Liu, MS MA
Senior Decision Support Analyst
Division of Health Policy and Clinical Effectiveness Cincinnati Children
Hospital Medical Center

______________________________________________
R-help at stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html

It _does_ do formatting:

write.xls(x,"matrixFromR_formatted3.xls","matrix",formats=rep("#,##0.0;[
Red]#-##0.0;\"\"",nc))

And also writes multiple sheets to a workbook (I didn't post all the
examples).

I need those features for the reports I generate, but if the perl module
has the same features, then I'm happy to contribute to a solution for
Greg's package that would use perl instead.  The R -> C++ -> Java
->
file method is a huge pain.

On the other hand, there may be no reason to continue to work on this
project as MS has suggested that it will use xml formats for the next
version of office: http://www.theregister.co.uk/2005/06/03/_office_xml/

btw, here's the list of alternatives suggested by the POI team:
http://jakarta.apache.org/poi/hssf/alternatives.html

fyi, here is the project homepage:
http://jakarta.apache.org/poi/index.html

Thx,
Whit

-----Original Message-----
From: r-help-bounces at stat.math.ethz.ch
[mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Dirk Eddelbuettel
Sent: Wednesday, July 20, 2005 11:53 AM
To: r-help at stat.math.ethz.ch
Subject: Re: [R]Is it possible to create highly customized report in
*.xlsformat by using R/S+?

Whit Armstrong <whit <at> twinfieldscapital.com>
writes:
> I have a package which I use to create excel files from R.
> 
> I have not been able to produce a configure script general enough for 
> me to post it to cran, but I will send it to you if you like.
> 
> I use it for production jobs on our linux servers.  You may have to 
> tinker a bit to get it to compile on windows.
> 
> Have a look at the examples below.  If it suits your needs, I will 
> send it to you.
Looks straightforward, but does not fancy-pants formatting.  If that is
sufficient, could one not borrow Greg's approach from read.xls() [ in
package gdata, part of the unbundled gregmisc bundle ] and create a
simpler write.xls() that uses Perl's SpreadSheet::WriteExcel to write
xls files -- just like Greg uses the related SpreadSheet::ReadExcel to
read xls files ?  That way you avoid the configure hassles of the Java
module you use here [ if I recall correctly, you borrowed this from an
Apache sub-project, no ? ]

Cheers, Dirk

______________________________________________
R-help at stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html