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
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.
xlsx_names(path, check_filetype = TRUE)
Path to the xlsx file.
Logical. Whether to check that the filetype is xlsx (or xlsm) by looking at the file itself, rather than using the filename extension.
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.
formula Usually a range of cells, but sometimes a whole formula, e.g.
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 <- 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