[R] write.xls

[Marc Schwartz]

On May 19, 2012, at 8:32 PM, Spencer Graves wrote:

> Hello, All:
> 
> 
>      The "writeFindFn2xls" function in the "sos" package tries to write an Excel file with 3 sheets ('PackageSum2', 'findFn', 'call'). Unfortunately, it is often unable to do this because of configuration problems that are not easy to fix.  I've found 3 contributed packages that provide facilities to write Excel files with multiple sheets. Unfortunately, I can't get any of them to work consistently for me. Below please find test cases that illustrate the problems.  Any suggestions for how to solve this problem will be appreciated.
> 
> 
>      Thanks,
>      Spencer
> 
> 
> library(dataframes2xls)
> 
> df1 <- data.frame(c1=1:2, c2=3:4, c3=5:6)
> df2 <- data.frame(c21=c(10.10101010101,20, 3), c22=c(50E50,60, 3) )
> outFile <- 'df12.xls'
> 
> write.xls(c(df1,df2), outFile)
> # works
> 
> do.call(write.xls, list(c(df1, df2), outFile))
> # Error in get(s[i]) : object 'structure(list(c1=1:2' not found
> 
> library(WriteXLS)
> testPerl()
> #Perl found.
> #The following Perl modules were not found on this system:
> #Text::CSV_XS
> #If you have more than one Perl installation, be sure the correct one was used here.
> #Otherwise, please install the missing modules. See the package INSTALL file for more information.
> 
> # *** NOTE:  I may be able to fix this for myself.
> # *** However, I want to use this in the 'sos' package,
> # *** and if it doesn't work easily for me, it may not work for others.
> 
> library(RODBC)
> xlsFile <- odbcConnectExcel(outFile, readOnly=FALSE)
> # NOTE:  This works with R 2.15.0 32-bit.
> # However, with 64-bit, I get the following error message here:
> # Error in odbcConnectExcel(outFile, readOnly = FALSE) :
> #   odbcConnectExcel is only usable with 32-bit Windows
> 
> # When this works, the following 3 lines of code
> #    suffice to create the outFile:
> sqlSave(xlsFile, df1, tablename='sheet1')
> sqlSave(xlsFile, df2, tablename='sheet2')
> odbcClose(xlsFile)
> 
> sessionInfo()
> #R version 2.15.0 (2012-03-30)
> #Platform: x86_64-pc-mingw32/x64 (64-bit)
> 
> #locale:
> #[1] LC_COLLATE=English_United States.1252
> #[2] LC_CTYPE=English_United States.1252
> #[3] LC_MONETARY=English_United States.1252
> #[4] LC_NUMERIC=C
> #[5] LC_TIME=English_United States.1252
> 
> #attached base packages:
> #[1] stats     graphics  grDevices utils     datasets  methods   base
> 
> #other attached packages:
> #[1] RODBC_1.3-5          WriteXLS_2.1.0       dataframes2xls_0.4.5

Hi Spencer,

The INSTALL file referenced for WriteXLS is also available on CRAN:

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

The missing Perl modules cannot be provided with the CRAN package as they contain C code that must be compiled for the target platform. So one either needs to install the source Perl package from CPAN via the CLI and have a C compiler on their computer or use a Perl package manager infrastructure (eg. ActiveState Perl) that provides pre-compiled binaries for each OS and a nice GUI. The INSTALL file provides instructions for Windows, OSX and Linux as to how to address that issue.

Note that the key issue that you face is that some of the mechanisms that you are trying will be OS specific (primarily Windows), such as RODBC, since ODBC drivers for Excel will be Windows only. If you want to provide your users of sos with cross-platform functionality, then you would need to look at solutions using Perl such as WriteXLS, Java such as XLConnect or Python such as dataframes2xls. Each will have installation issues, depending upon the OS and the useR's skill sets in ensuring the presence of the required foundation. Some users may have issues in certain environments in installing Perl, Python or Java due to IT/Security issues, so something to consider.

The path of least resistance would be to simply write CSV files, which can then be opened with Excel or similar applications. It just depends upon what assumptions you want to make pertaining to maximizing your potential user base, while minimizing the installation challenges useRs may face with your package.

Regards,

Marc Schwartz