I am using package xlsx
Version:0.5.7 Date: 2014-08-01. in R version 3.0.1 (2013-05-16) -- "Good Sport" Platform: i386-w64-mingw32/i386 (32-bit).
I have an xlsx file with at least 2 sheets (say A and B). I need to read data from A, edit them and save them in B. This has to be done on a periodical base.
I am able to 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 try with 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 it give 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 need to replace that existing sheet multiple times. How can I do that?
If you want to save your new dataframe in an existing excel file, you first have to load the xlsx-file:
wb <- loadWorkbook(file)
which sheets you have you'll get like this:
sheets <- getSheets(wb)
you can easily remove and add (and thus replace) sheets with:
removeSheet(wb, sheetName="Sheet1")
yourSheet <- createSheet(wb, sheetName="Sheet1")
than you can fill the sheets with dataframes:
addDataFrame(yourDataFrame, yourSheet, <options>)
addDataFrame(anotherDataFrame, yourSheet, startRow=nrow(yourDataFrame)+2)
and last step is saving the whole workbook as .xlsx:
saveWorkbook(wb, file)
btw: the documentation of the xlsx-package is really good and helpful on such questions :) http://cran.r-project.org/web/packages/xlsx/xlsx.pdf