xl_write: Write tables and other objects to an xlsx file with formatting

Description

Note that openxlsx package is required for these functions. It can be install by printing install.packages('openxlsx') in the console. On Windows system you also may need to install rtools. You can export several tables at once by combining them in a list. See examples. If you need to write all tables to the single sheet you can use xl_write_file. It automatically creates workbook, worksheet and save *.xlsx file for you.

Usage

xl_write(obj, wb, sheet, row = 1, col = 1, ...)
xl_write_file(obj, filename, sheetname = "Tables", ...)
# S3 method for default
xl_write(
  obj,
  wb,
  sheet,
  row = 1,
  col = 1,
  rownames = FALSE,
  colnames = !is.atomic(obj),
  ...
)
# S3 method for list
xl_write(obj, wb, sheet, row = 1, col = 1, gap = 1, ...)
# S3 method for etable
xl_write(
  obj,
  wb,
  sheet,
  row = 1,
  col = 1,
  remove_repeated = c("all", "rows", "columns", "none"),
  format_table = TRUE,
  borders = list(borderColour = "black", borderStyle = "thin"),
  header_format = openxlsx::createStyle(fgFill = "#EBEBEB", halign = "left", wrapText =
    FALSE),
  main_format = openxlsx::createStyle(halign = "right", numFmt = format(0, nsmall =
    get_expss_digits())),
  row_labels_format = openxlsx::createStyle(halign = "left"),
  total_format = openxlsx::createStyle(fgFill = "#EBEBEB", border = "TopBottom",
    borderStyle = "thin", halign = "right", numFmt = "0"),
  total_row_labels_format = openxlsx::createStyle(fgFill = "#EBEBEB", border =
    "TopBottom", borderStyle = "thin", halign = "left"),
  top_left_corner_format = header_format,
  row_symbols_to_remove = NULL,
  col_symbols_to_remove = NULL,
  other_rows_formats = NULL,
  other_row_labels_formats = NULL,
  other_cols_formats = NULL,
  other_col_labels_formats = NULL,
  additional_cells_formats = NULL,
  ...
)
# S3 method for with_caption
xl_write(
  obj,
  wb,
  sheet,
  row = 1,
  col = 1,
  remove_repeated = c("all", "rows", "columns", "none"),
  format_table = TRUE,
  borders = list(borderColour = "black", borderStyle = "thin"),
  header_format = openxlsx::createStyle(fgFill = "#EBEBEB", halign = "left", wrapText =
    FALSE),
  main_format = openxlsx::createStyle(halign = "right", numFmt = format(0, nsmall =
    get_expss_digits())),
  row_labels_format = openxlsx::createStyle(halign = "left"),
  total_format = openxlsx::createStyle(fgFill = "#EBEBEB", border = "TopBottom",
    borderStyle = "thin", halign = "right", numFmt = "0"),
  total_row_labels_format = openxlsx::createStyle(fgFill = "#EBEBEB", border =
    "TopBottom", borderStyle = "thin", halign = "left"),
  top_left_corner_format = header_format,
  row_symbols_to_remove = NULL,
  col_symbols_to_remove = NULL,
  other_rows_formats = NULL,
  other_row_labels_formats = NULL,
  other_cols_formats = NULL,
  other_col_labels_formats = NULL,
  additional_cells_formats = NULL,
  caption_format = openxlsx::createStyle(textDecoration = "bold", halign = "left"),
  ...
)

Value

invisibly return vector with rows and columns (c(rows, columns)) occupied by outputted object.

Arguments

obj: table - result of cro, fre and etc. obj also can be data.frame, list or other objects.
wb: xlsx workbook object, result of createWorkbook function.
sheet: character or numeric - worksheet name/number in the workbook wb
row: numeric - starting row for writing data
col: numeric - starting column for writing data
...: further arguments for xl_write
filename: A character string naming an xlsx file. For xl_write_file.
sheetname: A character name for the worksheet. For xl_write_file.
rownames: logical should we write data.frame row names?
colnames: logical should we write data.frame column names?
gap: integer. Number of rows between list elements.
remove_repeated: Should we remove duplicated row or column labels in the rows/columns of the etable? Possible values: "all", "rows", "columns", "none".
format_table: logical should we format table? If FALSE all format arguments will be ignored.
borders: list Style of the table borders. List with two named elements: borderColour and borderStyle. For details see createStyle function. If it is NULL then no table borders will be produced.
header_format: table header format - result of the createStyle function.
main_format: result of the createStyle function. Format of the table main area except total rows. Total rows is rows which row labels contain '#'.
row_labels_format: result of the createStyle function. Format of the row labels area except total rows. Total rows is rows which row labels contain '#'.
total_format: result of the createStyle function. Format of the total rows in the table main area. Total rows is rows which row labels contain '#'.
total_row_labels_format: result of the createStyle function. Format of the total rows in the row labels area. Total rows is rows which row labels contain '#'.
top_left_corner_format: result of the createStyle function.
row_symbols_to_remove: character vector. Perl-style regular expressions for substrings which will be removed from row labels.
col_symbols_to_remove: character vector. Perl-style regular expressions for substrings which will be removed from column names.
other_rows_formats: named list. Names of the list are perl-style regular expression patterns, items of the list are results of the createStyle function. Rows in the main area which row labels contain pattern will be formatted according to the appropriate style.
other_row_labels_formats: named list. Names of the list are perl-style regular expression patterns, items of the list are results of the createStyle function. Rows in the row labels area which row labels contain pattern will be formatted according to the appropriate style.
other_cols_formats: named list. Names of the list are perl-style regular expression patterns, items of the list are results of the createStyle function. Columns in the main area which column labels contain pattern will be formatted according to the appropriate style.
other_col_labels_formats: named list. Names of the list are perl-style regular expression patterns, items of the list are results of the createStyle function. Columns in the header area which column labels contain pattern will be formatted according to the appropriate style.
additional_cells_formats: list Each item of the list is list which consists of two elements. First element is two columns matrix or data.frame with row number and column numbers in the main area of the table. Such matrix can be produced with code which(logical_condition, arr.ind = TRUE). Instead of matrix one can use function which accepts original table (obj) and return such matrix. Second element is result of the createStyle function. Cells in the main area will be formatted according to this style.
caption_format: result of the createStyle function.

Examples

Run this code

if (FALSE) {
library(openxlsx)
data(mtcars)
# add labels to dataset
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"
)

# create table with caption
mtcars_table = cross_cpct(mtcars,
                             cell_vars = list(cyl, gear),
                             col_vars = list(total(), am, vs)
) %>% 
    set_caption("Table 1")


wb = createWorkbook()
sh = addWorksheet(wb, "Tables")
# export table
xl_write(mtcars_table, wb, sh)
saveWorkbook(wb, "table1.xlsx", overwrite = TRUE)

## quick export
xl_write_file(mtcars_table, "table1.xlsx")

## custom cells formatting
wb = createWorkbook()
sh = addWorksheet(wb, "Tables")

# we want to mark cells which are greater than total column
my_formatter = function(tbl){
    greater_than_total = tbl[,-1]>tbl[[2]]
    which(greater_than_total, arr.ind = TRUE)
}
# export table
xl_write(mtcars_table, wb, sh, 
    additional_cells_formats = list(
        list(my_formatter, createStyle(textDecoration =  "bold", fontColour = "blue"))
    )
)
saveWorkbook(wb, "table_with_additional_format.xlsx", overwrite = TRUE)

## automated report generation on multiple variables with the same banner
 
banner = with(mtcars, list(total(), am, vs))

# create list of tables
list_of_tables = lapply(mtcars, function(variable) {
    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")
# export list of tables with additional formatting
xl_write(list_of_tables, wb, sh, 
         # remove '#' sign from totals 
         col_symbols_to_remove = "#",
         row_symbols_to_remove = "#",
         # format total column as bold
         other_col_labels_formats = list("#" = createStyle(textDecoration = "bold")),
         other_cols_formats = list("#" = createStyle(textDecoration = "bold")),
)
saveWorkbook(wb, "report.xlsx", overwrite = TRUE)
}

Run the code above in your browser using DataLab

Data engineering and BI courses are free!

Description

Usage

Value

Arguments

Examples