Example of an {a11ytables} xlsx output that has two tables

two-table-a11ytables.R

# Example of an {a11ytables} xlsx output that has two tables
# 2023-04-25
# {a11ytables} v0.1.0

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

# Prepare contents table
contents_df <- tibble::tribble(
  ~"Sheet name", ~"Sheet title",
  "Notes", "Notes",
  "Table_1", "Example sheet title 1",
  "Table_2", "Example sheet title 2"
)

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

# Prepare demo tables
table_df <- mtcars
table_df[["car [note 1]"]] <- row.names(mtcars)
row.names(table_df) <- NULL
table_1_df <- head(table_df)  # table 1
table_2_df <- tail(table_df)  # table 2

# Create new a11ytable
my_a11ytable <-
  a11ytables::create_a11ytable(
    tab_titles = c(
      "Cover",
      "Contents",
      "Notes",
      "Table_1",
      "Table_2"
    ),
    sheet_types = c(
      "cover",
      "contents",
      "notes",
      "tables",
      "tables"
    ),
    sheet_titles = c(
      "Cover title (example)",
      "Contents",
      "Notes",
      "Example sheet title 1",
      "Example sheet title 2"
    ),
    blank_cells = c(
      NA_character_,
      NA_character_,
      NA_character_,
      NA_character_,
      NA_character_
    ),
    sources = c(
      NA_character_,
      NA_character_,
      NA_character_,
      "Example source.",
      "Example source."
    ),
    tables = list(
      cover_df,
      contents_df,
      notes_df,
      table_1_df,
      table_2_df
    )
  )

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

# Save the output as xlsx
openxlsx::saveWorkbook(
  my_wb,
  "~/Desktop/example.xlsx"  # change this
)

Morning, I wanted to create an accessible table with three tables and got this error:

Error in data.frame(tab_title = unlist(tab_titles), sheet_type = unlist(sheet_types), :
arguments imply differing numb
er of rows: 6, 5

If I add 5 tables I got the same error message with rows 8,7
However, when I add only two tables am able to create the workbook. Adding a third table throws an error. what could be causing this?

Hi @khonje-ja, I think this is because you must pass six elements to each argument of create_a11ytable() (because your workbook has six sheets). In other words, you've only passed five elements to the blank_cells argument, but there should be six.

I don't have your data, but I copied the code from your screenshot and have updated it to something that should work:

Collisiondata <-
  a11ytables::create_a11ytable(
    tab_titles = c(
      "Cover",
      "Contents",
      "Notes",
      "2018",
      "2019",
      "2020"
    ),
    sheet_types = c(
      "cover",
      "contents",
      "notes",
      "tables",
      "tables",
      "tables"
    ),
    sheet_titles = c(
      "Cover title (example)",
      "Contents",
      "Notes",
      "Collision level data for year 2019",
      "Collision level data for year 2019",
      "Collision level data for year 2020"  # assumed you meant 2020 here?
    ),
    blank_cells = c(
      NA_character_,
      NA_character_,
      NA_character_,
      NA_character_,
      NA_character_,
      NA_character_  # <-- this is missing in your example
    ),
    sources = c(
      NA_character_,
      NA_character_,
      NA_character_,
      "DfT",
      "DfT",
      "DfT"  # assumed you meant 'DfT' here?
    ),
    tables = list(
      cover_df,
      contents_df,
      notes_df,
      data_2018,
      data_2019,
      data_2020
    )  # I think these closing brackets were cut off in your screenshots
  ) 

Note that you can also use the rep() function to supply arguments. This makes it easier to express the arguments. See here how we can write rep(NA_character_, 6) for the blank_cells argument instead of writing NA_character six times:

Collisiondata <-
  a11ytables::create_a11ytable(
    tab_titles = c("Cover", "Contents", "Notes", 2018:2020),
    sheet_types = c("cover", "contents", "notes", rep("tables", 3)),
    sheet_titles = c(
      "Cover title (example)",
      "Contents",
      "Notes",
      paste("Collision level data for year ", 2018:2020)
    ),
    blank_cells = c(rep(NA_character_, 6)),
    sources = c(rep(NA_character_, 3), rep("DfT", 3)),
    tables = list(
      cover_df,
      contents_df,
      notes_df,
      data_2018,
      data_2019,
      data_2020
    ) 
  )

I added a few more 'shortcuts' like paste("Collision level data for year ", 2018:2020) for the 'sheet_titles' argument just to show that you can make more of the overall code more concise if you want to.

Let me know if this doesn't fix the issue. 👍

Thanks, Matt, yes, I was able to tweak the code as per your instructions and managed to add more tables. Am playing around with the second method, looks cool.