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.

Writing Excel workbooks with write_xlsx()

library(tsg)
library(dplyr)

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.

The big picture

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

One table

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")
Excel sheet: frequency

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"):

write_xlsx(frequency, "frequency-named.xlsx", sheet_name = "Marital status")
openxlsx::getSheetNames("frequency-named.xlsx")
#> [1] "Marital status"

A crosstab with merged headers

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")
Excel sheet: crosstab

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.

Several column variables side by side

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")
Excel sheet: crosstab-multi

A summary table

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")
Excel sheet: summary

A list of tables: one sheet each

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")
Excel sheet: list-sheet

All tables on one sheet

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.

write_xlsx(several, "stacked.xlsx", collapse_list = TRUE)
Excel sheet: stacked

One workbook per table

With separate_files = TRUE, path is a folder and each list item becomes a workbook named after it.

write_xlsx(tables, "separate", separate_files = TRUE)
list.files("separate")
#> [1] "Employment.xlsx" "Marital.xlsx"    "Sex.xlsx"

Grouped tables

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.

One group: one sheet per group

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"
Excel sheet: group-list

Groups in one table

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")
Excel sheet: hierarchy

Two or more groups: titles inside the sheet

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"
Excel sheet: nested

All groups on one sheet

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.

write_xlsx(by_sex, "group-one-sheet.xlsx", collapse_list = TRUE)
Excel sheet: group-one-sheet

Group names as a column

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.

write_xlsx(by_sex, "group-column.xlsx", collapse_list = TRUE, row_group_as_column = TRUE)
Excel sheet: group-column

It works the same on the sheets of a list with two or more groups: each sheet gets one column per group.

write_xlsx(nested, "nested-column.xlsx", row_group_as_column = TRUE)
Excel sheet: nested-column

Changing the look

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.

Excel sheet: facade-booktabs

"financial": a dark header, a thin grid and a bold last row.

Excel sheet: facade-financial

"dark": light text on a dark background.

Excel sheet: facade-dark

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")
Excel sheet: facade-offset

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").

Passing options to openxlsx

Extra arguments in ... go to openxlsx::createWorkbook() (such as creator) or openxlsx::addWorksheet() (such as zoom), matched by name:

write_xlsx(frequency, "options.xlsx", creator = "Survey Unit", zoom = 120)

Where to go next

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.