Using list names as names for sheets using XLConnect

Question

As the title suggest, I would like to do the above. Example below:

library(stringr)
library(XLConnect)

df <- data.frame(do.call("rbind", lapply(1:10, function(i) rnorm(10))))
df.list <- rep(list(df), 10)
names(df.list) <- paste("DataFrame", str_pad(1:length(df.list), 2, pad = "0"), sep = "")

df.list.workbook <- loadWorkbook("df.list.workbook.xlsx", create = TRUE)
lapply(1:length(df.list), function(i) createSheet(df.list.workbook, name = names(df.list[i])))
lapply(df.list[1:length(df.list)], function(i) writeWorksheet(df.list.workbook, i, sheet = names(i)))

The last line is where it throws up an error:

Error: IllegalArgumentException (Java): Sheet index (-1) is out of range (0..9)

To troubleshoot this, I tried:

lapply(df.list[1:length(df.list)], function(i) print(names(i)))

And realise that the names of the columns were being passed to the sheet variable. Any ideas how to overcome this?

@James, that give me `Error in print(names(df.list)[i]) : error in evaluating the argument 'x' in selecting a method for function 'print': Error in names(df.list)[i] : invalid subscript type 'list'` — Ben, Mar 24 '12 at 08:32
Ah, I see. Would also need to change to: `lapply(seq_along(df.list), function(i) writeWorksheet(df.list.workbook, df.list[[i]], sheet = names(df.list)[i])`. Need to lapply over the index instead of the list. — James, Mar 24 '12 at 08:38
Brilliant! Could you submit it as an answer so I can mark it as answered? — RJ-, Mar 24 '12 at 08:41

score 4 · Accepted Answer · answered Mar 24 '12 at 09:54

4

You need to lapply over the index instead of the list:

lapply(seq_along(df.list), function(i) writeWorksheet(df.list.workbook, df.list[[i]], sheet = names(df.list)[i]))

answered Mar 24 '12 at 09:54

James

65,548
14
155
193

score 3 · Answer 2 · answered Mar 26 '12 at 18:08

XLConnect is vectorized in many places. In your case this holds for createSheet and writeWorksheet, so you can write:

df.list.workbook <- loadWorkbook("df.list.workbook.xlsx", create = TRUE)
createSheet(df.list.workbook, name = names(df.list))
writeWorksheet(df.list.workbook, data = df.list, sheet = names(df.list))
saveWorkbook(df.list.workbook)

Using list names as names for sheets using XLConnect

2 Answers2