moj-analytical-services / xltabr Goto Github PK

Often figures will be redacted and replaced with a "*". This functionality might be useful as this is currently a very cumbersome task to do manually. Need to think how to generalise and only apply to body.

e,g, user should be able to provide their own df_orig$meta_formatting_ column (see here

Specifically, from the convenience functions - you currently can't pass in an existing tab, this should be an option

Simple fix to read.csv required

Following Hadley's guide

How to output formatted tables with multiple header rows?

e.g. if there are only two level of indent we may want to use header levels 2 and 4, for 3 indents, headers 1,3,5 etc.

When we use

reshape2::dcast and provide a formula with multiple categories in the top headers, dcast concatenates categories with _ into a single row. See here.

We want these to be able to occupy multiple rows in the excel spreadsheet

In practice, this probably means we need to provide a kind of xltabr::dcast type function, which wraps the reshape2 one, running reshape2::dcast under the hood, but returning a top_headers list which is compatible with the xltabr::add_top_headers function.

An alternative would be to provide a xltabr::get_top_headers_from_croostab type function, but I can't see a way of parsing out the list of top header rows unambiguously. The _ is not an unambiguous delimiter because it can occur in the columns.

A plausible algnorithm could be to replace _ in the cols of data in the data frame which is crosstabulated with e.g. --+--, dcaston these new columns, then split column headers on _, th

It's a bit unclear how we should deal with these since they're a property of a row rather than a cell. Note they're not a cell style, but instead are applied as https://rdrr.io/cran/openxlsx/man/setRowHeights.html

Compare the results of the following two chunks:
xltabr:::combine_all_styles(tab) gives the same result for all the body cells, but the styling that's actually applied to the workbook is different

# Test 1
ct <- reshape2::dcast(mtcars, am + gear ~ cyl, value.var= "mpg", margins=c("am", "gear"), fun.aggregate = mean)
headers <- colnames(ct)
tab <- xltabr::initialise()
tab <- xltabr::add_top_headers(tab, headers)
tab <- xltabr::add_body(tab, ct)
tab <- xltabr:::auto_detect_left_headers(tab)
tab <- xltabr:::auto_detect_body_title_level(tab)
tab <- xltabr:::auto_style_indent(tab)
tab <- xltabr::auto_style_number_formatting(tab)
tab <- xltabr:::write_all_elements_to_wb(tab)
tab <- xltabr:::add_styles_to_wb(tab)
xltabr:::combine_all_styles(tab)

if (open_output) openxlsx::openXL(tab$wb) else {
  if (file.exists("test1.xlsx")) file.remove("test1.xlsx")
  openxlsx::saveWorkbook(tab$wb, "test1.xlsx")
}

tab$body$body_df
# Test 2
ct <- reshape2::dcast(mtcars, am + gear ~ cyl, value.var= "mpg", margins=c("am", "gear"), fun.aggregate = mean)
headers <- colnames(ct)
tab <- xltabr::initialise()
tab <- xltabr::add_top_headers(tab, headers)
tab <- xltabr::add_body(tab, ct)
tab <- xltabr::add_title(tab, "Here is a title")
tab <- xltabr::add_footer(tab, "Here is a footer")
tab <- xltabr:::auto_detect_left_headers(tab)
tab <- xltabr:::auto_detect_body_title_level(tab)
tab <- xltabr:::auto_style_indent(tab)
tab <- xltabr::auto_style_number_formatting(tab)
tab <- xltabr:::write_all_elements_to_wb(tab)
tab <- xltabr:::add_styles_to_wb(tab)
xltabr:::combine_all_styles(tab)

if (open_output) openxlsx::openXL(tab$wb) else {
  if (file.exists("test2.xlsx")) file.remove("test2.xlsx")
  openxlsx::saveWorkbook(tab$wb, "test2.xlsx")
}

Do we need to incorporate any functionality that merges cells in multi-dimensional cross tabulations?

Code currently assumes all style names e.g. "left_header" are specified in the styles.xlsx and therefore will exist in style_catelogue. Need to add code to check this before calling add_styles_to_wb or any other style catelogue code.

Ideas:

Look at numbers in column - if max(num) > 1000, use a different format to eg. if the max is 1. Choose decimal places accordingly

Fails when

left_header_colname = ""

which is a common use case

Need to implement functionality that allows you to add additional style elements to a cell. At the moment, you can impose a new style, but this delete all previous styling

1. tab$cell_styles needs to be offset but row and column counter.
2. Each row, col combination needs to be grouped by style_key
3. apply addStyle for each style_key in cell_styles providing grouped row and col as inputs.

DO this in apply_default_styles which is currently being called in apply_styles_to_wb (with row and col calls commented out)

Think these should return non-factor string columns to stop any bugs in future. See #29 for more details.

Need to make sure we're happy with the core properties on tab, particularly so that the tab keeps track of e.g. its extent, how many header and footer rows it has etc, and we have a consistent way for keeping all of these fields up to date as we change things

I've updated styles_pub.xlsx to include some grey borders, which are represented in black when we render the xlsx

indent_0 in the styles.xslx is not read into the styleObjects. Currently investigating.

This is causing some issues with the r cmd check, e.g. the travis file - need to add all non essential files in github

Tracked bug down to here:

Whehn openxlsx::createStyle get passed numFmt =NULL it raises and error Error in if (numFmt == "date") { : argument is of length zero (the error seems to be unrelated to "date", it's just that when numFmt is NULL the if statement cannot evaluate.

To reproduce this error, use the following code:

library(magrittr)

headers <- names(mtcars)

tab <- xltabr::initialise() %>%
  xltabr::add_top_headers(headers) %>%
  xltabr::add_body(mtcars) %>% 
  xltabr::auto_style_number_formatting()

tab <- xltabr:::write_all_elements_to_wb(tab)

xltabr:::add_styles_to_wb(tab)

Note output from

xltabr:::top_headers_get_cell_styles_table(tab)
xltabr:::body_get_cell_styles_table(tab)

looks fine

The auto_open option opens Excel before applying the styles!!

Similar to #39

• We're currently using a deprecated sheet function
• Fixed a tiny bug in initialise

dplyr appears in a very small portion of the codebase. let's not use it at all

I've tracked down one of the bugs that was causing things to be flakey:

https://github.com/moj-analytical-services/xltabr/blob/dev/R/style_catalogue.R#L138

I think you probably just need to pass tab into this function (and therefore update everywhere where this function is called?)

Should test to see if text wrapping is applied in styles sheet. If so suggest default footer wrapper should be formatted as such.

You can give a style a 'cell indent' property

> so[[4]]$style
A custom cell style. 

 Cell formatting: GENERAL 
 Font name: Arial 
 Font size: 10 
 Cell horz. align: left 
 Cell indent: 2 
 Cell fill foreground:  theme: 0 
 Cell fill background:  indexed: 64 

https://stackoverflow.com/questions/27322110/define-excels-column-width-with-r

Try implementing the above

To reproduce, run this:
https://github.com/moj-analytical-services/xltabr/blob/dev/to_turn_into_unit_tests.R#L27

e.g. bold -> italic = italic

wanted outcome: bold -> italic = bold & italic

Does style$numFmt accept a custom number format?

• Make the convenience functions take a 'worksheet'
• Allow user to say 'write after this existing table'