writing unit test for calc functions

[Winfried Donkers]

Hi Markus,

> @Winfried: You are writing most of the spreadsheet functions right 
> now. Would that be something that would help you add tests for all new 
> functions? You could add your test cases directly in the spreadsheet 
> and use it for manual checking as well as later for an automated test.
I would like to add unit tests like 
sc/qa/unit/data/xlsx/functions-excel-2010.xlsx and tested in 
sc/qa/unit/subsequent_export-test.cxx (test principle conceived by 
Kohei), e.g. no csv files but an xlsx-document saved by Excel. The 
document contains boolean verifications is such a way that one cell 
(here Sheet1.E2) needs to be checked in the unit test for all functions 
in the document. Only if that cell contains a false value, the 
individual verifications for each function needs to be traversed to 
provide a message which function(s) went wrong.
With the xlsx, both excel-import of the function and the function itself 
are tested.
As at that time csv files were to be used for testing the special cases 
and various use cases of each function, and Raal seemed to pick up that 
part, I never went farther than the above-mentioned xlsx-document.

Of course the principle as used in the above test file can perfectly be 
used for more elaborate unit tests, removing the need for csv files. it 
is also possible for non-developers to create these test files, as long 
as the 'verification cells' are in a fixed row or column. The only 
developer action needed would be to include the file, like in 
sc/qa/unit/subsequent_export-test.cxx for the excel2010/2013 functions.

For the Excel2016 functions, on which I have bee working since last 
autumn, the FORECAST_ETS functions present a problem for me as far as 
testing is concerned. The results of the functions depend on the 
algorithms used (and thresholds used) to find optimum values for 
coefficients, and will always differ between Excel and Calc. Even with 
Calc I can imagine an improvement of the algorithm leading to different 
results. It wouldn't seem right to have to modify the unit test as well 
in that case. As for the other functions, There still being 
developed/evaluated and unit tests are on my to-do-list, just a 
providing a basis for Calc help.

One (personal) remark: deciding which use cases, which values, etc. to 
use for unit tests is a bit of a vague area for me. Arguments can be of 
different types (e.g. string, double, single/double external/internal 
reference, matrix, single value or range), can have almost unlimited 
different values and can be optional. Testing all use cases would fill a 
lot of cells and doesn't seem practical to me. But for deciding which 
use cases to test and which to skip clear guidelines would be a great 
help (for me at least).

Does all this match with your ideas?

Winfried