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 -- 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
I have been using XLConnect to write multisheet Excel without any problems. Sent from my iPad On May 19, 2012, at 21:32, Spencer Graves <spencer.graves at structuremonitoring.com> 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 > > > > -- > 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 at 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.
On Sat, May 19, 2012 at 9:32 PM, Spencer Graves <spencer.graves at structuremonitoring.com> 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 >Try this: dd <- list(df1 = df1, df2 = df2) do.call("WriteXLS", list("dd", outFile)) or this: do.call("WriteXLS", list(c("df1", "df2"), outFile) -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com
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.5Hi 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
Seems like you have a solution by now, but I didn't see: require(xlsx) 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' wb <- createWorkbook() sh1 <- createSheet(wb,'sheet1') addDataFrame(df1,sh1) sh2 <- createSheet(wb,'sheet2') addDataFrame(df2,sh2) saveWorkbook(wb,outFile) Which works consistently for me. -Don -- Don MacQueen Lawrence Livermore National Laboratory 7000 East Ave., L-627 Livermore, CA 94550 925-423-1062 On 5/19/12 6:32 PM, "Spencer Graves" <spencer.graves at structuremonitoring.com> 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 > > > >-- >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 at 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.