Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

18.6 KB
Raw Blame
import { readFileSync } from 'node:fs';
import { createRequire } from 'node:module';
import { initSync } from '@ironcalc/wasm';
import { beforeAll, describe, expect, it } from 'vitest';
import {
  createSpreadsheetCalculator,
  type SpreadsheetCalculator,
  setCalculationClock,
} from './calculation';
import {
  SPREADSHEET_DEFAULT_STYLE,
  type SpreadsheetCells,
} from './spreadsheet-document';

function cells(values: Record<string, string>): SpreadsheetCells {
  return Object.fromEntries(
    Object.entries(values).map(([address, value]) => [address, { value }])
  );
}

let calculator: SpreadsheetCalculator;

beforeAll(async () => {
  const require = createRequire(import.meta.url);
  initSync({
    module: readFileSync(require.resolve('@ironcalc/wasm/wasm_bg.wasm')),
  });
  calculator = await createSpreadsheetCalculator();
});

describe('spreadsheet calculation with IronCalc', () => {
  it('copies formulas with relative, absolute, mixed, range and quoted references', () => {
    const edits = calculator.copy([
      {
        from: { row: 3, column: 3 },
        to: { row: 4, column: 3 },
        cell: { value: '=B4-C4', bold: true, format: 'currency' },
      },
      {
        from: { row: 3, column: 3 },
        to: { row: 4, column: 4 },
        cell: { value: '=$B$4+B$4+$B4+SUM(B4:C4)+IF(TRUE,0,LEN("B4"))' },
      },
      {
        from: { row: 3, column: 3 },
        to: { row: 2, column: 2 },
        cell: { value: '=A1' },
      },
      {
        from: { row: 0, column: 0 },
        to: { row: 200, column: 0 },
        cell: { value: "'00123" },
      },
    ]);
    expect(edits.D5).toEqual({
      ...SPREADSHEET_DEFAULT_STYLE,
      value: '=B5-C5',
      bold: true,
      format: 'currency',
    });
    expect(edits.E5?.value).toBe(
      '=$B$4+C$4+$B5+SUM(C5:D5)+IF(TRUE,0,LEN("B4"))'
    );
    expect(edits.C3?.value).toContain('#REF!');
    expect(edits.A201?.value).toBe("'00123");
  });

  it('calculates formulas and spill results in appended rows', () => {
    const result = calculator.calculate(
      cells({ A201: '21', B201: '=A201*2', A299: '=SEQUENCE(2)' }),
      300
    );
    expect(result.B201.number).toBe(42);
    expect(result.A300.number).toBe(2);
  });
  it('evaluates dependent formulas, ranges, absolute references and comparisons', () => {
    const result = calculator.calculate(
      cells({
        A1: '100',
        A2: '50',
        B1: '=SUM(A1:A2)',
        B2: '=$A$1*10%',
        C1: '=IF(B1>100, B1-B2, 0)',
        C2: '=AVERAGE(A1:A2)',
      })
    );
    expect(result.B1.number).toBe(150);
    expect(result.B2.number).toBe(10);
    expect(result.C1.number).toBe(140);
    expect(result.C2.number).toBe(75);
  });

  it('keeps formula dependencies independent of source key order', () => {
    const source = cells({ A1: '7', B1: '=A1*2', C1: '=B1+1' });
    expect(
      calculator.calculate(Object.fromEntries(Object.entries(source).reverse()))
    ).toEqual(calculator.calculate(source));
  });

  it('recalculates after edits and removes deleted values', () => {
    expect(
      calculator.calculate(cells({ A1: '5', B1: '=A1*2' })).B1.number
    ).toBe(10);
    expect(
      calculator.calculate(cells({ A1: '9', B1: '=A1*2' })).B1.number
    ).toBe(18);
    const cleared = calculator.calculate(cells({ B1: '=A1*2' }));
    expect(cleared.A1).toBeUndefined();
    expect(cleared.B1.number).toBe(0);
  });

  it('reports circular references and formula errors without losing other cells', () => {
    const result = calculator.calculate(
      cells({
        A1: '=B1',
        B1: '=A1',
        C1: '=1/0',
        D1: '=NO_SUCH_FUNCTION(1)',
        E1: '3',
      })
    );
    expect(result.A1.display).toBe('#CIRC!');
    expect(result.B1.error).toContain('circular');
    expect(result.C1.display).toBe('#DIV/0!');
    expect(result.D1.display).toBe('#NAME?');
    expect(result.E1.number).toBe(3);
  });

  it('propagates errors but evaluates IF lazily', () => {
    const result = calculator.calculate(
      cells({
        A1: '=1/0',
        B1: '=A1+1',
        C1: '=IF(TRUE,42,A1)',
        D1: '=IFERROR(A1,0)',
      })
    );
    expect(result.B1.display).toBe('#DIV/0!');
    expect(result.C1.number).toBe(42);
    expect(result.D1.number).toBe(0);
  });

  it('keeps text, escaped values and booleans distinct from numeric values', () => {
    const result = calculator.calculate(
      cells({ A1: "'00123", A2: 'hello', A3: 'TRUE', A4: '=A2&" world"' })
    );
    expect(result.A1).toEqual({ display: '00123' });
    expect(result.A2).toEqual({ display: 'hello' });
    expect(result.A3).toEqual({ display: 'TRUE' });
    expect(result.A4.display).toBe('hello world');
  });

  it('preserves formulas that intentionally display an empty string', () => {
    expect(calculator.calculate(cells({ A1: '=IF(TRUE,"",1)' })).A1).toEqual({
      display: '',
    });
  });

  it('formats values independently from the full-precision calculation', () => {
    const result = calculator.calculate({
      A1: { value: '=1/3', format: 'number' },
      A2: { value: '=A1*3', format: 'currency' },
      A3: { value: '0.12345', format: 'percent' },
      A4: { value: '=0.1+0.2' },
      A5: { value: '=1/10^20' },
      A6: { value: '$12.34' },
    });
    expect(result.A1.display).toBe('0.33');
    expect(result.A1.number).toBeCloseTo(1 / 3, 14);
    expect(result.A2).toEqual({ display: '$1.00', number: 1 });
    expect(result.A3.display).toBe('12.35%');
    expect(result.A4.number).toBe(0.3);
    expect(result.A5.number).toBe(1e-20);
    expect(result.A6.number).toBe(12.34);
  });

  it('formats decimals, serial dates, times and scientific notation without rounding dependencies', () => {
    const result = calculator.calculate({
      A1: { value: '=1/3', format: 'number', decimals: 4 },
      A2: { value: '=A1*3', format: 'currency', decimals: 0 },
      A3: { value: '0.125', format: 'percent', decimals: 3 },
      A4: { value: '=DATE(2026,9,17)', format: 'date' },
      A5: { value: '=TIME(13,30,15)', format: 'time' },
      A6: { value: '12345', format: 'scientific', decimals: 3 },
      A7: { value: '60', format: 'date' },
      A8: { value: '0', decimals: 2 },
    });
    expect(result.A1.display).toBe('0.3333');
    expect(result.A1.number).toBeCloseTo(1 / 3, 14);
    expect(result.A2).toEqual({ number: 1, display: '$1' });
    expect(result.A3.display).toBe('12.500%');
    expect(result.A4.display).toBe('9/17/2026');
    expect(result.A5.display).toBe('1:30:15 PM');
    expect(result.A6.display).toBe('1.235E4');
    expect(result.A7.display).toBe('2/29/1900');
    expect(result.A8.display).toBe('0.00');
  });

  it('formats accounting results with parentheses and a dash for zero', () => {
    const result = calculator.calculate({
      A1: { value: '1234.5', format: 'accounting' },
      A2: { value: '=-A1*2', format: 'accounting' },
      A3: { value: '=A1-1234.5', format: 'accounting' },
      A4: { value: '=A1', format: 'accounting', decimals: 0 },
    });
    expect(result.A1).toEqual({ display: '$1,234.50', number: 1234.5 });
    expect(result.A2).toEqual({ display: '$(2,469.00)', number: -2469 });
    expect(result.A3).toEqual({ display: '$-', number: 0 });
    expect(result.A4.display).toBe('$1,235');
  });

  it('reads results at Excel’s fifteen significant digits', () => {
    const result = calculator.calculate(
      cells({
        A1: '=979',
        A2: '=9104+3',
        A3: '=0.1+0.2',
        A4: '=1/3',
        A5: '=-9905',
        A6: '=2^60',
      })
    );
    expect(result.A1).toEqual({ number: 979, display: '979' });
    expect(result.A2).toEqual({ number: 9107, display: '9107' });
    expect(result.A3.number).toBe(0.3);
    expect(result.A4.number).toBe(0.333333333333333);
    expect(result.A5.number).toBe(-9905);
    expect(result.A6.number).toBe(1.15292150460685e18);
  });

  it('counts with double negation over arrays, as Excel does', () => {
    const result = calculator.calculate(
      cells({
        A1: 'x',
        A2: 'y',
        A3: 'x',
        B1: '5',
        B2: '2',
        C1: '=SUMPRODUCT(--(A1:A3="x"))',
        C2: '=SUMPRODUCT(--(A1:A3="x"),B1:B3)',
        C3: '=SUMPRODUCT(--(--(A1:A3="x")))',
        C4: '=SUMPRODUCT(- -(A1:A3="y"))',
        // Subtraction, precedence and literals keep their meaning.
        D1: '=B1--B2',
        D2: '=2^--B2',
        D3: '=B1/--B2',
        D4: '=--"5"+1',
        D5: '="--"&A1',
        D6: '=--B1%',
        D7: '=--A1',
      })
    );
    expect(result.C1.number).toBe(2);
    expect(result.C2.number).toBe(5);
    expect(result.C3.number).toBe(2);
    expect(result.C4.number).toBe(1);
    expect(result.D1.number).toBe(7);
    expect(result.D2.number).toBe(4);
    expect(result.D3.number).toBe(2.5);
    expect(result.D4.number).toBe(6);
    expect(result.D5.display).toBe('--x');
    expect(result.D6.number).toBe(0.05);
    expect(result.D7.display).toBe('#VALUE!');
  });

  it('shows typed dates and date arithmetic as dates without an explicit format', () => {
    const result = calculator.calculate({
      A1: { value: '9/28/2026' },
      A2: { value: '2026-09-28' },
      A3: { value: '=A1+7' },
      A4: { value: '=DATE(2026,1,1)' },
      A5: { value: '=A3-A1' },
      A6: { value: '=A1+7', format: 'number' },
      A7: { value: '5/1/1985' },
      A8: { value: '=DATE(1985,5,1)' },
      A9: { value: '=A1+1' },
      A10: { value: '=A9+1' },
      A11: { value: '46293' },
      A12: { value: 'Sep 28, 2026' },
      A13: { value: '=YEAR(A1)' },
      A14: { value: '=A1+7', numberFormat: '0.0' },
    });
    expect(result.A1).toEqual({
      display: '9/28/2026',
      number: 46293,
      date: true,
    });
    expect(result.A2).toEqual({
      display: '9/28/2026',
      number: 46293,
      date: true,
    });
    expect(result.A3).toEqual({
      display: '10/5/2026',
      number: 46300,
      date: true,
    });
    expect(result.A4).toEqual({
      display: '1/1/2026',
      number: 46023,
      date: true,
    });
    // The engine formats a difference of dates as a date; a day count is not.
    expect(result.A5).toEqual({ display: '7', number: 7 });
    expect(result.A6.display).toBe('46,300.00');
    expect(result.A7).toEqual({
      display: '5/1/1985',
      number: 31168,
      date: true,
    });
    // Formula results before 2000 stay serial numbers; day counts are common there.
    expect(result.A8).toEqual({ display: '31168', number: 31168 });
    // Chained formulas are entered in grid order, so each inherits the format.
    expect(result.A9).toEqual({
      display: '9/29/2026',
      number: 46294,
      date: true,
    });
    expect(result.A10).toEqual({
      display: '9/30/2026',
      number: 46295,
      date: true,
    });
    expect(result.A11).toEqual({ display: '46293', number: 46293 });
    expect(result.A12).toEqual({ display: 'Sep 28, 2026' });
    expect(result.A13).toEqual({ display: '2026', number: 2026 });
    expect(result.A14.display).toBe('46300.0');
  });

  it('marks numbers shown as calendar dates, not times or plain numbers', () => {
    const result = calculator.calculate({
      A1: { value: '46293', format: 'date' },
      A2: { value: '46293.5', numberFormat: 'mmm d, yyyy h:mm' },
      A3: { value: '0.5', format: 'time' },
      A4: { value: '0.5', numberFormat: 'h:mm' },
      A5: { value: '46293', numberFormat: '#,##0 "days"' },
      A6: { value: '$1,000' },
    });
    expect(result.A1.date).toBe(true);
    expect(result.A2).toEqual({
      display: 'Sep 28, 2026 12:00',
      number: 46293.5,
      date: true,
    });
    for (const address of ['A3', 'A4', 'A5', 'A6'])
      expect(result[address].date).toBeUndefined();
  });

  it('shows serials in date formats as dates, and formulas on them inherit a date display', () => {
    const result = calculator.calculate({
      A1: { value: '46023', format: 'date', numberFormat: 'mmm yyyy' },
      A2: { value: '=A1+31' },
      A3: {
        value: '46282',
        format: 'date',
        numberFormat: 'dddd, mmmm d, yyyy',
      },
      A4: { value: '=A3-A1' },
      A5: { value: '46023', format: 'date' },
      A6: { value: '=A1+31', format: 'number', decimals: 0 },
    });
    expect(result.A1).toEqual({
      display: 'Jan 2026',
      number: 46023,
      date: true,
    });
    expect(result.A2).toEqual({
      display: '2/1/2026',
      number: 46054,
      date: true,
    });
    expect(result.A3).toEqual({
      display: 'Thursday, September 17, 2026',
      number: 46282,
      date: true,
    });
    expect(result.A4).toEqual({ display: '259', number: 259 });
    expect(result.A5).toEqual({
      display: '1/1/2026',
      number: 46023,
      date: true,
    });
    expect(result.A6.display).toBe('46,054');
  });

  it('keeps text-formatted values literal during calculation and copying', () => {
    const result = calculator.calculate({
      A1: { value: '=SUM(B1:B2)', format: 'text' },
      A2: { value: '00123', format: 'text' },
      A3: { value: '=RAND()', format: 'text' },
      A4: { value: 'TRUE', format: 'text' },
      A5: { value: '=ISTEXT(A2)' },
    });
    expect(result.A1).toEqual({ display: '=SUM(B1:B2)' });
    expect(result.A2).toEqual({ display: '00123' });
    expect(result.A3).toEqual({ display: '=RAND()' });
    expect(result.A4).toEqual({ display: 'TRUE' });
    expect(result.A5).toEqual({ display: 'TRUE' });
    const copied = calculator.copy([
      {
        from: { row: 0, column: 0 },
        to: { row: 1, column: 0 },
        cell: {
          value: '=B1',
          format: 'text',
          italic: true,
          fontSize: 20,
          fillColor: '#123456',
          borderTop: true,
        },
      },
    ]);
    expect(copied.A2).toEqual({
      ...SPREADSHEET_DEFAULT_STYLE,
      value: '=B1',
      format: 'text',
      italic: true,
      fontSize: 20,
      fillColor: '#123456',
      borderTop: true,
    });
    expect(
      calculator.copy([
        {
          from: { row: 0, column: 0 },
          to: { row: 1, column: 0 },
          cell: { value: '' },
        },
      ]).A2
    ).toEqual({ ...SPREADSHEET_DEFAULT_STYLE, value: '' });
  });

  it('includes array spills and detects blocked spill ranges', () => {
    const result = calculator.calculate(cells({ A1: '=SEQUENCE(2,2)' }));
    expect(result.A1.number).toBe(1);
    expect(result.B1.number).toBe(2);
    expect(result.A2.number).toBe(3);
    expect(result.B2.number).toBe(4);
    expect(
      calculator.calculate(cells({ A1: '=SEQUENCE(2,2)', B2: 'occupied' })).A1
        .display
    ).toBe('#SPILL!');
  });

  it('reads TODAY and NOW from the calculation clock and repeats a seeded RAND', () => {
    // 23 July 2026, 10:30 UTC: serial 46226.4375.
    setCalculationClock({
      time: Date.UTC(2026, 6, 23, 10, 30),
      timezone: 'UTC',
      seed: 7,
    });
    try {
      const sources = cells({
        A1: '=TODAY()',
        A2: '=NOW()',
        A3: '=RAND()',
        A4: '=RANDBETWEEN(1,6)',
        A5: '=_xlfn.RAND()<1',
        A6: '="RAND()"',
        A7: '=TEXT(TODAY(),"yyyy-mm-dd")',
      });
      const result = calculator.calculate(sources);
      expect(result.A1.number).toBe(46226);
      expect(result.A2.number).toBeCloseTo(46226.4375, 9);
      expect(result.A3.number).toBeGreaterThanOrEqual(0);
      expect(result.A3.number).toBeLessThan(1);
      expect([1, 2, 3, 4, 5, 6]).toContain(result.A4.number);
      expect(result.A5.display).toBe('TRUE');
      expect(result.A6).toEqual({ display: 'RAND()' });
      expect(result.A7.display).toBe('2026-07-23');
      setCalculationClock({
        time: Date.UTC(2026, 6, 23, 10, 30),
        timezone: 'UTC',
        seed: 7,
      });
      expect(calculator.calculate(sources).A3.number).toBe(result.A3.number);
    } finally {
      setCalculationClock(undefined);
    }
  });

  it('reads TODAY in the time zone of the clock', () => {
    // 02:00 UTC on 23 July 2026 is still 22 July in New York.
    setCalculationClock({
      time: Date.UTC(2026, 6, 23, 2),
      timezone: 'America/New_York',
    });
    try {
      expect(calculator.calculate(cells({ A1: '=TODAY()' })).A1.number).toBe(
        46225
      );
    } finally {
      setCalculationClock(undefined);
    }
  });

  it('ignores source addresses outside the supported grid', () => {
    const result = calculator.calculate(
      cells({ A0: '1', A201: '2', XFE1: '3' })
    );
    expect(result).toEqual({});
  });

  it('calculates cross-sheet references, quoted names, and spills in one workbook', () => {
    const result = calculator.calculateWorkbook([
      {
        id: 'budget',
        name: 'Budget 2026',
        rowCount: 200,
        cells: cells({ A1: '12', A2: '8', B1: '=SUM(A1:A2)' }),
      },
      {
        id: 'summary',
        name: 'Summary',
        rowCount: 200,
        cells: cells({
          A1: "='Budget 2026'!B1*2",
          B1: '=SEQUENCE(2)',
          C1: '=B2+A1',
        }),
      },
    ]);
    expect(result.budget.B1.number).toBe(20);
    expect(result.summary.A1.number).toBe(40);
    expect(result.summary.B2.number).toBe(2);
    expect(result.summary.C1.number).toBe(42);
  });

  it('resolves reversed default sheet names and circular cross-sheet dependencies', () => {
    const result = calculator.calculateWorkbook([
      {
        id: 'two',
        name: 'Sheet2',
        rowCount: 200,
        cells: cells({ A1: '=Sheet1!A1', B1: '=Sheet1!B1' }),
      },
      {
        id: 'one',
        name: 'Sheet1',
        rowCount: 200,
        cells: cells({ A1: '42', B1: '=Sheet2!B1' }),
      },
    ]);
    expect(result.two.A1.number).toBe(42);
    expect(result.two.B1.display).toBe('#CIRC!');
    expect(result.one.B1.display).toBe('#CIRC!');
  });

  it('translates references with the active workbook names during copying', () => {
    const result = calculator.copy(
      [
        {
          from: { row: 0, column: 0 },
          to: { row: 1, column: 0 },
          cell: { value: "='Budget 2026'!B4+Sheet2!$C$2" },
        },
      ],
      { sheetNames: ['Budget 2026', 'Sheet2'], activeSheet: 1 }
    );
    expect(result.A2?.value).toBe("='Budget 2026'!B5+Sheet2!$C$2");
  });

  it('calculates a full-size ten-sheet workbook with cross-sheet dependencies', () => {
    const sheets = Array.from({ length: 10 }, (_, index) => ({
      id: `sheet-${index}`,
      name: `Sheet${index + 1}`,
      rowCount: 1000,
      cells: cells({
        A1: String(index + 1),
        A1000: index ? `=Sheet${index}!A1000+A1` : '=A1',
      }),
    }));
    const result = calculator.calculateWorkbook(sheets);
    expect(Object.keys(result)).toHaveLength(10);
    expect(result['sheet-9'].A1000.number).toBe(55);
  }, 20_000);

  it('frees a disposed calculator without affecting another editor', async () => {
    const other = await createSpreadsheetCalculator();
    other.dispose();
    other.dispose();
    expect(() => other.calculate({})).toThrow('disposed');
    expect(calculator.calculate(cells({ A1: '42' })).A1.number).toBe(42);
  });
});