tableone_export_helpers.R

library(openxlsx)

###  tableone export helpers
###   Turn tableone output matrix into tidyverse data_frame
tableone_mat_to_data_frame <- function(mat) {
    bind_cols(data_frame(Variable = rownames(mat)),
              as_data_frame(mat))
}


###   Write a data frame to an xlsx file
write_df_to_xlsx <- function(df, file, font_size) {
    ## Create a workbook object with one sheet
    ## https://rdrr.io/cran/openxlsx/man/setColWidths.html
    wb <- createWorkbook()
    addWorksheet(wb, sheetName = "1")

    ## Write data frame data to the workbook object
    writeData(wb, sheet = 1, x = df)

    ## Format the variable name column
    ## https://rdrr.io/cran/openxlsx/man/createStyle.html
    varname_style <- createStyle(fontSize = font_size, halign = "left", valign = "center")
    addStyle(wb, sheet = 1, style = varname_style,
             rows = seq_len(nrow(df) + 1),
             cols = 1,
             gridExpand = TRUE)

    ## Format all other columns
    varval_style <- createStyle(fontSize = font_size, halign = "center", valign = "center")
    addStyle(wb, sheet = 1, style = varval_style,
             rows = seq_len(nrow(df) + 1),
             cols = seq_len(ncol(df))[-1],
             gridExpand = TRUE)

    ## Fix column width automatically
    setColWidths(wb, sheet = 1, cols = seq_len(ncol(df)), widths = "auto")

    ## Save to a file
    saveWorkbook(wb, file = file, overwrite = TRUE)
}

###   Write a tableone matrix to an xlsx file
write_tableone_mat_to_xlsx <- function(mat, file) {
    write_df_to_xlsx(df = tableone_mat_to_data_frame(mat),
                     file = file,
                     font_size = 8)
}

###   Write multiple data_frames to a single xlsx file (use openxlsx)
write_lst_of_df_to_xlsx <- function(lst_df, file, font_size) {
    ## Create a workbook object with one sheet
    ## https://rdrr.io/cran/openxlsx/man/setColWidths.html
    wb <- createWorkbook()

    ## Work on each data_frame
    for (i in seq_along(lst_df)) {

        sheet_name <- ifelse(is.null(names(lst_df[i])),
                             ## Index if not available
                             i,
                             ## Otherwise name
                             names(lst_df[i]))

        ## Add a worksheet with the name
        addWorksheet(wb, sheetName = sheet_name)

        ## Write data frame data to the workbook object
        writeData(wb, sheet = sheet_name, x = lst_df[[i]])

        ## Format the variable name column
        ## https://rdrr.io/cran/openxlsx/man/createStyle.html
        varname_style <- createStyle(fontSize = font_size, halign = "left", valign = "center")
        addStyle(wb, sheet = sheet_name, style = varname_style,
                 rows = seq_len(nrow(lst_df[[i]]) + 1),
                 cols = 1,
                 gridExpand = TRUE)

        ## Format all other columns
        varval_style <- createStyle(fontSize = font_size, halign = "center", valign = "center")
        addStyle(wb, sheet = sheet_name, style = varval_style,
                 rows = seq_len(nrow(lst_df[[i]]) + 1),
                 cols = seq_len(ncol(lst_df[[i]]))[-1],
                 gridExpand = TRUE)

        ## Fix column width automatically
        setColWidths(wb, sheet = sheet_name, cols = seq_len(ncol(lst_df[[i]])), widths = "auto")

    }

    ## Save to a file
    saveWorkbook(wb, file = file, overwrite = TRUE)
}