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

[Gabor Grothendieck]

If Excel has the capability to do it then by controlling Excel from R
using RDCOMClient or rcom packages you can do it (in Windows).
For example, the code below creates a plot in R and then creates an Excel
spreadsheet and inserts it.

Get up to speed on VBA and then use the Macro recorder in
Excel while you do it manually and look at the macro source that it
generates to find out what VBA commands it uses for a particular task.

plot(1:10)
savePlot("c:\\myplot", "wmf")

library(RDCOMClient)
xl <- COMCreate("Excel.Application")
xl[["Visible"]] <- TRUE
wkbk <- xl$Workbooks()$Add()

sh <- xl$ActiveSheet()

sh$Pictures()$Insert("C:\\myplot.wmf")

wkbk$SaveAs("\\test-pic.xls")
xl$Quit()



On 2/9/07, Mark W Kimpel <mwkimpel at gmail.com> wrote:
> 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
>