From gchappi at gmail.com  Mon Feb 12 10:31:27 2007
From: gchappi at gmail.com (Hans-Peter)
Date: Mon, 12 Feb 2007 10:31:27 +0100
Subject: [Rd] xlsReadWrite Pro and embedding objects and files in Excel
	worksheets
In-Reply-To: <45CFC808.5050202@gmail.com>
References: <45CACDD2.9060007@gmail.com>
	<47fce0650702111559y4486346k553a2b4604b4ba68@mail.gmail.com>
	<45CFC808.5050202@gmail.com>
Message-ID: <47fce0650702120131r56bca68dmbd388c4a73f0e2a9@mail.gmail.com>

Hi Mark,

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

What we do is:
- assemble all generated files (xls/png/txt) in a specific *folder*
- the pictures currently end in zip files
- Excel currently only holds the data (no images etc.)
- we don't save any workspace or .Rdata. Each calculation starts from
scratch (sometimes with cached data, but this is transparent)
- we have some packages with the most important/stable source code
- other code we source each time before a calculation
- all code is in a subversion repository and therefore has it's full history.

This works very nice for us. It was not so easy, i.e. we needed some
time to find a lean and flexible setup. R still feels difficult for
me.

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

I use Delphi (Object Pascal). The hardwork is done by a 3rd party
library which I bought (Flexcel/tmssoftware.com). You can download the
source of the free xlsReadWrite to see how it was done. (To compile
you would have to buy the flexcel library which - unfortunately - is
not open source and I am not allowed to distribute it.)

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

No, its pure Excel format. - IIRC there is a ODF Toolkit Project which
could be used to generate the files (maybe odfSweave does this
already?). The ODF Toolkit Project is certainly appealing, not least
because of the license situation.

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

About (small) original data, methods, parameters and matrix output I
agree.   Not so sure about graphs. I prefere to keep things separated.

You can do this already today by controlling Excel from R (see tipps
from Gabor) or (in part) with xlsReadWritePro.

-- 
Regards,
Hans-Peter



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