CODEWITHSUNDEEP
r
str_rst
str_rst

Reputation: 175

saving list of tables in xlsx file

I have many functions which create a list of tables. All the tables have different demographies.

I am looking for a something which dynamically save the list of tables in xlsx file one by one.

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

df <- mtcars[1:7,]

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")


t1 <- df1
t2 <- df2
t3 <- df3

t1 <- t1 %>%
      add_row() %>%
      mutate_all(~replace(., is.na(.), ""))
    
t2 <- t2 %>%
  add_row() %>%
  mutate_all(~replace(., is.na(.), ""))

t3 <- t3 %>%
  add_row() %>%
  mutate_all(~replace(., is.na(.), ""))

table_list1 <- list(t1,t2,t3)
  names(table_list1) <- banner
  Map(cbind, table_list1, SampleID = names(table_list1))
  
  
  colnames <- c("name","TT1","TT2", "TT3","TT4","TT5","TT6","TT7","TT8","TT9","TT10") 
  
  for (i in seq_along(table_list1)){
    colnames(table_list1[[i]]) <- colnames
  }
  
  t2 <- do.call(rbind,table_list1)
  t2

tbl1 <- t2  
tbl2 = mtcars %>% 
  cross_cpct(
    cell_vars = list(cyl, gear),
    col_vars = list(total(), am, vs)
  ) %>% 
  set_caption("Table 1")

# above this is everything is ok as required

tbls <- list(tbl1,tbl2)
startRows <- c(0, cumsum(2 + sapply(tbls, nrow)[-length(tbls)])) + 1

#fn <- tempfile(fileext = "xlsx")

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

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

I have tried this way but it its not showing t1.1, t1.2,t1.3 in xlsx file. Is there a solution for this?

actually its not saving tables in xlsx files also not showing the labels for t2 (Total, Transmission,engine) in separate row, i am getting many errors meanwhile trying this approach

it should save tables in xlsx files like below

enter image description here

Upvotes: 1

Views: 161

Answers (2)

deschen
deschen

Reputation: 11026

I heavily reduced your "example" to what is needed for your problem.

Use map2 from package purrr to loop over your tables list and the startrows vector at the same time.

library(purrr)
library(openxlsx)

tbl1 <- mtcars[1:3, ]
tbl2 <- mtcars[4:6, ]

tbls <- list(tbl1,tbl2)
startRows <- c(0, cumsum(2 + sapply(tbls, nrow)[-length(tbls)])) + 1

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

map2(.x = tbls,
     .y = startRows,
     .f = ~writeData(wb       = wb,
                     sheet    = "sheet1",
                     x        = .x,
                     startRow = .y))

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

Proof of created Excel file:

enter image description here

Upvotes: 1

Gregory Demin
Gregory Demin

Reputation: 4846

First of all, there are no "t1.1, t1.2,t1.3" tables in your list with tables "tbls".

If it is ok then there is a special function in the expss for saving list of tables in xlsx:

expss::xl_write_file(tbls, filename = "test.xlsx", sheet = "sheet1")

Upvotes: 1

Related Questions