Exporting multiple R data frames to a single Excel sheet

0
votes

I would like to export multiple data frames from R to a single Excel sheet. By using the following code:

write.xlsx(DF1, file="C:\\Users\\Desktop\\filename.xlsx", sheetName="sheet1", 
       col.names=TRUE, row.names=TRUE, append=FALSE)

write.xlsx(DF2, file="C:\\Users\\Desktop\\filename.xlsx", sheetName="sheet2", 
       col.names=TRUE, row.names=TRUE, append=TRUE)

I can export two data frames to a single excel workbook, but in two separate sheets. I would like to export them in a single sheet, andif possible, to determine the specific cells that these data frames will be placed in.

Any suggestions more than welcome.

3
rexport-to-excelxlsxxlconnect
What do DF1 and DF2 look like? If they have the same columns you could rbind(DF1, DF2) and then export, if they have the same rows you could cbind(DF1, DF2) and then export. - slackline
@Hackerman Error in .jcall(wb, "Lorg/apache/poi/ss/usermodel/Sheet;", "createSheet", : java.lang.IllegalArgumentException: The workbook already contains a sheet of this name - Andre Elrico
@slackline cbind works but the output is not really what I want. I have two tables equal number or rows (same grouping variable) and different number of columns. And I would like them to appear next to each other but with some space (few columns) between them. - Ioannis
@Hackerman unfortunately Andre Elrico is right. I tried the same thing that you suggested before I write this post, but it is not possible. - Ioannis
@Ioannis Add empty columns to the table you wish to be on the left before you cbind(DF1, DF2), something like DF1$dummy <- "". - slackline

3 Answers

1
votes

This is not a ready to use answer but this should get you to your target. It would be a mess to write it into a comment.

  1. Create the combined df with the tools of R
  2. Write df to excel

a few notes to point 1.:

vertical offset the second df from the first by using Reduce(rbind,c(list(mtcars),rep(list(NA),3))) for a 3 cell offset for e.g.

rbind the colnames to your df rbind(names(mtcars),mtcars)

use numbers as colnames for so you will not have a problem rbinding different df with different variables. names(mtcars) <- seq_along(mtcars)

To point 2.:

Since your colnames are numbers now make sure you have your colnames set as FALSE.

Hope this helps and you can get your desired output.

1
votes

Following most of your suggestions I realized that by using cbind.data.frame I get an output which is not optimal, but the amount of time that I need to restructure the data in EXCEL is really insignificant. So, I will proceed with this for the time being.

Thanks

0
votes

I can't comment yet, so I'll provide my input here:

Using write.xlsx in R, how to write in a Specific Row or column in excel file

In that link it is suggested to organize your data in a single data frame to then write that into the excel sheet. You should have a look at that.

as slackline suggested, this is quite easy if your columns or rows are the same, using his suggested methods

Edit: To add spaces in between, just insert empty columns in between before writing