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 [[alternative HTML version deleted]]
> 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, > MohsenHi, 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
Do you mean that you try to write several dataframes to the same sheet? You have asked this before, and I think it has been said that that is not possible with write.xls. I suppose that you still try to write rows of data of varying length to one sheet. I think that is possible with the lowlevel functions of xlsx (like createRow, createWorkbook), but not with write.xlsx. Read the documentation of xlsx for this. I have no experience with this. In addition to that: would you please stop sending messages in html? This has been asked to you before, and in general people are very irritated about this, and won't answer your questions. Frans 2016-01-15 15:43 GMT+01:00 Mohsen Jafarikia <jafarikia at gmail.com>:> 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 > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >[[alternative HTML version deleted]]
Hi Mohsen, I can guess two things that you might want. One is to join all of the data in the "CSV" files into a single data frame and then write that to a single XLSX sheet. If the names and data structure of these files are similar enough to "rbind" them, just do this in R and then write the result. If the names are different, you can change the names to be consistent across the data frames or reorder them. If the structures are different, add columns of NAs where the numbers of columns are different and "rbind" or "merge". Jim On Sat, Jan 16, 2016 at 1: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 > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >[[alternative HTML version deleted]]