expss - Exporting many tables to MS Excel

Question

I have read up on this in the article "expss - Tables with labels in R". Using the example supplied I can export 1 table to Excel. But I want to export many tables, all run at the same time. Can someone give me some pointers on how to export many tables at the same time. Regards

Answer 1

The simplest way is to put all your tables in the list. Then you can export this list to Excel. Example:

library(expss)
library(openxlsx)
data(mtcars)
mtcars = apply_labels(mtcars,
                      mpg = "Miles/(US) gallon",
                      cyl = "Number of cylinders",
                      disp = "Displacement (cu.in.)",
                      hp = "Gross horsepower",
                      drat = "Rear axle ratio",
                      wt = "Weight (lb/1000)",
                      qsec = "1/4 mile time",
                      vs = "Engine",
                      vs = c("V-engine" = 0,
                             "Straight engine" = 1),
                      am = "Transmission",
                      am = c("Automatic" = 0,
                             "Manual"=1),
                      gear = "Number of forward gears",
                      carb = "Number of carburetors"
)

banner = calc(mtcars, list(total(), am, vs))

library(comprehenr) # for 'to_list' function

list_of_tables = to_list(
    for(variable in mtcars){
        if(length(unique(variable))<7){
            cro_cpct(variable, banner) %>% significance_cpct()
        } else {
            # if number of unique values greater than seven we calculate mean
            cro_mean_sd_n(variable, banner) %>% significance_means()

        }    
    }
)

wb = createWorkbook()
sh = addWorksheet(wb, "Tables")

xl_write(list_of_tables, wb, sh)

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

If your tables are very different and you can't calculate them in the loop then you can put them in the list one by one, e. g.:

list_of_tables[[1]] = tab1

...

list_of_tables[[10]] = tab10

UPDATE. Function which additionally copies printed table to list with the name 'output'.

new_output = function(){
    output <<- list()
    print.etable <<- function(x, ...){
        output[[length(output) + 1]] <<- x
        expss:::print.etable(x, ...)
    }
    print.with_caption <<- function(x, ...){
        output[[length(output) + 1]] <<- x
        expss:::print.with_caption(x, ...)
    }
}

stop_output = function(){
    rm(print.etable, envir = .GlobalEnv)
    rm(print.with_caption, envir = .GlobalEnv)
}


new_output() # create empty list for output
banner = calc(mtcars, list(total(), am, vs))

# printed tables also will be added to 'output'
cro_cpct(mtcars$gear, banner)
cro_cpct(mtcars$carb, banner)

stop_output() # stop adding to list

wb = createWorkbook()
sh = addWorksheet(wb, "Tables")

xl_write(output, wb, sh)

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