openoffice-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rob Weir <>
Subject Re: Example of spreadsheet formula testing
Date Wed, 28 Aug 2013 12:30:38 GMT
On Wed, Aug 28, 2013 at 4:51 AM, Herbert Duerr <> wrote:
> On 27.08.2013 15:29, Rob Weir wrote:
>> On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr <> wrote:
>>> [...]
>>> A good idea. I just wrote a task [1] and a script [2] that takes a
>>> slightly
>>> modified version of Regina's sample document and checks whether all of
>>> its
>>> tests pass. This script is now part of the Functional Verification Test
>>> (a.k.a. FVT).
>>> [1]
>>> [2]
>>> Adding further test spreadsheets is simple. They need a "TestID" row and
>>> a
>>> "TestOK" row like [3]. Such new test spreadsheets have to be added to
>>> [4]'s
>>> testFormulaDocs() method to be picked up.
>> Nice.  If we can have test cases written by non-programmers, and then
>> automated testing of these sheets, then we'll have the best of both
>> worlds.
>> I assume we'd want to agree on a test template.
> Here is a suggested test template. It is small and fulfills the requirements
> of containing exactly one sheet and having two columns named "TestID" and
> "TestOK". Please use a monospaced font for your mail client to view it:
>          A           B       C        D           E           F
> 1      TestID    Addend1 Addend2 ExpectedSum CalculatedSum  TestOK
> 2   "OnePlusOne"    1       1         2         =B2+C2     =(D2=E2)
> 3      "Seven"      3       4         7         =B3+C3     =(D3=E3)

You are able to then introspect the sheet, similar to JUnit, and
report the results of each row?  That would be good.

A few complicating factors to consider:

1) Not all functions take two parameters.  Some take more, some less,
and many are variable number.  So if the test driver is sensitive to
only the label "TestID" and "TestOK" (and not the column index) this
would be best.

2) Some functions operate on ranges and require additional data, stuff
that cannot easily be fit into a single row.  For example, DCOUNT(),
VLOOKUP(), etc.  Where should we put that test data, so it will not
mess up the automation?  Would it be safe to mark the end of the test
cases by a blank row, and then any extra test data after that?

3) Then we have array functions, for example matrix functions like
MMULT().  These don't return a single value, but return values into a
range.  Maybe in those cases we would do the calculation in an special
"test data" area of the sheet, and then have test cases, one per row,
to test each value of the returned matrix.  So a 2x2 matrix would have
4 test rows.



>>  For example, one
>> approach is to bootstrap the tests like this:
>> 1) Have the fundamental test cases for IF(), TRUE(), FALSE() and a
>> handful of other core functions written in Java, not requiring a test
>> spreadsheet.
> Core functionionality must be checked in one of the pure automatic test
> suites like BVT ("Build Verification Tests") or FVT ("Functional
> Verification Tests") of course.
> The idea that additionally to this traditional tests there should also be
> test cases suitable for both manual and automated testing and that can be
> understood, checked and enhanced by non-developers opens options for testers
> and power-users that were not open before to non-developers.
>> 2) Then write the test cases for the specialized functions in test
>> documents, and reserve a cell (A1 for example) to return 1 if all
>> tests in the document pass, 0 otherwise.
> The new test script checks the results of each individual sub-test and the
> test log reports each failed test. Having a specialized cell that summarizes
> all the sub-tests into one result is possible of course. For the example
> above a cell =(SUM(F2:F3)=COUNT(F2:F3)) would provide just that. It isn't
> needed for the new automatism though.
> The test spreadsheets should mostly be developed to be most useful for
> manual testing and for extending by testers and power users while keeping
> the requirements ("one sheet", "one TestID column" and "one TestOK column")
> for the new automation at a minimum. Adding more requirements again would be
> counterproductive as lowering the barrier of entry is the central point of
> the new mechanism.
>> 3) Automate the loading and evaluation of the test documents.
>> (Since the test documents would depend on correct operation of IF(),
>> we cannot use the test documents to test IF() itself)
> A build where simple things like IF() don't work should never make it into
> manual testing. Testers should expect things like that to work.
> Herbert
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> For additional commands, e-mail:

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message