1

Question

If I want to manipulate a worksheet in an EXISTING workbook using R and the openxlsx package, how do I assign that to a variable for use in script?

Example

It's easy to do this (and well documented) when you are creating a workbook from scratch:

library(openxlsx)
f <- "Excel Output/Example.xlsx"
df <- data.frame("ColA" = c("A", "B", "C"),
                 "ColB" = c(1L, 4L, 9L))
wb <- createWorkbook()
sh <- addWorksheet(wb, sheetName = "MyExampleSheet")

# Now go do stuff using the `sh` variable like...
writeData(wb, sh, df)
saveWorkbook(wb, file = f, overwrite = TRUE)

But now let's say I'm not creating a workbook from scratch. I'm using an existing workbook with sheets pre-existing, and I want to write new data to those existing sheets:

wb <- loadWorkbook(f)  # Loading, not creating!
sh <- addWorksheet(wb, sheetName = "MyExampleSheet") 
#> Error in addWorksheet(wb, sheetName = "MyExampleSheet") : 
#>  A worksheet by the name 'MyExampleSheet' already exists! Sheet names must be unique case-insensitive.

Obviously addWorksheet() is the wrong function, but I cannot figure out how to get sh properly assigned to an existing worksheet.

ScottyJ
  • 945
  • 11
  • 16
  • 1
    `openxlsx::readWorkbook` and set the `sheet` argument? Or is there something more complicated? – camille Dec 03 '22 at 22:54
  • 1
    Not sure I understand the question, but will something like this `openxlsx::read.xlsx("Example.xlsx", sheet = "MyExampleSheet")`? – Zhiqiang Wang Dec 03 '22 at 23:12
  • 1
    Similar answer here: https://stackoverflow.com/questions/69899073/how-to-write-a-data-frame-to-an-existing-excel-sheet/70194376#70194376 – Jonni Mar 30 '23 at 01:57

2 Answers2

2

If your problem is simply write on an existing sheet, you even don´t need the addWorksheet function

library(openxlsx)
f <- "Excel Output/Example.xlsx"
df <- data.frame("ColA" = c("A", "B", "C"),
                 "ColB" = c(1L, 4L, 9L))
wb <- loadWorkbook(f)
sh <- "MyExampleSheet"

# If the sheet already exist writeData will write on that sheet whithout problem
writeData(wb, sh, df)
saveWorkbook(wb, file = f, overwrite = TRUE)

that's work fine for me, i hope it helps

Juan Osorio
  • 36
  • 2
  • 1
    This is it. I was thinking there needed to be a function to assign the sheet (as if it were an object rather than just a text string for the sheet name) to the variable. I've done a lot of VBA and still think in those terms when doing this. – ScottyJ Mar 31 '23 at 00:05
0

In your example you don't need sh. Worksheets are accessed via sheet name only.

library(openxlsx)

# creating data
df <- data.frame("ColA" = c("A", "B", "C"),
                 "ColB" = c(1L, 4L, 9L))

# create workbook from scratch
wb <- createWorkbook()
addWorksheet(wb, sheetName = "MyExampleSheet")
writeData(wb, "MyExampleSheet", df)

# loading workbook and writing onto it
f <- system.file("extdata", "loadExample.xlsx", package = "openxlsx")

wb <- loadWorkbook(file = f)
writeData(wb, "testing", df)
Jan Marvin
  • 426
  • 1
  • 4
  • 5
  • Well, my example is simplified for the purposes of the question, and yes, I do want to get a variable to reference the sheet so I can use the variable numerous times in subsequent operations. But looking at your answer, one way of doing that would be to use `sh <- "testing"`. I also discovered I'm making the mistake that a sheet is an object in `openxlsx` like a workbook is an object. It's not, I've learned -- it's just an index number. – ScottyJ Dec 05 '22 at 00:29