Exporting tableone results to a formatted xlsx file via openxlsx

tableone_openxlsx.R

###  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 xlsx file
write_tableone_mat_to_xlsx <- function(tableone_mat, file) {
    ## 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 = tableone_mat_to_data_frame(tableone_mat))

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

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

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

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