[R] write.xlsx- writing in a single sheet
marc_schwartz at me.com
Fri Jan 15 16:11:10 CET 2016
> 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:
> 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.
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.
More information about the R-help