Using write.xlsx to replace an existing sheet with R package xlsx

Question

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?

Here is example, go to bottom of the article: https://danganothererror.wordpress.com/2012/02/12/write-data-frame-to-excel-file/ — kosa, Jan 20 '15 at 19:04
I don't think `write.xlsx()` will overwrite an existing worksheet. Can you read the entire `.xlsx` file in and re-write all of the worksheets, including whatever the new sheet you need? — Steven, Jan 20 '15 at 19:14
We don't usually support "fixing" things on obsolete versions of R. Any reason you can't update to 3.1.2 and matching packages? — Carl Witthoft, Jan 20 '15 at 20:32
@CarlWitthoft A few versions later, the "problem" still exists. It's not a bug, but a desired behaiviour that prevents you from overriding your sheets — David, Jun 03 '19 at 11:04

score 25 · Answer 1 · answered Feb 17 '15 at 12:49

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

wouldn't it be easier to just use openxlsx as it provides functions to edit an existing sheet? — samsamara, Jul 13 '21 at 05:59

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

It may be that the Java installed on your computer is incompatible with the xlsx library. The following thread discusses a similar issue with regard to the same package: enter link description here

Alternatively, your issue may be solved by a different Excel-related package, such as XLConnect. See this post: enter link description here

edited May 23 '17 at 12:09

Community

1
1

answered Sep 01 '16 at 21:10

David C.

1,974
2
19
29

1

Please consider adding more details to your post. In the event that your links change or become unavailable, your answer will not be helpful. – dckuehn Sep 01 '16 at 21:33

score 0 · Answer 3 · answered Jun 12 '23 at 13:30

0

Not with R package xlsx but with the alternative R package openxlsx, which has an "overwrite" option:

library(openxlsx)
write.xlsx(yourdataframe, file = "~/yourexcelfile.xlsx",
sheetName = "yourexcelsheetname", overwrite = TRUE)

answered Jun 12 '23 at 13:30

Rolando Gonzales

336
2
8

Using write.xlsx to replace an existing sheet with R package xlsx

3 Answers3

Linked