Full document, spreadsheet, slideshow, and diagram tooling
1import { describe, it, expect } from 'vitest';
2import { evaluate, parseRef, colToLetter, letterToCol, cellId, formatCell } from '../src/sheets/formulas.js';
3
4// Helper: evaluate with a simple cell map
5function evalWith(formula, cells = {}) {
6 return evaluate(formula, (ref) => cells[ref] ?? '');
7}
8
9/**
10 * Extended formula tests — VLOOKUP/HLOOKUP edge cases, CSV-related functions,
11 * large datasets, mixed types, and additional function coverage.
12 */
13
14describe('VLOOKUP — edge cases', () => {
15 it('handles single-row table', () => {
16 const cells = { A1: 'x', B1: 100 };
17 expect(evalWith('VLOOKUP("x",A1:B1,2,FALSE)', cells)).toBe(100);
18 });
19
20 it('handles single-column lookup (col_index_num = 1)', () => {
21 const cells = { A1: 'apple', A2: 'banana', A3: 'cherry' };
22 expect(evalWith('VLOOKUP("banana",A1:A3,1,FALSE)', cells)).toBe('banana');
23 });
24
25 it('exact match with numeric 0', () => {
26 const cells = { A1: 0, B1: 'zero', A2: 1, B2: 'one' };
27 expect(evalWith('VLOOKUP(0,A1:B2,2,FALSE)', cells)).toBe('zero');
28 });
29
30 it('approximate match with values at boundary', () => {
31 const cells = { A1: 10, B1: 'a', A2: 20, B2: 'b', A3: 30, B3: 'c' };
32 // Looking for 10 exactly
33 expect(evalWith('VLOOKUP(10,A1:B3,2,TRUE)', cells)).toBe('a');
34 // Looking for 30 exactly
35 expect(evalWith('VLOOKUP(30,A1:B3,2,TRUE)', cells)).toBe('c');
36 });
37
38 it('approximate match with value beyond all entries', () => {
39 const cells = { A1: 10, B1: 'a', A2: 20, B2: 'b', A3: 30, B3: 'c' };
40 // 100 is beyond all entries, should return last row's value
41 expect(evalWith('VLOOKUP(100,A1:B3,2,TRUE)', cells)).toBe('c');
42 });
43
44 it('empty string in lookup column', () => {
45 const cells = { A1: '', B1: 'empty', A2: 'x', B2: 'found' };
46 expect(evalWith('VLOOKUP("",A1:B2,2,FALSE)', cells)).toBe('empty');
47 });
48
49 it('case-insensitive string matching', () => {
50 const cells = { A1: 'APPLE', B1: 1, A2: 'banana', B2: 2 };
51 expect(evalWith('VLOOKUP("apple",A1:B2,2,FALSE)', cells)).toBe(1);
52 expect(evalWith('VLOOKUP("BANANA",A1:B2,2,FALSE)', cells)).toBe(2);
53 });
54
55 it('numeric string vs number matching', () => {
56 const cells = { A1: 42, B1: 'found' };
57 // "42" should match 42
58 expect(evalWith('VLOOKUP("42",A1:B1,2,FALSE)', cells)).toBe('found');
59 });
60
61 it('wide table (5 columns)', () => {
62 const cells = {
63 A1: 'id1', B1: 'n1', C1: 10, D1: true, E1: 'x',
64 A2: 'id2', B2: 'n2', C2: 20, D2: false, E2: 'y',
65 A3: 'id3', B3: 'n3', C3: 30, D3: true, E3: 'z',
66 };
67 expect(evalWith('VLOOKUP("id2",A1:E3,3,FALSE)', cells)).toBe(20);
68 expect(evalWith('VLOOKUP("id2",A1:E3,5,FALSE)', cells)).toBe('y');
69 expect(evalWith('VLOOKUP("id3",A1:E3,4,FALSE)', cells)).toBe(true);
70 });
71});
72
73describe('HLOOKUP — edge cases', () => {
74 it('handles single-column table', () => {
75 const cells = { A1: 'x', A2: 100 };
76 expect(evalWith('HLOOKUP("x",A1:A2,2,FALSE)', cells)).toBe(100);
77 });
78
79 it('handles single-row lookup (row_index_num = 1)', () => {
80 const cells = { A1: 'a', B1: 'b', C1: 'c' };
81 expect(evalWith('HLOOKUP("b",A1:C1,1,FALSE)', cells)).toBe('b');
82 });
83
84 it('approximate match with value beyond all entries', () => {
85 const cells = { A1: 10, B1: 20, C1: 30, A2: 'a', B2: 'b', C2: 'c' };
86 expect(evalWith('HLOOKUP(100,A1:C2,2,TRUE)', cells)).toBe('c');
87 });
88
89 it('empty string in header row', () => {
90 const cells = { A1: '', B1: 'x', A2: 'empty', B2: 'found' };
91 expect(evalWith('HLOOKUP("",A1:B2,2,FALSE)', cells)).toBe('empty');
92 });
93
94 it('tall table (5 rows)', () => {
95 const cells = {
96 A1: 'q1', B1: 'q2', C1: 'q3',
97 A2: 10, B2: 20, C2: 30,
98 A3: 11, B3: 21, C3: 31,
99 A4: 12, B4: 22, C4: 32,
100 A5: 13, B5: 23, C5: 33,
101 };
102 expect(evalWith('HLOOKUP("q2",A1:C5,4,FALSE)', cells)).toBe(22);
103 expect(evalWith('HLOOKUP("q3",A1:C5,5,FALSE)', cells)).toBe(33);
104 });
105});
106
107describe('Large dataset formulas', () => {
108 it('SUM of 100 cells', () => {
109 const cells = {};
110 for (let i = 1; i <= 100; i++) {
111 cells[`A${i}`] = i;
112 }
113 // Sum 1..100 = 5050
114 expect(evalWith('SUM(A1:A100)', cells)).toBe(5050);
115 });
116
117 it('AVERAGE of 100 cells', () => {
118 const cells = {};
119 for (let i = 1; i <= 100; i++) {
120 cells[`A${i}`] = i;
121 }
122 expect(evalWith('AVERAGE(A1:A100)', cells)).toBe(50.5);
123 });
124
125 it('COUNT of 50 cells with gaps', () => {
126 const cells = {};
127 for (let i = 1; i <= 50; i++) {
128 cells[`A${i}`] = i % 2 === 0 ? i : ''; // even numbers only
129 }
130 expect(evalWith('COUNT(A1:A50)', cells)).toBe(25);
131 });
132
133 it('MAX/MIN of mixed positive and negative values', () => {
134 const cells = {};
135 for (let i = 1; i <= 20; i++) {
136 cells[`A${i}`] = i - 10; // -9 to 10
137 }
138 expect(evalWith('MAX(A1:A20)', cells)).toBe(10);
139 expect(evalWith('MIN(A1:A20)', cells)).toBe(-9);
140 });
141});
142
143describe('SUBSTITUTE function', () => {
144 it('replaces all occurrences without instance number', () => {
145 expect(evalWith('SUBSTITUTE("hello world hello","hello","hi")')).toBe('hi world hi');
146 });
147
148 it('replaces first occurrence with instance=1', () => {
149 const result = evalWith('SUBSTITUTE("aaa","a","b",1)');
150 expect(result).toBe('baa');
151 });
152
153 it('replaces second occurrence with instance=2', () => {
154 const result = evalWith('SUBSTITUTE("apple apple apple","apple","banana",2)');
155 expect(result).toBe('apple banana apple');
156 });
157
158 it('replaces third occurrence with instance=3', () => {
159 const result = evalWith('SUBSTITUTE("aaa","a","b",3)');
160 expect(result).toBe('aab');
161 });
162
163 it('returns unchanged if instance exceeds count', () => {
164 const result = evalWith('SUBSTITUTE("aaa","a","b",5)');
165 expect(result).toBe('aaa');
166 });
167
168 it('handles empty replacement', () => {
169 expect(evalWith('SUBSTITUTE("hello world","world","")')).toBe('hello ');
170 });
171});
172
173describe('TEXT function', () => {
174 it('formats with "0" format', () => {
175 expect(evalWith('TEXT(3.14,"0")')).toBe('3');
176 });
177
178 it('formats with "0.00" format', () => {
179 expect(evalWith('TEXT(3.14159,"0.00")')).toBe('3.14');
180 });
181
182 it('formats with "0%" format', () => {
183 expect(evalWith('TEXT(0.75,"0%")')).toBe('75%');
184 });
185
186 it('formats with "0.00%" format', () => {
187 expect(evalWith('TEXT(0.1234,"0.00%")')).toBe('12.34%');
188 });
189
190 it('formats with "0.0" (1 decimal)', () => {
191 expect(evalWith('TEXT(3.14159,"0.0")')).toBe('3.1');
192 });
193
194 it('formats with "0.000" (3 decimals)', () => {
195 expect(evalWith('TEXT(3.14159,"0.000")')).toBe('3.142');
196 });
197
198 it('formats with "#,##0.00" (comma + 2 decimals)', () => {
199 const result = evalWith('TEXT(1234567.89,"#,##0.00")');
200 expect(result).toContain('234');
201 expect(result).toContain('567');
202 expect(result).toContain('89');
203 });
204
205 it('formats with "#" (plain integer)', () => {
206 expect(evalWith('TEXT(7.9,"#")')).toBe('8');
207 });
208
209 it('formats with "0.0%" (1 decimal percent)', () => {
210 expect(evalWith('TEXT(0.1234,"0.0%")')).toBe('12.3%');
211 });
212
213 it('formats with scientific "0.00E+00"', () => {
214 const result = evalWith('TEXT(12345,"0.00E+00")');
215 expect(result).toMatch(/1\.23E\+4/i);
216 });
217});
218
219describe('FIND/SEARCH functions', () => {
220 it('SEARCH finds substring (case-insensitive)', () => {
221 expect(evalWith('SEARCH("world","Hello World")')).toBe(7);
222 });
223
224 it('SEARCH returns #VALUE! when not found', () => {
225 expect(evalWith('SEARCH("xyz","Hello World")')).toBe('#VALUE!');
226 });
227
228 it('FIND with start position', () => {
229 // Start searching from position 5
230 expect(evalWith('FIND("o","Hello World",5)')).toBe(5);
231 });
232});
233
234describe('AVERAGEIF function', () => {
235 it('averages values meeting criteria', () => {
236 const cells = { A1: 10, A2: 20, A3: 30, B1: 100, B2: 200, B3: 300 };
237 expect(evalWith('AVERAGEIF(A1:A3,">15",B1:B3)', cells)).toBe(250); // (200+300)/2
238 });
239
240 it('returns #DIV/0! for no matching criteria', () => {
241 const cells = { A1: 1, A2: 2, A3: 3 };
242 expect(evalWith('AVERAGEIF(A1:A3,">100")', cells)).toBe('#DIV/0!');
243 });
244});
245
246describe('ROUNDUP / ROUNDDOWN', () => {
247 it('ROUNDUP rounds up', () => {
248 expect(evalWith('ROUNDUP(3.21,1)')).toBeCloseTo(3.3);
249 expect(evalWith('ROUNDUP(3.14,0)')).toBe(4);
250 });
251
252 it('ROUNDDOWN rounds down', () => {
253 expect(evalWith('ROUNDDOWN(3.99,1)')).toBeCloseTo(3.9);
254 expect(evalWith('ROUNDDOWN(3.99,0)')).toBe(3);
255 });
256});
257
258describe('LOG / LN / EXP', () => {
259 it('LOG with one arg is log base 10', () => {
260 expect(evalWith('LOG(100)')).toBeCloseTo(2);
261 expect(evalWith('LOG(1000)')).toBeCloseTo(3);
262 });
263
264 it('LOG with two args is log base N', () => {
265 expect(evalWith('LOG(8,2)')).toBeCloseTo(3);
266 expect(evalWith('LOG(27,3)')).toBeCloseTo(3);
267 });
268
269 it('LN is natural log', () => {
270 expect(evalWith('LN(1)')).toBeCloseTo(0);
271 expect(evalWith(`LN(${Math.E})`)).toBeCloseTo(1);
272 });
273
274 it('EXP is e^x', () => {
275 expect(evalWith('EXP(0)')).toBe(1);
276 expect(evalWith('EXP(1)')).toBeCloseTo(Math.E);
277 });
278});
279
280describe('PI function', () => {
281 it('returns pi', () => {
282 expect(evalWith('PI()')).toBeCloseTo(3.14159, 4);
283 });
284});
285
286describe('COUNTA function', () => {
287 it('counts all non-empty values including strings', () => {
288 const cells = { A1: 1, A2: 'text', A3: 0, A4: true };
289 expect(evalWith('COUNTA(A1:A4)', cells)).toBe(4);
290 });
291
292 it('excludes empty strings/null/undefined', () => {
293 const cells = { A1: 1, A2: '', A3: 0 };
294 // A2 is empty string, gets filtered by flat()
295 expect(evalWith('COUNTA(A1:A3)', cells)).toBe(2); // 1 and 0
296 });
297});
298
299describe('Boolean operations in formulas', () => {
300 it('TRUE and FALSE are parsed as booleans', () => {
301 expect(evalWith('TRUE')).toBe(true);
302 expect(evalWith('FALSE')).toBe(false);
303 });
304
305 it('boolean arithmetic converts to 1/0', () => {
306 expect(evalWith('TRUE+TRUE')).toBe(2);
307 expect(evalWith('FALSE+1')).toBe(1);
308 expect(evalWith('TRUE*10')).toBe(10);
309 });
310});
311
312describe('String edge cases in evaluate', () => {
313 it('handles escaped quotes in strings', () => {
314 expect(evalWith('"hello \\"world\\""')).toBe('hello "world"');
315 });
316
317 it('empty string literal', () => {
318 expect(evalWith('""')).toBe('');
319 });
320
321 it('concatenation of numbers and strings', () => {
322 expect(evalWith('42&" items"')).toBe('42 items');
323 expect(evalWith('"Total: "&100')).toBe('Total: 100');
324 });
325});
326
327describe('formatCell — extended', () => {
328 it('formats number type', () => {
329 const result = formatCell(1234.5, 'number');
330 expect(result).toContain('1');
331 expect(result).toContain('234');
332 expect(result).toContain('50');
333 });
334
335 it('formats text type', () => {
336 expect(formatCell(42, 'text')).toBe('42');
337 expect(formatCell('hello', 'text')).toBe('hello');
338 });
339
340 it('formats undefined value', () => {
341 expect(formatCell(undefined, 'auto')).toBe('');
342 });
343
344 it('integer displays without decimals in auto mode', () => {
345 expect(formatCell(42, 'auto')).toBe('42');
346 expect(formatCell(0, 'auto')).toBe('0');
347 expect(formatCell(-7, 'auto')).toBe('-7');
348 });
349
350 it('float displays with 2 decimal places in auto mode', () => {
351 expect(formatCell(3.14159, 'auto')).toBe('3.14');
352 expect(formatCell(0.1, 'auto')).toBe('0.10');
353 });
354
355 it('passes through error strings unchanged', () => {
356 expect(formatCell('#N/A', 'currency')).toBe('#N/A');
357 expect(formatCell('#REF!', 'percent')).toBe('#REF!');
358 expect(formatCell('#VALUE!', 'auto')).toBe('#VALUE!');
359 });
360});