tableone xlsx export helper functions using openxlsx

tableone_helper_functions.R

###  tableone export helpers
###   Turn tableone output matrix into tidyverse data_frame
tableone_mat_to_data_frame <- function(mat) {
    mat %>%
        as.data.frame() %>%
        tibble::rownames_to_column(var = "Variable") %>%
        tibble::as_data_frame()
}


###   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 <- openxlsx::createWorkbook()
    openxlsx::addWorksheet(wb, sheetName = "1")

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

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

    ## Format all other columns
    varval_style <- openxlsx::createStyle(fontSize = font_size, halign = "center", valign = "center")
    openxlsx::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
    openxlsx::setColWidths(wb, sheet = 1, cols = seq_len(ncol(df)), widths = "auto")

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

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

###   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 <- openxlsx::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
        openxlsx::addWorksheet(wb, sheetName = sheet_name)

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

        ## Format the variable name column
        ## https://rdrr.io/cran/openxlsx/man/createStyle.html
        varname_style <- openxlsx::createStyle(fontSize = font_size, halign = "left", valign = "center")
        openxlsx::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 <- openxlsx::createStyle(fontSize = font_size, halign = "center", valign = "center")
        openxlsx::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
        openxlsx::setColWidths(wb, sheet = sheet_name, cols = seq_len(ncol(lst_df[[i]])), widths = "auto")

    }

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