[Rd] xlsReadWrite Pro and embedding objects and files in Excel worksheets
Mark W Kimpel
mwkimpel at gmail.com
Mon Feb 12 02:51:04 CET 2007
Welcome back, I hope you had a good time away :)
I got what I thought were some answers that misinterpreted what my
intent was, so let me ask again and try to be more clear.
I would like to be able to use a single Excel spreadsheet as an archive
for any output I generate in a single R session, including pdf files of
graphics and possibly the R history or even the R workspace itself. I
would envision writing the files to be inserted first to a temp file and
then inserting them into Excel using whatever commands (Visual Basic?)
that would do that. I know it can be done from the menu, so I am pretty
sure that it can be done with VB. I am unsure, however, exactly how you
are generating the Excel files. For my own edification, are you using VB
or something similar?
Also, to make this not so Windows specific, would these files be
compatible with openOffice or some other open-source spreadsheet program
that would be compatible with the other OS's that R users employ? That
might make it more broadly appealing.
I know that efforts have been made to generate R-compendia that include
code and data. I think that is great for archiving analyses for the use
of other R users, but what I am seeking would be much more friendly to
my end users, which are biologists and psychologists. They don't want
zip archives that generate a bunch of files, they want just one file
with everything neat and tidy.
For example, one file with these sheets:
1. .Rworkspace generated at time the last sheet was written. This would
be akin to an R-compendia but mostly designed for archiving, not
2. methods page. I am working on an auto-generating methods page that
could be copy and pasted into a paper with minimal editing. I am even
including references that vary depending on the p.adjust method I use.
3. parameters that are passed to major functions like filters. I list
what filter I used and how many probesets remained after the filter was
5. matrix output.
I am interested in what others think of this idea in general. I am not
just trying to get something for myself. If someone has a better idea of
how to package analyses with graphics for the end-user, I'd love to hear it.
I would also be interested in feedback from other developers as to what
they think of my general idea. Is it worth pursuing? Would it be worthy
of a simple package?
Thanks for your help and hard-work Hans-Peter and I look forward to
hearing how things are going,
> Hi Mark,
> Sorry to not reply earlier, I was away this week.
>> Hans-Peter and other R developers,
>> How are you? Have you made any progess with embedding Url's in Excel?
> Yes, but I started with the update of the free version and got delayed
> there (not that I didn't know that paying customers should be
> prefered...) - I'll update the pro version right now and you should
> have it until Wednesday, maybe tomorrow. Another person asked to write
> formulas directly, it will be included also.
>> I am cc'ing R-devel to see if any of
>> those guys have ideas as well.
> Good idea.
> Regarding your post about your needs (report generation, container, ...):
> I think the suggestion from Gabor with controlling Excel from within R
> is an excellent way. xlsReadWritePro could be an option which has some
> advantages and some disadvantages.
> - you can do everything that Excel supports
> - this comes at a price: the interface from R is a bit technical. (the
> suggestion with VBA and Macro recorder is a good one imho. Reading
> about the Excel Object Model may also help)
> - it's more free (what a statement for an excel-based dependency...)
> - dependencies (installed Excel, RDCOMClient (probably a non-issue at
> your situation))
> - more Excelversions are supported
> - you can only do what is currently implemented
> - the interface (should) let you program on a higher level and tries
> to shield you from technical details. One of the goals was to give an
> easy to use, well tested and well documented interface that feels
> - no dependencies (Perl/DCOM/Excel/Java)
> - it is native, i.e. works directly on the file.
> - (at least potentially) it could be ported to Linux
> - currently only Excel v97 - 2003. Excel 2007 is planned to follow
> (~end of 07, no promises).
> It depends on your situation. ActiveX was not an option for us as we
> needed to work on the plain file. Otherwise we have pratically the
> same requirements a you (just with other data).
> I could potentially implement almost the whole Excel object model
> functionality within xlsReadWritePro. But to be honest, it is costly
> and I don't think many people would need that.
> We basically implemented in the pro version what we needed ourself
> internally. Upon request I try to add features if they fit well in the
> existing interface and if I have time (or if it is payed for) but I
> cannot give any promises.
> Best regards,
Mark W. Kimpel MD
Department of Psychiatry
Indiana University School of Medicine
More information about the R-devel