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

[Mark W Kimpel]

Gabor,

What I want is a bit more than hyperlinks, although I did ask the 
package developer about that to. My idea is, from within R, place things 
like pdf files and .Rdata directly into an Excel spreadsheet. As a 
practical matter, if I can create a report with some data that someone 
else can manipulate as a "regular" spreadsheet (ex. sort gene lists) and 
then have other sheets that contain pdf output files of graphs I do 
within R. I would also like to archive my R workspace at time of 
analysis so that I could, if I had to, the analysis again. As I and 
others are constantly tweaking what functions do, it is sometimes 
impossible for me to go back and figure out what versions of what 
functions I was using. sessionInfo won't do what I want.

Since Hans-Peter came up with his really nice package, I thought I would 
throw this out as an idea. I have been doing this manually for some time 
  and my boss likes it because he only has to get one file from me, not 
10. I include worksheets with the values of parameters passed to 
functions, abbreviations, etc. Then 5 months from now and he wants me to 
explain the sheet to him, everything is in one place.

In a way, I want to treat an Excel spreadsheet as a list (the workbook) 
that can contain different kinds of objects (spreadsheets, pdfs, Rdata, 
ex.). The Excel file acts as a binder for these different files. My boss 
doesn't even want to deal with zipped files because when they are 
unzipped he ends up with tons of files.

I know this might not make a lot of sense to UNIX users who mostly 
interact with other programmers, but for those of us who deal with the 
computer-barely-literate biologists who run Windows, it could be a nice 
way of keeping things together.

BTW, I only mention Excel and Windows because that is what I use. I 
think it would be great to come up with a common format that Linux, Mac, 
and UNIX users could use. Could openOffice serve that purpose?

Thanks for your input.

Mark

Gabor Grothendieck wrote:
> Its not entirely clear to me what it is that you are looking
> for.  Maybe you want to create an Excel spreadsheet with a hyperlink
> to a web page?  This R code will do that.  It requires a Windows machine 
> that
> has Excel running on it.
> 
> 
> library(RDCOMClient)
> xl <- COMCreate("Excel.Application")
> xl[["Visible"]] <- TRUE
> wkbk <- xl$Workbooks()$Add()
> 
> sh <- xl$ActiveSheet()
> 
> B2R <- sh$Range("B3")
> B2R[["Formula"]] <- '=HYPERLINK("http://www.r-project.org")'
> 
> wkbk$SaveAs("\\test-url.xls")
> xl$Quit()
> 
> 
> 
> 
> On 2/8/07, Mark W Kimpel <mwkimpel at gmail.com> wrote:
>> Hans-Peter and other R developers,
>>
>> How are you? Have you made any progess with embedding Url's in Excel?
>>
>> Well, I have been busy thinking of more things for you to do;)
>>
>> My colleagues in the lab are not R literate, and some are barely
>> computer literate, so I give them everything in Excel workbooks. I have
>> gradually evolved a system such that these workbooks have become
>> compendia of my data, output, and methods. That, in fact, is why I
>> bought the Pro version of xlsReadWritePro. I have been saving graphics
>> as PDF files, then inserting them as object in Excel sheets.
>>
>> What I would like to be able to do is to embed objects (files) in sheets
>> of a workbook directly from within R. I would also like to be able to
>> save my current R workspace as an object embedded in a sheet so that in
>> the future, if packages change, I could go back and recreate the
>> analysis. I do not need to be able to manuipulate files that R has not
>> created, like a PDF file from another user. I would, however, like to be
>> able to save my graphics as PDF files inside a worksheet, even if it
>> meant creating a  temp file or something.
>>
>> Before people begin talking about how MySQL or some other database could
>> handle all that archiving, let me say that that is not what my
>> colleagues want. They want a nice Excel file that they can take home on
>> there laptops. One thing I like about worksheets is that they themselves
>> can contain many embedded files, so it keeps our virtual desks neater
>> and less confusing.
>>
>> Hans, if you could do this, it would be of tremendous benefit to me and
>> hopefully a lot of people. R developers tend to think that all
>> scientists are running Linux on 64-bit computers, but most biomedical
>> researches still store date in Excel files. This won't solve everybody's
>> needs, but it could be a start.
>>
>> Well, let me know what you think. I am cc'ing R-devel to see if any of
>> those guys have ideas as well.
>>
>> Thanks,
>> Mark
>>
>>
>>
>> -- 
>> Mark W. Kimpel MD
>> Neuroinformatics
>> Department of Psychiatry
>> Indiana University School of Medicine
>>
>> ______________________________________________
>> R-devel at r-project.org mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-devel
>>
> 

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