How to write a data.frame to an existing EXCEL sheet?

Question

How to write a data.frame data to current existing excel file (and the sheet name existing also---refer to the image)?

I tried xlsx or openxlsx , but all of them failed:

setwd('C:\\Users\\Administrator\\Desktop\\MFOLDER)
mydata <- data.frame(category=LETTERS,
                     amount=1:26)



# METHOD 1, show error as below
library(xlsx)
xlsx::write.xlsx(mydata,file='existingfile.xlsx',
                 sheetName = 'Sheet1',
                 append = TRUE)

# Error in .jcall(wb, "Lorg/apache/poi/ss/usermodel/Sheet;", "createSheet",  : 
#                   java.lang.IllegalArgumentException: The workbook already contains a sheet of this name




# METHOD 2, this code no error,but it remove current excel file and create  new one
library(openxlsx)
openxlsx::write.xlsx(mydata,file='existingfile.xlsx',
                      sheetName = 'Sheet1',
                      append = TRUE)

'

If you cannot get `openxlsx::` to add a sheet to an existing workbook, then you're doing something wrong (or the workbook has problems). Share the code you've tried. — r2evans, Nov 09 '21 at 13:44
Possible duplicate of [write data into a specific workbook sheet](https://stackoverflow.com/questions/15151255/write-data-into-a-specific-workbook-sheet) and associated links — Mata, Nov 09 '21 at 13:47
@r2evans thanks for your replay , I edited my post ( add the code etc.). — anderwyang, Nov 10 '21 at 03:44

score 0 · Answer 1 · answered Dec 02 '21 at 04:56

Easiest may be load existing file as workbook object. I wasn't sure if you were adding data to bottom of existing sheet or next to/right of existing data. Both are demonstrated below as well as just adding separate sheet to existing workbook

library(openxlsx)
#Create data to append       
myappendeddata <- data.frame(category2 = rep(c("foo","bar"), 13), 
                                     price = 101:126)
#Load existing file
        wb <- loadWorkbook(file = "C:\\Users\\Administrator\\Desktop\\MFOLDER\\existingdatafile.xlsx")

#Row append data to existing data, specify startrow/startcol/dropping column names    
    writeData(wb, sheet = "Sheet 1", myappendeddata, startRow = 27, startCol = 1, colNames = FALSE)

#Column append data to right of existing data    
    writeData(wb, sheet = "Sheet 1", myappendeddata, startRow = 1, startCol = 3)

#Adding worksheet and then data to new worksheet
    addWorksheet(wb, sheetName = "Iris")
    writeData(wb, sheet = "Iris", x = iris)

    openXL(wb)

How to write a data.frame to an existing EXCEL sheet?

1 Answers1

Linked