[R] write.xls

[Spencer Graves]

On 5/20/2012 6:47 AM, Marc Schwartz wrote:
> 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.


       Thanks, Marc.


       The current code first tries WriteXLS If that fails, it then 
tries RODBC.  If that fails, it writes csv files.  If I can get 
dataframes2xls and / or XLConnect to work, I plan to start with one of 
those.  These other packages are "suggests", so sos will load without them.


       Best Wishes,
       Spencer
>
> Regards,
>
> Marc Schwartz
>
>
>
>
>
>


-- 
Spencer Graves, PE, PhD
President and Chief Technology Officer
Structure Inspection and Monitoring, Inc.
751 Emerson Ct.
San José, CA 95126
ph:  408-655-4567
web:  www.structuremonitoring.com