export_xlsx: Export Data to XLSX Files

In mintyr: Grouped and Nested Data Pipelines Built on 'data.table'

Export Data to XLSX Files

Description

The natural complement to import_xlsx(). Accepts either a combined data.frame (as produced by import_xlsx() with combine = TRUE) or a list of data.frames, and writes the result to disk.

The output destination is controlled by a single path argument – there are no separate modes to choose:

• path ends in .xlsx – write everything into a single workbook: one sheet per list element, or (for data.frame input) one sheet per file_col / sheet_col value.

• path is anything else – treat it as a directory and write one .xlsx file per list element or per file_col value. For data.frame input that also has a sheet_col column, each file contains one sheet per sheet_col value, which reproduces the original file/sheet layout read by import_xlsx().

List input

res <- list(res1 = data1, res2 = data2, res3 = data3)

  # Directory mode -- writes res1.xlsx, res2.xlsx, res3.xlsx
  export_xlsx(res, path = "output/")

  # Single-file mode -- one workbook with sheets res1, res2, res3
  export_xlsx(res, path = "output/all.xlsx")

Each element must be a data.frame, data.table, or tibble; types may be mixed and column sets may differ. Missing names are filled in as Sheet1, Sheet2, ...; supplied names must be unique.

Usage

export_xlsx(
  data,
  path,
  file_col = "excel_name",
  sheet_col = "sheet_name",
  sheet_name = "Sheet1",
  drop_cols = TRUE,
  overwrite = TRUE,
  verbose = FALSE
)

Arguments

data	A data.frame / data.table / tibble, or a list of such objects. For list input, names become file names (directory mode) or sheet names (single-file mode).
path	character(1). Output destination. A path ending in .xlsx (case-insensitive) is a single workbook; anything else is an output directory. Missing directories are created recursively.
file_col	character(1). Column identifying the source file (data.frame input only). Default "excel_name".
sheet_col	character(1). Column identifying the source sheet (data.frame input only). Default "sheet_name".
sheet_name	character(1). Sheet name used when a data.frame has neither tracking column. Default "Sheet1".
drop_cols	logical(1). If TRUE (default), the tracking columns are removed from the exported sheets (data.frame input only).
overwrite	logical(1). Allow overwriting existing files. When FALSE, all targets are checked before anything is written, so a failure never leaves a partial export behind. Default TRUE.
verbose	logical(1). Print a message for every file and sheet written. Default FALSE.

Details

Why writexl?

writexl writes .xlsx via a minimal C library with no Java or Perl dependency. It is fast and produces small files, at the cost of no cell formatting, formulas, or styles. For those, use openxlsx2.

Name sanitisation

Sheet names are limited to 31 characters, may not contain [ ] * ? / \ :, and may not start or end with an apostrophe. File names have characters that are invalid on Windows replaced by _. If sanitising or truncating makes two names collide (Excel compares sheet names case-insensitively), suffixes such as _2, _3 are appended.

Missing group values

Rows with NA in file_col or sheet_col are not dropped: they are exported under the label "NA" and a warning is issued.

Directory vs. file dispatch

path is classified purely by its extension. To use a directory whose name ends in .xlsx, append a trailing slash.

Value

Invisibly, a named character vector of written file paths. In directory mode it is named by list element / file_col value; in single-workbook mode it is named by path.

Examples

# Example 1: A plain data.frame -> one workbook, one sheet
out_file <- file.path(tempdir(), "mtcars.xlsx")
export_xlsx(mtcars, path = out_file, sheet_name = "mtcars")
invisible(file.remove(out_file))

# Example 2: data.table input works exactly the same way
out_file <- file.path(tempdir(), "mtcars_dt.xlsx")
export_xlsx(data.table::as.data.table(mtcars),
            path = out_file, sheet_name = "mtcars")
invisible(file.remove(out_file))

# Example 3: One sheet per group in a single workbook
# Each Species value becomes a sheet; keep the Species column
out_file <- file.path(tempdir(), "iris_by_species.xlsx")
export_xlsx(iris, path = out_file, file_col = "Species", drop_cols = FALSE)
invisible(file.remove(out_file))

# Example 4: One file per group (directory mode: no .xlsx extension)
out_dir <- file.path(tempdir(), "iris_by_species")
out_files <- export_xlsx(iris, path = out_dir, file_col = "Species")
basename(out_files)
unlink(out_dir, recursive = TRUE)

# Example 5: Round-trip the layout produced by import_xlsx(combine = TRUE)
# Rows are routed back to their original file and sheet
combined <- data.frame(
  excel_name = c("sales", "sales", "costs"),
  sheet_name = c("2024",  "2025",  "2024"),
  amount     = c(100, 120, 80)
)
out_dir <- file.path(tempdir(), "roundtrip")
out_files <- export_xlsx(combined, path = out_dir)   # sales.xlsx, costs.xlsx
basename(out_files)
unlink(out_dir, recursive = TRUE)

# Example 6: Named list -> single workbook, one sheet per element
out_file <- file.path(tempdir(), "combined.xlsx")
res <- list(res1 = iris, res2 = mtcars)
export_xlsx(res, path = out_file)
invisible(file.remove(out_file))

# Example 7: Named list -> directory, one file per element
out_dir <- file.path(tempdir(), "combined")
out_files <- export_xlsx(res, path = out_dir)        # res1.xlsx, res2.xlsx
basename(out_files)
unlink(out_dir, recursive = TRUE)

mintyr documentation built on Oct. 5, 2026, 5:08 p.m.