Spreadsheet Export Formats Compared

Practical Snippet

Key points: (1) CSV stores only strings and Excel guesses types, so 00123 becomes 123, dates get re-parsed and long integer IDs become scientific notation — use XLSX when types must survive, or encode type in the data with ISO 8601 dates and string IDs; (2) always export CSV as UTF-8 with BOM so double-clicking in Excel does not garble, and detect the BOM before decoding rather than guessing since GBK and UTF-8 look similar; (3) Excel caps at 1,048,576 rows by 16,384 columns and 32,767 characters per cell, and some exporters truncate silently past that without any indication — switch to Parquet or Arrow at larger volumes; (4) escape CSV fields per RFC 4180, doubling quotes and wrapping when the value contains delimiters, quotes or newlines.

// ① CSV 没有类型信息 ⇒ 需要类型时用 XLSX，或把类型写进数据
// ❌ 直接写：00123 会被 Excel 读成 123
// ✅ 明确声明
const rows = [
  { id: '00123', createdAt: '2024-06-01T00:00:00Z', amount: '19.99', active: 'true' },
]
// 日期用 ISO 8601 带时区、ID 用字符串、金额用字符串保精度
// 导出为 CSV 时至少加表头说明类型，或直接改用 XLSX

// ② 导出 CSV 一律用 UTF-8 with BOM（Excel 双击才不会乱码）
// Node 侧手动加 BOM
function toCsvWithBom(rows) {
  const body = rows.map(r => Object.values(r).map(csvEscape).join(',')).join('
')
  return '' + body                    // BOM 前置
}
function csvEscape(v) {
  const s = String(v ?? '')
  // RFC 4180：含分隔符/引号/换行时用双引号包裹，内部引号翻倍
  return /[",
]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s
}

// ③ 读取 CSV：先探测 BOM，再按声明的编码解码
function decodeCsv(buffer, declaredEncoding = 'utf-8') {
  const b = new Uint8Array(buffer)
  if (b[0] === 0xef && b[1] === 0xbb && b[2] === 0xbf) {
    return { encoding: 'utf-8-bom', text: new TextDecoder('utf-8').decode(b.slice(3)) }
  }
  if (b[0] === 0xff && b[1] === 0xfe) {
    return { encoding: 'utf-16le', text: new TextDecoder('utf-16le').decode(b) }
  }
  // 无 BOM ⇒ 用声明的编码（不要靠猜，中文场景 GBK 与 UTF-8 视觉接近）
  return { encoding: declaredEncoding, text: new TextDecoder(declaredEncoding).decode(b) }
}

// ④ Excel 硬限制：超限时**静默截断**是最大的坑
const XLSX_LIMITS = { rows: 1048576, cols: 16384, cellChars: 32767 }
function checkExcelLimits(data) {
  const warnings = []
  if (data.length > XLSX_LIMITS.rows - 1) {
    warnings.push('行数 ' + data.length + ' 接近上限，导出器可能静默截断')
  }
  for (const row of data) {
    for (const [k, v] of Object.entries(row)) {
      if (String(v ?? '').length > XLSX_LIMITS.cellChars) {
        warnings.push(k + ' 超单元格字符上限会被截断')
        break
      }
    }
  }
  return warnings        // 空数组才安全；非空请改用 Parquet/Arrow
}