Use write.xlsx to replace the existing sheet with the xlsx R package

Question

I am using xlsx package Version: 0.5.7 Date: 2014-08-01. in version R 3.0.1 (2013-05-16) - the Good Sport platform: i386-w64-mingw32 / i386 (32-bit version).

I have an xlsx file containing at least 2 sheets (e.g. A and B). I need to read the data from A, edit it and save it to B. This should be done on a periodic basis.

I can read data from A with read.xlsx . After editing the data frame, I want to save it in an existing sheet B in the same xlsx file.

I am trying to use this line

 write.xlsx(down, paste0(root,'/registration reports/registration complete_WK.xlsx'), sheet="data_final", col.names=T, row.names=F, append=T, showNA=F) 

but he gives me this error:

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

I really need to replace this existing sheet several times. How can i do this?

thanks filippo

Answer 1

If you want to save the new data framework in an existing excel file, you first need to download the xlsx file:

 wb <- loadWorkbook(file) 

which sheets you have, you will get the following:

 sheets <- getSheets(wb) 

you can easily remove and add (and, accordingly, replace) the sheets:

 removeSheet(wb, sheetName="Sheet1") yourSheet <- createSheet(wb, sheetName="Sheet1") 

how can you fill out the sheets with data:

 addDataFrame(yourDataFrame, yourSheet, <options>) addDataFrame(anotherDataFrame, yourSheet, startRow=nrow(yourDataFrame)+2) 

and the last step is to save the whole book as .xlsx:

 saveWorkbook(wb, file) 

btw: the xlsx package documentation is really good and helpful on such issues :) http://cran.r-project.org/web/packages/xlsx/xlsx.pdf

Answer 2

It is possible that Java installed on your computer is not compatible with the xlsx library. The next section discusses a similar problem with the same package: enter the link here

Alternatively, your problem can be solved by another package related to Excel, for example XLConnect. View this post: enter the link here