The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.
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.These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.