export list of tables as a object in xlsx file

Question

I want to export tables with captions as a single object into a xlsx file. I have a list of tables and want to save them in a xlsx file.

library(dplyr)
library(openxlsx)
library(expss)
df <- mtcars[1:8,4:ncol(mtcars)]
df1 <- subset(df,vs==1)
df2 <- subset(df,am==1)
df3 <- subset(df,gear==3)
df_list <- list(df1,df2,df3)


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


    t1 <- df_list[i]

    t1
  
  

xl_write(dfs,wb,1)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE) 

Answer 1

This is one way using dplyr and purrr with openxlsx

library(dplyr)
library(tibble)
library(purrr)
library(openxlsx)


# car models from row names into data frame proper

df <- 
  mtcars[1:8,4:ncol(mtcars)] %>% 
  rownames_to_column(var = "model")


df1 <- subset(df, vs==1)
df2 <- subset(df, am==1)
df3 <- subset(df, gear==3)

df_list <- list(df1,df2,df3)

# code to determine start row for each data frame

row_nr <- unlist(map(df_list, nrow))

skip = 2 # allows for header and one blank row between tables

df_row <- 
  data.frame(start_row = c(1, row_nr[-length(row_nr)])) %>%
  mutate(start_row = ifelse(row_number() == 1L, 1, start_row + skip),
         start_row = cumsum(start_row))

# create workbook and worksheet objects
wb <- createWorkbook()
addWorksheet(wb, "dfs")

# add data frames to worksheet
walk2(df_list, df_row$start_row, ~writeData(wb, "dfs", x = .x, startRow = .y))

saveWorkbook(wb, "test.xlsx", overwrite = TRUE)

output

Answer 2

There is a special function in the expss for saving list of tables in xlsx:

expss::xl_write_file(df_list, filename = "test.xlsx")