Hallo all, I`ve read a lot of things in this forum about an Excel export via R. It is no problem to export my data frames via write.table or write.xls (xls or csv), but some things are not very convenient for me: I always have to adjust the column with to see all the numbers or the text and there is no frame between the cells. And I missing the possibility to make some headers bold or coloured. I`ve see the output from a Perl-Export and this is a very nice thing, so my question: Is there really no possibility to produce a xls or a xlsx - File with this nice features. Thank you very much in advance, Jens. -- View this message in context: http://www.nabble.com/Excel-Export-in-a-beauty-way-tp23850532p23850532.html Sent from the R help mailing list archive at Nabble.com.
If you are working on Windows and Excel installed on the same machine as R, the rcom library by Thomas Baier gives you full programmability for Excel From R. You can control all the effect you want. You also could use the RExcel addin for Excel which allows to transfer date from R into Excel with Excel menus. Then you can change everything you want in Excel manually or with VBA macros. On Jun 3, 2009, at 2:29 PM, koj wrote:> > Hallo all, > > I`ve read a lot of things in this forum about an Excel export via R. > It is > no problem to export my data frames via write.table or write.xls > (xls or > csv), but some things are not very convenient for me: I always have to > adjust the column with to see all the numbers or the text and there > is no > frame between the cells. And I missing the possibility to make some > headers > bold or coloured. I`ve see the output from a Perl-Export and this is > a very > nice thing, so my question: Is there really no possibility to > produce a xls > or a xlsx - File with this nice features. > > Thank you very much in advance, > > Jens. > -- > View this message in context: http://www.nabble.com/Excel-Export-in-a-beauty-way-tp23850532p23850532.html > Sent from the R help mailing list archive at Nabble.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. >-- Erich Neuwirth, University of Vienna Faculty of Computer Science Computer Supported Didactics Working Group Visit our SunSITE at http://sunsite.univie.ac.at Phone: +43-1-4277-39464 Fax: +43-1-4277-39459
If you are willing to do the work RDCOMClient or rcom packages give you complete control. See example here: https://stat.ethz.ch/pipermail/r-help/2005-July/075877.html This assumes your platform is Windows and that you have R and Excel on the same machine so that R can control Excel. On Wed, Jun 3, 2009 at 8:29 AM, koj <jens.koch at gmx.li> wrote:> > Hallo all, > > I`ve read a lot of things in this forum about an Excel export via R. It is > no problem to export my data frames via write.table or write.xls (xls or > csv), but some things are not very convenient for me: I always have to > adjust the column with to see all the numbers or the text and there is no > frame between the cells. And I missing the possibility to make some headers > bold or coloured. I`ve see the output from a Perl-Export and this is a very > nice thing, so my question: Is there really no possibility to produce a xls > or a xlsx - File with this nice features. > > Thank you very much in advance, > > Jens. > -- > View this message in context: http://www.nabble.com/Excel-Export-in-a-beauty-way-tp23850532p23850532.html > Sent from the R help mailing list archive at Nabble.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. >
Erich Studerus, Psychiatrische Uni-Klinik
2009-Jun-06 21:38 UTC
[R] Excel Export in a beauty way
Hi,
Here's a function to export dataframes to an excel-file with the RDCOMClient
package. It makes bold headers and fits the column widths automatically. If
more than one dataframe is provided to the function, the dataframes are
saved to seperate spreadheets within file.
export.xls <- function (..., colnames = TRUE, rownames = FALSE) {
require(RDCOMClient, quietly = TRUE)
y <- COMCreate("Excel.Application")
wbs <- y$Workbooks()
wb <-wbs$add()
shs <- wb$Worksheets()
export <- function(df, sheetname, sheetnr, colnames = colnames,
rownames = rownames) {
if (!is.data.frame(df)) {
df <- as.data.frame(df)
if (ncol(df) == 1) names(df) <- sheetname}
cl <- sapply(df, function(x) class(x)[1])
dates <- which(cl == 'Date')
for (i in dates) df[, i] <- format(df[,i], '%Y.%m.%d')
datetimes <- which(cl %in% c('POSIXt', 'POSIXct',
'POSIXlt'))
for (i in datetimes) df[, i] <- format(df[,i], '%Y.%m.%d %H:%M')
numerics <- cl == 'numeric'
for (i in which(!numerics)) df[, i] <- as.character(df[, i])
nas <- sapply(df, function(x) any(is.na(x)))
missings <- which(!numerics & nas)
for (i in missings) df[is.na(df[, i]), i] <- ''
sh <- shs$Item(sheetnr)
sh[['Name']] <- sheetname
nr <- nrow(df) + colnames
nc <- ncol(df) + rownames
cn <- dimnames(df)[[2]]
rn <- dimnames(df)[[1]]
startstop <- function(x) {
l <- length(x)
stops <- starts <- rep(NA, l)
if (x[1] == TRUE) starts[1] <- 1
for (i in seq_along(x)[-1]) {
if (x[i] == TRUE & x[i-1] == FALSE) starts[i] <- i
if (x[i] == FALSE & x[i-1] == TRUE) stops[i] <- i-1
}
if (x[l] == TRUE) stops[l] <- l
rbind(starts = starts[!is.na(starts)], stops = stops[!is.na(stops)])
}
startrow <- 1+colnames
startcol <- 1+rownames
num <- startstop(numerics)
num2 <- num + rownames
for (i in seq_len(ncol(num))) {
x <- sh$Range(sh$Cells(startrow,num2[1,i]), sh$Cells(nr, num2[2,i]))
x[['Value']] <- asCOMArray(df[, num[1,i]:num[2,i]])
}
notnum <- startstop(!numerics)
notnum2 <- notnum + rownames
for (i in seq_len(ncol(notnum))) {
x <- sh$Range(sh$Cells(startrow, notnum2[1, i]), sh$Cells(nr,
notnum2[2, i]))
x[['Value']] <- asCOMArray(df[,notnum[1,i]:notnum[2,i]])
}
missings <- which(numerics & nas) + rownames
for (i in missings) {
na <- startstop(is.na(df[,i-rownames])) + colnames
for (k in seq_len(ncol(na))) {
x <- sh$Range(sh$Cells(na[1, k] , i), sh$Cells(na[2, k], i))
x[['Value']] <- ''
}
}
for (i in (1+rownames):nc) {
x <- sh$Cells(1,i)
if (colnames) {
x[['Value']]<-cn[i-rownames]
Font <- x$Font()
Font[['Bold']] <- TRUE
}
EntCol <-x$EntireColumn()
if (i %in% dates) EntCol[['NumberFormat']] <-
'JJJJ.MM.TT'
if (i %in% datetimes) EntCol[['NumberFormat']] <-
'JJJJ.MM.TT hh:mm'
EntCol$AutoFit()
}
if (rownames) {
x <- sh$Range(sh$Cells(startrow,1),sh$Cells(nr,1))
x[['Value']] <- asCOMArray(rn)
EntCol <-x$EntireColumn()
Font <- x$Font()
Font[['Bold']] <- TRUE
}
}
dfn <- deparse(substitute(list(...)))
df <- list(...)
dfn <- gsub('^list\\(', '', dfn)
dfn <- gsub('\\)$', '', dfn)
dfn <- gsub('[ ]', '', dfn)
dfn <- strsplit(dfn, split = ',')[[1]]
dfn <- make.names(dfn, unique = TRUE)
if (length(df) > 2) for (i in seq_len(length(df) - 2)) shs$Add()
for (i in seq_len(length(df))) export(df[[i]], sheetname = dfn[i],
sheetnr = i, colnames = colnames, rownames = rownames)
y[["Visible"]] <- TRUE
}
#example:
export.xls(iris, mtcars, trees)
>If you are willing to do the work RDCOMClient or rcom packages
>give you complete control. See example here:
>https://stat.ethz.ch/pipermail/r-help/2005-July/075877.html
>This assumes your platform is Windows and that you have R and Excel
>on the same machine so that R can control Excel.
>
>On Wed, Jun 3, 2009 at 8:29 AM, koj <jens.koch <at> gmx.li>
wrote:
>
>> Hallo all,
>>
>> I`ve read a lot of things in this forum about an Excel export via R. It
is
>> no problem to export my data frames via write.table or write.xls (xls
or
>> csv), but some things are not very convenient for me: I always have to
>> adjust the column with to see all the numbers or the text and there is
no
>> frame between the cells. And I missing the possibility to make some
headers
>> bold or coloured. I`ve see the output from a Perl-Export and this is a
very
>> nice thing, so my question: Is there really no possibility to produce a
xls
>> or a xlsx - File with this nice features.
>>
>> Thank you very much in advance,
>>
>> Jens.
Another useful way to create a formatted Excel file is to write out an HTML file, but put an XLS extension on it. When Excel reads it, it will convert it. Users will treat it like an Excel file. This trick allows you to add formatted titles, table footnotes, links to other files (pdf graphs for example), and more. To create HTML, you have several packages that can help you out: R2HTML, Rpad, hwriter, and xtable. Not everything might convert properly, so you may have to experiment. Data frames as tables normally convert nicely. - Tom Tom Short