Skip to content

Evenfire is an intelligence layer platform for running LLM agents that take real actions, on your infrastructure

TypeScript
27.9 KB
Raw Blame
/**
 * What lands in each XLSX cell: its type, its number format and the rules that
 * style it. Each file is read back with ExcelJS, because a success flag says
 * nothing about whether "1,234.50" became a number Excel can sum.
 */
import { afterEach, beforeEach, describe, expect, it } from 'vitest'
import ExcelJS from 'exceljs'
import * as fs from 'fs'
import * as os from 'os'
import * as path from 'path'
import { INTERNAL_TOOLS } from '../internalTools'
import { StepMcpRouter } from '../stepRouter'
import type { InternalToolResult } from '../types'
import { zipEntryText } from './support/zipEntries'

let dir: string

beforeEach(() => {
  dir = fs.mkdtempSync(path.join(os.tmpdir(), 'clerum-xlsx-values-'))
})

afterEach(() => {
  fs.rmSync(dir, { recursive: true, force: true })
})

const xlsx = INTERNAL_TOOLS.find(t => t.name === 'clerum__generate_xlsx')!

async function generate(args: Record<string, unknown>): Promise<InternalToolResult> {
  return xlsx.execute(args, dir)
}

async function sheetOf(result: InternalToolResult, index = 0): Promise<ExcelJS.Worksheet> {
  expect(result.success, result.error).toBe(true)
  const wb = new ExcelJS.Workbook()
  await wb.xlsx.readFile(result.artifact!.path)
  return wb.worksheets[index]
}

async function oneSheet(rows: unknown[][], extra: Record<string, unknown> = {}) {
  const result = await generate({ filename: 'v.xlsx', sheets: [{ name: 'S', rows, ...extra }] })
  return { result, ws: await sheetOf(result) }
}

function fillOf(cell: ExcelJS.Cell): string | undefined {
  const fill = cell.fill as ExcelJS.FillPattern | undefined
  return fill?.fgColor?.argb
}

describe('numbers reach the sheet as numbers', () => {
  it('accepts numeric, boolean and null cells on the workflow path', async () => {
    const router = new StepMcpRouter(() => {
      throw new Error('no MCP servers in this test')
    })
    router.registerInternalTools(INTERNAL_TOOLS, dir)
    const { result } = await router.callTool('clerum__generate_xlsx', {
      filename: 'wf.xlsx',
      sheets: [
        {
          name: 'S',
          rows: [
            ['Month', 'Revenue', 'Units', 'Paid'],
            ['Jan', 12500.5, 320, true],
            ['Feb', 13900, null, false],
          ],
          conditionalFormatting: [{ column: 1, rules: [{ equals: 13900, fillColor: '#fee2e2' }] }],
        },
      ],
    })
    expect(result.isError).toBe(false)
    const ws = await sheetOf(result.content as InternalToolResult)
    expect(ws.getCell('B2').value).toBe(12500.5)
    expect(ws.getCell('C2').value).toBe(320)
    expect(ws.getCell('D2').value).toBe(true)
    expect(fillOf(ws.getCell('B3'))).toBe('FFFEE2E2')
  })

  it('converts numbers, currency and percents written as text', async () => {
    const { ws } = await oneSheet([
      ['Item', 'Amount', 'Pct', 'Price', 'Delta'],
      ['A', '1,234.50', '45%', '$1,200', '-300'],
      ['B', '980', '12.5%', '€2,500.75', '-12.5'],
    ])
    expect(ws.getCell('B2').value).toBe(1234.5)
    expect(ws.getCell('B3').value).toBe(980)
    expect(ws.getCell('C2').value).toBeCloseTo(0.45, 12)
    expect(ws.getCell('C2').numFmt).toMatch(/%/)
    expect(ws.getCell('C3').value).toBeCloseTo(0.125, 12)
    expect(ws.getCell('D2').value).toBe(1200)
    expect(ws.getCell('D2').numFmt).toContain('"$"')
    expect(ws.getCell('D3').value).toBe(2500.75)
    expect(ws.getCell('D3').numFmt).toContain('"€"')
    expect(ws.getCell('E2').value).toBe(-300)
    expect(ws.getCell('E3').value).toBe(-12.5)
  })

  it('keeps identifiers and ambiguous text as text', async () => {
    const { result, ws } = await oneSheet([
      ['Code', 'Card', 'Phone', 'Weight', 'Monto'],
      ['00123', '4111111111111111', '+1 555 0100', '12 kg', '1.200'],
    ])
    expect(ws.getCell('A2').value).toBe('00123')
    expect(ws.getCell('B2').value).toBe('4111111111111111')
    expect(ws.getCell('C2').value).toBe('+1 555 0100')
    expect(ws.getCell('D2').value).toBe('12 kg')
    // "1.200" is 1.2 in English and 1200 in Spanish: text, and the agent is told.
    expect(ws.getCell('E2').value).toBe('1.200')
    expect(result.content).toContain("'1.200'")
  })

  it('does not reinterpret text in a column declared as text', async () => {
    const { ws } = await oneSheet([['Ref'], ['1,234'], ['45%']], { columnFormats: { Ref: 'text' } })
    expect(ws.getCell('A2').value).toBe('1,234')
    expect(ws.getCell('A3').value).toBe('45%')
  })

  it('turns strict ISO 8601 dates into dates', async () => {
    const { result, ws } = await oneSheet([
      ['Fecha', 'Stamp', 'Bad'],
      ['2026-09-22', '2026-09-23T10:00:00Z', '2026-02-30'],
      ['2026-09-24', '2026-09-23T10:00:00-06:00', '22/09/2026'],
    ])
    const a2 = ws.getCell('A2')
    expect(a2.value).toBeInstanceOf(Date)
    expect((a2.value as Date).toISOString()).toBe('2026-09-22T00:00:00.000Z')
    expect(a2.numFmt).toBe('yyyy-mm-dd')
    const b2 = ws.getCell('B2')
    expect((b2.value as Date).toISOString()).toBe('2026-09-23T10:00:00.000Z')
    expect(b2.numFmt).toBe('yyyy-mm-dd hh:mm')
    expect((ws.getCell('B3').value as Date).toISOString()).toBe('2026-09-23T16:00:00.000Z')
    expect(result.content).toMatch(/UTC/)
    expect(ws.getCell('C2').value).toBe('2026-02-30')
    expect(ws.getCell('C3').value).toBe('22/09/2026')
  })

  it('keeps dates Excel would show a day late as text', async () => {
    const { result, ws } = await oneSheet([
      ['Fecha'],
      ['1900-01-15'],
      ['1900-02-28'],
      ['1900-03-01'],
    ])
    expect(ws.getCell('A2').value).toBe('1900-01-15')
    expect(ws.getCell('A3').value).toBe('1900-02-28')
    expect((ws.getCell('A4').value as Date).toISOString()).toBe('1900-03-01T00:00:00.000Z')
    // Serial 61 is 1900-03-01 in Excel, which counts a 29 February 1900.
    expect(zipEntryText(result.artifact!.path, 'xl/worksheets/sheet1.xml')).toMatch(
      /<c r="A4"[^>]*><v>61<\/v>/
    )
  })

  it('names cells that could not take the format their column asked for', async () => {
    const long = 'x'.repeat(100)
    const { result, ws } = await oneSheet(
      [
        ['Fecha', 'Monto', 'Units'],
        ['22/09/2026', '1.234,50', long],
        ['2026-09-22', 1234.5, 3],
      ],
      { columnFormats: { Fecha: 'date', Monto: 'currency:EUR', Units: 'integer' } }
    )
    expect(ws.getCell('A2').value).toBe('22/09/2026')
    expect(ws.getCell('B3').numFmt).toContain('€')
    expect(result.content).toMatch(/'Fecha'.*'22\/09\/2026'.*ISO 8601/)
    expect(result.content).toMatch(/'Monto'.*'1\.234,50'.*JSON number/)
    expect(result.content).toContain(`'${'x'.repeat(40)}…'`)
    expect(result.content).not.toContain(long)
  })

  it('keeps an identifier column all numbers or all text, never a mix', async () => {
    const { ws } = await oneSheet([
      ['Order ID', 'Zip'],
      [100234, '02134'],
      ['100235', '90210'],
    ])
    expect(ws.getCell('A3').value).toBe(100235)
    expect(ws.getCell('A3').numFmt).toBe('0')
    expect(ws.getCell('B2').value).toBe('02134')
    expect(ws.getCell('B3').value).toBe('90210')
  })
})

describe('characters outside the BMP', () => {
  it('writes every emoji whole, wherever it falls in the shared strings', async () => {
    // 9,000 emoji are 18,000 UTF-16 units, so one straddles the 16,384 mark.
    const text = '\u{1F600}'.repeat(9000)
    const result = await generate({
      filename: 'e.xlsx',
      sheets: [{ name: 'E', rows: [['Emoji'], [text]] }],
    })
    expect(result.success, result.error).toBe(true)
    const shared = zipEntryText(result.artifact!.path, 'xl/sharedStrings.xml')
    expect(shared).not.toContain('\uFFFD')
    expect(shared).toContain(text)
  })
})

describe('column formats', () => {
  it('reads 0-100 values in a percent column as percentage points', async () => {
    const { ws } = await oneSheet([
      ['Conversion rate %', 'Share'],
      [12.5, 0.12],
      [45, 0.45],
    ])
    expect(ws.getCell('A2').value).toBeCloseTo(0.125, 12)
    expect(ws.getCell('A3').value).toBeCloseTo(0.45, 12)
    expect(ws.getCell('A2').numFmt).toMatch(/%$/)
    expect(ws.getCell('B2').value).toBe(0.12)
    expect(ws.getCell('B2').numFmt).toMatch(/%$/)
  })

  it('matches header words, not fragments of them', async () => {
    const { ws } = await oneSheet([
      ['Candidates', 'Strategy score', 'Carrier', 'Feedback score', 'Shareholders'],
      [12, 85, 3, 4.5, 20],
    ])
    expect(ws.getCell('A2').value).toBe(12)
    expect(ws.getCell('A2').numFmt).not.toMatch(/y/)
    expect(ws.getCell('B2').numFmt ?? '').not.toMatch(/%/)
    expect(ws.getCell('C2').numFmt ?? '').not.toMatch(/\$/)
    expect(ws.getCell('D2').numFmt ?? '').not.toMatch(/\$/)
    expect(ws.getCell('E2').numFmt ?? '').not.toMatch(/%/)
  })

  it('understands Spanish and Portuguese headers', async () => {
    const { ws } = await oneSheet([
      ['Fecha', 'Tasa de conversión', 'Importe EUR', 'Preço (R$)'],
      [46023, 0.12, 8100.25, 99.9],
    ])
    expect(ws.getCell('A2').value).toBeInstanceOf(Date)
    expect(ws.getCell('A2').numFmt).toBe('yyyy-mm-dd')
    expect(ws.getCell('B2').numFmt).toMatch(/%$/)
    expect(ws.getCell('C2').numFmt).toContain('"€"')
    expect(ws.getCell('D2').numFmt).toContain('"R$"')
  })

  it('writes years and identifiers without a thousands separator', async () => {
    const { ws } = await oneSheet([
      ['Year', 'Order ID', 'Año', 'Units'],
      [2026, 100234, 2025, 1500],
    ])
    expect(ws.getCell('A2').numFmt).toBe('0')
    expect(ws.getCell('B2').numFmt).toBe('0')
    expect(ws.getCell('C2').numFmt).toBe('0')
    expect(ws.getCell('D2').numFmt).toBe('#,##0')
  })

  it('uses the currency the header names instead of assuming dollars', async () => {
    const { ws } = await oneSheet(
      [
        ['Cost EUR', 'Monto MXN', 'Revenue', 'Fee'],
        [8100.25, 99000, 1200.5, 10],
      ],
      { columnFormats: { Fee: 'currency:GBP' } }
    )
    expect(ws.getCell('A2').numFmt).toContain('"€"')
    expect(ws.getCell('A2').numFmt).not.toContain('"$"')
    expect(ws.getCell('B2').numFmt).toContain('"MX$"')
    expect(ws.getCell('C2').numFmt).toContain('"$"')
    expect(ws.getCell('D2').numFmt).toContain('"£"')
  })

  it('resolves columnFormats keys by header in any case, letter or index', async () => {
    const { result, ws } = await oneSheet(
      [
        ['Month', 'Total', 'Net', 'Tax'],
        ['Jan', 1500.5, 20, 0.16],
      ],
      {
        columnFormats: {
          total: 'currencyUsd',
          C: 'currencyEur',
          '3': 'percentage',
          Missing: 'decimal',
          Month: 'money please',
        },
      }
    )
    expect(ws.getCell('B2').numFmt).toContain('"$"')
    expect(ws.getCell('C2').numFmt).toContain('"€"')
    expect(ws.getCell('D2').numFmt).toBe('0.0%')
    expect(result.content).toContain("'Missing'")
    expect(result.content).toContain("'money please'")
  })
})

describe('conditional formatting', () => {
  it('finds the column by header, letter or 0-based index and compares numbers as numbers', async () => {
    const { result, ws } = await oneSheet(
      [
        ['Status', 'Amount', 'Quick Win'],
        ['Late', '1,500', 1],
        ['ok', 100, '0'],
      ],
      {
        conditionalFormatting: [
          { column: 'status', rules: [{ equals: 'late', fillColor: '#fee2e2' }] },
          { column: 'B', rules: [{ greaterThan: 1000, fillColor: '#dcfce7' }] },
          { column: 1, rules: [{ equals: '100', fillColor: '#fef9c3' }] },
          { column: 'Quick Win', rules: [{ equals: 1, fillColor: '#fef3c7' }] },
          { column: 'Nope', rules: [{ equals: 1, fillColor: '#000000' }] },
        ],
      }
    )
    expect(fillOf(ws.getCell('A2'))).toBe('FFFEE2E2')
    expect(fillOf(ws.getCell('B2'))).toBe('FFDCFCE7')
    expect(fillOf(ws.getCell('B3'))).toBe('FFFEF9C3')
    expect(fillOf(ws.getCell('C2'))).toBe('FFFEF3C7')
    expect(fillOf(ws.getCell('C3'))).not.toBe('FFFEF3C7')
    expect(result.content).toContain("'Nope'")
  })

  it('compares percentages and currency with the number as written', async () => {
    const { ws } = await oneSheet(
      [
        ['Growth %', 'Price'],
        ['45%', '$1,200'],
        [12, '$80'],
      ],
      {
        conditionalFormatting: [
          { column: 'Growth %', rules: [{ greaterThan: 40, fillColor: '#dcfce7' }] },
          { column: 'Price', rules: [{ between: [1000, 2000], fillColor: '#fee2e2' }] },
        ],
      }
    )
    expect(fillOf(ws.getCell('A2'))).toBe('FFDCFCE7')
    expect(fillOf(ws.getCell('A3'))).not.toBe('FFDCFCE7')
    expect(fillOf(ws.getCell('B2'))).toBe('FFFEE2E2')
    expect(fillOf(ws.getCell('B3'))).not.toBe('FFFEE2E2')
  })

  it('tests regex and contains against the text as written', async () => {
    const { ws } = await oneSheet([['Price'], ['$1,200'], ['80']], {
      conditionalFormatting: [
        { column: 'Price', rules: [{ regex: '^\\$', fillColor: '#fee2e2' }] },
      ],
    })
    expect(fillOf(ws.getCell('A2'))).toBe('FFFEE2E2')
    expect(fillOf(ws.getCell('A3'))).not.toBe('FFFEE2E2')
  })
})

describe('rules on a column of mixed percents', () => {
  it('notes that 0.45 and 50% are compared as written', async () => {
    const { result } = await oneSheet([['Share'], [0.45], ['50%'], [0.3]], {
      conditionalFormatting: [
        { column: 'Share', rules: [{ greaterThan: 40, fillColor: '#fee2e2' }] },
      ],
    })
    expect(result.content).toContain('numeric rules compare each as written')
  })

  it('says nothing when the column is sent one way', async () => {
    const { result } = await oneSheet([['Share'], ['45%'], ['50%']], {
      conditionalFormatting: [
        { column: 'Share', rules: [{ greaterThan: 40, fillColor: '#fee2e2' }] },
      ],
    })
    expect(result.content).not.toContain('compare each as written')
  })
})

describe('colors', () => {
  it('accepts shorthand hex and CSS names, and falls back with a warning otherwise', async () => {
    const result = await generate({
      filename: 'c.xlsx',
      sheets: [
        {
          name: 'S',
          titleRow: { text: 'T', fillColor: '#f00', fontColor: 'white' },
          rows: [['v'], [5], [6]],
          conditionalFormatting: [
            {
              column: 'v',
              rules: [
                { equals: 5, fillColor: 'green' },
                { equals: 6, fillColor: 'not-a-color' },
              ],
            },
          ],
        },
      ],
    })
    const ws = await sheetOf(result)
    expect(fillOf(ws.getCell('A1'))).toBe('FFFF0000')
    expect(fillOf(ws.getCell('A3'))).toBe('FF008000')
    expect(result.content).toContain('conditionalFormatting[0].rules[1].fillColor')
  })

  it('reads #RRGGBBAA as CSS and a bare 8-digit hex as Excel ARGB', async () => {
    const result = await generate({
      filename: 'c8.xlsx',
      sheets: [
        {
          name: 'S',
          titleRow: { text: 'T', fillColor: '#ff000080' },
          rows: [['v'], [5]],
          conditionalFormatting: [{ column: 'v', rules: [{ equals: 5, fillColor: 'FF1E3A8A' }] }],
        },
      ],
    })
    const ws = await sheetOf(result)
    expect(fillOf(ws.getCell('A1'))).toBe('FFFF0000')
    expect(fillOf(ws.getCell('A3'))).toBe('FF1E3A8A')
  })
})

describe('signs, identifiers and text left as sent', () => {
  it('reads a leading plus as a sign on a quantity, and keeps phone numbers as text', async () => {
    const { ws } = await oneSheet([
      ['Metric', 'Change %', 'Change $', 'Delta', 'Phone'],
      ['Revenue', '+12%', '+$144', '+5', '+34600111222'],
      ['Churn', '-3%', '-$0.15', '-3', '+1 555 0100'],
      ['NPS', '+0.5%', '+$0.2', '+1,500', '+1-800-555-0100'],
    ])
    expect(ws.getCell('B2').value).toBeCloseTo(0.12, 12)
    expect(ws.getCell('B4').value).toBeCloseTo(0.005, 12)
    expect(ws.getCell('C2').value).toBe(144)
    expect(ws.getCell('C4').value).toBe(0.2)
    expect(ws.getCell('D2').value).toBe(5)
    expect(ws.getCell('D4').value).toBe(1500)
    expect(ws.getCell('E2').value).toBe('+34600111222')
    expect(ws.getCell('E3').value).toBe('+1 555 0100')
    expect(ws.getCell('E4').value).toBe('+1-800-555-0100')
  })

  it('gives the percent cells of a column one format, with the places any needs', async () => {
    const { ws } = await oneSheet([['Growth %'], ['12.5%'], ['10%'], ['8%']])
    expect(['A2', 'A3', 'A4'].map(a => ws.getCell(a).numFmt)).toEqual(['0.0%', '0.0%', '0.0%'])
  })

  it('keeps showing the plus a number was written with', async () => {
    const { ws } = await oneSheet([
      ['Dial prefix', 'Score change', 'Share'],
      ['+44', '+5', '+12%'],
      ['+1', '-3', '-3%'],
    ])
    expect(ws.getCell('A2').value).toBe(44)
    expect(ws.getCell('A2').numFmt).toMatch(/^\+/)
    expect(ws.getCell('B2').value).toBe(5)
    expect(ws.getCell('B2').numFmt).toMatch(/^\+[^;]*;-/)
    expect(ws.getCell('B3').numFmt ?? '').not.toMatch(/^\+/)
    expect(ws.getCell('C2').numFmt).toMatch(/^\+[^;]*%/)
  })

  it('reads accounting parentheses as a sign on a quantity, and notes refused dates', async () => {
    const { ws, result } = await oneSheet([
      ['Account', 'Q1', 'Q2', 'Note', 'Booked on'],
      ['A', 1200, '($300)', '(1)', '2026-02-27'],
      ['B', '(500)', '(1,200.50)', '(2)', '2026-02-29'],
      ['C', 700, 1200, '(3)', '1899-12-31'],
    ])
    expect(ws.getCell('B3').value).toBe(-500)
    expect(ws.getCell('C2').value).toBe(-300)
    expect(ws.getCell('C3').value).toBe(-1200.5)
    expect(ws.getCell('D2').value).toBe('(1)')
    expect(ws.getCell('E3').value).toBe('2026-02-29')
    expect(result.content).toContain(
      "column 'Booked on': 2 dates such as '2026-02-29' were kept as text"
    )
  })

  it('keeps a year in parentheses as text, and reads a grouped quantity as negative', async () => {
    const { ws } = await oneSheet([
      ['Value', 'Amount'],
      ['(2024)', '(1,500)'],
      ['(1850)', '(2,024)'],
    ])
    expect(ws.getCell('A2').value).toBe('(2024)')
    expect(ws.getCell('A3').value).toBe('(1850)')
    expect(ws.getCell('B2').value).toBe(-1500)
    expect(ws.getCell('B3').value).toBe(-2024)
  })

  it('applies a time zone before the 1900 check, and refuses an offset no zone has', async () => {
    const { ws } = await oneSheet([
      ['When'],
      ['1900-03-01T01:00+05:00'],
      ['2026-05-01T10:00+99:99'],
      ['2026-05-01T10:00+14:00'],
    ])
    // 1900-02-28T20:00 UTC: Excel would show a day that never existed.
    expect(ws.getCell('A2').value).toBe('1900-03-01T01:00+05:00')
    expect(ws.getCell('A3').value).toBe('2026-05-01T10:00+99:99')
    expect((ws.getCell('A4').value as Date).toISOString()).toBe('2026-04-30T20:00:00.000Z')
  })

  it('writes identifier columns without thousands separators', async () => {
    const headers = [
      'Invoice No.',
      'Invoice #',
      'Account number',
      'Employee number',
      'Número de factura',
      'NIF',
      'Ticket',
    ]
    const { ws } = await oneSheet([
      [...headers, 'Number of employees'],
      [10023, 10023, 1234567890, 4501, 20260001, 12345678, 5521, 1500],
    ])
    headers.forEach((_, c) => expect(ws.getRow(2).getCell(c + 1).numFmt).toBe('0'))
    expect(ws.getCell('H2').numFmt).toBe('#,##0')
  })

  it('keeps a column of codes as text when any of them has a leading zero', async () => {
    const { result, ws } = await oneSheet([['Account'], ['0012345678'], ['1234567890'], [9]])
    expect(ws.getCell('A2').value).toBe('0012345678')
    expect(ws.getCell('A3').value).toBe('1234567890')
    expect(ws.getCell('A4').value).toBe('9')
    expect(result.content).toContain("'0012345678'")
  })

  it('names number- and date-looking text it could not read, once per column', async () => {
    const { result, ws } = await oneSheet([
      ['Concepto', 'Importe', 'Tasa', 'Precio', 'Fecha', 'Delta', 'Big'],
      ['Alquiler', '1.234,50', '12,5%', '€1.200,00', '22/09/2026', '−12.5', '1e6'],
      ['Luz', '98,10', '3,1%', '€80,00', '23/09/2026', '−3', '2e6'],
    ])
    expect(ws.getCell('B2').value).toBe('1.234,50')
    // U+2212 is a minus sign and nothing else.
    expect(ws.getCell('F2').value).toBe(-12.5)
    const notes = result.content ?? ''
    for (const column of ['Importe', 'Tasa', 'Precio', 'Fecha', 'Big']) {
      expect(notes).toMatch(new RegExp(`'${column}': 2 values such as '[^']+' were kept as text`))
    }
    expect(notes).not.toContain("'Concepto'")
    expect(notes).not.toContain("'Delta'")
  })

  it("cuts text at Excel's 32,767-character cell limit and says where", async () => {
    const long = 'x'.repeat(40_000)
    const { result, ws } = await oneSheet(
      [
        ['Notes', 'Other'],
        [long, 'ok'],
      ],
      { titleRow: { text: `T${long}` } }
    )
    expect((ws.getCell('A3').value as string).length).toBe(32_767)
    expect((ws.getCell('A1').value as string).length).toBe(32_767)
    expect(result.content).toContain('cell A3 was cut to 32,767 characters')
    expect(result.content).toContain('titleRow.text was cut to 32,767 characters')
  })

  it('does not split an emoji when cutting at the cell limit', async () => {
    const text = `${'x'.repeat(32_766)}\u{1F600}tail`
    const { ws } = await oneSheet([['Notes'], [text]])
    expect(ws.getCell('A2').value).toBe('x'.repeat(32_766))
  })
})

describe('percent columns and format codes', () => {
  it('says how an inferred percent column was read', async () => {
    const { result, ws } = await oneSheet([
      ['Month', 'Churn %', 'Interest rate %', 'Share'],
      ['Jan', 0.8, 0.25, 0.12],
      ['Feb', 1.1, 0.5, 0.45],
      ['Mar', 0.9, 0.75, 0.3],
    ])
    // Past 1 with a '%' header, 1.1 is 1.1%, not 110%.
    expect(ws.getCell('B3').value).toBe(0.011)
    expect(result.content).toMatch(/'Churn %'.*percentage points.*'Churn %': 'percent'/)
    expect(ws.getCell('C2').value).toBe(0.25)
    expect(result.content).toMatch(/'Interest rate %'.*fractions \(0\.8 = 80%\).*percentPoints/)
    expect(result.content).toMatch(/'Share'.*fractions/)
  })

  it('refuses Excel format codes with more than four sections or unbalanced quotes', async () => {
    const { result, ws } = await oneSheet(
      [
        ['A', 'B', 'C', 'D'],
        [1.5, 2.5, 3.5, 4.5],
      ],
      { columnFormats: { A: '#,##0.0;;;;', B: '0;0;0;0;0', C: '0 "kg', D: '#,##0.0;;;' } }
    )
    expect(ws.getCell('A2').numFmt).not.toBe('#,##0.0;;;;')
    expect(ws.getCell('B2').numFmt).not.toBe('0;0;0;0;0')
    expect(ws.getCell('C2').numFmt).not.toBe('0 "kg')
    expect(ws.getCell('D2').numFmt).toBe('#,##0.0;;;')
    for (const key of ["['A']", "['B']", "['C']"]) expect(result.content).toContain(key)
    expect(result.content).not.toContain("['D']")
  })
})

describe('regex rules', () => {
  it('reports an invalid regex as invalid, not as slow', async () => {
    const { result } = await oneSheet([['Region'], ['North']], {
      conditionalFormatting: [{ column: 'Region', rules: [{ regex: '[', fillColor: '#000000' }] }],
    })
    expect(result.content).toContain('rules[0].regex is not a valid regular expression')
    expect(result.content).not.toContain('nested repetition')
  })

  it('stops regexes that backtrack exponentially within one time budget', async () => {
    const evil = ['^(a|a)*$', '^(a|aa)*$', '^(\\w|\\d)*$']
    const sheet = (name: string) => ({
      name,
      rows: [
        ['Name'],
        ...Array.from({ length: 20 }, (_, i) => [`${(i % 2 ? '1' : 'a').repeat(35 + i)}!`]),
      ],
      conditionalFormatting: [
        { column: 'Name', rules: evil.map(regex => ({ regex, fillColor: '#fee2e2' })) },
      ],
    })
    const started = Date.now()
    const result = await generate({ filename: 'redos.xlsx', sheets: [sheet('A'), sheet('B')] })
    // Loose, so a slow machine passes; one unbounded match of these takes minutes.
    expect(Date.now() - started).toBeLessThan(15_000)
    expect(result.success, result.error).toBe(true)
    expect(result.content).toContain('regex took too long to test')
    // A rule after the one that ran out of time is not blamed on its own pattern.
    expect(result.content).toMatch(/regex was skipped: a regex rule before it took too long/)
    const ws = await sheetOf(result)
    expect(fillOf(ws.getCell('A2'))).not.toBe('FFFEE2E2')
  }, 30_000)

  it('runs thousands of cheap regex rules within the budget', async () => {
    // Setting up matching per rule would use the budget before the rules do.
    const sheet = (s: number) => ({
      name: `S${s}`,
      rows: [['Code'], ...Array.from({ length: 20 }, (_, i) => [`FAIL-${i}`])],
      conditionalFormatting: [
        {
          column: 'Code',
          rules: Array.from({ length: 40 }, (_, r) => ({
            regex: `^FAIL-${r % 20}$`,
            fillColor: '#fee2e2',
          })),
        },
      ],
    })
    const result = await generate({
      filename: 'many.xlsx',
      sheets: Array.from({ length: 50 }, (_, s) => sheet(s)),
    })
    expect(result.success, result.error).toBe(true)
    expect(result.content ?? '').not.toContain('regex')
  }, 30_000)

  it('sizes a title of very many lines', async () => {
    const result = await generate({
      filename: 'title.xlsx',
      sheets: [{ name: 'T', titleRow: { text: 'line\n'.repeat(200_000) }, rows: [] }],
    })
    expect(result.success, result.error).toBe(true)
    expect(result.content).toContain('titleRow.text was cut to 32,767 characters')
  }, 30_000)

  it('still runs ordinary regex rules on a large sheet', async () => {
    const rows = [['Code'], ...Array.from({ length: 20_000 }, (_, i) => [`R-${i}`])]
    const { result, ws } = await oneSheet(rows, {
      conditionalFormatting: [
        { column: 'Code', rules: [{ regex: '^R-1\\d*5$', fillColor: '#fee2e2' }] },
      ],
    })
    expect(result.content ?? '').not.toContain('took too long')
    expect(fillOf(ws.getCell('A17'))).toBe('FFFEE2E2')
    expect(fillOf(ws.getCell('A18'))).not.toBe('FFFEE2E2')
  }, 30_000)
})

describe('formats that fit the value', () => {
  it('keeps the format of a percent or another currency under a requested format, with a note', async () => {
    const { result, ws } = await oneSheet(
      [
        ['Growth', 'Share', 'Price'],
        ['45%', '$1,200', '$1,200'],
        [12, 0.3, '€50'],
      ],
      { columnFormats: { Growth: 'integer', Share: 'percent', Price: 'currency:EUR' } }
    )
    expect(ws.getCell('A2').numFmt).toBe('0%')
    expect(ws.getCell('B2').numFmt).toContain('"$"')
    expect(ws.getCell('C2').numFmt).toContain('"$"')
    expect(ws.getCell('C3').numFmt).toContain('€')
    expect(ws.getCell('A3').numFmt).toBe('#,##0')
    expect(result.content).toContain("column 'Growth': '45%' does not fit the format")
  })

  it('reads amounts under headers that name an identifier word as amounts', async () => {
    const { ws } = await oneSheet([
      ['Ticket price', 'Code coverage', 'Mobile revenue', 'Ticket'],
      ['$45.00', '85%', '$1,200', '00123'],
    ])
    expect(ws.getCell('A2').value).toBe(45)
    expect(ws.getCell('B2').value).toBe(0.85)
    expect(ws.getCell('C2').value).toBe(1200)
    expect(ws.getCell('D2').value).toBe('00123')
  })

  it('keeps counts under event words as counts, and dates them only when the header says so', async () => {
    const { ws } = await oneSheet([
      ['Accounts created', 'Created at'],
      [24512, 46000],
    ])
    expect(ws.getCell('A2').value).toBe(24512)
    expect(ws.getCell('B2').value).toBeInstanceOf(Date)
  })

  it('shows as many decimals as the values need, and a percent cell its own', async () => {
    const { ws } = await oneSheet([
      ['p-value', 'Latitude', 'Score', 'Pct mix'],
      [0.0012, 40.416775, 12.5, '12.5%'],
      [0.00004, -3.7038, 3.25, 5],
    ])
    expect(ws.getCell('A2').numFmt).toBe('#,##0.00000')
    expect(ws.getCell('B2').numFmt).toBe('#,##0.000000')
    expect(ws.getCell('C2').numFmt).toBe('#,##0.00')
    expect(ws.getCell('D2').numFmt).toBe('0.0%')
  })

  it('refuses an image anchor outside the sheet with a message naming it', async () => {
    const chart = INTERNAL_TOOLS.find(t => t.name === 'clerum__generate_chart')!
    await chart.execute(
      { filename: 'c.png', type: 'bar', data: { labels: ['a'], datasets: [{ data: [1] }] } },
      dir
    )
    const result = await generate({
      filename: 'a.xlsx',
      sheets: [{ name: 'S', rows: [['a'], [1]], images: [{ path: 'c.png', anchor: 'ZZZ1' }] }],
    })
    expect(result.success).toBe(false)
    expect(result.error).toContain('Invalid cell anchor "ZZZ1"')
  })
})