Switch branches/tags
Find file History
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
demo_examples.R
example_1.png
example_2.png
example_3.png
example_4.png
example_5.png
example_6.png
example_7.png
readme.Rmd
to_turn_into_unit_tests.R
xltabr_from_bottom_up.Rmd

readme.Rmd

---
title: "Readme"
author: Robin Linacre
date: "`r format(Sys.time(), '%d %B, %Y')`"
output:
  md_document:
    variant: markdown_github
---

[![Coverage Status](https://img.shields.io/codecov/c/github/moj-analytical-services/xltabr/master.svg)](https://codecov.io/github/moj-analytical-services/xltabr?branch=master)
[![Build Status](https://travis-ci.org/moj-analytical-services/xltabr.svg?branch=dev)](https://travis-ci.org/moj-analytical-services/xltabr)
[![Cran Status](http://www.r-pkg.org/badges/version/xltabr)](https://cran.r-project.org/web/packages/xltabr/index.html) [![Cran Downloads](https://cranlogs.r-pkg.org/badges/xltabr)](https://www.r-pkg.org/pkg/xltabr)

**Warning: `xltabr` is in early development.  Please raise an [issue](https://github.com/moj-analytical-services/xltabr/issues) if you find any bugs**

## Introduction

`xltabr` allows you to write formatted cross tabulations to Excel using [`openxlsx`](https://github.com/awalker89/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.

```{r eval=FALSE, include=TRUE}
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)
```

![image](vignettes/example_1.png?raw=true)

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](https://github.com/moj-analytical-services/xltabr/blob/master/inst/extdata/synthetic_data.csv).


## Getting started

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:

```{r}
# Read in data 
path <- system.file("extdata", "synthetic_data.csv", package="xltabr")
df <- read.csv(path, stringsAsFactors = FALSE)
```

### Example 1:  Simple cross tabulation to Excel

```{r, include=TRUE}
# 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)
```

![image](vignettes/example_2.png?raw=true)

### Example 2:  Standard data frame to Excel

There is also a convenience function to  write a standard data.frame to Excel:

```{r, eval=FALSE, include=TRUE}
wb <- xltabr::auto_df_to_wb(mtcars)
openxlsx::openXL(wb)
```

![image](vignettes/example_3.png?raw=true)

### Example 3:  Add in titles and footers

```{r}
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)
```

![image](vignettes/example_1.png?raw=true)

### Example 4: Supply custom styles

```{r}
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)

```

![image](vignettes/example_5.png?raw=true)

### Example 5:  Output more than one table

```{r}
# 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)
```

![image](vignettes/example_4.png?raw=true)

### Example 6:  Output more than one table, with different styles

```{r}
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()
```

![image](vignettes/example_6.png?raw=true)

### Example 7:  Auoindent off

```{r}
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)
```

![image](vignettes/example_7.png?raw=true)

### auto_crosstab_to_wb options 

The following provides a list of all the options you can provide to `auto_crosstab_to_wb`

```{r include=FALSE}
suppressMessages(devtools::document())
tools::Rd2txt_options(underline_titles = FALSE, width = 300)
```

```{r echo=FALSE}
tools::Rd2txt("../man/auto_crosstab_to_wb.Rd")
```

## Advanced usage

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.

```{r, eval=FALSE, include=TRUE}

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](https://github.com/moj-analytical-services/xltabr/blob/dev/R/convenience.R). 


## Implementation diagrams.

See [here](https://www.draw.io/?lightbox=1&highlight=0000ff&edit=_blank&layers=1&nav=1&title=xltabr#Uhttps%3A%2F%2Fdrive.google.com%2Fa%2Fdigital.justice.gov.uk%2Fuc%3Fid%3D0BwYwuy7YhhdxY2hGQnVGNFN6QkE%26export%3Ddownload)