mariposa (version 0.7.3)

write_xlsx: Export Data to Excel with Label Support

Description

Writes data to an Excel (.xlsx) file with support for variable labels, value labels, and tagged NA metadata. When exporting labelled survey data, a "Labels" reference sheet is automatically included so that reviewers can look up what each numeric code means -- even without R access.

Three input types are supported:

1. Data frame: Exports the data plus a "Labels" reference sheet with variable labels, value labels, and missing value codes.

2. Codebook object: Exports the codebook as it appears in the HTML viewer -- with values, value labels, and frequencies stacked inside each cell, tagged NAs separated by a line, and an overview sheet with dataset metadata.

3. Named list: Each list element becomes a separate sheet. Elements can be data frames, frequency() results, or codebook() results -- types can be mixed freely. For data frame elements, a combined "Labels" sheet is appended.

Usage

write_xlsx(x, file, ...)
# S3 method for frequency
write_xlsx(x, file, overwrite = TRUE, ...)

Value

Invisibly returns the file path.

Arguments

x

Object to export: a data frame, a codebook() result, or a named list of data frames.

file

Path to the output .xlsx file. Must end in .xlsx.

...

Additional arguments passed to methods:

labels

(data.frame and list methods) Include a "Labels" reference sheet? Default: TRUE

frequencies

(codebook method) Include per-variable frequency sheets? Default: FALSE

overwrite

Overwrite existing file? Default: TRUE.

Methods (by class)

• write_xlsx(frequency): Export frequency tables to Excel

  Exports one or more frequency tables to a single Excel sheet, mirroring the console print output. Each variable gets a header row, stats summary, column headers, data rows, and total rows. Multiple variables are separated by 3 blank rows.

Details

Data Frame Export

The "Labels" sheet provides a complete lookup table for all variable and value labels in your data, structured in long format. The "Type" column distinguishes between regular value labels ("valid") and tagged missing value labels ("missing"), making it easy to filter in Excel.

Data values are written as their underlying numeric or character codes (not as label text), preserving the original coding scheme.

Codebook Export

The codebook export reproduces the HTML codebook layout: each variable occupies one row, with values, value labels, and frequencies stacked within their cells using line breaks. Tagged NAs are shown below a separator line, matching the visual style of codebook() in the RStudio Viewer.

When to Use This

Use write_xlsx() when you:

• Need to share survey data with non-R users who need label context

• Want to export a codebook for manual review or documentation

• Need to combine multiple tables (data, codebook, frequencies) in one Excel file

See Also

codebook() for generating codebook objects, frequency() for frequency tables, read_spss(), read_por(), read_stata(), read_sas(), read_xpt(), read_xlsx() for importing labelled data

Other data-export: write_spss(), write_stata(), write_xpt()

Examples

# \donttest{
if (requireNamespace("openxlsx2", quietly = TRUE)) {
  # Export data with automatic label reference sheet
  tmp <- tempfile(fileext = ".xlsx")
  write_xlsx(survey_data, tmp)

  # Export a codebook (reproduces HTML layout in Excel)
  tmp_cb <- tempfile(fileext = ".xlsx")
  codebook(survey_data) |> write_xlsx(tmp_cb)

  # Export frequency tables
  tmp_freq <- tempfile(fileext = ".xlsx")
  frequency(survey_data, gender) |> write_xlsx(tmp_freq)

  # Multi-sheet export (mixed types: data frames, frequencies, codebooks)
  tmp_multi <- tempfile(fileext = ".xlsx")
  write_xlsx(
    list(
      "Frequencies" = frequency(survey_data, gender, life_satisfaction),
      "Codebook"    = codebook(survey_data),
      "Data"        = survey_data
    ),
    tmp_multi
  )

  unlink(c(tmp, tmp_cb, tmp_freq, tmp_multi))
}
# }

# \donttest{
if (requireNamespace("openxlsx2", quietly = TRUE)) {
  # Single variable
  tmp <- tempfile(fileext = ".xlsx")
  frequency(survey_data, gender) |> write_xlsx(tmp)

  # Multiple variables on one sheet
  tmp2 <- tempfile(fileext = ".xlsx")
  frequency(survey_data, gender, life_satisfaction, region) |>
    write_xlsx(tmp2)

  unlink(c(tmp, tmp2))
}
# }