1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
###############################################################################
#
# Tests for Excel::Writer::XLSX::Worksheet methods.
#
# Copyright 2000-2021, John McNamara, jmcnamara@cpan.org
#
use strict;
use warnings;
use Excel::Writer::XLSX::Worksheet;
use Test::More tests => 145;
###############################################################################
#
# Test formula string preparation and expansion.
#
my $got;
my $expected;
my $caption = " \tWorksheet: _prepare_formula()";
my $worksheet = new Excel::Writer::XLSX::Worksheet;
$worksheet->{_use_future_functions} = 1;
my @testcases = (
['=foo()', 'foo()'],
['{foo()}', 'foo()'],
['{=foo()}', 'foo()'],
# Dynamic array functions.
['LET()', '_xlfn.LET()'],
['SEQUENCE(10)', '_xlfn.SEQUENCE(10)'],
['UNIQUES(A1:A10)', 'UNIQUES(A1:A10)'],
['UUNIQUE(A1:A10)', 'UUNIQUE(A1:A10)'],
['SINGLE(A1:A3)', '_xlfn.SINGLE(A1:A3)'],
['UNIQUE(A1:A10)', '_xlfn.UNIQUE(A1:A10)'],
['_xlfn.SEQUENCE(10)', '_xlfn.SEQUENCE(10)'],
['SORT(A1:A10)', '_xlfn._xlws.SORT(A1:A10)'],
['RANDARRAY(10,1)', '_xlfn.RANDARRAY(10,1)'],
['ANCHORARRAY(C1)', '_xlfn.ANCHORARRAY(C1)'],
['SORTBY(A1:A10,B1)', '_xlfn.SORTBY(A1:A10,B1)'],
['FILTER(A1:A10,1)', '_xlfn._xlws.FILTER(A1:A10,1)'],
['XMATCH(B1:B2,A1:A10)', '_xlfn.XMATCH(B1:B2,A1:A10)'],
['COUNTA(ANCHORARRAY(C1))', 'COUNTA(_xlfn.ANCHORARRAY(C1))'],
['SEQUENCE(10)*SEQUENCE(10)', '_xlfn.SEQUENCE(10)*_xlfn.SEQUENCE(10)'],
['XLOOKUP("India",A22:A23,B22:B23)', '_xlfn.XLOOKUP("India",A22:A23,B22:B23)'],
['XLOOKUP(B1,A1:A10,ANCHORARRAY(D1))', '_xlfn.XLOOKUP(B1,A1:A10,_xlfn.ANCHORARRAY(D1))'],
['LAMBDA(_xlpm.number, _xlpm.number + 1)(1)', '_xlfn.LAMBDA(_xlpm.number, _xlpm.number + 1)(1)'],
# Future functions.
['COT()', '_xlfn.COT()'],
['CSC()', '_xlfn.CSC()'],
['IFS()', '_xlfn.IFS()'],
['PHI()', '_xlfn.PHI()'],
['RRI()', '_xlfn.RRI()'],
['SEC()', '_xlfn.SEC()'],
['XOR()', '_xlfn.XOR()'],
['ACOT()', '_xlfn.ACOT()'],
['BASE()', '_xlfn.BASE()'],
['COTH()', '_xlfn.COTH()'],
['CSCH()', '_xlfn.CSCH()'],
['DAYS()', '_xlfn.DAYS()'],
['IFNA()', '_xlfn.IFNA()'],
['SECH()', '_xlfn.SECH()'],
['ACOTH()', '_xlfn.ACOTH()'],
['BITOR()', '_xlfn.BITOR()'],
['F.INV()', '_xlfn.F.INV()'],
['GAMMA()', '_xlfn.GAMMA()'],
['GAUSS()', '_xlfn.GAUSS()'],
['IMCOT()', '_xlfn.IMCOT()'],
['IMCSC()', '_xlfn.IMCSC()'],
['IMSEC()', '_xlfn.IMSEC()'],
['IMTAN()', '_xlfn.IMTAN()'],
['MUNIT()', '_xlfn.MUNIT()'],
['SHEET()', '_xlfn.SHEET()'],
['T.INV()', '_xlfn.T.INV()'],
['VAR.P()', '_xlfn.VAR.P()'],
['VAR.S()', '_xlfn.VAR.S()'],
['ARABIC()', '_xlfn.ARABIC()'],
['BITAND()', '_xlfn.BITAND()'],
['BITXOR()', '_xlfn.BITXOR()'],
['CONCAT()', '_xlfn.CONCAT()'],
['F.DIST()', '_xlfn.F.DIST()'],
['F.TEST()', '_xlfn.F.TEST()'],
['IMCOSH()', '_xlfn.IMCOSH()'],
['IMCSCH()', '_xlfn.IMCSCH()'],
['IMSECH()', '_xlfn.IMSECH()'],
['IMSINH()', '_xlfn.IMSINH()'],
['MAXIFS()', '_xlfn.MAXIFS()'],
['MINIFS()', '_xlfn.MINIFS()'],
['SHEETS()', '_xlfn.SHEETS()'],
['SKEW.P()', '_xlfn.SKEW.P()'],
['SWITCH()', '_xlfn.SWITCH()'],
['T.DIST()', '_xlfn.T.DIST()'],
['T.TEST()', '_xlfn.T.TEST()'],
['Z.TEST()', '_xlfn.Z.TEST()'],
['COMBINA()', '_xlfn.COMBINA()'],
['DECIMAL()', '_xlfn.DECIMAL()'],
['RANK.EQ()', '_xlfn.RANK.EQ()'],
['STDEV.P()', '_xlfn.STDEV.P()'],
['STDEV.S()', '_xlfn.STDEV.S()'],
['UNICHAR()', '_xlfn.UNICHAR()'],
['UNICODE()', '_xlfn.UNICODE()'],
['BETA.INV()', '_xlfn.BETA.INV()'],
['F.INV.RT()', '_xlfn.F.INV.RT()'],
['ISO.CEILING()', 'ISO.CEILING()'],
['NORM.INV()', '_xlfn.NORM.INV()'],
['RANK.AVG()', '_xlfn.RANK.AVG()'],
['T.INV.2T()', '_xlfn.T.INV.2T()'],
['TEXTJOIN()', '_xlfn.TEXTJOIN()'],
['AGGREGATE()', '_xlfn.AGGREGATE()'],
['BETA.DIST()', '_xlfn.BETA.DIST()'],
['BINOM.INV()', '_xlfn.BINOM.INV()'],
['BITLSHIFT()', '_xlfn.BITLSHIFT()'],
['BITRSHIFT()', '_xlfn.BITRSHIFT()'],
['CHISQ.INV()', '_xlfn.CHISQ.INV()'],
['ECMA.CEILING()', 'ECMA.CEILING()'],
['F.DIST.RT()', '_xlfn.F.DIST.RT()'],
['FILTERXML()', '_xlfn.FILTERXML()'],
['GAMMA.INV()', '_xlfn.GAMMA.INV()'],
['ISFORMULA()', '_xlfn.ISFORMULA()'],
['MODE.MULT()', '_xlfn.MODE.MULT()'],
['MODE.SNGL()', '_xlfn.MODE.SNGL()'],
['NORM.DIST()', '_xlfn.NORM.DIST()'],
['PDURATION()', '_xlfn.PDURATION()'],
['T.DIST.2T()', '_xlfn.T.DIST.2T()'],
['T.DIST.RT()', '_xlfn.T.DIST.RT()'],
['WORKDAY.INTL()', 'WORKDAY.INTL()'],
['BINOM.DIST()', '_xlfn.BINOM.DIST()'],
['CHISQ.DIST()', '_xlfn.CHISQ.DIST()'],
['CHISQ.TEST()', '_xlfn.CHISQ.TEST()'],
['EXPON.DIST()', '_xlfn.EXPON.DIST()'],
['FLOOR.MATH()', '_xlfn.FLOOR.MATH()'],
['GAMMA.DIST()', '_xlfn.GAMMA.DIST()'],
['ISOWEEKNUM()', '_xlfn.ISOWEEKNUM()'],
['NORM.S.INV()', '_xlfn.NORM.S.INV()'],
['WEBSERVICE()', '_xlfn.WEBSERVICE()'],
['ERF.PRECISE()', '_xlfn.ERF.PRECISE()'],
['FORMULATEXT()', '_xlfn.FORMULATEXT()'],
['LOGNORM.INV()', '_xlfn.LOGNORM.INV()'],
['NORM.S.DIST()', '_xlfn.NORM.S.DIST()'],
['NUMBERVALUE()', '_xlfn.NUMBERVALUE()'],
['QUERYSTRING()', '_xlfn.QUERYSTRING()'],
['CEILING.MATH()', '_xlfn.CEILING.MATH()'],
['CHISQ.INV.RT()', '_xlfn.CHISQ.INV.RT()'],
['CONFIDENCE.T()', '_xlfn.CONFIDENCE.T()'],
['COVARIANCE.P()', '_xlfn.COVARIANCE.P()'],
['COVARIANCE.S()', '_xlfn.COVARIANCE.S()'],
['ERFC.PRECISE()', '_xlfn.ERFC.PRECISE()'],
['FORECAST.ETS()', '_xlfn.FORECAST.ETS()'],
['HYPGEOM.DIST()', '_xlfn.HYPGEOM.DIST()'],
['LOGNORM.DIST()', '_xlfn.LOGNORM.DIST()'],
['PERMUTATIONA()', '_xlfn.PERMUTATIONA()'],
['POISSON.DIST()', '_xlfn.POISSON.DIST()'],
['QUARTILE.EXC()', '_xlfn.QUARTILE.EXC()'],
['QUARTILE.INC()', '_xlfn.QUARTILE.INC()'],
['WEIBULL.DIST()', '_xlfn.WEIBULL.DIST()'],
['CHISQ.DIST.RT()', '_xlfn.CHISQ.DIST.RT()'],
['FLOOR.PRECISE()', '_xlfn.FLOOR.PRECISE()'],
['NEGBINOM.DIST()', '_xlfn.NEGBINOM.DIST()'],
['NETWORKDAYS.INTL()', 'NETWORKDAYS.INTL()'],
['PERCENTILE.EXC()', '_xlfn.PERCENTILE.EXC()'],
['PERCENTILE.INC()', '_xlfn.PERCENTILE.INC()'],
['CEILING.PRECISE()', '_xlfn.CEILING.PRECISE()'],
['CONFIDENCE.NORM()', '_xlfn.CONFIDENCE.NORM()'],
['FORECAST.LINEAR()', '_xlfn.FORECAST.LINEAR()'],
['GAMMALN.PRECISE()', '_xlfn.GAMMALN.PRECISE()'],
['PERCENTRANK.EXC()', '_xlfn.PERCENTRANK.EXC()'],
['PERCENTRANK.INC()', '_xlfn.PERCENTRANK.INC()'],
['BINOM.DIST.RANGE()', '_xlfn.BINOM.DIST.RANGE()'],
['FORECAST.ETS.STAT()', '_xlfn.FORECAST.ETS.STAT()'],
['FORECAST.ETS.CONFINT()', '_xlfn.FORECAST.ETS.CONFINT()'],
['FORECAST.ETS.SEASONALITY()', '_xlfn.FORECAST.ETS.SEASONALITY()'],
['Z.TEST(Z.TEST(Z.TEST()))', '_xlfn.Z.TEST(_xlfn.Z.TEST(_xlfn.Z.TEST()))'],
);
for my $testcase (@testcases) {
my $formula = $testcase->[0];
my $expected = $testcase->[1];
my $got = $worksheet->_prepare_formula($formula);
is( $got, $expected, $caption );
}
|
