Read .xlsx workbooks from R, password-protected ones included, with no
system XML, ZIP or cryptography library required.
zuxlsx bundles the xlsxio reader and
links the Expat parser, the
miniz ZIP reader and
TF-PSA-Crypto statically at
install time, out of the sibling packages
zuxml,
zukomp and
zucrypt. Those are LinkingTo
dependencies only: the archives end up inside zuxlsx.so, so none of the
three has to be installed or loadable once zuxlsx is built.
Writing workbooks is out of scope.
library(zuxlsx)
path <- system.file("extdata", "two-sheets.xlsx", package = "zuxlsx")
xlsx_sheets(path) # "readings" "notes"
xlsx_read(path) # first worksheet as a data frame
xlsx_read(path, sheet = "readings", range = "A1:B3")
xlsx_read() gives each column the type its cells support: logical, double,
character, Date or POSIXct. A column that cannot hold its cells becomes
character rather than failing. range takes A1 notation, and either corner
may name a cell, a column or a row, so "B2:D10", "A:C" and "2:10" are
all accepted.
Both Excel date systems are handled. The workbook's own date1904 setting
decides which is used, and the 1900 system's phantom 29 February 1900 is
accounted for, so dates before March 1900 are not a day early.
A password-protected workbook is read by passing its password, to any of the readers:
xlsx_read("protected.xlsx", password = "s3cret")
It is decrypted in memory and never written to disk. zuxlsx decrypts agile encryption, which Excel has written since 2013, and checks the integrity code stored with the file before decrypting anything. Office 2007's older standard encryption is recognised and refused by name.
Underneath, for worksheets that are not rectangular enough for a data frame:
xlsx_cells(path) # one row per cell: position, type, text, numeric value
xlsx_rows(path) # a worksheet row by row as text, padded to a rectangle
# A chunk at a time; returning FALSE stops the read, so finding something
# near the top of a large worksheet does not pay for the rest.
xlsx_stream_cells(path, callback = function(cells) { str(cells); FALSE })
zuxlsx needs zuxml, zukomp and zucrypt at build time, and its
configure script will stop with an explanatory message if their static
archives are missing. All three are on CRAN, so installing zuxlsx installs
them first:
install.packages("zuxlsx")
The development version is on GitHub:
# install.packages("pak")
# pak::pak("pedrobtz/zuxlsx")
install.packages("zuxlsx", repos = c('https://pedrobtz.r-universe.dev', 'https://cloud.r-project.org'))
Every error carries a condition class, so calling code can tell a corrupt archive from a missing file without matching on message text:
tryCatch(
xlsx_sheets("broken.xlsx"),
zuxlsx_zip_error = function(e) "not a readable ZIP archive",
zuxlsx_ooxml_error = function(e) "a ZIP, but not a workbook",
zuxlsx_error = function(e) "something else went wrong"
)
See ?"zuxlsx-conditions" for the full list.
The bundled xlsxio reader is MIT licensed and is never edited in place. It is
re-derived from the pristine 0.2.36 release archive plus the patches in
tools/patches/xlsxio/, and tools/vendor/verify checks offline that the
committed tree, the manifests and the attribution all still agree.