Cannot write to a worksheet with openxlsx2

Question

Here's my reprex

library(openxlsx2)

wb <- openxlsx2::wb_workbook()
openxlsx2::wb_add_worksheet(wb, "All Courses")

allLines = c("This is the first test", 
             "This is the second test",
             "This is the third test")
openxlsx2::wb_add_data(wb, sheet = "All Courses", allLines, start_col = 1, start_row = 1)
openxlsx2::wb_save(wb, file = "C:/Temp/My xlsx2 Test.xlsx", overwrite = TRUE)

I reliably get the following error message when I run this code:

openxlsx2::wb_add_data(wb, sheet = "All Courses", allLines, start_col = 1, start_row = 1)
Error in wb$.__enclos_env__$private$get_sheet_index(sheet) : 
  Sheet name(s) not found: all courses

I can't figure out if this is a bug or if I am doing something wrong. Thanks in advance for your help

Thomas Philips

stefan · Accepted Answer · 2024-01-30 18:32:39Z

6

The issue is that using the wb_xxx family of functions you have to assign the result back to the wb object, i.e. use wb <- openxlsx2::wb_xxx(...):

library(openxlsx2)

allLines <- c(
  "This is the first test",
  "This is the second test",
  "This is the third test"
)

wb <- openxlsx2::wb_workbook()
wb <- openxlsx2::wb_add_worksheet(wb, "All Courses")
wb <- openxlsx2::wb_add_data(wb,
  sheet = "All Courses", allLines,
  start_col = 1, start_row = 1
)
openxlsx2::wb_save(wb, file = "test.xlsx", overwrite = TRUE)

I you want to avoid that you can call the workbook methods using wb$xxx like so:

wb <- openxlsx2::wb_workbook()
wb$add_worksheet("All Courses")
wb$add_data(
  sheet = "All Courses", allLines,
  start_col = 1, start_row = 1
)
wb$save(file = "test1.xlsx", overwrite = TRUE)

edited Jan 30, 2024 at 18:32

answered Jan 30, 2024 at 16:23

stefan

130k6 gold badges41 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Philips Over a year ago

Thanks - the syntax wasn't obvious from the manual. I did see wb$add_worksheet() , and assumed wb_add_worksheet() would work identically.

Collectives™ on Stack Overflow

Cannot write to a worksheet with openxlsx2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related