[Rd] xlsReadWrite Pro and embedding objects and files in Excel worksheets

[Mark W Kimpel]

Hans-Peter,

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 
data-abstraction.
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 
applied.
4. graphs
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,

Mark

Hans-Peter wrote:
> 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.
> 
> ActiveX/RDCOMClient:
> - 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
> 
> xlsReadWritePro:
> - 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
> R-ish.
> - 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,
> Hans-Peter
> 

-- 
Mark W. Kimpel MD
Neuroinformatics
Department of Psychiatry
Indiana University School of Medicine