Write a data frame to a .xls file with a title

Question

I adapted a script to run the multiple (post-hoc) comparisons dunn.test function from the dunn.test package, on multiple variables, which generates multiple data frames, saved with the bellow (simplified) code in an .xls file as separate tables. What I want is to save with the each date frame also the name of variable (column) that was analyzed, as table titles (not column titles). I am looking for a solution for several hours, but without success. How to do this? They are too many and I do not want to enter them manually.

wb <- createWorkbook()
sheet <- createSheet(wb, sheetName="Sheet1")
# start analyzing data in a for loop here
# and add created data frames to a sheet
    addDataFrame(mydataframe, sheet)
# end loop here
saveWorkbook(wb, "myfile.xls", sep="")

Answer 1

You could convert your source data.frame to a text matrix, prefix whatever title you want, and write that to file.

For example, given this data.frame:

dat <- data.frame(Fruit=c('Apple', 'Banana'), 
                  Quantity=1:2, 
                  Notes=c('Hello', 'Some Text'))

You could use a function like this:

text_matrix <- function(dat, table_title) {

  rbind(c(table_title, rep('', ncol(dat)-1)), # title
        rep('', ncol(dat)), # blank spacer row
        names(dat), # column names
        unname(sapply(dat, as.character))) # data

}

Then you can call the function and write the output to file (using write.table or one of the xls conversion packages if you must).

temp <- text_matrix(dat, table_title='Here is a table title')
write.table(temp, 'temp.csv', row.names=F, col.names=F, sep=',')

The result, opened in Excel, is this:

Answer 2

Hope the following helps. I created quickly an example dataset. Output.csv can be found in your home directory. Opening it in excell will show the column titles.

n = 20
set.seed(12345)
data = data.frame(cbind(pond=1:2, habitat=1:3, value = rnorm(n)))

write.csv(data, file="output.csv")