expss (version 0.11.6)

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

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)
}