Skip to contents

Import named formulas from xlsx (Excel) files

Source: R/xlsx_names.R
xlsx_names.Rd

xlsx_names() returns the names and definitions of named formulas (aka named ranges) in xlsx (Excel) files.

Most names refer to ranges of cells, but they can also be defined as formulas. xlsx_names() tells you whether or not they are a range, using is_range() to work this out.

Names are scoped either globally (used only once in the file), or locally to each sheet (can be reused with different definitions in different sheets). For sheet-scoped names, xlsx_names() provides the name of the sheet.

Usage

xlsx_names(path, check_filetype = TRUE)

Arguments

path

Path to the xlsx file.

check_filetype

Logical. Whether to check that the filetype is xlsx (or xlsm) by looking at the file itself, rather than using the filename extension.

Value

A data frame, one row per name, with the following columns.

  • sheet If the name is defined only for a specific sheet, the name of the sheet. Otherwise NA for names defined globally.

  • name

  • formula Usually a range of cells, but sometimes a whole formula, e.g. MAX(A2,1).

  • comment A description given by the spreadsheet author.

  • hidden Whether or not the name is visible to the user in spreadsheet applications. Hidden names are usually ones that were created automatically by the spreadsheet application.

  • is_range Whether or not the formula is a range of cells. This is handy for joining to the set of cells referred to by a name. In this context, commas between cell addresses are always regarded as union operators -- this differs from xlex(), see that help file for details.

Examples

examples <- system.file("extdata/examples.xlsx", package = "tidyxl")
xlsx_names(examples)
#>   rId    sheet                 name                     formula    comment
#> 1   1   Sheet1  named_local_formula MAX(Sheet1!$A$129:$A$130)+1       <NA>
#> 2   4 E09904.2   sheet_beyond_chart E09904.2!$A$1,E09904.2!$C$1       <NA>
#> 3  NA     <NA>         intersection   Sheet1!$B:$B Sheet1!$8:$8       <NA>
#> 4  NA     <NA> named_global_formula             Sheet1!$A$129-1       <NA>
#> 5  NA     <NA>          named_range               Sheet1!$A$129 My comment
#>   hidden is_range
#> 1  FALSE    FALSE
#> 2  FALSE     TRUE
#> 3  FALSE     TRUE
#> 4  FALSE    FALSE
#> 5  FALSE     TRUE