[R] write.xls

[Gabor Grothendieck]

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

Unfortunately this is getting increasingly complex due to the
non-standard evaluation done by dataframes2xls but if you want to do
it then this will do it.  We copy dataframes2xls to the current
environment and reset write.xls's environment so that it finds the
arguments.


run <- function(x1, x2, file){

   df.names <- paste0(deparse(substitute(x1)), ":::", deparse(substitute(x2)))

   # copy dataframe2xls namespace contents here & reset environment of write.xls
   here <- environment()
   ns <- asNamespace("dataframes2xls")
   for(nm in ls(ns)) here[[nm]] <- ns[[nm]]
   environment(write.xls) <- here

   x23 <- quote(c(x1, x2))
   do.call("write.xls", list(x23, file, sh.names = df.names))
}

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'

run(df1, df2, outFile)


-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com