Export multiple dataframes to one Excel Sheet with r

Question

I have around 30 different data frames in r and I want them to be in one Excel sheet, with the dataframe name as well.

For output I'm looking for something like this:

dfname1
col1    col2
  x        x
  x        x
dfname2
col1    col3
  x        x

How should I do this as an iterative process?

Possible duplicate of how to write multiple dataframe to a single csv file in a loop in R? — Mako212
– Mako212, Commented Mar 22, 2018 at 17:28
Possible duplicate of R: easy way to export multiple data.frame to multiple excel worksheets? — Rui Barradas
– Rui Barradas, Commented Mar 22, 2018 at 17:34
The first suggested method places all of them into a csv but does not have the name above each new df — Yolo_chicken
– Yolo_chicken, Commented Mar 22, 2018 at 17:37
Not duplicate. OP needs it all in a single Excel spreadsheet. To be fare, it's been almost 2 years after and I haven't found other but this question asking it. — Leonardo
– Leonardo, Commented Jan 17, 2020 at 17:56

ngm · Accepted Answer · 2018-03-22 17:51:57Z

8

Sounds like a bad idea to intersperse data frame names with data in the same worksheet, but that's up to you.

Anyway, use openxlsx. Here's a way to do it:

dfname1 <- data.frame(col1 = c("x", "x"), col2 = c("x", "x"), stringsAsFactors = FALSE)
dfname2 <- data.frame(col1 = c("x"), col3 = c("x"), stringsAsFactors = FALSE)

df_list <- list(dfname1=dfname1,
                dfname2=dfname2)

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Foo")

curr_row <- 1
for(i in seq_along(df_list)) {
  writeData(wb, "Foo", names(df_list)[i], startCol = 1, startRow = curr_row)
  writeData(wb, "Foo", df_list[[i]], startCol = 1, startRow = curr_row+1)
  curr_row <- curr_row + nrow(df_list[[i]]) + 2
}

saveWorkbook(wb, "bar.xlsx")

This gives you (literally) what you asked for.

edited Mar 22, 2018 at 17:51

answered Mar 22, 2018 at 17:45

ngm

2,60910 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Leonardo Over a year ago

That works a charm. But why would it be a bad idea "intersperse data frame names with data in the same worksheet"? I've been doing it to kinda concatenate lots of statistics tests and never saw a problem.

Collectives™ on Stack Overflow

Export multiple dataframes to one Excel Sheet with r

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related