Skip to contents

Export Data to Excel with Label Support

Source: R/write_xlsx.R
write_xlsx.Rd

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.

Four 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. Analysis results: frequency() and crosstab() tables in their printed layout; every other result (e.g. describe(), t_test(), oneway_anova(), reliability(), linear_regression()) as its result tables.

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

Usage

write_xlsx(x, file, ...)

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

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

Arguments

x

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

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.

Value

Invisibly returns the file path.

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

  • Want to hand analysis results (descriptives, crosstabs, test results) to colleagues as Excel tables

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.

  • write_xlsx(default): Export any other mariposa analysis result (e.g. describe(), crosstab(), t_test(), reliability(), linear_regression()) to one sheet. crosstab() results are written in the SPSS table layout (counts and the requested percentages per row category, with totals); all other results as the table as.data.frame() returns, followed by the secondary tables SPSS shows next to it (group descriptives, mean ranks, item statistics, model summary, ...). Grouped results keep the grouping variables as leading columns (crosstabs get one block per group).

See also

codebook() for generating codebook objects, frequency() for frequency tables, as.data.frame() for analysis results as data frames, 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))
}
# }

if (requireNamespace("openxlsx2", quietly = TRUE)) {
  # Analysis results: one sheet each, or several in one workbook
  tmp_res <- tempfile(fileext = ".xlsx")
  t_test(survey_data, age, income, group = gender) |> write_xlsx(tmp_res)

  tmp_list <- tempfile(fileext = ".xlsx")
  write_xlsx(
    list(
      "Descriptives" = describe(survey_data, age, income),
      "Crosstab"     = crosstab(survey_data, gender, region),
      "ANOVA"        = oneway_anova(survey_data, age, group = education)
    ),
    tmp_list
  )

  unlink(c(tmp_res, tmp_list))
}