Rules format reference (rules.json)

Rules are pure JSON: patterns are strings and there are no functions, so the rules can be hashed, shared with the server and ported to other languages. A columns array makes them 0.2 rules; without it, or with a number, the 0.1 rules (headers, a column count) apply. Giving both headers and columns[] throws 82excel: use either headers or columns[].

{
  "$schema": "https://82excel.com/schema/rules-1.json",
  "id": "orders-upload", "version": "3",
  "columns": [
    { "key": "orderNo", "header": "주문번호", "aliases": ["주문 번호", "OrderNo"],
      "nullable": false, "unique": true, "pattern": "^[A-Z]{2}\\d{6}$",
      "message": "주문번호는 AB123456 형식입니다" },
    { "key": "kind",   "header": "구분",       "enum": ["개인", "법인"] },
    { "key": "crn",    "header": "법인등록번호", "type": "kr.crn", "required": false },
    { "key": "phone",  "header": "전화번호",    "type": "kr.phone", "phone": { "kind": "mobile" } },
    { "key": "amount", "header": "금액",        "type": "kr.amount", "min": 0 },
    { "key": "start",  "header": "시작일",      "type": "date" },
    { "key": "end",    "header": "종료일",      "type": "date" }
  ],
  "extraColumns": "warn",
  "rows": { "min": 1, "max": 10000, "blank": "skip", "totals": "warn" },
  "rowRules": [
    { "when": { "col": "kind", "eq": "법인" }, "require": ["crn"] },
    { "compare": ["start", "<=", "end"] },
    { "uniqueWith": ["start", "orderNo"] }
  ],
  "formulaInjection": "warn",
  "pii": { "detect": ["rrn", "card", "phone", "email"], "mask": true }
}

defineRules(json) validates the rules, fills defaults and returns a frozen object. Unknown keys, bad types, patterns that do not compile, contradictions such as min > max, and row rules naming unknown columns throw 82excel: …. check, readRows, validate and makeTemplate accept the JSON as is or the defineRules() result.

import { defineRules, check, validate, rulesHash } from '82excel';

const rules = defineRules(await (await fetch('/rules.json')).json()); // throws "82excel: …" on a bad rules file
const r = await check(file, { ...rules, readRows: true });            // browser: the file
const v = validate(r.rows, rules);                                     // server: the cleaned rows
await rulesHash(rules);                                                // 'sha256:…', the same as r.report.schemaHash

Top-level keys and defaults

Column rules

Each cell is checked empty → type → enum → pattern → range → length → unique. A type failure stops the rest for that cell. Pattern and length apply to string values, min/max to numbers.

Types and canonical forms

A cleaned (canonical) value always passes again, and cleaning twice gives the same value (idempotent). The server receives cleaned values.

• Empty means null, missing, or '' after cleanup. The number 0 and false are not empty. Empty cells are null in result rows.
• A cell failing its type keeps its raw input in the row; one that passes its type but fails a later rule (enum …) keeps its cleaned value.
• integer and kr.amount reject values beyond ±2^53−1 (Number.MAX_SAFE_INTEGER).

Cleanup

• Typed columns (number, integer, date, boolean, email, kr.*) always tolerate surrounding whitespace, invisible characters (zero-width, BOM, NBSP) and full-width digits and signs, whatever normalize says.
• The normalize switches govern text output only: trim (both ends, U+3000 included), invisible (zero-width and BOM removed, NBSP → space).
• text keeps full-width characters as typed (１호점), so fullwidthDigits currently changes nothing (accepted for compatibility). No NFKC on text.

Dates

• Output is always YYYY-MM-DD, and that canonical form is always accepted whatever formats says. Years 0001–9999, Gregorian leap years.
• Tokens: YYYY (4 digits), YY (2 digits; below pivot is 20YY, else 19YY; default 50), MM/DD (exactly 2 digits), M/D (1–2 digits). Literals: . - / space 년 월 일. One year, month and day token each.
• Default formats: YYYY-M-D, YYYY.M.D, YYYY. M. D., YYYY.M.D., YYYY/M/D, YY-M-D, YYYYMMDD, YYYY년 M월 D일, YYYY년M월D일.
• Excel date cells. Date-formatted number cells are Excel serials (from 1899-12-30, the 1900-02-29 bug handled, date1904 honoured); the time is dropped. This happens when the file is read, so a server never sees a serial.
• A number without a date format (and any JSON number) is read as its digits: 20240105 → 2024-01-05.

Column mapping

• header and aliases are matched against the header row ignoring spacing, case and NFKC differences. Result rows are keyed by key, not by the header text.
• A missing required: true column is COLUMN_MISSING (error); a column found twice is COLUMN_DUPLICATE (the first is used); columns not in the rules follow extraColumns (COLUMN_EXTRA).
• The columns of a check() result is [{ key, source, col }] (the header text found and the column letter).

Row rules

• when takes one of eq, neq, in, empty: true, notEmpty: true, with strict equality on cleaned values; an empty cell only matches empty.
• compare is skipped when either cell is empty or failed its type; uniqueWith skips rows with an empty cell in the set.
• Each rule may carry severity and message. Columns named by a rule must exist in columns (catches typos).
• JS hooks: validate(rows, rules, { rowRules: [(row) => [...issues]] }). They are not part of the JSON, so the server must implement the same hook itself.

Blank and totals rows

• rows.blank: skip (default, left out), warn / error (BLANK_ROW), stop (stop at the first blank row; later rows are not read). Under any policy a blank row is never a result row, and blank rows after the last data row are dropped silently.
• rows.totals: the last data row is a totals row when its first value starts with 합계, 소계, 총계 or Total, or when every cell is a formula. warn (default) / error (TOTALS_ROW) / ignore (left out). It needs the file, so only the browser (check, readRows) checks it.

Formula injection

• Strings in text and email columns that start with = + - @, a tab or CR. Real formula cells in text columns of an xlsx count too (browser only).
• No false alarms: values that read as numbers, phone numbers or amounts (-1000, +82 10-1234-5678, -₩5,000) and values already starting with ' are left alone.
• warn (default) / error / escape (a ' is put in front of the result value; '=… is canonical, so it passes on the server too, and unique / row rules compare without the ') / off.
• Values containing HYPERLINK(, DDE(, cmd|, IMPORTXML( or IMPORTDATA( are errors under every setting, off included.

Personal data

• Detection (pii.detect, text columns only): rrn (Korean resident registration numbers: format and birth date, no checksum, so numbers issued since 2020 are caught too), card (13–19 digits, Luhn), phone (Korean mobile numbers), email. Default ["rrn"]. One PERSONAL_DATA warning per column, at the first row, with the count and the rows.
• Per column: pii: "allow" turns detection off there; pii: "forbid" makes a hit the error PII_FORBIDDEN. Columns whose type is meant to hold that data (such as kr.rrn) are not scanned.
• Masking (pii.mask: true by default): RRN 900101-1******, card 1234-****-****-5678, phone 010-****-5678, email a***@b.com, in messages, the raw/value of issues, summary examples, result.report, the CSV and xlsx error reports and the component's previews.
• result.rows is never masked: it is the data you upload.
• Detection takes time linear in the cell length; values over maxLength are already CELL_LENGTH and are not scanned.

Messages

Cell codes are fixed upper-case constants (Error codes), English by default and Korean with locale: 'ko'. A column message applies to that column's cell issues; messages: { CELL_TYPE: '…' } (in the rules or as an option) applies to every 0.2 code and is overridden by a column message. {row}, {col}, {header}, {raw}, {min} … are filled in.

Hash

rulesHash(rules) is the SHA-256 of the canonical JSON (sorted keys, no whitespace), as sha256:<hex>. For a defineRules() result it hashes the JSON as given, before defaults. readRows, previewRows, locale and telemetry are left out, so the browser report's schemaHash equals what the server computes from its own rules.json.

JSON Schema and editor completion

The JSON Schema (draft 2020-12) of the rules file is served at https://82excel.com/schema/rules-1.json (application/json, CORS open). Put it in $schema and editors such as VS Code complete keys and values and flag unknown keys. The npm package ships the same file as 82excel/rules-1.schema.json.

{
  "$schema": "https://82excel.com/schema/rules-1.json",
  "columns": [ … ]
}