saving multiple tables in xlsx files with function

[ashley21]

I am trying to create a function which can save multiple tables in xl file one by one but there should be a one row gap between tables .

I tried two ways but nothing is working perfectly as required.

library(expss)
library(openxlsx)

tt <- list()

tt[[length(tt)+1]]  = mtcars %>% 
  cross_cpct(
    cell_vars = list(cyl, gear),
    col_vars = list(total(), am, vs)
  )


tt[[length(tt)+1]] = mtcars %>%
  tab_cols(total(), am %nest% vs) %>%
  tab_cells(mpg, hp) %>%
  tab_stat_mean() %>%
  tab_cells(cyl) %>%
  tab_stat_cpct() %>%
  tab_pivot()


brands = as.sheet(t(replicate(20,sample(c(1:5,NA),4,replace = FALSE))))
score = sample(-1:1,20,replace = TRUE)
var_lab(brands) = "Used brands"
val_lab(brands) = autonum("
Brand A
Brand B
Brand C
Brand D
Brand E
")

tt[[length(tt)+1]] <- cro_cpct(mrset(brands), score)

wb1 <- createWorkbook()

tabl_theme <- function(tbls, wb){
  addWorksheet(wb, "Tables")
  rows <- c(0, cumsum(2 + sapply(tbls, nrow)[-length(tbls)])) + 1
  setColWidths(wb, 1,cols = 1:100, widths = "auto")
  for(i in seq_along(tbls)){
    xl_write(tbls[[i]],wb,1,row = rows[i])
  }
  return(wb)
}

tabl_theme(tbls=tt,wb=wb1)
saveWorkbook(wb1, "test3.xlsx", overwrite = TRUE)
file.show("test3.xlsx")

I also tried to save like below but this also doesn't work do we have any other solution or what i am doing wrong.

The objective is to save n number of tables in tt, so i have more than 500 list of tables in tt and i want to save them in xlsx file with xl_write format only. also there should be one row gap between all the tables.

saving_tabls <- function(tbls, wb){

addWorksheet(wb, "Tables")

start_col <- 1
cols_between_data <- 3 

for(i in length(tbls)) {
  xl_write(xt[[1]],wb,1,row = start_col)
 }
  start_col <- start_col + nrow(xt[[1]]) + cols_between_data

}

[FJCC]

I notice that in this version of your code

wb1 <- createWorkbook()

tabl_theme <- function(tbls, wb){
  addWorksheet(wb, "Tables")
  rows <- c(0, cumsum(2 + sapply(tbls, nrow)[-length(tbls)])) + 1
  setColWidths(wb, 1,cols = 1:100, widths = "auto")
  for(i in seq_along(tbls)){
    xl_write(tbls[[i]],wb,1,row = rows[i])
  }
  return(wb)
}

tabl_theme(tbls=tt,wb=wb1)
saveWorkbook(wb1, "test3.xlsx", overwrite = TRUE)

you never assign any data to wb1. What if you do

wb1 <- tabl_theme(tbls=tt,wb=wb1)

and then use saveWorkbook()?

[ashley21]

I assigned it but still tables are pasting one by one without any blank row between them

[FJCC]

If I run the following code, I get an Excel file with the tables separated by one row. I directly use openxlsx instead of expss but the logic should be directly transferable. I also invented a simpler list of tables than you used in your code, just for brevity. Can you adapt this to your case?

library(openxlsx)
#> Warning: package 'openxlsx' was built under R version 4.1.2
#make 4 dta frames with varying number of rows.
Smpls <- c(6,3,10, 5)
Tbls <- lapply(Smpls, function(R) head(mtcars, R))

wb1 <- createWorkbook()
WrtFunc <- function(Ts, wb) {
  addWorksheet(wb, "Tables")
  rawRows <- sapply(Ts,nrow)
  HeaderSpace <- rawRows + 2
  StartRows <- c(1, 1 + cumsum(HeaderSpace[-length(HeaderSpace)]))
  for (i in seq_along(StartRows)) {
    writeData(wb,"Tables", x = Ts[[i]], startRow = StartRows[i])
  }
  saveWorkbook(wb, "ExcelTables.xlsx")
}
WrtFunc(Tbls,wb1)

^{Created on 2022-03-17 by the reprex package (v2.0.1)}

[ashley21]

Actually the main objective was to export tables in SPSS styles with banners . that why i am looking for a solution with xl_write.