saving list of tables in xlsx file

[ashley21]

I have many functions which create a list of tables. All the tables have different demographies.
I am looking for a function to dynamically save the list of tables in xlsx file one by one.

library(expss)
library(dplyr)
library(purrr)

df <- mtcars

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

df_list <- list(df1,df2,df3)
banner <- c("T1","T2","T3")


sub_fun<-function(db,var,var_name){
  var = rlang::parse_expr(var)

  df1<- db %>% filter(!is.na(!!var)) %>%   summarise(
    Median =quantile(!!var, type=6, probs = seq(0, 1, 0.25), na.rm=TRUE)[3],
    Mean =  mean(!! var, na.rm=TRUE),
    N = sum(!is.na(!!var)))
  df<- df1 %>% mutate(" "=!!var_name,
                                       Median = Median,
                                       Mean = Mean,)
  df <- df %>% select(" ",everything(),N)
  df

  }
func1<-function(db,list_var,var_name_list,....){
  table_list1<-list()
  for (d in 1:length(df_list)) {
    
    table_list<-list()
    for (i in 1:length(list_var)) {
      
      
      table_list[[i]]<-sub_fun(db, list_var[i],var_name_list[i])
      
      t1 <- do.call(rbind,table_list)
      
    }
    
    colnames(t1)[1] <- banner[[d]]
    t1 <- t1 %>%
      add_row() %>%
      mutate_all(~replace(., is.na(.), ""))
    
    table_list1[[d]] <- t1
  }
  names(table_list1) <- banner
  Map(cbind, table_list1, SampleID = names(table_list1))
  
  
  colnames <- c("name","Median","Mean", "N") 
  
  for (i in seq_along(table_list1)){
    colnames(table_list1[[i]]) <- colnames
  }
  
  t2 <- do.call(rbind,table_list1)
  t2
}

t1<- func1(db=df,list_var=c("cyl","disp","hp"),var_name_list=c("klick","Nemar","Wingo"))
t2 = mtcars %>% 
  cross_cpct(
    cell_vars = list(cyl, gear),
    col_vars = list(total(), am, vs)
  ) %>% 
  set_caption("Table 1")

tbls <- c(t1,t2)
startRows <- c(0, cumsum(2 + sapply(tbls, nrow)[-length(tbls)])) + 1

fn <- tempfile(fileext = "xlsx")

wb <- createWorkbook()
addWorksheet(wb, "tbls")
mapply(function(tbl, startRow) writeData(wb, "tbls", x = tbl, startRow = startRow), tbls, startRows)

saveWorkbook(wb, fn, overwrite = TRUE) %>% file.show(.)

I have tried this way but it doesn't work. Is there a solution for this?

actually its not saving tables in xlsx files , i am getting many errors meanwhile trying this approach

it should save tables in xlsx files like below

[FJCC]

I think this answers your basic question but uses data frames and row numbers made manually.

library(openxlsx)

DF1 <- data.frame(Name = c("A", "B", "C"), Value = 1:3)
DF2 <- data.frame(V1 = rnorm(10), V2 = rnorm(10), V3 = rnorm(10),
                  V4 = rnorm(10), V5 = rnorm(10), V6 = rnorm(10))
DF3 <- data.frame(Date = seq.Date(as.Date("2022-01-01"), as.Date("2022-01-06"), by = 1),
                  Set = paste0(LETTERS[1:6], "_1"),
                  value = 11:16)
tbls <- list(DF1, DF2, DF3)
start_rows <- c(1, 6, 18)

wb <- createWorkbook()
addWorksheet(wb, "Tbls")
mapply(function(TBL, Row) writeData(wb, "Tbls", x = TBL, startRow = Row), tbls, start_rows)
saveWorkbook(wb, file = "MultiTable.xlsx")

[ashley21]

That what i want to make it dynamic, because i have many tables with different rows and columns.
do we have any other solution to make it dynamic...???
also some of my tables are data frame and some of them are showing as etable,dataframe

[FJCC]

Sorry, I do not understand what you mean by "other solution to make it dynamic". The writing of the data frames into the Excel sheet will work for any list of data frames as long as you also have a vector of start rows. Do you want the vector of start rows to be built by code? Should the list of data frames be made by code? Please explain the steps that you want the process to include.

[str_guru]

I have tried but it its not showing t1.1, t1.2,t1.3 in xlsx file. Is there a solution for this?
also not showing the labels for t2 (Total, Transmission,engine) in separate row.

it should save tables in xlsx file like shown in picture. i want to manke it dynamic mean i want a function which takes tables from list "tbls" and save them in xlsx file one by one.

FYI DF1, DF2, DF3 objects are data frame or etable.

[FJCC]

@str_guru

i want a function which takes tables from list "tbls" and save them in xlsx file one by one.

This is exactly what the following code from my earlier post does.

wb <- createWorkbook()
addWorksheet(wb, "Tbls")
mapply(function(TBL, Row) writeData(wb, "Tbls", x = TBL, startRow = Row), tbls, start_rows)
saveWorkbook(wb, file = "MultiTable.xlsx")

It seems that you want the data frames saved automatically and with very particular placement. I cannot be sure because I do not have your tbls object. If you post the output of

dput(tbls)

someone might be able to help. The solution might require a carefully tailored function. that would take a lot of effort to write.