---
title: "Getting started with unexcel"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Getting started with unexcel}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
```

```{r setup}
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`:

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

unexcel_xlsx(path, restore = FALSE)
```

## The recommended path: read the workbook

`unexcel_xlsx()` reverses the conversion:

```{r}
unexcel_xlsx(path)
```

Notice the `reading` column. Its values — 45000 to 45400 — sit squarely inside
the range of plausible date serials, closer to a real serial than some of the
`dose` values. It was left untouched anyway, because the workbook does not
format it as a date. That distinction is not available from the imported
numbers; it has to come from the file.

Notice also `visit`. It was restored to `1.7`, `2.11`, `3.4` … rather than
`7.1`, `11.2`, `4.3` …, because that column carries an `mm-dd-yy` format code
and so reads month-first.

## 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:

```{r}
excel_date_system(path)
excel_date_system(system.file("extdata", "legacy-1904.xlsx", package = "unexcel"))
```

**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:

```{r}
excel_date_cells(path)
```

**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:

```{r}
excel_date_columns(path)
```

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

```{r}
excel_sheets(path)

unexcel_xlsx(path, sheet = "month-first")
```

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:

```{r}
unexcel_xlsx(path, output = "character")$dose
```

## 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:

```{r}
restore_day_month(c(45746, 12.5, 45823))
```

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`:

```{r}
restore_day_month(45746, year_window = 1990:2000) # outside the window: untouched
restore_day_month(19999)                          # outside the serial range
```

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

```{r}
restore_day_month(44284, date_system = "1904")
restore_day_month(45746, order = "md")
```

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

```{r}
df <- data.frame(dose = c(45746, 45823), weight = c(70.5, 62.1))
fix_serial_columns(df)
```

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

```{r}
fix_serial_columns(df, cols = "dose")
```

## 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:

```{r}
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
```

## Summary

- `unexcel_xlsx()` — read an `.xlsx` and undo its date auto-conversion. Nothing
  is inferred; prefer it whenever the file is available.
- `excel_date_system()`, `excel_date_cells()`, `excel_date_columns()` — the
  evidence, on its own, for pairing with any other reader.
- `serial_to_day_month()` — the exact conversion, given a system and an order.
- `restore_day_month()`, `fix_serial_columns()` — for values already imported,
  where the evidence is gone and guardrails have to stand in for it.