Export different dataframes to the same Excel sheet

user_11696623 0
Viewed 105

I am using openxlsx package to export data frames from R to excel.

How can I export several dataframes:

a <- data.frame("y"=c(2009,2010,2011,2012),"b"=c(3,4,5,6))
b <- data.frame("y"=c(2009,2010,2011,2012),"b"=c(12,2,7,8))
c <- data.frame("y"=c(2009,2010,2011,2012),"b"=c(5,9,1,6))

on the same excel sheet, each one separated from the previous one by an empty row, and add a column names of each one?

openxlsx dataframe export r
2 Answers

We can use writeData playing with startRow argument, here an example:

# create workbook
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")

# add dataframes a,b,c starting on different rows
writeData(wb = wb, sheet = "Sheet1", startCol = 1,
               startRow = 1, x = a)
writeData(wb = wb, sheet = "Sheet1", startCol = 1,
               startRow = 1 + nrow(a) + 2, x = b)
writeData(wb = wb, sheet = "Sheet1", startCol = 1,
               startRow = 1 + nrow(a) + 2 + nrow(b) + 2, x = c)

# outout to a file
saveWorkbook(wb, "myFile.xlsx", overwrite = TRUE)

enter image description here

user_6800680

Maybe you can try something like below

df <- do.call(rbind,lapply(list(a,b,c),function(x) rbind(x,"")))
write.xlsx(df,"xxx.xlsx")

where df looks like

> df
      y  b
1  2009  3
2  2010  4
3  2011  5
4  2012  6
5
6  2009 12
7  2010  2
8  2011  7
9  2012  8
10
11 2009  5
12 2010  9
13 2011  1
14 2012  6
15

If you want to vertically stack those data frames, here is another version (similar to the one by @zx8754)

wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
lst <- list(a,b,c)
startRows <- cumsum(c(1,(sapply(lst,nrow)+2)[-length(lst)]))
for (k in seq_along(lst)) {
  writeData(wb = wb, sheet = "Sheet1", startCol = 1, startRow = startRows[k], x = lst[[k]])
}
saveWorkbook(wb, "xxxx.xlsx", overwrite = TRUE)
user_121587570
Related