Ability to specify decimal places

[phil-hall-moj]

Would be useful to be able to customise the number of visible decimal places but retain the full precision within the underlying figure in the cell.

At the moment, you either need to round in R which means losing precision, or export the full number with all decimal places which clutters the final table.

This is confirmed as being compatible with screen readers in the guidance: "Consider leaving the underlying figures unrounded. A screen reader user will be able to access both the rounded figure and the unrounded one."

[matt-dray]

Thanks @phil-hall-moj, this is something we've been thinking about in our own publications as well.

I'm yet to explore the full functionality of {openxlsx} and I'm hoping that this sort of control will be possible.

Related: #52 re decimal value displays.

[penniedfe]

Another vote for this. I'm looking into using this as part of a Shiny app with a download function so the option to manually apply formatting isn't available to me. I know this is possible with openxlsx but it would be great to see it implemented here.

[matt-dray]

Thanks for highlighting this.

You're right, you can do it with {openxlsx} using numFmt. For my own benefit I've prepared an example below; you may find it useful too.

Click for code to build a test Workbook object from an a11ytable object.

# This example is roughly the one provided in the {a11ytables} RStudio
# addin, under "Insert Full 'a11ytables' Template Workflow"

# Prepare tables

cover_df <- tibble::tribble(
  ~subsection_title, ~subsection_content,
  "Purpose", "Example results for something.",
  "Workbook properties", "Some placeholder information.",
  "Contact", "Placeholder email"
)

contents_df <- tibble::tribble(
  ~"Sheet name", ~"Sheet title",
  "Notes", "Notes",
  "Table_1", "Example sheet title"
)

notes_df <- tibble::tribble(
  ~"Note number", ~"Note text",
  "[note 1]", "Placeholder note.",
  "[note 2]", "Placeholder note."
)

table_df <- mtcars
table_df[["car [note 1]"]] <- row.names(mtcars)
row.names(table_df) <- NULL
table_df <- table_df[1:5, c("car [note 1]", "mpg", "cyl")]
table_df["longnum"] <- runif(n = nrow(table_df))  # new col, many decimal places
table_df["Notes"] <- c("[note 2]", rep(NA_character_, nrow(table_df) - 1))

# Create new a11ytable
my_a11ytable <- a11ytables::create_a11ytable(
  tab_titles = c("Cover", "Contents", "Notes", "Table_1"),
  sheet_types = c("cover", "contents", "notes", "tables"),
  sheet_titles = c(
    "Cover title (example)",
    "Contents",
    "Notes",
    "Example sheet title"
  ),
  blank_cells = c(rep(NA_character_, 3), "Blank cells mean there's no note."),
  sources = c(rep(NA_character_, 3), "Example source."),
  tables = list(cover_df, contents_df, notes_df, table_df)
)

# Generate workbook from a11ytable
my_wb <- a11ytables::generate_workbook(my_a11ytable)

And then we can pass numFmt as a style to the Workbook object:

openxlsx::addStyle(
  my_wb,
  4,
  style = openxlsx::createStyle(numFmt = "0.00"),
  rows = 7:11,
  cols = 4,
  gridExpand = TRUE
) |> 
openxlsx::openXL()  # open temporary example

You can see the result here:

Of course, the annoying downside is that you have to specify the table, rows and columns yourself. One of the main benefits of {a11ytables} is that it applies all other styles automatically under the hood. The package lacks finesse for style specifics like 'decimals places to show', however.

I think the general 'fix' for this might be to allow users to specify a limited set of styling options in a11ytables::generate_workbook() that could include the number of decimal points to show. I say 'limited' because {a11ytables} is intentionally designed to be simple and 'restrictive', with the understanding that users can always apply their own specific {openxlsx} styles later. But I think decimal places might be a good example of something that should be included as an option in {a11ytables} (but only in the sense that the cell can show fewer decimal places than are present in the underlying value; it will always be the user's responsibility to round their values if they only want to show and provide a certain number of decimal places).

[penniedfe]

Thanks Matt that's helpful and I certainly appreciate the current simplicity and need for a fix to limit any formatting to keep in line with accessibility guidelines.

For my own purposes it would be great to be able to adjust for the number decimal points visible, format percentages, and at a stretch dates. Because the output will be used by both analysts and policy I want to maintain the underlying data but make it user friendly for those that just want to use the spreadsheet as is. I'm sure that's not an uncommon set of circumstances.

[matt-dray]

Just realised: I never noted that you can do this with {openxlsx}'s options as well, like options("openxlsx.numFmt" = "0.00"). So 1.2345 would be displayed as 1.23, but the underlying value would remain 1.2345. And something like options("openxlsx.numFmt" = "#,0.00") would display 1234 as 1,234.

Of course, this will only be applied to numeric columns, which means it won't be applied to any columns containing a placeholder like [c] (because they're recognised as text columns). The scenario of having placeholders is common enough that there probably still needs to be some kind of solution for this within {a11ytables} itself.