서버 2차 검수 (Node 예제)

브라우저                                         서버
check(file, rules.json)  ── { schema, rows, report } ──▶  1. schema.hash == rulesHash(rules.json) ?  아니면 409
  (1차: 파일·머리글·칸)        (+ file, upload-file=include)  2. validate(rows, rules.json)            실패면 422 + CSV
                                                          3. (파일을 받았으면) sha256(file) == report.file.sha256 ?
                                                          4. result.rows 저장

{
  "$schema": "https://82excel.com/schema/rules-1.json",
  "id": "orders-upload",
  "version": "1",
  "columns": [
    { "key": "orderNo", "header": "주문번호", "nullable": false, "unique": true, "pattern": "^[A-Z]{2}\\d{6}$" },
    { "key": "date", "header": "주문일", "type": "date", "nullable": false },
    { "key": "phone", "header": "전화번호", "type": "kr.phone", "phone": { "kind": "mobile" } },
    { "key": "amount", "header": "금액", "type": "kr.amount", "min": 0 }
  ],
  "rows": { "min": 1, "max": 10000 }
}

// server.mjs — the second pass: re-check what the browser sent, with the same rules.json.
import express from 'express';
import { readFile } from 'node:fs/promises';
import { makeErrorReport, rulesHash, sha256, validate } from '82excel';

const rules = JSON.parse(await readFile(new URL('./rules.json', import.meta.url), 'utf8'));
const RULES_HASH = await rulesHash(rules); // the same value as the browser's report.schemaHash
// validate() reports rows.max itself (ROWS_TOO_MANY), but only after reading every row. The browser never
// sends blank rows, so refusing longer arrays up front costs a real upload nothing.
const MAX_ROWS = rules.rows?.max ?? 10000;

/** validate() and, on failure, a 422 with the CSV error report. Returns the cleaned rows or null. */
async function recheck(rows, res) {
  if (Array.isArray(rows) && rows.length > MAX_ROWS) {
    res.status(413).json({ error: `at most ${MAX_ROWS} rows` });
    return null;
  }
  let result;
  try {
    result = validate(rows, rules); // throws "82excel: …" when rows is not an array of plain objects
  } catch (e) {
    res.status(400).json({ error: e.message });
    return null;
  }
  // Cell and row rules, rows.min / rows.max and failOn: the same errors as the browser's check().
  if (!result.ok) {
    const csv = Buffer.from(await makeErrorReport(result, 'csv').arrayBuffer());
    res.status(422).type('text/csv; charset=utf-8').attachment('errors.csv').send(csv);
    return null;
  }
  return result.rows;
}

const app = express();

// 1) Default upload (upload-as="json"): { fileName, sheet, schema, columns, rows, report }.
app.post('/api/orders', express.json({ limit: '20mb' }), async (req, res) => {
  const body = req.body ?? {};
  if (body.schema?.hash !== RULES_HASH) return res.status(409).json({ error: 'rules changed; reload the page' });
  const rows = await recheck(body.rows, res);
  if (!rows) return;
  // body.report is only a hint (it came from the browser): log it, never trust it.
  res.json({ saved: rows.length, first: rows[0] });
});

// 2) upload-file="include": multipart with file, data (the JSON above without report) and report.
app.post('/api/orders-with-file', express.raw({ type: 'multipart/form-data', limit: '60mb' }), async (req, res) => {
  // Node's own multipart parser (undici); multer or busboy work just as well.
  let form, data, report;
  try {
    form = await new Request('http://local/', {
      method: 'POST',
      headers: { 'content-type': req.get('content-type') },
      body: req.body,
    }).formData();
    data = JSON.parse(String(form.get('data') ?? '{}'));
    report = JSON.parse(String(form.get('report') ?? '{}'));
  } catch {
    return res.status(400).json({ error: 'malformed multipart body or JSON field' }); // not a 500
  }
  const file = form.get('file');
  if (!(file instanceof File)) return res.status(400).json({ error: 'file is missing' });
  if (data?.schema?.hash !== RULES_HASH) return res.status(409).json({ error: 'rules changed; reload the page' });
  const bytes = new Uint8Array(await file.arrayBuffer());
  if ((await sha256(bytes)) !== report?.file?.sha256) return res.status(400).json({ error: 'file does not match the report' });
  const rows = await recheck(data.rows, res);
  if (!rows) return;
  res.json({ saved: rows.length, file: file.name, bytes: bytes.length });
});

const server = app.listen(process.env.PORT ?? 3000, () => console.log(`listening on ${server.address().port}`));
export default server;

// try.mjs — plays the browser: check() a CSV in Node, then send what <excel-82> sends.
import { check } from '82excel';

process.env.PORT = '0';
const { default: server } = await import('./server.mjs');
if (!server.listening) await new Promise((r) => server.once('listening', r));
const base = `http://127.0.0.1:${server.address().port}`;
const rules = (await import('./rules.json', { with: { type: 'json' } })).default;

const csv = '주문번호,주문일,전화번호,금액\r\nAB000001,2024.1.5,010 1234 5678,"₩12,000"\r\nAB000002,2024-01-06,+82 10-9876-5432,500원\r\n';
const file = new File([csv], 'orders.csv', { type: 'text/csv' });
const r = await check(file, { ...rules, readRows: true });
console.log('browser check:', r.ok, JSON.stringify(r.rows));

// The JSON body <excel-82> builds for v2 rules (spec §5 P-1).
const data = {
  fileName: file.name,
  sheet: r.info.sheets[0].name,
  schema: { id: r.report.schemaId, version: r.report.schemaVersion, hash: r.report.schemaHash },
  columns: r.columns.map((c) => c.key),
  rows: r.rows,
};
const post = (path, body, type) => fetch(base + path, { method: 'POST', body, headers: type ? { 'content-type': type } : {} });
const json = (path, body) => post(path, JSON.stringify(body), 'application/json');
const show = async (label, res) => console.log(`${label}: ${res.status}\n${(await res.text()).replace(/^/, '').trimEnd()}`);

await show('1 valid', await json('/api/orders', { ...data, report: r.report }));

// Someone skips the browser and posts rows directly.
const bad = { ...data, rows: [{ $row: 2, orderNo: 'X1', date: '2024-02-30', phone: '010-123-4567', amount: -5 }] };
await show('2 tampered', await json('/api/orders', bad));

await show('3 old rules', await json('/api/orders', { ...data, schema: { ...data.schema, hash: 'sha256:0' } }));
await show('4 not rows', await json('/api/orders', { ...data, rows: 'x' }));

const form = (f) => {
  const fd = new FormData();
  fd.append('file', f);
  fd.append('data', JSON.stringify(data));
  fd.append('report', JSON.stringify(r.report));
  return fd;
};
await show('5 include file', await post('/api/orders-with-file', form(file)));
await show('6 other file', await post('/api/orders-with-file', form(new File(['주문번호\r\nZZ999999\r\n'], 'orders.csv'))));

// rows.min is 1: an empty upload fails as it would in the browser.
await show('7 no rows', await json('/api/orders', { ...data, rows: [] }));

const broken = form(file);
broken.set('data', '{not json');
await show('8 malformed data', await post('/api/orders-with-file', broken));

server.close();

npm i 82excel express
node try.mjs

listening on 4211
browser check: true [{"$row":2,"orderNo":"AB000001","date":"2024-01-05","phone":"010-1234-5678","amount":12000},{"$row":3,"orderNo":"AB000002","date":"2024-01-06","phone":"010-9876-5432","amount":500}]
1 valid: 200
{"saved":2,"first":{"$row":2,"orderNo":"AB000001","date":"2024-01-05","phone":"010-1234-5678","amount":12000}}
2 tampered: 422
Row,Column,Header,Original value,Message,Code,Severity
2,,주문번호,X1,Row 2 (주문번호): Does not match the required pattern,CELL_PATTERN,error
2,,주문일,2024-02-30,Row 2 (주문일): Date does not exist,CELL_DATE,error
2,,전화번호,010-***-4567,Row 2 (전화번호): Invalid mobile phone number,KR_PHONE,error
2,,금액,-5,Row 2 (금액): Must be at least 0,CELL_RANGE,error
3 old rules: 409
{"error":"rules changed; reload the page"}
4 not rows: 400
{"error":"82excel: rows must be an array of row objects"}
5 include file: 200
{"saved":2,"file":"orders.csv","bytes":134}
6 other file: 400
{"error":"file does not match the report"}
7 no rows: 422
Row,Column,Header,Original value,Message,Code,Severity
1,,,,"Expected at least 1 rows, found 0",ROWS_TOO_FEW,error
8 malformed data: 400
{"error":"malformed multipart body or JSON field"}