write_xlsx() turns a tsg table, or a list of tables,
into a formatted Excel workbook. What the workbook looks like depends on
the structure of the table you generated: a single table, a crosstab
with merged headers, a list of tables, or a grouped list. This vignette
goes through each structure and shows the result.
The screenshots are of the sheets as Microsoft Excel prints them.
write_xlsx(
data, # a tsg table, or a (nested) list of them
path, # a file, or a folder when separate_files = TRUE
title, subtitle, # used when a table has none of its own
source_note, footnotes,
sheet_name, # the sheet of a single table
separate_files, # one workbook per list item
collapse_list, # stack a list of tables on one sheet
row_group_as_column, # show group names as a column instead of titles
include_table_list, # add a "List of Tables" sheet
table_list_reference, # titles, numbers and notes kept in a data frame or file
facade # the look: fonts, colours, borders
)| You pass | You get |
|---|---|
| One table | One sheet |
| A list of tables | One sheet per table |
A list with collapse_list = TRUE |
All tables stacked on one sheet |
A list with separate_files = TRUE |
One workbook per table, in a folder |
group_as_list = TRUE with one group |
One sheet per group |
group_as_list = TRUE with two or more groups |
One sheet per first group, with the other groups as titles on that sheet |
Any of these with include_table_list = TRUE |
The same, plus a “List of Tables” sheet with links |
A frequency table is the simplest case: a header row, one row per category and a Total row. Titles and notes go above and below the table.
frequency <- person_record |>
generate_frequency(marital_status) |>
add_table_title("Table 1. Marital status of household members") |>
add_table_subtitle("Both sexes, all ages") |>
add_source_note("Source: person_record sample data") |>
add_footnote("Percentages are calculated from all members, including those not reported.")
write_xlsx(frequency, "frequency.xlsx")The title, subtitle, source note and footnotes live on the table
itself, so they travel with it. You can also pass title,
subtitle, source_note and
footnotes straight to write_xlsx(). A table’s
own text always wins over the arguments.
sheet_name names the sheet (the default is
"Sheet1"):
A crosstab has a two-level header: the variable (here
Frequency and Percent) sits above the
categories of the column variable. write_xlsx() writes this
as merged header cells. The stub header (Marital status)
and Total are merged downwards so they span both header
rows.
crosstab <- person_record |>
generate_crosstab(marital_status, sex) |>
add_table_title("Table 2. Marital status by sex") |>
add_source_note("Source: person_record sample data")
write_xlsx(crosstab, "crosstab.xlsx")Header levels come from the column names, split at
names_separator ("__" by default). If you
build a table with your own column names, "Frequency__Male"
becomes a Frequency header over a Male
header.
With collapse_list = TRUE,
generate_crosstab() puts the crosstabs against several
column variables next to each other in one table. The header then has
three levels: the variable, the statistic and the category.
side_by_side <- person_record |>
generate_crosstab(marital_status, sex, employed, collapse_list = TRUE)
write_xlsx(side_by_side, "crosstab-multi.xlsx")generate_summary() gives one row per numeric variable
and one column per statistic.
summary_table <- person_record |>
generate_summary(age, stats = c("n", "mean", "sd", "median", "min", "max")) |>
add_table_title("Table 3. Age of household members")
write_xlsx(summary_table, "summary.xlsx")Give write_xlsx() a named list and every table gets its
own sheet, named after the list item.
tables <- list(
Sex = generate_frequency(person_record, sex),
Marital = generate_frequency(person_record, marital_status),
Employment = generate_crosstab(person_record, employed, sex)
)
write_xlsx(tables, "tables.xlsx")
openxlsx::getSheetNames("tables.xlsx")
#> [1] "Sex" "Marital" "Employment"Calling generate_frequency() with several variables
returns such a list, named by the variable labels, so you can write it
directly:
several <- generate_frequency(person_record, sex, employed, seeing)
names(several)
#> [1] "Sex" "Employment status"
#> [3] "Seeing, even if wearing glasses"Sheet names follow Excel’s rules: no / (written as ” or
“) and at most 31 characters. A longer name is an error, so you notice
before a label is cut in the middle of a word. Set
trim_sheet_names = TRUE to cut long names instead; names
that then clash are numbered (2), (3). Unnamed
list items become Table 1, Table 2, and so
on.
long_names <- generate_frequency(person_record, hearing, walking)
names(long_names)
#> [1] "Hearing, even if using hearing aid"
#> [2] "Walking or climbing steps, even if with cane or artificial leg"
write_xlsx(long_names, "long-names.xlsx", trim_sheet_names = TRUE)
openxlsx::getSheetNames("long-names.xlsx")
#> [1] "Hearing, even if using hearing" "Walking or climbing steps, even"When you pass title to a list, each sheet gets
"<title>: <sheet name>" unless the table
already has a title of its own:
write_xlsx(tables, "tables-titled.xlsx", title = "Household survey", source_note = "Source: person_record")Use collapse_list = TRUE to stack the tables on a single
sheet, each under its own heading. This is handy for a quick overview or
for pasting into a document.
Group the data with dplyr::group_by() and
generate_*() can return one table per group. How
write_xlsx() lays them out depends on how many groups there
are.
by_sex <- person_record |>
group_by(sex) |>
generate_frequency(marital_status, group_as_list = TRUE)
names(by_sex)
#> [1] "Male" "Female"
write_xlsx(by_sex, "group-list.xlsx")
openxlsx::getSheetNames("group-list.xlsx")
#> [1] "Male" "Female"Without group_as_list, the group is a column and the
groups sit under each other in a single table. With
group_as_hierarchy = TRUE there is also an All
block for the whole data.
hierarchy <- person_record |>
group_by(sex) |>
generate_frequency(marital_status, group_as_hierarchy = TRUE)
write_xlsx(hierarchy, "hierarchy.xlsx")With two groups, the first group gets a sheet each, and the second
group becomes a title above its own block of rows. A third group would
add a second level of titles, named by their path, such as
"Yes - No difficulty".
nested <- person_record |>
group_by(sex, employed) |>
generate_frequency(marital_status, group_as_list = TRUE)
write_xlsx(nested, "nested.xlsx")
openxlsx::getSheetNames("nested.xlsx")
#> [1] "Male" "Female"To keep the groups together, write a one-group list with
collapse_list = TRUE. The groups then share one header, and
each group gets a title row above its rows.
A title row is awkward if you want to filter or sort the sheet in
Excel. With row_group_as_column = TRUE the group names
become the first column instead.
It works the same on the sheets of a list with two or more groups: each sheet gets one column per group.
Set include_table_list = TRUE to add a first sheet,
List of Tables, with one row per table. The tab names are
links to the sheets, and each table sheet gets a link back.
write_xlsx(tables, "indexed.xlsx", include_table_list = TRUE)
openxlsx::getSheetNames("indexed.xlsx")
#> [1] "List of Tables" "Sex" "Marital" "Employment"Without a reference, the table numbers follow the list order, and each title is the table’s own title, or its list name when it has none.
In a long report, titles, table numbers and notes are easier to
manage in one place than spread through the code. Give
table_list_reference a data frame (or the path to a
.csv, .json or .xlsx file) with
one row per table.
| Column | Meaning |
|---|---|
table_id |
The name of the list item. This is how a row finds its table. |
table_name |
The sheet name, also shown in the index. |
table_number |
The number in the index and in the title (1 becomes
“Table 1.”). |
title |
The title above the table. |
subtitle, source_note,
footnotes |
Optional. They replace the table’s own text. |
reference <- data.frame(
table_id = c("Sex", "Marital", "Employment"),
table_name = c("By sex", "By marital status", "Employment by sex"),
table_number = 1:3,
title = c(
"Household members by sex",
"Household members by marital status",
"Employment status by sex"
),
subtitle = c(NA, NA, "Persons with a reported status"),
source_note = "Source: person_record sample data",
footnotes = NA
)
write_xlsx(
tables,
"reference.xlsx",
include_table_list = TRUE,
table_list_reference = reference
)
openxlsx::getSheetNames("reference.xlsx")
#> [1] "List of Tables" "By sex" "By marital status"
#> [4] "Employment by sex"The index lists the tables by their table_name and
number:
Each table sheet is named by table_name. The title is
prefixed with the table number, and the subtitle and source note come
from the reference:
The reference can be a file, so someone who does not write R can edit
titles and numbers. generate_template() writes an empty one
with the right columns:
write.csv(reference, "table-list.csv", row.names = FALSE)
write_xlsx(
tables,
"reference-file.xlsx",
include_table_list = TRUE,
table_list_reference = "table-list.csv"
)A few rules worth knowing:
table_id is in the reference are
looked up. A table with no row keeps its own title and its list name as
the sheet name.table_id matches no list item is
ignored.table_id must match the list names exactly, so name
your list items.table_list_reference only has an effect together with
include_table_list = TRUE.The look of a workbook is a facade: fonts, fills, borders and column widths. Pass a built-in one by name:
crosstab_plain <- person_record |>
generate_crosstab(marital_status, sex) |>
add_table_title("Marital status by sex")
write_xlsx(crosstab_plain, "booktabs.xlsx", facade = "booktabs")
write_xlsx(crosstab_plain, "financial.xlsx", facade = "financial")
write_xlsx(crosstab_plain, "dark.xlsx", facade = "dark")"booktabs": a thick rule on top and bottom, a thin one
under the header.
"financial": a dark header, a thin grid and a bold last
row.
"dark": light text on a dark background.
To change one table only, use add_facade(). It takes
precedence over the facade argument of
write_xlsx():
crosstab_plain |>
add_facade(header.fgFill = "#FFF2CC", table.offsetRow = 1, table.offsetCol = 1) |>
write_xlsx("offset.xlsx")The same facades style HTML, PDF and Word output. All the facade
settings and the full list of built-in styles are in
vignette("facade").
Extra arguments in ... go to
openxlsx::createWorkbook() (such as creator)
or openxlsx::addWorksheet() (such as zoom),
matched by name:
vignette("facade") for styles and every facade
setting.vignette("output-formats") for HTML, PDF and Word.vignette("google-sheets") to send the same workbook to
Google Sheets.