Change cell value in openxlsx workbook

Question

I want to use openxlsx to change an individual cell in a workbook sheet and write it back out as the same .xlsx without losing the styling, validation, etc of the original .xlsx file. I stipulate openxlsx because it doesn't have a rJava dependency.

Here's a dummy workbook:

library(openxlsx)

## Make a dummy workbook to read in
write.xlsx(list(iris = iris, mtcars = mtcars), file = 'test.xlsx')

wb <- loadWorkbook('test.xlsx')
isS4(wb)

How can I change the value of cell [2,1] so that it essentially is identical to the original .xlsx file but with on cell altered?

I can of course read in the workbook but I don't know what good that does me.

m <- readWorkbook(wb)
m[2, 1] <- 20
m[1:5,]

Answer 1

writeData can help you with this.

test.fpath <- 'test.xlsx'
openxlsx::write.xlsx(list(iris = iris, mtcars = mtcars), file = test.fpath)
.wb <- openxlsx::loadWorkbook(test.fpath)
openxlsx::writeData(
  wb = .wb,
  sheet = 1,
  x = 20,
  xy = c(2,1)
)
openxlsx::saveWorkbook(
  .wb,
  test.fpath,
  overwrite = TRUE
)