content column (#80)xlex() (#57).include_blank_cells = FALSE had a bug that
returned blank cells as an empty row in the xlsx_cells() data frame.-F10 (#26 @cablegui)._ as dates when
the underscore is followed by a date-ish character like M (#24).include_blank_cells = FALSE in
xlsx_cells() (#25).maybe_xlsx() is
provided for checking whether a file might be in the xlsx format. It is
impossible to be sure from the magic number alone, because the magic numbers
are either common to all zip files, or common to other Microsoft Office files
(e.g. .doc, .ppt).xlsx_cells() and xlsx_formats() replace tidy_xlsx(), which has been
deprecated. xlsx_cells() returns a single data frame of all the cells in
scope (the whole workbook, or chosen sheets), rather than a list of separate
data frames for each sheet. xlsx_formats() performs orders of magnitude
faster.xlsx_validation() imports validation rules from cells that restrict data
input, such as cells that require a selection from a drop-down list. See
the
vignette
vignette("data-validation-rules", package = "tidyxl").xlsx_names() imports defined names (aka named ranges/formulas), which can
be used to filter for particular ranges of cells by name. Use is_range()
to filter for ones that are named ranges, and then read
joining rules to cells
for how to join cell ranges to cell addresses. This will become easier in a
future release.is_range() checks whether a formula is simply ranges of cells.xlex() tokenises formulas. This is useful for detecting
spreadsheet smells like embedded constants and deep nesting. There is a
demo Shiny app, and a
vignette
vignette("smells", package = "tidyxl"). A vector of Excel function names
excel_functions can be used to separated built-in functions from custom
functions. More experimental features will be implemented in the off-CRAN
package lexl before becoming part of
tidyxl.xlsx_cells()$character_formatted is a new column for the in-cell formatting
of text (#5). This is for when different parts of text in a single cell
have been formatted differently from one another.is_date_format() checks whether a number format string is a date format.
This is useful if a cell formula contains a number formatting string (e.g.
TEXT(45678,"yyyy")), and you need to know that the constant 45678 is a
date in order to recover it at full resolution (rather than parsing the
character output "2025" as a year).xlsx_color_theme() and it's British alias xlsx_colour_theme() returns the
theme colour palette used in a file. This is useful to monitor use of a
corporate standard theme.xlsx_color_standard and it's British alias xlsx_colour_standard are data
frames of the standard Excel palette (red, blue, etc.).=A1*2 in cell B1 becomes =A2*2 in cell B2 (for more details see
issue #7).tidy_xlsx() has been deprecated in favour of xlsx_cells(),
which returns a data frame of all the cells in the workbook (or in the
requested sheets), and xlsx_formats(), which returns a lookup list of cell
formats.tidy_xlsx() and one of it's replacments xlsx_cells()
content has been replaced by is_blank, a logical value
indicating whether the cell contains data. Please replace !is.na(content)
with !is_blank to filter out blank cells (ones with formatting but no
value).formula_type has been replaced by is_array, a logical value
indicating whether the cell's formula is an array formula or not. In Excel
array formulas are represented visually by being surrounded by curly braces
{}.xlsx_formats() and tidy_xlsx(), theme colours are given by name rather
than by number, e.g. "accent6" instead of 4."[Cyan]0%")
are no longer mis-detect as dates (#21). is_date_format() tests whether a
number format is a date format.xlsx_formats() is now thoroughly tested, and several relatively minor bugs
fixed. For example, xlsx_formats(path)$local$fill$patternFill$patternType
consistently returns NA and never "none" when a pattern fill has not
been set, and escape-backslashes are consistently omitted from numFmts.xlex(), is_range() and the handling of relative references in shared
formulas requires a dependency on the
piton package, which wraps the
PEGTL C++ parser generator.
date1904 attribute for "false" or "1" to support files
created by the openxlsx package (#8)."Normal") instead of by index integer. All the vectors under
x$formats$style are named according to the style names.
x$data$sheet$style_format_id has been renamed to x$data$sheet$style_format
and its type changed from integer (index into style formats) to character
(still an index, but looking up the named vectors by name). There are
examples in the README and vignette.NEWS.md file to track changes to the package.