R devel - Feb 2007 - xlsReadWrite Pro and embedding objects and files in Excel worksheets

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

I don't know of any native xls read/write facility in R, either
in core or as add-ons (I could be wrong), but if you want some source 
code to scavenge on to build some R package out of it, there are two
perl modules, Spreadsheet::ParseExcel and Spreadsheet::WriteExcel
which are small enough to "read from front cover to back cover",
so to speak, might be useful for reference and steal code from.

The other open-source packages which can read/write excel files
are gnumeric and openoffice and probably too big to find one's way 
around the source code to steal there :-).

Good luck.

HTL

Mark W Kimpel 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
> 
> 
>

you can also use my package which uses Jakarta POI to write the excel
files.

It can be used on any platform that supports java.

The Perl solution may be better if you want to do anything complicated,
but this package supports writing all basic R objects.

http://code.google.com/p/rexcelpoi/



> -----Original Message-----
> From: r-devel-bounces at r-project.org 
> [mailto:r-devel-bounces at r-project.org] On Behalf Of Hin-Tak Leung
> Sent: Thursday, February 08, 2007 8:50 AM
> To: James W. MacDonald
> Cc: mwkimpel at gmail.com; Mark W. Kimpel MD 
> (mkimpel at iupui.edu); r-devel at r-project.org
> Subject: Re: [Rd] xlsReadWrite Pro and embedding objects and 
> files in Excel worksheets
> 
> James W. MacDonald wrote:
> > Have you looked at RDCOMClient? I would imagine you could 
> do what you 
> > want with this package.
> > 
> > http://www.omegahat.org/RDCOMClient/
> 
> Interesting point. But the dcom client would be windows-specific?
> (those I mentioned - the perl mondules, openoffice, run on 
> windows, as well as unix boxes - not sure about gnumeric :-).
> 
> In fact there is a very perverse way of doing it - 
> ActiveState provides a PerlCom product for hooking up dcom 
> with activestate perl. i.e. you can go via R -> RDComClient 
> -> PerlCom -> ActiveState Perl ->
> SpreadSheet::* . Just so that it does not require Excel 
> installed or an MS Office license...
> 
> In that sense, probably technology based on bridging over 
> odbc is also acceptable?
> 
> HTL
> 
> > Hin-Tak Leung wrote:
> >> I don't know of any native xls read/write facility in R,
either in
> >> core or as add-ons (I could be wrong), but if you want some source
> >> code to scavenge on to build some R package out of it, 
> there are two 
> >> perl modules, Spreadsheet::ParseExcel and Spreadsheet::WriteExcel 
> >> which are small enough to "read from front cover to back 
> cover", so 
> >> to speak, might be useful for reference and steal code from.
> >>
> >> The other open-source packages which can read/write excel 
> files are 
> >> gnumeric and openoffice and probably too big to find one's 
> way around 
> >> the source code to steal there :-).
> >>
> >> Good luck.
> >>
> >> HTL
> >>
> >> Mark W Kimpel 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
> >>>
> >>>
> >>>
> >>
> >>
> >> ______________________________________________
> >> R-devel at r-project.org mailing list
> >> https://stat.ethz.ch/mailman/listinfo/r-devel
> > 
> >
> 
> ______________________________________________
> R-devel at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-devel
> 



This e-mail message is intended only for the named recipient(s) above. It may
contain confidential information. If you are not the intended recipient you are
hereby notified that any dissemination, distribution or copying of this e-mail
and any attachment(s) is strictly prohibited. If you have received this e-mail
in error, please immediately notify the sender by replying to this e-mail and
delete the message and any attachment(s) from your system. Thank you.

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
>

Another option for creating XLS files it to write out HTML instead. Excel can
read html files just fine, and a useful trick is giving the html file a .xls
extension. So, from the user's point of view, it is an excel file even
though it's just an html file. 

Using html works great for embedding links and formatted tables. You can use
the R2HTML package to generate HTML files, including formatting for a large
number of R objects.

One thing you can't do with this approach is include graphics. In theory you
could do that by extending this approach. Excel can read in *.mhtml files,
which are multipart mime-encoded bundles that include the html file plus
mime-encoded graphics files that go with it. You could generate png files in
R to include. Excel will also happily read in mhtml files with a .xls
extension. The following links could help you get started:

http://en.wikipedia.org/wiki/MHTML
http://finzi.psych.upenn.edu/R/library/caTools/html/base64.html

I don't know of an R package that has a function to encode files as a
multipart mime, but the link above is a good start.

- Tom

Tom Short
EPRI


Mark W Kimpel 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
> 
> 
-- 
View this message in context:
http://www.nabble.com/xlsReadWrite-Pro-and-embedding-objects-and-files-in-Excel-worksheets-tf3191737.html#a8871790
Sent from the R devel mailing list archive at Nabble.com.

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