[R] write.xls

[jim holtman]

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

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



-- 
Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.