#1 Problems with dataframes2xls

[guido van steen]

Hi, Guido:

Thanks for your useful "dataframes2xls" package. I just modified writeFindFn2xls{sos} to use it if it cannot get either RODBC or WriteXLS (which had previously been programmed) to work -- with substantial help from Gabor Grothendieck. Along the way, I encountered 3 concerns that I felt a need to report to you:

(1) With "\n" in a character string in the data.frame, write.xls fails to write a file, noting, "Infile name is not a valid csv file" without
throwing an error.

(2) With "," in a character string in the data.frame, it splits that element over multiple columns, thereby corrupting what is written.

(3) It insists on quoting all all character strings. I don't need or want those extraneous quotes, but I don't know how to get rid of them.

I've gotten past the first two problems using gsub('\n|,', ' ', strings), and I plan to ignore the third. If you know how to specify a format that will suppress the quotes, I will appreciate it. You may not have the time to consider improving these features of dataframes2xls, but I felt a need to mention them in case you do. I know of no way to write an Excel file that works out of the box on all platforms. Your dataframes2xls seems to provide something useful in this regard.

Thanks,
Spencer

[guido van steen]

Reported by Spencer Graves on Sun, 20 May 2012 22:18:59 -0700

[guido van steen]

Hi, Guido:

You might be interested in the problems I had getting "write.xls" to work inside another function and the solutions that Gabor provided me; see below.

As usual, thanks for your contributions to the R project, etc., and don't hesitate to ignore this if you have no time for it. No good deed goes unpunished ;-)

Best Wishes,
Spencer

run2 <- function(x1, x2, x3, file){
X1 <- x1
X2 <- x2
X3 <- x3
# df.names <- 'X1:::X2:::X3'

# 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
wx <- write.xls
environment(wx) <- here

x23 <- quote(c(X1, X2, X3))
do.call("wx", list(x23, file, sh.names='sh1:::sh2:::sh3'))
}

run3 <- function(x, file){
X1. <- PackageSum2(x)
X1l <- lapply(X1., function(x)
if(is.numeric(x))x else
gsub('\n', ' ', as.character(x)))
X1 <- as.data.frame(X1l)#, stringsAsFactors=FALSE)
X1$Date <- as.character(as.Date(X1$Date))
X2. <- lapply(x, function(x)
if(is.numeric(x))x else as.character(x))
X2 <- as.data.frame(X2., stringsAsFactors=FALSE)
X3 <- data.frame(call=as.character(attr(x, 'call')))#,
# stringsAsFactors=FALSE)
# df.names <- 'X1:::X2:::X3'

# 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
# wx <- write.xls
# environment(wx) <- here
# X1. <- X1[10, , drop=FALSE]
x23 <- quote(c(X1, X2, X3))
do.call("write.xls", list(x23, file, sh.names='sh1:::sh2:::sh3'))
}

library(dataframes2xls)

df1 <- data.frame(c1=1:2, c2=3:4, c3=c('a', 'b'))#,
# stringsAsFactors=FALSE)
df2 <- data.frame(c21=c(10.10101010101,20, 3), c22=c(50E50,60, 3) )
outFile <- 'df12c.xls'

run2(df1, df2, df1, 'df12c.xls')

library(sos)

splineSearch <- findFn("spline", maxPages = 1)
run3(splineSearch, 'df12d.xls')
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

[guido van steen]

Hi Spencer and Gabor,

Thank you for reporting this.

It has been some time since I have taken a look inside dataframes2xls.
I wish I had time to update the package, but at the moment I am quite
busy with other work.

I will publish your bug reports on the Sourceforge website, so that
these limitations are known.

If more progress is made, I will let you know.

Best wishes,

Guido