openoffice-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Herbert Duerr <>
Subject Re: Example of spreadsheet formula testing
Date Wed, 28 Aug 2013 14:00:28 GMT
On 28.08.2013 14:30, Rob Weir wrote:
> 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.

Yes, the script uses Liu Zhe's test framework based on junit to 
introspect the sheet.

It only looks for the identifiers "TestID" and "TestOK" so it doesn't 
matter in which columns they are. For performance reason it doesn't 
check every one of the 32 million possible cell positions for these 
markers but only the first 8x8 cells for now. These limits can be 
increased, but as the test documents are also intended for human 
consumption keeping these markers at the start of the test document is a 
reasonable restriction.

> 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.

Yes. The other columns are completely ignored. They are just for making 
the calculation clean and obvious for the reader of the spreadsheet.

> 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?

Rows with empty test-ids are ignored. The test-id of a row is the text 
content of the cell "TestID" column.

> 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.

Yes, just keep the test-ids of the rows with intermediate results empty. 
They will be ignored.


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

View raw message