Part 1:

I am trying to rename worksheets in a list of workbooks using lapply and XLConnect (I need to rename them for the next part of the code to run properly, more on this in part 2):

library(XLConnect)

# testWB.xlsx contains a blank worksheet called Sheet1
testWB <- rep(lapply("testWB.xlsx", loadWorkbook), 3)

lapply(1:length(testWB), function(x) {
  renameSheet(testWB[[x]], "Sheet1", "test1")
})

Gives me the error:

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

But:

renameSheet(testWB[[1]], "Sheet1", "test1")

Renames the sheet as it is supposed to. It is weird, renameSheet does NOT work with lapply, but getActiveSheetIndex does work with lapply.

unlist(lapply(1:length(testWB), function(x) {
  getActiveSheetIndex(testWB[[x]])
}))
[1] 1 1 1

I've tested other XLConnect functions and some work in lapply and others do not.

Part 2:

I need to rename sheets to get the writeWorksheet function to work. E.g.:

cell_data <- c("Larry", "Curly", "Moe")

unlist(lapply(1:length(testWB), function(x) {
  writeWorksheet(testWB[[x]], cell_data[x], sheet = "sheet1", header = F)
  readWorksheet(testWB[[x]], "Sheet1", header = F)
}))
Col1    Col1    Col1 
"Larry" "Curly"   "Moe"

But looking at testWB after running the above loop:

unlist(lapply(1:length(testWB), function(x) {
  readWorksheet(testWB[[x]], "Sheet1", header = F)
}))
Col1  Col1  Col1 
"Moe" "Moe" "Moe"

As you can see this ends up inputting Moe into all of the A1 cells of each sheet in each workbook instead of Larry, Curly, Moe in A1 cell of each respective workbook. If the workbook sheets have different names (e.g., Sheet1, Sheet2, Sheet3) then it works properly. Hence my issue in part one.

Since I have not gotten this to work, I have had to reconstruct testWB.xlsx templates in R and reapply formatting. testWB.xlsx in reality is quite a nuanced excel form so recreating it is not ideal.

I hope I am just missing something small and thank you in advance for any suggestions.

XLConnect version 0.2-12

Part 1:

Part 2:

Recommended topics

Hot tags