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
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
