Getting started with unexcel

library(unexcel)

The problem

Type 30.3 into a spreadsheet cell in a day-first locale and it is not stored as the number 30.3. The application decides you meant the 30th of March, converts the entry to a date, and stores a day count — a serial. Import the file later and the column arrives as 45746, with nothing in it to say that a number was ever typed.

The example workbook shipped with the package is exactly this situation. Five doses were typed as 30.3, 15.6, 3.10, 1.12 and 28.2:

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

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

Where the certainty comes from

An .xlsx file is a zip archive of XML. Three of its parts settle everything unexcel would otherwise have to assume.

Which date system is in force. xl/workbook.xml carries a date1904 attribute. Reading it costs nothing and getting it wrong costs four years and a day on every value:

excel_date_system(path)
#> [1] "1900"
excel_date_system(system.file("extdata", "legacy-1904.xlsx", package = "unexcel"))
#> [1] "1904"

Which cells are dates. xl/styles.xml maps every cell’s style to a number format. A cell is a date because the workbook formats it as one, not because its magnitude looks right:

excel_date_cells(path)
#>    ref row col serial       date num_fmt_id format_code field_order day_month
#> 1   B2   2   2  45746 2025-03-30        164    d/m/yyyy          dm     30.30
#> 2   E2   2   5  45664 2025-01-07         14    mm-dd-yy          md      1.70
#> 3   B3   3   2  45823 2025-06-15        164    d/m/yyyy          dm     15.60
#> 4   E3   3   5  45699 2025-02-11         14    mm-dd-yy          md      2.11
#> 5   B4   4   2  45933 2025-10-03        164    d/m/yyyy          dm      3.10
#> 6   E4   4   5  45720 2025-03-04         14    mm-dd-yy          md      3.40
#> 7   B5   5   2  45992 2025-12-01        164    d/m/yyyy          dm      1.12
#> 8   E5   5   5  45756 2025-04-09         14    mm-dd-yy          md      4.90
#> 9   B6   6   2  45716 2025-02-28        164    d/m/yyyy          dm     28.20
#> 10  E6   6   5  45783 2025-05-06         14    mm-dd-yy          md      5.60

Which field comes first. The same format code names the field order, so d/m/yyyy and m/d/yy columns are treated differently without being told:

excel_date_columns(path)
#>   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

Between them, these three facts leave nothing to infer. excel_date_cells() is the audit trail: every value unexcel_xlsx() changes appears there with the serial it came from, the date it resolves to, and the format code that justified the change.

Sheets, headers and output types

excel_sheets(path)
#> [1] "day-first"   "month-first"

unexcel_xlsx(path, sheet = "month-first")
#>   sample  dose
#> 1     S1  3.30
#> 2     S2  6.15
#> 3     S3 10.30

A value typed as 3.10 — the 3rd of October — is the number 3.1, and no numeric vector can tell it from a value typed as 3.1. When that trailing zero matters, ask for character output:

unexcel_xlsx(path, output = "character")$dose
#> [1] "30.3" "15.6" "3.10" "1.12" "28.2"

When the workbook is gone

CSV exports, legacy .xls files and data frames received from a colleague carry no formatting, so the file-based route is closed. restore_day_month() works from the values alone:

restore_day_month(c(45746, 12.5, 45823))
#> [1] 30.3 12.5 15.6

Here the conversion is still exact — but which values are serials is now a judgement call, made by three guardrails. A value is converted only if it is a whole number, falls between low_serial and high_serial, and resolves to a year inside year_window:

restore_day_month(45746, year_window = 1990:2000) # outside the window: untouched
#> [1] 45746
restore_day_month(19999)                          # outside the serial range
#> [1] 19999

Supply what you do know rather than letting the defaults stand in for it:

restore_day_month(44284, date_system = "1904")
#> [1] 30.3
restore_day_month(45746, order = "md")
#> [1] 3.3

For data frames, fix_serial_columns() scans for serial-dominated columns:

df <- data.frame(dose = c(45746, 45823), weight = c(70.5, 62.1))
fix_serial_columns(df)
#>   dose weight
#> 1 30.3   70.5
#> 2 15.6   62.1

The scan is a heuristic; naming the columns replaces it with a decision:

fix_serial_columns(df, cols = "dose")
#>   dose weight
#> 1 30.3   70.5
#> 2 15.6   62.1

Meeting in the middle

If you have imported the workbook with another reader but still have the file, you can take the columns and the date system from the file and apply them to the frame you already have — the heuristics drop out entirely:

df <- unexcel_xlsx(path, restore = FALSE)   # stand-in for any other reader
cols <- excel_date_columns(path)

for (i in seq_len(nrow(cols))) {
  df[[cols$name[i]]] <- restore_day_month(
    df[[cols$name[i]]],
    date_system = excel_date_system(path),
    order = cols$field_order[i]
  )
}
df
#>   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

Summary