How to use write.xlsx in R when the excel file exists or the sheet exists

Question

I am trying to use openxlsx::write.xlsx to write results into Excel spreadsheet in R.

1. if the file exists and a new sheet is to be added, I can use append=T. Instead of using if to check the file, are there any ways to automatically check?

2. if the file and sheet both exist and this sheet is to be updated, how should I do to overwrite the results? Thanks.

Answer 1

Here is an openxlsx answer. In order to demonstrate, we need some data.

## Create a simple test file 
library(openxlsx)
hs <- createStyle(textDecoration = "Bold")
l <- list("IRIS" = iris, "MTCARS" = mtcars)
write.xlsx(l, file = "TestFile.xlsx", borders = "columns", headerStyle = hs)

Question 1

You can check whether or not the file exists with

## Check existence of file
file.exists("TestFile.xlsx")

You can check if the tab (sheet) exists within the workbook

## Check available sheets
getSheetNames("TestFile.xlsx")

Steps for question 2:
1. Read the file into a Workbook object.
2. Pull the data from the sheet you want to modify into a data.frame.
3. Modify the data.frame to taste
4. Save the data back into the Workbook
5. Save the Workbook out to disk
In order to have a simple example to work with, let's create a simple test file.

## Load existing file
wb = loadWorkbook("TestFile.xlsx")

## Pull all data from sheet 1
Data = read.xlsx(wb, sheet=1)

## Change a single element for demonstration
## ** Beware!! **    Because of the header,
##   the 2,2 position in the data 
##   is row 3 column 2 in the spreadsheet
Data[2,2] = 1492

## Put the data back into the workbook
writeData(wb, sheet=1, Data)

## Save to disk
saveWorkbook(wb, "TestFile.xlsx", overwrite = TRUE)

You can open up the spreadsheet and check that the change has been made.

If you want to completely change the sheet (as in your comment), you can just delete the old sheet and replace it with a new one using the same name.

removeWorksheet(wb, "IRIS")
addWorksheet(wb, "IRIS")
NewData = data.frame(X1=1:4, X2= LETTERS[1:4], X3=9:6)
writeData(wb, "IRIS", NewData)
saveWorkbook(wb, "TestFile.xlsx", overwrite = TRUE)

Answer 2

You can check if sheet exists before and if so remove it, if not append it to existing file this command also will create file if it does not exist.

library(xlsx)

path <- "testing.xlsx"
sheet_name = "new_sheet"

data <-
  data.frame(
    B = c(1, 2, 3, 4)
  )

if(sheet_name %in% names(getSheets(loadWorkbook(path)))){
  wb <- loadWorkbook(path)
  removeSheet(wb, sheetName = sheet_name)
  saveWorkbook(wb, path)
}

write.xlsx(data, path, sheetName = sheet_name, append = TRUE)