[R] write.xls

[Spencer Graves]

Hi, Jim:


On 5/20/2012 4:54 PM, jim holtman wrote:
> Here is what it take to write out two sheets with XLConnect
>
>
> # function to write out a sheet to an EXCEL file that I use
> f.writeXLSheet<-
> function (data, sheet, fileToWrite, rownames = NULL)
> {
>      require(XLConnect)
>      writeWorksheetToFile(fileToWrite, data = data, sheet = sheet,
>          rownames = rownames, styleAction = XLC$STYLE_ACTION.NONE)
> }


Thanks.  Unfortunately, I can't get that far:


 > library(XLConnect)
Loading required package: XLConnectJars
Loading required package: rJava
Error : .onLoad failed in loadNamespace() for 'rJava', details:
   call: stop("No CurrentVersion entry in '", key, "'! Try re-installing 
Java and make sure R and Java have matching architectures.")
   error: object 'key' not found
Error: package 'rJava' could not be loaded

 > 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
 >

       Best Wishes,
       Spencer
>
> 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'
>
> unlink(outFile)  # make sure there is no file to start with
> f.writeXLSheet(df1, 'df1', outFile)
> f.writeXLSheet(df2, 'df2', outFile)
>
> I have attached the resulting Excel file (see how far it makes it).
>
> On Sun, May 20, 2012 at 7:15 PM, Spencer Graves
> <spencer.graves at structuremonitoring.com>  wrote:
>> On 5/20/2012 5:52 AM, Gabor Grothendieck wrote:
>>> On Sun, May 20, 2012 at 8:30 AM, Gabor Grothendieck
>>> <ggrothendieck at gmail.com>    wrote:
>>>> 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)
>>>>
>>> That was for WriteXLS.  For dataframes2xls try this:
>>>
>>> do.call("write.xls", list(quote(c(df1, df2)), outFile))
>>
>> Hi, Gabor:  Thanks.  I think we're gaining on it, but I'm still not quite
>> there.  Consider the following extension of my previous toy example:
>>
>> 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(quote(c(df1, df2)), outFile))
>> # works
>>
>> df2x<- function(x, file)
>>     do.call(write.xls, list(x, file))
>> df2x(quote(c(df1, df2)), outFile)
>> # works
>>
>> df2<- function(x1, x2, file){
>>     x23<- quote(c(x1, x2))
>>     do.call(write.xls, list(x23, file))
>> }
>> df2(df1, df2, outFile)
>> # Error in get(s[i]) : object 'x1' not found
>>
>>
>>       This is closer to what I need.  The answer may lie in getting the right
>> "envir" argument for "do.call".  However, I haven't yet found something that
>> works there.
>>
>>
>>       Thanks,
>>       Spencer
>>>
>>> and also check out this page:
>>>
>>> http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
>>>
>> ______________________________________________
>> 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.