FreeCAD

1
# ***************************************************************************
2
# *   Copyright (c) 2016 Werner Mayer <wmayer[at]users.sourceforge.net>     *
3
# *   Copyright (c) 2016 Eivind Kvedalen <eivind@kvedalen.name>             *
4
# *                                                                         *
5
# *   This program is free software; you can redistribute it and/or modify  *
6
# *   it under the terms of the GNU General Public License (GPL)            *
7
# *   as published by the Free Software Foundation; either version 2 of     *
8
# *   the License, or (at your option) any later version.                   *
9
# *   for detail see the LICENCE text file.                                 *
10
# *                                                                         *
11
# *   FreeCAD is distributed in the hope that it will be useful,            *
12
# *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
13
# *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
14
# *   GNU Library General Public License for more details.                  *
15
# *                                                                         *
16
# *   You should have received a copy of the GNU Library General Public     *
17
# *   License along with FreeCAD; if not, write to the Free Software        *
18
# *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  *
19
# *   USA                                                                   *
20
# ***************************************************************************/
21

22
import os
23
import sys
24
import math
25
from math import sqrt
26
import unittest
27
import FreeCAD
28
import Part
29
import Sketcher
30
import tempfile
31
from FreeCAD import Base
32
from FreeCAD import Units
33

34
v = Base.Vector
35

36
# ----------------------------------------------------------------------------------
37
# define the functions to test the FreeCAD Spreadsheet module and expression engine
38
# ----------------------------------------------------------------------------------
39

40

41
class SpreadsheetCases(unittest.TestCase):
42
    def setUp(self):
43
        self.doc = FreeCAD.newDocument()
44
        self.TempPath = tempfile.gettempdir()
45
        FreeCAD.Console.PrintLog("  Using temp path: " + self.TempPath + "\n")
46

47
    def testAggregates(self):
48
        """Test all aggregate functions"""
49
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
50
        sheet.set("B13", "4")
51
        sheet.set("B14", "5")
52
        sheet.set("B15", "6")
53
        sheet.set("C13", "4mm")
54
        sheet.set("C14", "5mm")
55
        sheet.set("C15", "6mm")
56
        sheet.set("C16", "6")
57

58
        sheet.set("A1", "=sum(1)")
59
        sheet.set("A2", "=sum(1;2)")
60
        sheet.set("A3", "=sum(1;2;3)")
61
        sheet.set("A4", "=sum(1;2;3;B13)")
62
        sheet.set("A5", "=sum(1;2;3;B13:B15)")
63

64
        sheet.set("B1", "=min(1)")
65
        sheet.set("B2", "=min(1;2)")
66
        sheet.set("B3", "=min(1;2;3)")
67
        sheet.set("B4", "=min(1;2;3;B13)")
68
        sheet.set("B5", "=min(1;2;3;B13:B15)")
69

70
        sheet.set("C1", "=max(1)")
71
        sheet.set("C2", "=max(1;2)")
72
        sheet.set("C3", "=max(1;2;3)")
73
        sheet.set("C4", "=max(1;2;3;B13)")
74
        sheet.set("C5", "=max(1;2;3;B13:B15)")
75

76
        sheet.set("D1", "=stddev(1)")
77
        sheet.set("D2", "=stddev(1;2)")
78
        sheet.set("D3", "=stddev(1;2;3)")
79
        sheet.set("D4", "=stddev(1;2;3;B13)")
80
        sheet.set("D5", "=stddev(1;2;3;B13:B15)")
81

82
        sheet.set("E1", "=count(1)")
83
        sheet.set("E2", "=count(1;2)")
84
        sheet.set("E3", "=count(1;2;3)")
85
        sheet.set("E4", "=count(1;2;3;B13)")
86
        sheet.set("E5", "=count(1;2;3;B13:B15)")
87

88
        sheet.set("F1", "=average(1)")
89
        sheet.set("F2", "=average(1;2)")
90
        sheet.set("F3", "=average(1;2;3)")
91
        sheet.set("F4", "=average(1;2;3;B13)")
92
        sheet.set("F5", "=average(1;2;3;B13:B15)")
93

94
        sheet.set("G1", "=average(C13:C15)")
95
        sheet.set("G2", "=min(C13:C15)")
96
        sheet.set("G3", "=max(C13:C15)")
97
        sheet.set("G4", "=count(C13:C15)")
98
        sheet.set("G5", "=stddev(C13:C15)")
99
        sheet.set("G6", "=sum(C13:C15)")
100

101
        sheet.set("H1", "=average(C13:C16)")
102
        sheet.set("H2", "=min(C13:C16)")
103
        sheet.set("H3", "=max(C13:C16)")
104
        sheet.set("H4", "=count(C13:C16)")
105
        sheet.set("H5", "=stddev(C13:C16)")
106
        sheet.set("H6", "=sum(C13:C16)")
107

108
        self.doc.recompute()
109
        self.assertEqual(sheet.A1, 1)
110
        self.assertEqual(sheet.A2, 3)
111
        self.assertEqual(sheet.A3, 6)
112
        self.assertEqual(sheet.A4, 10)
113
        self.assertEqual(sheet.A5, 21)
114

115
        self.assertEqual(sheet.B1, 1)
116
        self.assertEqual(sheet.B2, 1)
117
        self.assertEqual(sheet.B3, 1)
118
        self.assertEqual(sheet.B4, 1)
119
        self.assertEqual(sheet.B5, 1)
120

121
        self.assertEqual(sheet.C1, 1)
122
        self.assertEqual(sheet.C2, 2)
123
        self.assertEqual(sheet.C3, 3)
124
        self.assertEqual(sheet.C4, 4)
125
        self.assertEqual(sheet.C5, 6)
126

127
        self.assertTrue(
128
            sheet.D1.startswith("ERR: Invalid number of entries: at least two required.")
129
        )
130
        self.assertEqual(sheet.D2, 0.7071067811865476)
131
        self.assertEqual(sheet.D3, 1.0)
132
        self.assertEqual(sheet.D4, 1.2909944487358056)
133
        self.assertEqual(sheet.D5, 1.8708286933869707)
134

135
        self.assertEqual(sheet.E1, 1)
136
        self.assertEqual(sheet.E2, 2)
137
        self.assertEqual(sheet.E3, 3)
138
        self.assertEqual(sheet.E4, 4)
139
        self.assertEqual(sheet.E5, 6)
140

141
        self.assertEqual(sheet.F1, 1)
142
        self.assertEqual(sheet.F2, (1.0 + 2.0) / 2.0)
143
        self.assertEqual(sheet.F3, (1.0 + 2 + 3) / 3)
144
        self.assertEqual(sheet.F4, (1.0 + 2 + 3 + 4) / 4)
145
        self.assertEqual(sheet.F5, (1.0 + 2 + 3 + 4 + 5 + 6) / 6)
146

147
        self.assertEqual(sheet.G1, Units.Quantity("5 mm"))
148
        self.assertEqual(sheet.G2, Units.Quantity("4 mm"))
149
        self.assertEqual(sheet.G3, Units.Quantity("6 mm"))
150
        self.assertEqual(sheet.G4, 3)
151
        self.assertEqual(sheet.G5, Units.Quantity("1 mm"))
152
        self.assertEqual(sheet.G6, Units.Quantity("15 mm"))
153

154
        self.assertTrue(
155
            sheet.H1.startswith("ERR: Quantity::operator +=(): Unit mismatch in plus operation")
156
        )
157
        self.assertTrue(
158
            sheet.H2.startswith(
159
                "ERR: Quantity::operator <(): quantities need to have same unit to compare"
160
            )
161
        )
162
        self.assertTrue(
163
            sheet.H3.startswith(
164
                "ERR: Quantity::operator >(): quantities need to have same unit to compare"
165
            )
166
        )
167
        self.assertEqual(sheet.H4, 4)
168
        self.assertTrue(
169
            sheet.H5.startswith("ERR: Quantity::operator -(): Unit mismatch in minus operation")
170
        )
171
        self.assertTrue(
172
            sheet.H6.startswith("ERR: Quantity::operator +=(): Unit mismatch in plus operation")
173
        )
174

175
    def assertMostlyEqual(self, a, b):
176
        if type(a) is Units.Quantity:
177
            self.assertTrue(math.fabs(a.Value - b.Value) < 1e-14)
178
            self.assertTrue(a.Unit == b.Unit)
179
        else:
180
            self.assertTrue(math.fabs(a - b) < 1e-14)
181

182
    def testFunctions(self):
183
        """Test all built-in simple functions"""
184
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
185
        sheet.set("A1", "=cos(60)")  # Cos
186
        sheet.set("B1", "=cos(60deg)")
187
        sheet.set("C1", "=cos(pi / 2 * 1rad)")
188
        sheet.set("A2", "=sin(30)")  # Sin
189
        sheet.set("B2", "=sin(30deg)")
190
        sheet.set("C2", "=sin(pi / 6 * 1rad)")
191
        sheet.set("A3", "=tan(45)")  # Tan
192
        sheet.set("B3", "=tan(45deg)")
193
        sheet.set("C3", "=tan(pi / 4 * 1rad)")
194
        sheet.set("A4", "=abs(3)")  # Abs
195
        sheet.set("B4", "=abs(-3)")
196
        sheet.set("C4", "=abs(-3mm)")
197
        sheet.set("A5", "=exp(3)")  # Exp
198
        sheet.set("B5", "=exp(-3)")
199
        sheet.set("C5", "=exp(-3mm)")
200
        sheet.set("A6", "=log(3)")  # Log
201
        sheet.set("B6", "=log(-3)")
202
        sheet.set("C6", "=log(-3mm)")
203
        sheet.set("A7", "=log10(10)")  # Log10
204
        sheet.set("B7", "=log10(-3)")
205
        sheet.set("C7", "=log10(-3mm)")
206
        sheet.set("A8", "=round(3.4)")  # Round
207
        sheet.set("B8", "=round(3.6)")
208
        sheet.set("C8", "=round(-3.4)")
209
        sheet.set("D8", "=round(-3.6)")
210
        sheet.set("E8", "=round(3.4mm)")
211
        sheet.set("F8", "=round(3.6mm)")
212
        sheet.set("G8", "=round(-3.4mm)")
213
        sheet.set("H8", "=round(-3.6mm)")
214
        sheet.set("A9", "=trunc(3.4)")  # Trunc
215
        sheet.set("B9", "=trunc(3.6)")
216
        sheet.set("C9", "=trunc(-3.4)")
217
        sheet.set("D9", "=trunc(-3.6)")
218
        sheet.set("E9", "=trunc(3.4mm)")
219
        sheet.set("F9", "=trunc(3.6mm)")
220
        sheet.set("G9", "=trunc(-3.4mm)")
221
        sheet.set("H9", "=trunc(-3.6mm)")
222
        sheet.set("A10", "=ceil(3.4)")  # Ceil
223
        sheet.set("B10", "=ceil(3.6)")
224
        sheet.set("C10", "=ceil(-3.4)")
225
        sheet.set("D10", "=ceil(-3.6)")
226
        sheet.set("E10", "=ceil(3.4mm)")
227
        sheet.set("F10", "=ceil(3.6mm)")
228
        sheet.set("G10", "=ceil(-3.4mm)")
229
        sheet.set("H10", "=ceil(-3.6mm)")
230
        sheet.set("A11", "=floor(3.4)")  # Floor
231
        sheet.set("B11", "=floor(3.6)")
232
        sheet.set("C11", "=floor(-3.4)")
233
        sheet.set("D11", "=floor(-3.6)")
234
        sheet.set("E11", "=floor(3.4mm)")
235
        sheet.set("F11", "=floor(3.6mm)")
236
        sheet.set("G11", "=floor(-3.4mm)")
237
        sheet.set("H11", "=floor(-3.6mm)")
238
        sheet.set("A12", "=asin(0.5)")  # Asin
239
        sheet.set("B12", "=asin(0.5mm)")
240
        sheet.set("A13", "=acos(0.5)")  # Acos
241
        sheet.set("B13", "=acos(0.5mm)")
242
        sheet.set("A14", "=atan(sqrt(3))")  # Atan
243
        sheet.set("B14", "=atan(0.5mm)")
244
        sheet.set("A15", "=sinh(0.5)")  # Sinh
245
        sheet.set("B15", "=sinh(0.5mm)")
246
        sheet.set("A16", "=cosh(0.5)")  # Cosh
247
        sheet.set("B16", "=cosh(0.5mm)")
248
        sheet.set("A17", "=tanh(0.5)")  # Tanh
249
        sheet.set("B17", "=tanh(0.5mm)")
250
        sheet.set("A18", "=sqrt(4)")  # Sqrt
251
        sheet.set("B18", "=sqrt(4mm^2)")
252
        sheet.set("A19", "=mod(7; 4)")  # Mod
253
        sheet.set("B19", "=mod(-7; 4)")
254
        sheet.set("C19", "=mod(7mm; 4)")
255
        sheet.set("D19", "=mod(7mm; 4mm)")
256
        sheet.set("A20", "=atan2(3; 3)")  # Atan2
257
        sheet.set("B20", "=atan2(-3; 3)")
258
        sheet.set("C20", "=atan2(3mm; 3)")
259
        sheet.set("D20", "=atan2(3mm; 3mm)")
260
        sheet.set("A21", "=pow(7; 4)")  # Pow
261
        sheet.set("B21", "=pow(-7; 4)")
262
        sheet.set("C21", "=pow(7mm; 4)")
263
        sheet.set("D21", "=pow(7mm; 4mm)")
264
        sheet.set("A23", "=hypot(3; 4)")  # Hypot
265
        sheet.set("B23", "=hypot(-3; 4)")
266
        sheet.set("C23", "=hypot(3mm; 4)")
267
        sheet.set("D23", "=hypot(3mm; 4mm)")
268
        sheet.set("A24", "=hypot(3; 4; 5)")  # Hypot
269
        sheet.set("B24", "=hypot(-3; 4; 5)")
270
        sheet.set("C24", "=hypot(3mm; 4; 5)")
271
        sheet.set("D24", "=hypot(3mm; 4mm; 5mm)")
272
        sheet.set("A26", "=cath(5; 3)")  # Cath
273
        sheet.set("B26", "=cath(-5; 3)")
274
        sheet.set("C26", "=cath(5mm; 3)")
275
        sheet.set("D26", "=cath(5mm; 3mm)")
276

277
        l = math.sqrt(5 * 5 + 4 * 4 + 3 * 3)
278
        sheet.set("A27", "=cath(%0.15f; 5; 4)" % l)  # Cath
279
        sheet.set("B27", "=cath(%0.15f; -5; 4)" % l)
280
        sheet.set("C27", "=cath(%0.15f mm; 5mm; 4)" % l)
281
        sheet.set("D27", "=cath(%0.15f mm; 5mm; 4mm)" % l)
282

283
        self.doc.recompute()
284
        self.assertMostlyEqual(sheet.A1, 0.5)  # Cos
285
        self.assertMostlyEqual(sheet.B1, 0.5)
286
        self.assertMostlyEqual(sheet.C1, 0)
287
        self.assertMostlyEqual(sheet.A2, 0.5)  # Sin
288
        self.assertMostlyEqual(sheet.B2, 0.5)
289
        self.assertMostlyEqual(sheet.C2, 0.5)
290
        self.assertMostlyEqual(sheet.A3, 1)  # Tan
291
        self.assertMostlyEqual(sheet.B3, 1)
292
        self.assertMostlyEqual(sheet.C3, 1)
293
        self.assertMostlyEqual(sheet.A4, 3)  # Abs
294
        self.assertMostlyEqual(sheet.B4, 3)
295
        self.assertMostlyEqual(sheet.C4, Units.Quantity("3 mm"))
296
        self.assertMostlyEqual(sheet.A5, math.exp(3))  # Exp
297
        self.assertMostlyEqual(sheet.B5, math.exp(-3))
298
        self.assertTrue(sheet.C5.startswith("ERR: Unit must be empty."))
299
        self.assertMostlyEqual(sheet.A6, math.log(3))  # Log
300
        self.assertTrue(math.isnan(sheet.B6))
301
        self.assertTrue(sheet.C6.startswith("ERR: Unit must be empty."))
302
        self.assertMostlyEqual(sheet.A7, math.log10(10))  # Log10
303
        self.assertTrue(math.isnan(sheet.B7))
304
        self.assertTrue(sheet.C7.startswith("ERR: Unit must be empty."))
305
        self.assertMostlyEqual(sheet.A8, 3)  # Round
306
        self.assertMostlyEqual(sheet.B8, 4)
307
        self.assertMostlyEqual(sheet.C8, -3)
308
        self.assertMostlyEqual(sheet.D8, -4)
309
        self.assertEqual(sheet.E8, Units.Quantity("3 mm"))
310
        self.assertEqual(sheet.F8, Units.Quantity("4 mm"))
311
        self.assertEqual(sheet.G8, Units.Quantity("-3 mm"))
312
        self.assertEqual(sheet.H8, Units.Quantity("-4 mm"))
313
        self.assertMostlyEqual(sheet.A9, 3)  # Trunc
314
        self.assertMostlyEqual(sheet.B9, 3)
315
        self.assertMostlyEqual(sheet.C9, -3)
316
        self.assertMostlyEqual(sheet.D9, -3)
317
        self.assertEqual(sheet.E9, Units.Quantity("3 mm"))
318
        self.assertEqual(sheet.F9, Units.Quantity("3 mm"))
319
        self.assertEqual(sheet.G9, Units.Quantity("-3 mm"))
320
        self.assertEqual(sheet.H9, Units.Quantity("-3 mm"))
321
        self.assertMostlyEqual(sheet.A10, 4)  # Ceil
322
        self.assertMostlyEqual(sheet.B10, 4)
323
        self.assertMostlyEqual(sheet.C10, -3)
324
        self.assertMostlyEqual(sheet.D10, -3)
325
        self.assertMostlyEqual(sheet.E10, Units.Quantity("4 mm"))
326
        self.assertMostlyEqual(sheet.F10, Units.Quantity("4 mm"))
327
        self.assertMostlyEqual(sheet.G10, Units.Quantity("-3 mm"))
328
        self.assertMostlyEqual(sheet.H10, Units.Quantity("-3 mm"))
329
        self.assertMostlyEqual(sheet.A11, 3)  # Floor
330
        self.assertMostlyEqual(sheet.B11, 3)
331
        self.assertMostlyEqual(sheet.C11, -4)
332
        self.assertMostlyEqual(sheet.D11, -4)
333
        self.assertMostlyEqual(sheet.E11, Units.Quantity("3 mm"))
334
        self.assertMostlyEqual(sheet.F11, Units.Quantity("3 mm"))
335
        self.assertMostlyEqual(sheet.G11, Units.Quantity("-4 mm"))
336
        self.assertMostlyEqual(sheet.H11, Units.Quantity("-4 mm"))
337
        self.assertMostlyEqual(sheet.A12, Units.Quantity("30 deg"))  # Asin
338
        self.assertTrue(sheet.B12.startswith("ERR: Unit must be empty."))
339
        self.assertMostlyEqual(sheet.A13, Units.Quantity("60 deg"))  # Acos
340
        self.assertTrue(sheet.B13.startswith("ERR: Unit must be empty."))
341
        self.assertMostlyEqual(sheet.A14, Units.Quantity("60 deg"))  # Atan
342
        self.assertTrue(sheet.B14.startswith("ERR: Unit must be empty."))
343
        self.assertMostlyEqual(sheet.A15, math.sinh(0.5))  # Sinh
344
        self.assertTrue(sheet.B15.startswith("ERR: Unit must be empty."))
345
        self.assertMostlyEqual(sheet.A16, math.cosh(0.5))  # Cosh
346
        self.assertTrue(sheet.B16.startswith("ERR: Unit must be empty."))
347
        self.assertMostlyEqual(sheet.A17, math.tanh(0.5))  # Tanh
348
        self.assertTrue(sheet.B17.startswith("ERR: Unit must be empty."))
349
        self.assertMostlyEqual(sheet.A18, 2)  # Sqrt
350
        self.assertMostlyEqual(sheet.B18, Units.Quantity("2 mm"))
351
        self.assertMostlyEqual(sheet.A19, 3)  # Mod
352
        self.assertMostlyEqual(sheet.B19, -3)
353
        self.assertMostlyEqual(sheet.C19, Units.Quantity("3 mm"))
354
        self.assertEqual(sheet.D19, 3)
355
        self.assertMostlyEqual(sheet.A20, Units.Quantity("45 deg"))  # Atan2
356
        self.assertMostlyEqual(sheet.B20, Units.Quantity("-45 deg"))
357
        self.assertTrue(sheet.C20.startswith("ERR: Units must be equal"))
358
        self.assertMostlyEqual(sheet.D20, Units.Quantity("45 deg"))
359
        self.assertMostlyEqual(sheet.A21, 2401)  # Pow
360
        self.assertMostlyEqual(sheet.B21, 2401)
361
        self.assertMostlyEqual(sheet.C21, Units.Quantity("2401mm^4"))
362
        self.assertTrue(sheet.D21.startswith("ERR: Exponent is not allowed to have a unit."))
363
        self.assertMostlyEqual(sheet.A23, 5)  # Hypot
364
        self.assertMostlyEqual(sheet.B23, 5)
365
        self.assertTrue(sheet.C23.startswith("ERR: Units must be equal"))
366
        self.assertMostlyEqual(sheet.D23, Units.Quantity("5mm"))
367

368
        l = math.sqrt(3 * 3 + 4 * 4 + 5 * 5)
369
        self.assertMostlyEqual(sheet.A24, l)  # Hypot
370
        self.assertMostlyEqual(sheet.B24, l)
371
        self.assertTrue(sheet.C24.startswith("ERR: Units must be equal"))
372
        self.assertMostlyEqual(sheet.D24, Units.Quantity("7.07106781186548 mm"))
373
        self.assertMostlyEqual(sheet.A26, 4)  # Cath
374
        self.assertMostlyEqual(sheet.B26, 4)
375
        self.assertTrue(sheet.C26.startswith("ERR: Units must be equal"))
376
        self.assertMostlyEqual(sheet.D26, Units.Quantity("4mm"))
377

378
        l = math.sqrt(5 * 5 + 4 * 4 + 3 * 3)
379
        l = math.sqrt(l * l - 5 * 5 - 4 * 4)
380
        self.assertMostlyEqual(sheet.A27, l)  # Cath
381
        self.assertMostlyEqual(sheet.B27, l)
382
        self.assertTrue(sheet.C27.startswith("ERR: Units must be equal"))
383
        self.assertMostlyEqual(sheet.D27, Units.Quantity("3 mm"))
384

385
    def testRelationalOperators(self):
386
        """Test relational operators"""
387
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
388
        # All should be 1 as result
389
        sheet.set("A1", "=1 == 1     ? 1 : 0")
390
        sheet.set("A2", "=1 != 1     ? 0 : 1")
391
        sheet.set("A3", "=1e9 == 1e9 ? 1 : 0")
392
        sheet.set("A4", "=1e9 != 1e9 ? 0 : 1")
393
        sheet.set("A5", "=1 > 1      ? 0 : 1")
394
        sheet.set("A6", "=2 > 1      ? 1 : 0")
395
        sheet.set("A7", "=1 > 2      ? 0 : 1")
396
        sheet.set("A8", "=1 < 1      ? 0 : 1")
397
        sheet.set("A9", "=1 < 2      ? 1 : 0")
398
        sheet.set("A10", "=2 < 1      ? 0 : 1")
399
        sheet.set("A11", "=1 >= 1     ? 1 : 0")
400
        sheet.set("A12", "=2 >= 1     ? 1 : 0")
401
        sheet.set("A13", "=1 >= 2     ? 0 : 1")
402
        sheet.set("A14", "=1 <= 1     ? 1 : 1")
403
        sheet.set("A15", "=1 <= 2     ? 1 : 0")
404
        sheet.set("A16", "=2 <= 1     ? 0 : 1")
405
        sheet.set("A17", "=1 >= 1.000000000000001 ? 0 : 1")
406
        sheet.set("A18", "=1 >= 1.0000000000000001 ? 1 : 0")
407
        sheet.set("A19", "=1 <= 1.000000000000001 ? 1 : 0")
408
        sheet.set("A20", "=1 <= 1.0000000000000001 ? 1 : 0")
409
        sheet.set("A21", "=1 == 1.000000000000001 ? 0 : 1")
410
        sheet.set("A22", "=1 == 1.0000000000000001 ? 1 : 0")
411
        sheet.set("A23", "=1 != 1.000000000000001 ? 1 : 0")
412
        sheet.set("A24", "=1 != 1.0000000000000001 ? 0 : 1")
413

414
        self.doc.recompute()
415
        self.assertEqual(sheet.A1, 1)
416
        self.assertEqual(sheet.A2, 1)
417
        self.assertEqual(sheet.A3, 1)
418
        self.assertEqual(sheet.A4, 1)
419
        self.assertEqual(sheet.A5, 1)
420
        self.assertEqual(sheet.A6, 1)
421
        self.assertEqual(sheet.A7, 1)
422
        self.assertEqual(sheet.A8, 1)
423
        self.assertEqual(sheet.A9, 1)
424
        self.assertEqual(sheet.A10, 1)
425
        self.assertEqual(sheet.A11, 1)
426
        self.assertEqual(sheet.A12, 1)
427
        self.assertEqual(sheet.A13, 1)
428
        self.assertEqual(sheet.A14, 1)
429
        self.assertEqual(sheet.A15, 1)
430
        self.assertEqual(sheet.A16, 1)
431
        self.assertEqual(sheet.A17, 1)
432
        self.assertEqual(sheet.A18, 1)
433
        self.assertEqual(sheet.A19, 1)
434
        self.assertEqual(sheet.A20, 1)
435
        self.assertEqual(sheet.A21, 1)
436
        self.assertEqual(sheet.A22, 1)
437
        self.assertEqual(sheet.A23, 1)
438
        self.assertEqual(sheet.A24, 1)
439

440
    def testUnits(self):
441
        """Units -- test unit calculations."""
442
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
443
        sheet.set("A1", "=2mm + 3mm")
444
        sheet.set("A2", "=2mm - 3mm")
445
        sheet.set("A3", "=2mm * 3mm")
446
        sheet.set("A4", "=4mm / 2mm")
447
        sheet.set("A5", "=(4mm)^2")
448
        sheet.set("A6", "=5(mm^2)")
449
        sheet.set("A7", "=5mm^2")  #  ^2 operates on whole number
450
        sheet.set("A8", "=5")
451
        sheet.set("A9", "=5*1/K")  # Currently fails
452
        sheet.set("A10", "=5 K^-1")  # Currently fails
453
        sheet.set("A11", "=9.8 m/s^2")  # Currently fails
454
        sheet.setDisplayUnit("A8", "1/K")
455
        self.doc.recompute()
456
        self.assertEqual(sheet.A1, Units.Quantity("5mm"))
457
        self.assertEqual(sheet.A2, Units.Quantity("-1 mm"))
458
        self.assertEqual(sheet.A3, Units.Quantity("6 mm^2"))
459
        self.assertEqual(sheet.A4, Units.Quantity("2"))
460
        self.assertEqual(sheet.A5, Units.Quantity("16 mm^2"))
461
        self.assertEqual(sheet.A6, Units.Quantity("5 mm^2"))
462
        self.assertEqual(sheet.A7, Units.Quantity("5 mm^2"))
463
        self.assertEqual(sheet.A8, Units.Quantity("5"))
464
        self.assertEqual(sheet.A9, Units.Quantity("5 K^-1"))
465
        self.assertEqual(sheet.A10, Units.Quantity("5 K^-1"))
466
        self.assertEqual(sheet.A11, Units.Quantity("9.8 m/s^2"))
467

468
    def testPrecedence(self):
469
        """Precedence -- test precedence for relational operators and conditional operator."""
470
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
471
        sheet.set("A1", "=1 < 2 ? 3 : 4")
472
        sheet.set("A2", "=1 + 2 < 3 + 4 ? 5 + 6 : 7 + 8")
473
        sheet.set("A3", "=1 + 2 * 1 < 3 + 4 ? 5 * 2 + 6 * 3 + 2 ^ 4 : 7 * 2 + 8 * 3 + 2 ^ 3")
474
        sheet.set("A4", "=123")
475
        sheet.set("A5", "=123 + 321")
476
        sheet.set("A6", "=123 * 2 + 321")
477
        sheet.set("A7", "=123 * 2 + 333 / 3")
478
        sheet.set("A8", "=123 * (2 + 321)")
479
        sheet.set("A9", "=3 ^ 4")
480
        sheet.set("A10", "=3 ^ 4 * 2")
481
        sheet.set("A11", "=3 ^ (4 * 2)")
482
        sheet.set("A12", "=3 ^ 4 + 4")
483
        sheet.set("A13", "=1 + 4 / 2 + 5")
484
        sheet.set("A14", "=(3 + 6) / (1 + 2)")
485
        sheet.set("A15", "=1 * 2 / 3 * 4")
486
        sheet.set("A16", "=(1 * 2) / (3 * 4)")
487
        # Test associativity
488
        sheet.set(
489
            "A17", "=3 ^ 4 ^ 2"
490
        )  # exponentiation is left-associative; to follow excel, openoffice, matlab, octave
491
        sheet.set("A18", "=3 ^ (4 ^ 2)")  # exponentiation is left-associative
492
        sheet.set("A19", "=(3 ^ 4) ^ 2")  # exponentiation is left-associative
493
        sheet.set("A20", "=3 + 4 + 2")
494
        sheet.set("A21", "=3 + (4 + 2)")
495
        sheet.set("A22", "=(3 + 4) + 2")
496
        sheet.set("A23", "=3 - 4 - 2")
497
        sheet.set("A24", "=3 - (4 - 2)")
498
        sheet.set("A25", "=(3 - 4) - 2")
499
        sheet.set("A26", "=3 * 4 * 2")
500
        sheet.set("A27", "=3 * (4 * 2)")
501
        sheet.set("A28", "=(3 * 4) * 2")
502
        sheet.set("A29", "=3 / 4 / 2")
503
        sheet.set("A30", "=3 / (4 / 2)")
504
        sheet.set("A31", "=(3 / 4) / 2")
505
        sheet.set("A32", "=pi * 3")
506
        sheet.set("A33", "=A32 / 3")
507
        sheet.set("A34", "=1 < 2 ? <<A>> : <<B>>")
508
        sheet.set("A35", "=min(A32:A33)")
509
        sheet.set("A36", "=(1 < 2 ? 0 : 1) * 3")
510
        sheet.set("A37", "=8/(2^2*2)")
511
        sheet.set("A38", "=(2^2*2)/8")
512
        sheet.set("A39", "=2^(2*2)/8")
513
        sheet.set("A40", "=8/2^(2*2)")
514
        sheet.set("A41", "=-1")
515
        sheet.set("A42", "=-(1)")
516
        sheet.set("A43", "=-(1 + 1)")
517
        sheet.set("A44", "=-(1 - 1)")
518
        sheet.set("A45", "=-(-1 + 1)")
519
        sheet.set("A46", "=-(-1 + -1)")
520
        sheet.set("A47", "=+1")
521
        sheet.set("A48", "=+(1)")
522
        sheet.set("A49", "=+(1 + 1)")
523
        sheet.set("A50", "=+(1 - 1)")
524
        sheet.set("A51", "=+(-1 + 1)")
525
        sheet.set("A52", "=+(-1 + -1)")
526

527
        self.doc.addObject("Part::Cylinder", "Cylinder")
528
        # We cannot use Thickness, as this feature requires a source shape,
529
        # otherwise it will cause recomputation failure. The new logic of
530
        # App::Document will not continue recompute any dependent objects
531

532
        #  self.doc.addObject("Part::Thickness", "Pipe")
533
        self.doc.addObject("Part::Box", "Box")
534
        self.doc.Box.Length = 1
535

536
        sheet.set("B1", "101")
537
        sheet.set("A53", "=-(-(B1-1)/2)")
538
        sheet.set("A54", '=-(Cylinder.Radius + Box.Length - 1"/2)')
539

540
        self.doc.recompute()
541
        self.assertEqual(sheet.getContents("A1"), "=1 < 2 ? 3 : 4")
542
        self.assertEqual(sheet.getContents("A2"), "=1 + 2 < 3 + 4 ? 5 + 6 : 7 + 8")
543
        self.assertEqual(
544
            sheet.getContents("A3"),
545
            "=1 + 2 * 1 < 3 + 4 ? 5 * 2 + 6 * 3 + 2 ^ 4 : 7 * 2 + 8 * 3 + 2 ^ 3",
546
        )
547
        self.assertEqual(sheet.A1, 3)
548
        self.assertEqual(sheet.A2, 11)
549
        self.assertEqual(sheet.A3, 44)
550
        self.assertEqual(sheet.A4, 123)
551
        self.assertEqual(sheet.A5, 444)
552
        self.assertEqual(sheet.A6, 567)
553
        self.assertEqual(sheet.A7, 357)
554
        self.assertEqual(sheet.A8, 39729)
555
        self.assertEqual(sheet.A9, 81)
556
        self.assertEqual(sheet.A10, 162)
557
        self.assertEqual(sheet.A11, 6561)
558
        self.assertEqual(sheet.A12, 85)
559
        self.assertEqual(sheet.A13, 8)
560
        self.assertEqual(sheet.A14, 3)
561
        self.assertEqual(sheet.A15, 8.0 / 3)
562
        self.assertEqual(sheet.A16, 1.0 / 6)
563
        self.assertEqual(sheet.A17, 6561)
564
        self.assertEqual(sheet.A18, 43046721)
565
        self.assertEqual(sheet.A19, 6561)
566
        self.assertEqual(sheet.A20, 9)
567
        self.assertEqual(sheet.A21, 9)
568
        self.assertEqual(sheet.A22, 9)
569
        self.assertEqual(sheet.A23, -3)
570
        self.assertEqual(sheet.A24, 1)
571
        self.assertEqual(sheet.A25, -3)
572
        self.assertEqual(sheet.A26, 24)
573
        self.assertEqual(sheet.A27, 24)
574
        self.assertEqual(sheet.A28, 24)
575
        self.assertEqual(sheet.A29, 3.0 / 8)
576
        self.assertEqual(sheet.A30, 3.0 / 2)
577
        self.assertEqual(sheet.A31, 3.0 / 8)
578
        self.assertEqual(sheet.A37, 1)
579
        self.assertEqual(sheet.A38, 1)
580
        self.assertEqual(sheet.A39, 2)
581
        self.assertEqual(sheet.A40, 0.5)
582
        self.assertEqual(sheet.A41, -1)
583
        self.assertEqual(sheet.A42, -1)
584
        self.assertEqual(sheet.A43, -2)
585
        self.assertEqual(sheet.A44, 0)
586
        self.assertEqual(sheet.A45, 0)
587
        self.assertEqual(sheet.A46, 2)
588
        self.assertEqual(sheet.A47, 1)
589
        self.assertEqual(sheet.A48, 1)
590
        self.assertEqual(sheet.A49, 2)
591
        self.assertEqual(sheet.A50, 0)
592
        self.assertEqual(sheet.A51, 0)
593
        self.assertEqual(sheet.A52, -2)
594
        self.assertEqual(sheet.A53, 50)
595
        self.assertEqual(sheet.A54, Units.Quantity("9.7mm"))
596
        self.assertEqual(sheet.getContents("A1"), "=1 < 2 ? 3 : 4")
597
        self.assertEqual(sheet.getContents("A2"), "=1 + 2 < 3 + 4 ? 5 + 6 : 7 + 8")
598
        self.assertEqual(
599
            sheet.getContents("A3"),
600
            "=1 + 2 * 1 < 3 + 4 ? 5 * 2 + 6 * 3 + 2 ^ 4 : 7 * 2 + 8 * 3 + 2 ^ 3",
601
        )
602
        self.assertEqual(sheet.getContents("A4"), "123")
603
        self.assertEqual(sheet.getContents("A5"), "=123 + 321")
604
        self.assertEqual(sheet.getContents("A6"), "=123 * 2 + 321")
605
        self.assertEqual(sheet.getContents("A7"), "=123 * 2 + 333 / 3")
606
        self.assertEqual(sheet.getContents("A8"), "=123 * (2 + 321)")
607
        self.assertEqual(sheet.getContents("A9"), "=3 ^ 4")
608
        self.assertEqual(sheet.getContents("A10"), "=3 ^ 4 * 2")
609
        self.assertEqual(sheet.getContents("A11"), "=3 ^ (4 * 2)")
610
        self.assertEqual(sheet.getContents("A12"), "=3 ^ 4 + 4")
611
        self.assertEqual(sheet.getContents("A13"), "=1 + 4 / 2 + 5")
612
        self.assertEqual(sheet.getContents("A14"), "=(3 + 6) / (1 + 2)")
613
        self.assertEqual(sheet.getContents("A15"), "=1 * 2 / 3 * 4")
614
        self.assertEqual(sheet.getContents("A16"), "=1 * 2 / (3 * 4)")
615
        self.assertEqual(sheet.getContents("A17"), "=3 ^ 4 ^ 2")
616
        self.assertEqual(sheet.getContents("A18"), "=3 ^ (4 ^ 2)")
617
        self.assertEqual(sheet.getContents("A19"), "=3 ^ 4 ^ 2")
618
        self.assertEqual(sheet.getContents("A20"), "=3 + 4 + 2")
619
        self.assertEqual(sheet.getContents("A21"), "=3 + 4 + 2")
620
        self.assertEqual(sheet.getContents("A22"), "=3 + 4 + 2")
621
        self.assertEqual(sheet.getContents("A23"), "=3 - 4 - 2")
622
        self.assertEqual(sheet.getContents("A24"), "=3 - (4 - 2)")
623
        self.assertEqual(sheet.getContents("A25"), "=3 - 4 - 2")
624
        self.assertEqual(sheet.getContents("A26"), "=3 * 4 * 2")
625
        self.assertEqual(sheet.getContents("A27"), "=3 * 4 * 2")
626
        self.assertEqual(sheet.getContents("A28"), "=3 * 4 * 2")
627
        self.assertEqual(sheet.getContents("A29"), "=3 / 4 / 2")
628
        self.assertEqual(sheet.getContents("A30"), "=3 / (4 / 2)")
629
        self.assertEqual(sheet.getContents("A31"), "=3 / 4 / 2")
630
        self.assertEqual(sheet.getContents("A32"), "=pi * 3")
631
        self.assertEqual(sheet.getContents("A33"), "=A32 / 3")
632
        self.assertEqual(sheet.getContents("A34"), "=1 < 2 ? <<A>> : <<B>>")
633
        self.assertEqual(sheet.getContents("A35"), "=min(A32:A33)")
634
        self.assertEqual(sheet.getContents("A36"), "=(1 < 2 ? 0 : 1) * 3")
635
        self.assertEqual(sheet.getContents("A37"), "=8 / (2 ^ 2 * 2)")
636
        self.assertEqual(sheet.getContents("A38"), "=2 ^ 2 * 2 / 8")
637
        self.assertEqual(sheet.getContents("A39"), "=2 ^ (2 * 2) / 8")
638
        self.assertEqual(sheet.getContents("A40"), "=8 / 2 ^ (2 * 2)")
639
        self.assertEqual(sheet.getContents("A41"), "=-1")
640
        self.assertEqual(sheet.getContents("A42"), "=-1")
641
        self.assertEqual(sheet.getContents("A43"), "=-(1 + 1)")
642
        self.assertEqual(sheet.getContents("A44"), "=-(1 - 1)")
643
        self.assertEqual(sheet.getContents("A45"), "=-(-1 + 1)")
644
        self.assertEqual(sheet.getContents("A46"), "=-(-1 + -1)")
645
        self.assertEqual(sheet.getContents("A47"), "=+1")
646
        self.assertEqual(sheet.getContents("A48"), "=+1")
647
        self.assertEqual(sheet.getContents("A49"), "=+(1 + 1)")
648
        self.assertEqual(sheet.getContents("A50"), "=+(1 - 1)")
649
        self.assertEqual(sheet.getContents("A51"), "=+(-1 + 1)")
650
        self.assertEqual(sheet.getContents("A52"), "=+(-1 + -1)")
651

652
    def testNumbers(self):
653
        """Test different numbers"""
654
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
655
        sheet.set("A1", "1")
656
        sheet.set("A2", "1.5")
657
        sheet.set("A3", ".5")
658
        sheet.set("A4", "1e2")
659
        sheet.set("A5", "1E2")
660
        sheet.set("A6", "1e-2")
661
        sheet.set("A7", "1E-2")
662
        sheet.set("A8", "1.5e2")
663
        sheet.set("A9", "1.5E2")
664
        sheet.set("A10", "1.5e-2")
665
        sheet.set("A11", "1.5E-2")
666
        sheet.set("A12", ".5e2")
667
        sheet.set("A13", ".5E2")
668
        sheet.set("A14", ".5e-2")
669
        sheet.set("A15", ".5E-2")
670
        sheet.set("A16", "1/1")
671
        sheet.set("A17", "1/2")
672
        sheet.set("A18", "2/4")
673
        self.doc.recompute()
674
        self.assertEqual(sheet.A1, 1)
675
        self.assertEqual(sheet.A2, 1.5)
676
        self.assertEqual(sheet.A3, 0.5)
677
        self.assertEqual(sheet.A4, 1e2)
678
        self.assertEqual(sheet.A5, 1e2)
679
        self.assertEqual(sheet.A6, 1e-2)
680
        self.assertEqual(sheet.A7, 1e-2)
681
        self.assertEqual(sheet.A8, 1.5e2)
682
        self.assertEqual(sheet.A9, 1.5e2)
683
        self.assertEqual(sheet.A10, 1.5e-2)
684
        self.assertEqual(sheet.A11, 1.5e-2)
685
        self.assertEqual(sheet.A12, 0.5e2)
686
        self.assertEqual(sheet.A13, 0.5e2)
687
        self.assertEqual(sheet.A14, 0.5e-2)
688
        self.assertEqual(sheet.A15, 0.5e-2)
689
        self.assertEqual(sheet.A16, 1)
690
        self.assertEqual(sheet.A17, 0.5)
691
        self.assertEqual(sheet.A18, 0.5)
692

693
    def testQuantitiesAndFractionsAsNumbers(self):
694
        """Test quantities and simple fractions as numbers"""
695
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
696
        sheet.set("A1", "1mm")
697
        sheet.set("A2", "1/2")
698
        sheet.set("A3", "4mm/2")
699
        sheet.set("A4", "2/mm")
700
        sheet.set("A5", "4/2mm")
701
        sheet.set("A6", "6mm/3s")
702
        self.doc.recompute()
703
        self.assertEqual(sheet.A1, Units.Quantity("1 mm"))
704
        self.assertEqual(sheet.A2, 0.5)
705
        self.assertEqual(sheet.A3, Units.Quantity("2 mm"))
706
        self.assertEqual(sheet.A4, Units.Quantity("2 1/mm"))
707
        self.assertEqual(sheet.A5, Units.Quantity("2 1/mm"))
708
        self.assertEqual(sheet.A6, Units.Quantity("2 mm/s"))
709

710
    def testRemoveRows(self):
711
        """Removing rows -- check renaming of internal cells"""
712
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
713
        sheet.set("A3", "123")
714
        sheet.set("A1", "=A3")
715
        sheet.removeRows("2", 1)
716
        self.assertEqual(sheet.getContents("A1"), "=A2")
717

718
    def testInsertRows(self):
719
        """Inserting rows -- check renaming of internal cells"""
720
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
721
        sheet.set("B1", "=B2")
722
        sheet.set("B2", "124")
723
        # Calling getContents() here activates ObjectIdentifier internal cache,
724
        # which needs to be tested as well.
725
        self.assertEqual(sheet.getContents("B1"), "=B2")
726
        sheet.insertRows("2", 1)
727
        self.assertEqual(sheet.getContents("B1"), "=B3")
728

729
    def testIssue3225(self):
730
        """Inserting rows -- check renaming of internal cells"""
731
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
732
        sheet.set("B2", "25")
733
        sheet.set("B3", "=B2")
734
        sheet.insertRows("2", 1)
735
        self.assertEqual(sheet.getContents("B4"), "=B3")
736

737
    def testRenameAlias(self):
738
        """Test renaming of alias1 to alias2 in a spreadsheet"""
739
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
740
        sheet.set("B1", "124")
741
        sheet.setAlias("B1", "alias1")
742
        sheet.set("B2", "=alias1")
743
        self.doc.recompute()
744
        self.assertEqual(sheet.get("alias1"), 124)
745
        self.assertEqual(sheet.get("B1"), 124)
746
        self.assertEqual(sheet.get("B2"), 124)
747
        sheet.setAlias("B1", "alias2")
748
        self.doc.recompute()
749
        self.assertEqual(sheet.get("alias2"), 124)
750
        self.assertEqual(sheet.getContents("B2"), "=alias2")
751

752
    def testRenameAlias2(self):
753
        """Test renaming of alias1 to alias2 in a spreadsheet, when referenced from another object"""
754
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
755
        sheet.set("B1", "124")
756
        sheet.setAlias("B1", "alias1")
757
        box = self.doc.addObject("Part::Box", "Box")
758
        box.setExpression("Length", "Spreadsheet.alias1")
759
        sheet.setAlias("B1", "alias2")
760
        self.assertEqual(box.ExpressionEngine[0][1], "Spreadsheet.alias2")
761

762
    def testRenameAlias3(self):
763
        """Test renaming of document object referenced from another object"""
764
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
765
        sheet.set("B1", "124")
766
        sheet.setAlias("B1", "alias1")
767
        box = self.doc.addObject("Part::Box", "Box")
768
        box.setExpression("Length", "Spreadsheet.alias1")
769
        box2 = self.doc.addObject("Part::Box", "Box")
770
        box2.setExpression("Length", "<<Spreadsheet>>.alias1")
771
        sheet.Label = "Params"
772
        self.assertEqual(box.ExpressionEngine[0][1], "Spreadsheet.alias1")
773
        self.assertEqual(box2.ExpressionEngine[0][1], "<<Params>>.alias1")
774

775
    def testAlias(self):
776
        """Playing with aliases"""
777
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Calc")
778
        sheet.setAlias("A1", "Test")
779
        self.assertEqual(sheet.getAlias("A1"), "Test")
780

781
        sheet.set("A1", "4711")
782
        self.doc.recompute()
783
        self.assertEqual(sheet.get("Test"), 4711)
784
        self.assertEqual(sheet.get("Test"), sheet.get("A1"))
785

786
    def testAmbiguousAlias(self):
787
        """Try to set the same alias twice (bug #2402)"""
788
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Calc")
789
        sheet.setAlias("A1", "Test")
790
        try:
791
            sheet.setAlias("A2", "Test")
792
            self.fail("An ambiguous alias was set which shouldn't be allowed")
793
        except Exception:
794
            self.assertEqual(sheet.getAlias("A2"), None)
795

796
    def testClearAlias(self):
797
        """This was causing a crash"""
798
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Calc")
799
        sheet.setAlias("A1", "Test")
800
        sheet.setAlias("A1", "")
801
        self.assertEqual(sheet.getAlias("A1"), None)
802

803
    def testSetInvalidAlias(self):
804
        """Try to use a cell address as alias name"""
805
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Calc")
806
        try:
807
            sheet.setAlias("A1", "B1")
808
        except Exception:
809
            self.assertEqual(sheet.getAlias("A1"), None)
810
        else:
811
            self.fail("A cell address was used as alias which shouldn't be allowed")
812

813
    def testSetInvalidAlias2(self):
814
        """Try to use a unit (reserved word) as alias name"""
815
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Calc")
816
        try:
817
            sheet.setAlias("A1", "mA")
818
        except Exception:
819
            self.assertEqual(sheet.getAlias("A1"), None)
820
        else:
821
            self.fail("A unit (reserved word) was used as alias which shouldn't be allowed")
822

823
    def testPlacementName(self):
824
        """Object name is equal to property name (bug #2389)"""
825
        if not FreeCAD.GuiUp:
826
            return
827

828
        import FreeCADGui
829

830
        o = self.doc.addObject("Part::FeaturePython", "Placement")
831
        FreeCADGui.Selection.addSelection(o)
832

833
    def testInvoluteGear(self):
834
        """Support of boolean or integer values"""
835
        try:
836
            import InvoluteGearFeature
837
        except ImportError:
838
            return
839
        InvoluteGearFeature.makeInvoluteGear("InvoluteGear")
840
        self.doc.recompute()
841
        sketch = self.doc.addObject("Sketcher::SketchObject", "Sketch")
842
        sketch.addGeometry(Part.LineSegment(v(0, 0, 0), v(10, 10, 0)), False)
843
        sketch.addConstraint(Sketcher.Constraint("Distance", 0, 65.285388))
844
        sketch.setExpression("Constraints[0]", "InvoluteGear.NumberOfTeeth")
845
        self.doc.recompute()
846
        self.assertIn("Up-to-date", sketch.State)
847

848
    def testSketcher(self):
849
        """Mixup of Label and Name (bug #2407)"""
850
        sketch = self.doc.addObject("Sketcher::SketchObject", "Sketch")
851
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
852
        sheet.setAlias("A1", "Length")
853
        self.doc.recompute()
854
        sheet.set("A1", "47,11")
855
        self.doc.recompute()
856

857
        index = sketch.addGeometry(Part.LineSegment(v(0, 0, 0), v(10, 10, 0)), False)
858
        sketch.addConstraint(Sketcher.Constraint("Distance", index, 14.0))
859
        self.doc.recompute()
860
        sketch.setExpression("Constraints[0]", "<<Spreadsheet>>.Length")
861
        self.doc.recompute()
862
        sheet.Label = "Calc"
863
        self.doc.recompute()
864
        self.assertEqual(sketch.ExpressionEngine[0][1], "<<Calc>>.Length")
865
        self.assertIn("Up-to-date", sketch.State)
866

867
    def testCrossDocumentLinks(self):
868
        """Expressions across files are not saved (bug #2442)"""
869

870
        # Create a box
871
        box = self.doc.addObject("Part::Box", "Box")
872

873
        # Create a second document with a cylinder
874
        doc2 = FreeCAD.newDocument()
875
        cylinder = doc2.addObject("Part::Cylinder", "Cylinder")
876
        cylinder.setExpression("Radius", "cube#Cube.Height")
877

878
        # Save and close first document
879
        self.doc.saveAs(self.TempPath + os.sep + "cube.fcstd")
880
        FreeCAD.closeDocument(self.doc.Name)
881

882
        # Save and close second document
883
        doc2.saveAs(self.TempPath + os.sep + "cylinder.fcstd")
884
        FreeCAD.closeDocument(doc2.Name)
885

886
        # Open both documents again
887
        self.doc = FreeCAD.openDocument(self.TempPath + os.sep + "cube.fcstd")
888
        doc2 = FreeCAD.openDocument(self.TempPath + os.sep + "cylinder.fcstd")
889

890
        # Check reference between them
891
        self.assertEqual(doc2.getObject("Cylinder").ExpressionEngine[0][1], "cube#Cube.Height")
892

893
        # Close second document
894
        FreeCAD.closeDocument(doc2.Name)
895

896
    def testMatrix(self):
897
        """Test Matrix/Vector/Placement/Rotation operations"""
898

899
        def plm_equal(plm1, plm2):
900
            from math import sqrt
901

902
            qpair = zip(plm1.Rotation.Q, plm2.Rotation.Q)
903
            qdiff1 = sqrt(sum([(v1 - v2) ** 2 for v1, v2 in qpair]))
904
            qdiff2 = sqrt(sum([(v1 + v2) ** 2 for v1, v2 in qpair]))
905
            return (plm1.Base - plm2.Base).Length < 1e-7 and (qdiff1 < 1e-12 or dqiff2 < 1e-12)
906

907
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
908

909
        mat = FreeCAD.Matrix()
910
        mat.scale(2, 1, 2)
911
        imat = mat.inverse()
912

913
        vec = FreeCAD.Vector(2, 1, 2)
914

915
        rot = FreeCAD.Rotation(FreeCAD.Vector(0, 1, 0), 45)
916
        irot = rot.inverted()
917

918
        pla = FreeCAD.Placement(vec, rot)
919
        ipla = pla.inverse()
920

921
        sheet.set("A1", "=vector(2, 1, 2)")
922

923
        # different ways of calling mscale()
924
        sheet.set("B1", "=mscale(create(<<matrix>>), A1)")
925
        sheet.set("C1", "=mscale(create(<<matrix>>), tuple(2, 1, 2))")
926
        sheet.set("A2", "=mscale(create(<<matrix>>), 2, 1, 2)")
927

928
        # test matrix power operation
929
        sheet.set("B2", "=A2^-2")
930
        sheet.set("C2", "=A2^-1")
931
        sheet.set("D2", "=A2^0")
932
        sheet.set("E2", "=A2^1")
933
        sheet.set("F2", "=A2^2")
934
        sheet.set("G2", "=matrix(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)")
935
        sheet.set("H2", "=G2^-1")
936

937
        sheet.set("A3", "=rotation(vector(0, 1, 0), 45)")
938

939
        # test rotation power operation
940
        sheet.set("B3", "=A3^-2")
941
        sheet.set("C3", "=A3^-1")
942
        sheet.set("D3", "=A3^0")
943
        sheet.set("E3", "=A3^1")
944
        sheet.set("F3", "=A3^2")
945

946
        sheet.set("A4", "=placement(A1, A3)")
947

948
        # test placement power operation
949
        sheet.set("B4", "=A4^-2")
950
        sheet.set("C4", "=A4^-1")
951
        sheet.set("D4", "=A4^0")
952
        sheet.set("E4", "=A4^1")
953
        sheet.set("F4", "=A4^2")
954

955
        # vector transformation with mixing matrix and placement and rotation
956
        sheet.set("A5", "=A2*A3*A4*A1")
957
        sheet.set("B5", "=B2*B4*B3*A1")
958
        sheet.set("C5", "=C3*C2*C4*A1")
959
        sheet.set("D5", "=D3*D4*D2*A1")
960
        sheet.set("E5", "=E4*E2*E3*A1")
961
        sheet.set("F5", "=F3*F4*F2*A1")
962

963
        # inverse of the above transformation with power -1 and minvert()
964
        sheet.set("A6", "=A4^-1 * minvert(A3) * A2^-1 * A5")
965
        sheet.set("B6", "=minvert(B3) * B4^-1 * minvert(B2) * B5")
966
        sheet.set("C6", "=C4^-1 * C2^-1 * C3^-1 * C5")
967
        sheet.set("D6", "=minvert(D4*D2) * minvert(D3) * D5")
968
        sheet.set("E6", "=(E2 * E3)^-1 * E4^-1 * E5")
969
        sheet.set("F6", "=(F3*F4*F2)^-1 * F5")
970

971
        # Rotate and translate.
972
        sheet.set("A7", "=placement(vector(1; 2; 3), vector(1; 0; 0); 0)")
973
        sheet.set("B7", "=mrotate(A7; vector(1; 0; 0); 90)")
974
        sheet.set("C7", "=mrotatex(A7; 90)")
975
        sheet.set("D7", "=mrotatey(A7; 90)")
976
        sheet.set("E7", "=mrotatez(A7; 90)")
977
        sheet.set("F7", "=mtranslate(A7; vector(1; 2; 3))")
978
        sheet.set("G7", "=mtranslate(A7; 1; 2; 3)")
979

980
        # Compatibility with old syntax.
981
        sheet.set("A8", "=create(<<vector>>, 2, 1, 2)")
982
        sheet.set("B8", "=create(<<rotation>>, create(<<vector>>, 0, 1, 0), 45)")
983
        sheet.set("C8", "=create(<<placement>>, A8, B8)")
984

985
        self.doc.recompute()
986

987
        self.assertEqual(sheet.A1, vec)
988

989
        self.assertEqual(sheet.B1, mat)
990
        self.assertEqual(sheet.C1, mat)
991
        self.assertEqual(sheet.A2, mat)
992

993
        self.assertEqual(sheet.B2, imat * imat)
994
        self.assertEqual(sheet.B2, mat**-2)
995
        self.assertEqual(sheet.C2, imat)
996
        self.assertEqual(sheet.C2, mat**-1)
997
        self.assertEqual(sheet.D2, FreeCAD.Matrix())
998
        self.assertEqual(sheet.D2, mat**0)
999
        self.assertEqual(sheet.E2, mat)
1000
        self.assertEqual(sheet.E2, mat**1)
1001
        self.assertEqual(sheet.F2, mat * mat)
1002
        self.assertEqual(sheet.F2, mat**2)
1003

1004
        self.assertTrue(sheet.H2.startswith("ERR: Cannot invert singular matrix"))
1005

1006
        self.assertEqual(sheet.A3, rot)
1007

1008
        rtol = 1e-12
1009
        self.assertTrue(sheet.B3.isSame(irot * irot, rtol))
1010
        self.assertTrue(sheet.B3.isSame(rot**-2, rtol))
1011
        self.assertTrue(sheet.C3.isSame(irot, rtol))
1012
        self.assertTrue(sheet.C3.isSame(rot**-1, rtol))
1013
        self.assertTrue(sheet.D3.isSame(FreeCAD.Rotation(), rtol))
1014
        self.assertTrue(sheet.D3.isSame(rot**0, rtol))
1015
        self.assertTrue(sheet.E3.isSame(rot, rtol))
1016
        self.assertTrue(sheet.E3.isSame(rot**1, rtol))
1017
        self.assertTrue(sheet.F3.isSame(rot * rot, rtol))
1018
        self.assertTrue(sheet.F3.isSame(rot**2, rtol))
1019

1020
        self.assertEqual(sheet.A4, pla)
1021

1022
        self.assertTrue(plm_equal(sheet.B4, ipla * ipla))
1023
        self.assertTrue(plm_equal(sheet.B4, pla**-2))
1024
        self.assertTrue(plm_equal(sheet.C4, ipla))
1025
        self.assertTrue(plm_equal(sheet.C4, pla**-1))
1026
        self.assertTrue(plm_equal(sheet.D4, FreeCAD.Placement()))
1027
        self.assertTrue(plm_equal(sheet.D4, pla**0))
1028
        self.assertTrue(plm_equal(sheet.E4, pla))
1029
        self.assertTrue(plm_equal(sheet.E4, pla**1))
1030
        self.assertTrue(plm_equal(sheet.F4, pla * pla))
1031
        self.assertTrue(plm_equal(sheet.F4, pla**2))
1032

1033
        tol = 1e-10
1034

1035
        self.assertLess(
1036
            sheet.A5.distanceToPoint(
1037
                sheet.A2.multiply(sheet.A3.Matrix).multiply(sheet.A4.Matrix).multVec(vec)
1038
            ),
1039
            tol,
1040
        )
1041
        self.assertLess(
1042
            sheet.B5.distanceToPoint(
1043
                sheet.B2.multiply(sheet.B4.Matrix).multiply(sheet.B3.Matrix).multVec(vec)
1044
            ),
1045
            tol,
1046
        )
1047
        self.assertLess(
1048
            sheet.C5.distanceToPoint(
1049
                sheet.C3.Matrix.multiply(sheet.C2).multiply(sheet.C4.Matrix).multVec(vec)
1050
            ),
1051
            tol,
1052
        )
1053
        self.assertLess(
1054
            sheet.D5.distanceToPoint(
1055
                sheet.D3.Matrix.multiply(sheet.D4.Matrix).multiply(sheet.D2).multVec(vec)
1056
            ),
1057
            tol,
1058
        )
1059
        self.assertLess(
1060
            sheet.E5.distanceToPoint(
1061
                sheet.E4.Matrix.multiply(sheet.E2).multiply(sheet.E3.Matrix).multVec(vec)
1062
            ),
1063
            tol,
1064
        )
1065
        self.assertLess(
1066
            sheet.F5.distanceToPoint(
1067
                sheet.F3.Matrix.multiply(sheet.F4.Matrix).multiply(sheet.F2).multVec(vec)
1068
            ),
1069
            tol,
1070
        )
1071

1072
        self.assertLess(sheet.A6.distanceToPoint(vec), tol)
1073
        self.assertLess(sheet.B6.distanceToPoint(vec), tol)
1074
        self.assertLess(sheet.C6.distanceToPoint(vec), tol)
1075
        self.assertLess(sheet.D6.distanceToPoint(vec), tol)
1076
        self.assertLess(sheet.E6.distanceToPoint(vec), tol)
1077
        self.assertLess(sheet.F6.distanceToPoint(vec), tol)
1078

1079
        self.assertTrue(sheet.A7.Base.isEqual(FreeCAD.Vector(1, 2, 3), tol))
1080
        self.assertTrue(sheet.B7.Base.isEqual(FreeCAD.Vector(1, -3, 2), tol))
1081
        self.assertTrue(sheet.C7.Base.isEqual(FreeCAD.Vector(1, -3, 2), tol))
1082
        self.assertTrue(sheet.D7.Base.isEqual(FreeCAD.Vector(3, 2.0, -1), tol))
1083
        self.assertTrue(sheet.E7.Base.isEqual(FreeCAD.Vector(-2, 1, 3.0), tol))
1084
        self.assertTrue(sheet.F7.Base.isEqual(FreeCAD.Vector(2, 4, 6), tol))
1085
        self.assertTrue(sheet.G7.Base.isEqual(FreeCAD.Vector(2, 4, 6), tol))
1086

1087
        self.assertEqual(sheet.A8, vec)
1088
        self.assertEqual(sheet.B8, rot)
1089
        self.assertEqual(sheet.C8, pla)
1090

1091
    def testIssue3128(self):
1092
        """Regression test for issue 3128; mod should work with arbitrary units for both arguments"""
1093
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1094
        sheet.set("A1", "=mod(7mm;3mm)")
1095
        sheet.set("A2", "=mod(7kg;3mm)")
1096
        self.doc.recompute()
1097
        self.assertEqual(sheet.A1, Units.Quantity("1"))
1098
        self.assertEqual(sheet.A2, Units.Quantity("1 kg/mm"))
1099

1100
    def testIssue3363(self):
1101
        """Regression test for issue 3363; Nested conditionals statement fails with additional conditional statement in false-branch"""
1102
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1103
        sheet.set("A1", "1")
1104
        sheet.set("B1", "=A1==1?11:(A1==2?12:13)")
1105
        sheet.set("C1", "=A1==1?(A1==2?12:13) : 11")
1106
        self.doc.recompute()
1107

1108
        # Save and close first document
1109
        self.doc.saveAs(self.TempPath + os.sep + "conditionals.fcstd")
1110
        FreeCAD.closeDocument(self.doc.Name)
1111

1112
        # Open documents again
1113
        self.doc = FreeCAD.openDocument(self.TempPath + os.sep + "conditionals.fcstd")
1114

1115
        sheet = self.doc.getObject("Spreadsheet")
1116
        self.assertEqual(sheet.getContents("B1"), "=A1 == 1 ? 11 : (A1 == 2 ? 12 : 13)")
1117
        self.assertEqual(sheet.getContents("C1"), "=A1 == 1 ? (A1 == 2 ? 12 : 13) : 11")
1118

1119
    def testIssue3432(self):
1120
        """Regression test for issue 3432; numbers with units are ignored from aggregates"""
1121
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1122
        sheet.set("A1", "1mm")
1123
        sheet.set("B1", "2mm")
1124
        sheet.set("C1", "=max(A1:B1;3mm)")
1125
        self.doc.recompute()
1126
        self.assertEqual(sheet.get("C1"), Units.Quantity("3 mm"))
1127

1128
    def testIssue4156(self):
1129
        """Regression test for issue 4156; necessarily use of leading '=' to enter an expression, creates inconsistent behavior depending on the spreadsheet state"""
1130
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1131
        sheet.set("A3", "A1")
1132
        sheet.set("A1", "1000")
1133
        self.doc.recompute()
1134
        sheet.set("A3", "")
1135
        sheet.set("A3", "A1")
1136
        self.assertEqual(sheet.getContents("A3"), "'A1")
1137

1138
    def testInsertRowsAlias(self):
1139
        """Regression test for issue 4429; insert rows to sheet with aliases"""
1140
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1141
        sheet.set("A3", "1")
1142
        sheet.setAlias("A3", "alias1")
1143
        sheet.set("A4", "=alias1 + 1")
1144
        sheet.setAlias("A4", "alias2")
1145
        sheet.set("A5", "=alias2 + 1")
1146
        self.doc.recompute()
1147
        sheet.insertRows("1", 1)
1148
        self.doc.recompute()
1149
        self.assertEqual(sheet.A6, 3)
1150

1151
    def testInsertColumnsAlias(self):
1152
        """Regression test for issue 4429; insert columns to sheet with aliases"""
1153
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1154
        sheet.set("C1", "1")
1155
        sheet.setAlias("C1", "alias1")
1156
        sheet.set("D1", "=alias1 + 1")
1157
        sheet.setAlias("D1", "alias2")
1158
        sheet.set("E1", "=alias2 + 1")
1159
        self.doc.recompute()
1160
        sheet.insertColumns("A", 1)
1161
        self.doc.recompute()
1162
        self.assertEqual(sheet.F1, 3)
1163

1164
    def testRemoveRowsAlias(self):
1165
        """Regression test for issue 4429; remove rows from sheet with aliases"""
1166
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1167
        sheet.set("A3", "1")
1168
        sheet.setAlias("A3", "alias1")
1169
        sheet.set("A5", "=alias1 + 1")
1170
        sheet.setAlias("A5", "alias2")
1171
        sheet.set("A4", "=alias2 + 1")
1172
        self.doc.recompute()
1173
        sheet.removeRows("1", 1)
1174
        self.doc.recompute()
1175
        self.assertEqual(sheet.A3, 3)
1176

1177
    def testRemoveRowsAliasReuseName(self):
1178
        """Regression test for issue 4492; deleted aliases remains in database"""
1179
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1180
        sheet.setAlias("B2", "test")
1181
        self.doc.recompute()
1182
        sheet.removeRows("2", 1)
1183
        sheet.setAlias("B3", "test")
1184

1185
    def testRemoveColumnsAlias(self):
1186
        """Regression test for issue 4429; remove columns from sheet with aliases"""
1187
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1188
        sheet.set("C1", "1")
1189
        sheet.setAlias("C1", "alias1")
1190
        sheet.set("E1", "=alias1 + 1")
1191
        sheet.setAlias("E1", "alias2")
1192
        sheet.set("D1", "=alias2 + 1")
1193
        self.doc.recompute()
1194
        sheet.removeColumns("A", 1)
1195
        self.doc.recompute()
1196
        self.assertEqual(sheet.C1, 3)
1197

1198
    def testRemoveColumnsAliasReuseName(self):
1199
        """Regression test for issue 4492; deleted aliases remains in database"""
1200
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1201
        sheet.setAlias("B2", "test")
1202
        self.doc.recompute()
1203
        sheet.removeColumns("B", 1)
1204
        sheet.setAlias("C3", "test")
1205

1206
    def testUndoAliasCreationReuseName(self):
1207
        """Test deleted aliases by undo remains in database"""
1208
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1209

1210
        self.doc.UndoMode = 1
1211
        self.doc.openTransaction("create alias")
1212
        sheet.setAlias("B2", "test")
1213
        self.doc.commitTransaction()
1214
        self.doc.recompute()
1215

1216
        self.doc.undo()
1217
        self.doc.recompute()
1218
        sheet.setAlias("C3", "test")
1219

1220
    def testCrossLinkEmptyPropertyName(self):
1221
        # https://forum.freecad.org/viewtopic.php?f=3&t=58603
1222
        base = FreeCAD.newDocument("base")
1223
        sheet = base.addObject("Spreadsheet::Sheet", "Spreadsheet")
1224
        sheet.setAlias("A1", "x")
1225
        sheet.set("x", "42mm")
1226
        base.recompute()
1227

1228
        square = FreeCAD.newDocument("square")
1229
        body = square.addObject("PartDesign::Body", "Body")
1230
        box = square.addObject("PartDesign::AdditiveBox", "Box")
1231
        body.addObject(box)
1232
        box.Length = 10.00
1233
        box.Width = 10.00
1234
        box.Height = 10.00
1235
        square.recompute()
1236

1237
        basePath = self.TempPath + os.sep + "base.FCStd"
1238
        base.saveAs(basePath)
1239
        squarePath = self.TempPath + os.sep + "square.FCStd"
1240
        square.saveAs(squarePath)
1241

1242
        base.save()
1243
        square.save()
1244

1245
        FreeCAD.closeDocument(square.Name)
1246
        FreeCAD.closeDocument(base.Name)
1247

1248
        ##
1249
        ## preparation done
1250
        base = FreeCAD.openDocument(basePath)
1251
        square = FreeCAD.openDocument(squarePath)
1252

1253
        square.Box.setExpression("Length", "base#Spreadsheet.x")
1254
        square.recompute()
1255

1256
        square.save()
1257
        base.save()
1258
        FreeCAD.closeDocument(square.Name)
1259
        FreeCAD.closeDocument(base.Name)
1260

1261
    def testExpressionWithAlias(self):
1262
        # https://forum.freecad.org/viewtopic.php?p=564502#p564502
1263
        ss1 = self.doc.addObject("Spreadsheet::Sheet", "Input")
1264
        ss1.setAlias("A1", "one")
1265
        ss1.setAlias("A2", "two")
1266
        ss1.set("A1", "1")
1267
        ss1.set("A2", "2")
1268
        self.doc.recompute()
1269

1270
        ss2 = self.doc.addObject("Spreadsheet::Sheet", "Output")
1271
        ss2.set("A1", "=Input.A1 + Input.A2")
1272
        ss2.set("A2", "=Input.one + Input.two")
1273
        ss2.set("A3", "=<<Input>>.A1 + <<Input>>.A2")
1274
        ss2.set("A4", "=<<Input>>.one + <<Input>>.two")
1275
        self.doc.recompute()
1276

1277
        self.assertEqual(ss2.get("A1"), 3)
1278
        self.assertEqual(ss2.get("A2"), 3)
1279
        self.assertEqual(ss2.get("A3"), 3)
1280
        self.assertEqual(ss2.get("A4"), 3)
1281

1282
        project_path = self.TempPath + os.sep + "alias.FCStd"
1283
        self.doc.saveAs(project_path)
1284
        FreeCAD.closeDocument(self.doc.Name)
1285
        self.doc = FreeCAD.openDocument(project_path)
1286
        ss1 = self.doc.Input
1287
        ss2 = self.doc.Output
1288

1289
        self.assertEqual(ss2.get("A1"), 3)
1290
        self.assertEqual(ss2.get("A2"), 3)
1291
        self.assertEqual(ss2.get("A3"), 3)
1292
        self.assertEqual(ss2.get("A4"), 3)
1293

1294
        ss1.set("A1", "2")
1295
        self.doc.recompute()
1296

1297
        self.assertEqual(ss1.get("A1"), 2)
1298
        self.assertEqual(ss1.get("one"), 2)
1299

1300
        self.assertEqual(ss2.get("A1"), 4)
1301
        self.assertEqual(ss2.get("A2"), 4)
1302
        self.assertEqual(ss2.get("A3"), 4)
1303
        self.assertEqual(ss2.get("A4"), 4)
1304

1305
    def testIssue6844(self):
1306
        body = self.doc.addObject("App::FeaturePython", "Body")
1307
        body.addProperty("App::PropertyEnumeration", "Configuration")
1308
        body.Configuration = ["Item1", "Item2", "Item3"]
1309

1310
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Sheet")
1311
        sheet.addProperty("App::PropertyString", "A2")
1312
        sheet.A2 = "Item2"
1313
        sheet.addProperty("App::PropertyEnumeration", "body")
1314
        sheet.body = ["Item1", "Item2", "Item3"]
1315

1316
        sheet.setExpression(".body.Enum", "cells[<<A2:|>>]")
1317
        sheet.setExpression(
1318
            ".cells.Bind.B1.ZZ1",
1319
            "tuple(.cells; <<B>> + str(hiddenref(Body.Configuration) + 2); <<ZZ>> + str(hiddenref(Body.Configuration) + 2))",
1320
        )
1321

1322
        self.doc.recompute()
1323
        self.doc.UndoMode = 0
1324
        self.doc.removeObject("Body")
1325
        sheet.clearAll()
1326

1327
    def testIssue6840(self):
1328
        body = self.doc.addObject("App::FeaturePython", "Body")
1329
        body.addProperty("App::PropertyEnumeration", "Configuration")
1330
        body.Configuration = ["Item1", "Item2", "Item3"]
1331

1332
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Sheet")
1333
        sheet.addProperty("App::PropertyString", "A2")
1334
        sheet.A2 = "Item2"
1335
        sheet.addProperty("App::PropertyEnumeration", "body")
1336
        sheet.body = ["Item1", "Item2", "Item3"]
1337

1338
        sheet.setExpression(".body.Enum", "cells[<<A2:|>>]")
1339
        sheet.setExpression(
1340
            ".cells.Bind.B1.ZZ1",
1341
            "tuple(.cells; <<B>> + str(hiddenref(Body.Configuration) + 2); <<ZZ>> + str(hiddenref(Body.Configuration) + 2))",
1342
        )
1343

1344
        self.doc.recompute()
1345
        self.doc.clearDocument()
1346

1347
    def testFixPR6843(self):
1348
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Sheet")
1349
        sheet.set("A5", "a")
1350
        sheet.set("A6", "b")
1351
        self.doc.recompute()
1352
        sheet.insertRows("6", 1)
1353
        self.doc.recompute()
1354
        self.assertEqual(sheet.A5, "a")
1355
        self.assertEqual(sheet.A7, "b")
1356
        with self.assertRaises(AttributeError):
1357
            self.assertEqual(sheet.A6, "")
1358

1359
    def testBindAcrossSheets(self):
1360
        ss1 = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet1")
1361
        ss2 = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet2")
1362
        ss2.set("B1", "B1")
1363
        ss2.set("B2", "B2")
1364
        ss2.set("C1", "C1")
1365
        ss2.set("C2", "C2")
1366
        ss2.set("D1", "D1")
1367
        ss2.set("D2", "D2")
1368

1369
        ss1.setExpression(".cells.Bind.A3.C4", "tuple(Spreadsheet2.cells, <<B1>>, <<D2>>)")
1370
        self.doc.recompute()
1371

1372
        self.assertEqual(ss1.A3, ss2.B1)
1373
        self.assertEqual(ss1.A4, ss2.B2)
1374
        self.assertEqual(ss1.B3, ss2.C1)
1375
        self.assertEqual(ss1.B4, ss2.C2)
1376
        self.assertEqual(ss1.C3, ss2.D1)
1377
        self.assertEqual(ss1.C4, ss2.D2)
1378

1379
        self.assertEqual(len(ss1.ExpressionEngine), 1)
1380
        ss1.setExpression(".cells.Bind.A3.C4", None)
1381
        self.doc.recompute()
1382

1383
    def testBindHiddenRefAcrossSheets(self):
1384
        ss1 = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet1")
1385
        ss2 = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet2")
1386
        ss2.set("B1", "B1")
1387
        ss2.set("B2", "B2")
1388
        ss2.set("C1", "C1")
1389
        ss2.set("C2", "C2")
1390
        ss2.set("D1", "D1")
1391
        ss2.set("D2", "D2")
1392

1393
        self.doc.recompute()
1394
        ss1.setExpression(".cells.Bind.A3.C4", None)
1395
        ss1.setExpression(
1396
            ".cells.BindHiddenRef.A3.C4", "hiddenref(tuple(Spreadsheet2.cells, <<B1>>, <<D2>>))"
1397
        )
1398
        self.doc.recompute()
1399

1400
        ss1.recompute()  # True
1401
        self.assertEqual(ss1.A3, ss2.B1)
1402

1403
        ss1.setExpression(".cells.Bind.A3.C4", None)
1404
        ss1.setExpression(".cells.BindHiddenRef.A3.C4", None)
1405
        self.doc.recompute()
1406
        self.assertEqual(len(ss1.ExpressionEngine), 0)
1407

1408
    def testMergeCells(self):
1409
        ss1 = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet1")
1410
        ss1.mergeCells("A1:B4")
1411
        ss1.mergeCells("C1:D4")
1412
        self.doc.recompute()
1413
        ss1.set("B1", "fail")
1414
        self.doc.recompute()
1415
        with self.assertRaises(AttributeError):
1416
            self.assertEqual(ss1.B1, "fail")
1417

1418
    def testMergeCellsAndBind(self):
1419
        ss1 = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet1")
1420
        ss1.mergeCells("A1:B1")
1421
        ss1.setExpression(".cells.Bind.A1.A1", "tuple(.cells, <<A2>>, <<A2>>)")
1422
        ss1.set("A2", "test")
1423
        self.doc.recompute()
1424
        self.assertEqual(ss1.A1, ss1.A2)
1425
        ss1.set("B1", "fail")
1426
        self.doc.recompute()
1427
        with self.assertRaises(AttributeError):
1428
            self.assertEqual(ss1.B1, "fail")
1429

1430
    def testGetUsedCells(self):
1431
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1432
        test_cells = ["B13", "C14", "D15"]
1433
        for i, cell in enumerate(test_cells):
1434
            sheet.set(cell, str(i))
1435

1436
        used_cells = sheet.getUsedCells()
1437
        self.assertEqual(len(used_cells), len(test_cells))
1438
        for cell in test_cells:
1439
            self.assertTrue(cell in used_cells)
1440

1441
        for cell in test_cells:
1442
            sheet.set(cell, "")
1443
            sheet.setAlignment(cell, "center")
1444
        non_empty_cells = sheet.getUsedCells()
1445
        self.assertEqual(len(non_empty_cells), len(test_cells))  # Alignment counts as "used"
1446

1447
    def testGetUsedRange(self):
1448
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1449
        test_cells = ["C5", "Z3", "D10", "E20"]
1450
        for i, cell in enumerate(test_cells):
1451
            sheet.set(cell, str(i))
1452
        used_range = sheet.getUsedRange()
1453
        self.assertEqual(used_range, ("C3", "Z20"))
1454

1455
        for i, cell in enumerate(test_cells):
1456
            sheet.set(cell, "")
1457
            sheet.setAlignment(cell, "center")
1458
        used_range = sheet.getUsedRange()
1459
        self.assertEqual(used_range, ("C3", "Z20"))
1460

1461
    def testGetNonEmptyCells(self):
1462
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1463
        test_cells = ["B13", "C14", "D15"]
1464
        for i, cell in enumerate(test_cells):
1465
            sheet.set(cell, str(i))
1466

1467
        non_empty_cells = sheet.getNonEmptyCells()
1468
        self.assertEqual(len(non_empty_cells), len(test_cells))
1469
        for cell in test_cells:
1470
            self.assertTrue(cell in non_empty_cells)
1471

1472
        for cell in test_cells:
1473
            sheet.set(cell, "")
1474
            sheet.setAlignment(cell, "center")
1475
        non_empty_cells = sheet.getNonEmptyCells()
1476
        self.assertEqual(len(non_empty_cells), 0)  # Alignment does not count as "non-empty"
1477

1478
    def testGetNonEmptyRange(self):
1479
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1480
        test_cells = ["C5", "Z3", "D10", "E20"]
1481
        for i, cell in enumerate(test_cells):
1482
            sheet.set(cell, str(i))
1483
        non_empty_range = sheet.getNonEmptyRange()
1484
        self.assertEqual(non_empty_range, ("C3", "Z20"))
1485

1486
        for i, cell in enumerate(test_cells):
1487
            sheet.set(cell, "")
1488
            sheet.setAlignment(cell, "center")
1489
        more_cells = ["D10", "X5", "E10", "K15"]
1490
        for i, cell in enumerate(more_cells):
1491
            sheet.set(cell, str(i))
1492
        non_empty_range = sheet.getNonEmptyRange()
1493
        self.assertEqual(non_empty_range, ("D5", "X15"))
1494

1495
    def testAliasEmptyCell(self):
1496
        # https://github.com/FreeCAD/FreeCAD/issues/7841
1497
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1498
        sheet.setAlias("A1", "aliasOfEmptyCell")
1499
        self.assertEqual(sheet.getCellFromAlias("aliasOfEmptyCell"), "A1")
1500

1501
    def testParensAroundCondition(self):
1502
        """Parens around a condition should be accepted"""
1503
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1504

1505
        sheet.set("A1", "=(1 == 1) ? 1 : 0")
1506
        self.doc.recompute()
1507
        self.assertEqual(sheet.getContents("A1"), "=1 == 1 ? 1 : 0")
1508
        self.assertEqual(sheet.A1, 1)
1509

1510
    def testIssue6395(self):
1511
        """Testing strings are correctly saved and restored"""
1512
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1513
        sheet.set("A1", "'36C")  # Use a string that may be parsed as a quantity
1514
        self.doc.recompute()
1515

1516
        self.doc.saveAs(self.TempPath + os.sep + "string.fcstd")
1517
        FreeCAD.closeDocument(self.doc.Name)
1518

1519
        self.doc = FreeCAD.openDocument(self.TempPath + os.sep + "string.fcstd")
1520

1521
        sheet = self.doc.getObject("Spreadsheet")
1522
        self.assertEqual(sheet.getContents("A1"), "'36C")
1523
        self.assertEqual(sheet.get("A1"), "36C")
1524

1525
    def testVectorFunctions(self):
1526
        sheet = self.doc.addObject("Spreadsheet::Sheet", "Spreadsheet")
1527

1528
        sheet.set("A1", "=vcross(vector(1; 2; 3); vector(1; 5; 7))")
1529

1530
        sheet.set("B1", "=vdot(vector(1; 2; 3); vector(4; -5; 6))")
1531

1532
        sheet.set("C1", "=vangle(vector(1; 0; 0); vector(0; 1; 0))")
1533

1534
        sheet.set("D1", "=vnormalize(vector(1; 0; 0))")
1535
        sheet.set("D2", "=vnormalize(vector(1; 1; 1))")
1536

1537
        sheet.set("E1", "=vscale(vector(1; 2; 3); 2; 3; 4)")
1538
        sheet.set("E2", "=vscalex(vector(1; 2; 3); -2)")
1539
        sheet.set("E3", "=vscaley(vector(1; 2; 3); -2)")
1540
        sheet.set("E4", "=vscalez(vector(1; 2; 3); -2)")
1541

1542
        sheet.set("F1", "=vlinedist(vector(1; 2; 3); vector(2; 3; 4); vector(3; 4; 5))")
1543
        sheet.set("F2", "=vlinesegdist(vector(1; 2; 3); vector(2; 3; 4); vector(3; 4; 5))")
1544
        sheet.set("F3", "=vlineproj(vector(1; 2; 3); vector(2; 3; 4); vector(3; 4; 5))")
1545
        sheet.set("F4", "=vplanedist(vector(1; 2; 3); vector(2; 3; 4); vector(3; 4; 5))")
1546
        sheet.set("F5", "=vplaneproj(vector(1; 2; 3); vector(2; 3; 4); vector(3; 4; 5))")
1547

1548
        self.doc.recompute()
1549

1550
        tolerance = 1e-10
1551

1552
        self.assertEqual(sheet.A1, FreeCAD.Vector(-1, -4, 3))
1553

1554
        self.assertEqual(sheet.B1, 12)
1555

1556
        self.assertEqual(sheet.C1, 90)
1557

1558
        self.assertEqual(sheet.D1, FreeCAD.Vector(1, 0, 0))
1559
        self.assertLess(
1560
            sheet.D2.distanceToPoint(FreeCAD.Vector(1 / sqrt(3), 1 / sqrt(3), 1 / sqrt(3))),
1561
            tolerance,
1562
        )
1563

1564
        self.assertEqual(sheet.E1, FreeCAD.Vector(2, 6, 12))
1565
        self.assertEqual(sheet.E2, FreeCAD.Vector(-2, 2, 3))
1566
        self.assertEqual(sheet.E3, FreeCAD.Vector(1, -4, 3))
1567
        self.assertEqual(sheet.E4, FreeCAD.Vector(1, 2, -6))
1568

1569
        self.assertLess(abs(sheet.F1.Value - 0.3464), 0.0001)
1570
        self.assertEqual(sheet.F2, FreeCAD.Vector(1, 1, 1))
1571
        self.assertLess(sheet.F3.distanceToPoint(FreeCAD.Vector(0.28, 0.04, -0.2)), tolerance)
1572
        self.assertLess(abs(sheet.F4.Value - -1.6971), 0.0001)
1573
        self.assertEqual(sheet.F5, FreeCAD.Vector(1.72, 2.96, 4.2))
1574

1575
    def tearDown(self):
1576
        # closing doc
1577
        FreeCAD.closeDocument(self.doc.Name)
1578