Read an .xlsx sheet and undo its date auto-conversion

Reads a worksheet and restores the values a spreadsheet turned into dates, using only what the file states about itself: the date system from xl/workbook.xml, and each cell's number format from xl/styles.xml. A cell is restored when the workbook says it is formatted as a date – not when its magnitude happens to fall in a plausible range – so no threshold, year window or reference date is involved, and an ordinary number that happens to look like a serial is never touched.

Usage

unexcel_xlsx(
  path,
  sheet = 1,
  col_names = TRUE,
  restore = TRUE,
  order = c("format", "dm", "md"),
  output = c("numeric", "character"),
  na = ""
)

Arguments

path

Path to an .xlsx file.

sheet

Sheet name, or its index in the workbook. Defaults to the first sheet.

col_names

If TRUE (default), the first row of the sheet holds column names, as it would for utils::read.csv() or readxl::read_excel().

restore

If TRUE (default), date-formatted cells come back as day.month numerics. If FALSE, they come back as Date values, giving a plain read of the sheet.

order

"format" (default) takes the day/month order from each column's number format code, so a d/m/yyyy column restores as day.month and an m/d/yyyy column as month.day. Use "dm" or "md" to override.

output

"numeric" or "character" for the restored columns; see serial_to_day_month().

na

Strings to read as NA.

Value

A data frame. The unexcel attribute holds the excel_date_columns() report for the columns that were restored, and date_system records the system the workbook declares.

Details

Compared with restore_day_month(), which works on values already imported and must therefore infer all three of those things, this function has nothing left to infer. Prefer it whenever the original .xlsx is at hand.

See also

excel_date_cells() to inspect what would change before changing it; restore_day_month() when only the imported values survive.

Examples

path <- system.file("extdata", "typed-numbers.xlsx", package = "unexcel")

# What the spreadsheet stored
unexcel_xlsx(path, restore = FALSE)
#>   sample       dose weight reading      visit
#> 1     S1 2025-03-30   70.5   45000 2025-01-07
#> 2     S2 2025-06-15   62.1   45120 2025-02-11
#> 3     S3 2025-10-03   80.0   45230 2025-03-04
#> 4     S4 2025-12-01   55.4   45310 2025-04-09
#> 5     S5 2025-02-28   91.2   45400 2025-05-06

# What was typed
unexcel_xlsx(path)
#>   sample  dose weight reading visit
#> 1     S1 30.30   70.5   45000  1.70
#> 2     S2 15.60   62.1   45120  2.11
#> 3     S3  3.10   80.0   45230  3.40
#> 4     S4  1.12   55.4   45310  4.90
#> 5     S5 28.20   91.2   45400  5.60

# Which columns were touched, and why
attr(unexcel_xlsx(path), "unexcel")
#>   col  name n_date n_values prop_date field_order format_code
#> 1   2  dose      5        5         1          dm    d/m/yyyy
#> 2   5 visit      5        5         1          md    mm-dd-yy