[R] Write an Excel workbook?

[Marc Schwartz]

On Jun 27, 2013, at 2:14 PM, Spencer Graves <spencer.graves at structuremonitoring.com> wrote:

> Hello:
> 
> 
> Is there a fully transportable way to write an Excel workbook?
> 
> 
> The "writeFindFn2xls" function in the "sos" package attempts to write search results to an Excel workbook consisting of (1) a summary of the packages identified in the search, (2) the individual help pages found, and (3) the search that produced 1 and 2. Unfortunately, for some time now, it only works with 32-bit R; the 64-bit R writes this information to three separate csv files.
> 
> 
> The current code first tries WriteXLS{WriteXLS}. Unfortunately, this fails on my computer, because it can't find the Perl module "Text::CSV_XS". I don't see an easy fix to this.
> 
> 
> If WriteXLS fails, the code then tries odbcConnectExcel{ODBC}. From this, I get, "odbcConnectExcel is only usable with 32-bit Windows". That's OK, but it means that I need to run all my searches on 32-bit R, and I usually use 64-bit R.
> 
> 
> I tried write.xls{dataframes2xls}. This put quotes around everything, which I found unacceptable.
> 
> 
> findFn('write.xls') identified a function by that name in the "marray" package. Unfortunately, install.packages("marray") produced, "Warning message: package ‘marray’ is not available (for R version 3.0.1)" "???xls" failed to produce anything else that looked to me like it would write a complete workbook.
> 
> 
> Suggestions?
> Thanks,
> Spencer Graves



The problem that you will likely run into Spencer, is that most cross-OS solutions for R will likely require Perl (as in the case of my WriteXLS package), Python or Java as in the case of some of the others, like XLConnect. Java, while perhaps the easiest of the cross-OS solutions, may be problematic from a security standpoint, if the user's computer has had Java removed or disabled (not just the browser plug-in, but the JRE itself).

In the case of WriteXLS, the solution to the missing Perl module(s) is defined in the INSTALL file that is included in the package:

  http://cran.r-project.org/web/packages/WriteXLS/INSTALL

As noted there and as you find above, the typical missing module is Text::CSV_XS (because it contains C code requiring compilation), with the exception of (as of the last time I checked) the ActiveState Perl distribution. The ActiveState distribution includes pre-compiled binaries of the Perl modules for the target OS's, which obviates the need for the user to have C related development tools installed locally.

Regards,

Marc Schwartz