[R] write.xlsx- writing in a single sheet

[Marc Schwartz]

> On Jan 15, 2016, at 8:43 AM, Mohsen Jafarikia <jafarikia at gmail.com> wrote:
> 
> Hello all:
> 
> I am having problem writing a few files in a single sheet of excel. It
> seems R has problem writing on the same sheet. Maybe there is a command
> that I am missing. Here is the code I am using:
> 
> library(xlsx)
> 
> ifn11 <- "A1.xlsx"
> dat11  <- read.xlsx(ifn11, sheetName="A.csv", header = TRUE)
> 
> ifn12 <- "A2.xlsx"
> dat12  <- read.xlsx(ifn12, sheetName="A.csv", header = TRUE)
> 
> ifn13 <- "A3.xlsx"
> dat13  <- read.xlsx(ifn13, sheetName="A.csv", header = TRUE)
> 
> ifn21 <- "F1.xlsx"
> dat21 <- read.xlsx(ifn21, sheetName="F.csv",header = TRUE)
> 
> ifn22 <- "F2.xlsx"
> dat22  <- read.xlsx(ifn22, sheetName="F.csv",header = TRUE)
> 
> ifn23 <- "F3.xlsx"
> dat23 <- read.xlsx(ifn23, sheetName="F.csv",header = TRUE)
> 
> write.xlsx(dat11,  file="AC.xlsx", sheetName="A",  append=FALSE)
> write.xlsx(dat12,  file="AC.xlsx",                             append= TRUE)
> write.xlsx(dat13,  file="AC.xlsx",                             append= TRUE)
> write.xlsx(dat21,  file="AC.xlsx", sheetName="F",  append= TRUE)
> write.xlsx(dat22,  file="AC.xlsx",                             append= TRUE)
> write.xlsx(dat23,  file="AC.xlsx",                             append= TRUE)
> 
> And here is the error message I am having:
> 
> Error in .jcall(wb, "Lorg/apache/poi/ss/usermodel/Sheet;", "createSheet",
> :
>  java.lang.IllegalArgumentException: The workbook already contains a sheet
> of this name
> 
> This error message comes after running the write.xlsx(dat13,
> file="AC.xlsx", showNA=FALSE, row.names=FALSE, append= TRUE) line. Program
> creates a sheet named "A" when writes dat11, then creates "sheet1" after
> writing dat12 and when tries to write dat13, it gives me error. It seems it
> tries to write on "sheet1" which already exists. I would like dat11, dat12
> and dat13 will be all written after each other on sheet "A" and dat12,
> dat22 and dat23 in sheet "F".
> 
> Anybody has any comments please.
> 
> Regards,
> Mohsen

Hi,

From a review of the package documentation (hint...hint), the write.xlsx() function can add new worksheets to a new or existing Excel file. write.xlsx() cannot append data to an existing worksheet.

The 'append = TRUE' argument enables you to add a new worksheet to an existing Excel file, as opposed to creating a new Excel file or overwriting an existing Excel file.

It appears that the addDataFrame() function might support the approach of adding the contents of a data frame object to an existing worksheet.

I have not used the xlsx package, but note that the XLConnect package, which I have not used either, also seems to support the ability to append data to an existing worksheet.

I would recommend spending more time reviewing the package documentation.

Regards,

Marc Schwartz