Warning: xltabr
is in early development. Please raise an issue if you find any bugs
xltabr
allows you to write formatted cross tabulations to Excel using openxlsx
. It has been developed to help automate the process of publishing Official Statistics.
The package works best when the input dataframe is the output of a crosstabulation performed by reshape2:dcast
. This allows the package to autorecognise various elements of the cross tabulation, which can be styled accordingly.
For example, given a crosstabulation ct
produced by reshape2
, the following code produces the table shown.
titles = c("Breakdown of car statistics", "Cross tabulation of drive and age against type*") footers = "*age as of January 2015" wb <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = footers) openxlsx::openXL(wb)
This readme provides a variety of examples of increasing complexity. It is based on a simulated dataset built into the package, which you can see here.
Much of xltabr
utility comes from its ability to automatically format cross tabulations which have been produced by reshape2:dcast
.
The package provides a core convenience function called xltabr::auto_crosstab_to_xl
. This wraps more advanced functionality, at the cost of reducing flexibility.
The following code assumes you've read in the synthetic data as follows:
# Read in data path <- system.file("extdata", "synthetic_data.csv", package="xltabr") df <- read.csv(path, stringsAsFactors = FALSE)
# Create a cross tabulation using reshape2 ct <- reshape2::dcast(df, drive + age ~ type, value.var= "value", margins=c("drive", "age"), fun.aggregate = sum) ct <- dplyr::arrange(ct, -row_number()) # Use the main convenience function from xltabr to output to excel tab <- xltabr::auto_crosstab_to_wb(ct, return_tab = TRUE) #wb is an openxlsx workbook object openxlsx::openXL(tab$wb)
There is also a convenience function to write a standard data.frame to Excel:
wb <- xltabr::auto_df_to_wb(mtcars) openxlsx::openXL(wb)
titles = c("Breakdown of car statistics", "Cross tabulation of drive and age against type*") footers = "*age as of January 2015" wb <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = footers) openxlsx::openXL(wb)
path <- system.file("extdata", "styles_pub.xlsx", package = "xltabr") cell_path <- system.file("extdata", "style_to_excel_number_format_alt.csv", package = "xltabr") xltabr::set_style_path(path) xltabr::set_cell_format_path(cell_path) wb <- xltabr::auto_crosstab_to_wb(ct) openxlsx::openXL(wb)
# Change back to default styles xltabr::set_style_path() xltabr::set_cell_format_path() # Create second crosstab ct2 <- reshape2::dcast(df, drive + age ~ colour, value.var= "value", margins=c("drive", "age"), fun.aggregate = sum) ct2 <- dplyr::arrange(ct2, -row_number()) tab <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = c(footers, ""), return_tab = TRUE) titles2 = c("Table 2: More car statistics", "Cross tabulation of drive and age against colour*") footers2 = "*age as of January 2015" wb <- xltabr::auto_crosstab_to_wb(ct2, titles = titles2, footers = footers2, insert_below_tab = tab) openxlsx::openXL(wb)
tab <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = c(footers, ""), return_tab = TRUE) xltabr::set_style_path(path) xltabr::set_cell_format_path(cell_path) wb <- xltabr::auto_crosstab_to_wb(ct2, titles = titles2, footers = footers2, insert_below_tab = tab) openxlsx::openXL(wb) # Change back to default styles xltabr::set_style_path() xltabr::set_cell_format_path()
ct <- reshape2::dcast(df, drive + age ~ type, value.var= "value", fun.aggregate = sum) wb <- xltabr::auto_crosstab_to_wb(ct, titles = titles, footers = c(footers, ""), indent = FALSE, left_header_colnames = c("drive", "age")) openxlsx::openXL(wb)
The following provides a list of all the options you can provide to auto_crosstab_to_wb
suppressMessages(devtools::document()) tools::Rd2txt_options(underline_titles = FALSE, width = 300)
tools::Rd2txt("../man/auto_crosstab_to_wb.Rd")
The simple examples above wrap lower-level functions. These functions can be used to customise the output in a number of ways.
The following example shows the range of functions available.
tab <- xltabr::initialise() %>% #Options here for providing an existing workbook, changing worksheet name, and position of table in wb xltabr::add_title(title_text) %>% # Optional title_style_names allows user to specify formatting xltabr::add_top_headers(h_list) %>% # Optional row_style_names and col_style_names allows custom formatting xltabr::add_body(df) %>% #Optional left_header_colnames, row_style_names, left_header_style_names col_style names xltabr::add_footer(footer_text) %>% # Optional footer_style_names xltabr::auto_detect_left_headers() %>% # Auto detect left headers through presence of keyword, default = '(all)' xltabr::auto_detect_body_title_level() %>% # Auto detect level of emphasis of each row in body, through presence of keyword xltabr::auto_style_indent() %>% # Consolidate all left headers into a single column, with indentation to signify emphasis level xltabr::auto_merge_title_cells() %>% # merge the cells in the title xltabr::auto_merge_footer_cells() # merge the cells in the footer
The convenience functions contain further examples of how to build up a tab. See here.
See here
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.