automated testing

FLX TMS FlexCel for VCL & FMX
1

I am working on a project which emits a large number of Excel spreadsheets, each of which is build based on a number of customer options. Because of the large number of different sheets and possible results, I am considering how we might accomplish some automated testing. 


My thoughts so far:
1. We accumulate a collection of datasets (content and options) which have been validated.  These can be binary or XML streams.
2. We can implement logging, in which the output amounts to a script of actions taken to produce the spreadsheet. These could be stored as "gold standards", once validated, and compared to newly produced logs, to check for variations, as code is modified.
3. Once we have validated "gold standard" reference spreadsheets, I am thinking that a good way of implementing testing might be to use APIMate to produce code for the validated sheets, then use APIMate to generate code for the new sheet, and compare the two.

Are there any other techniques you could suggest? My concern, obviously, is to determine a process which will avoid visual checking as much as possible on new releases.

Thanks,

Bill Meyer
2

Hi,

Myself, I have a similar situation: I have many thousands of "gold standards" that FlexCel must be able to produce correctly in order to do a release. I do look at a lower level than APIMate, because that would be fooling myself: APIMate uses FlexCel, and so it can't be trusted to reveal bugs in FlexCel. So in my case I do a lot of manual comparing, and have setup a big framework that helps me separate cosmetic changes from real changes and bugs.

But in your case, I think I would use APIMate for comparing, yes. I've even blogged about how to diff Excel files with APIMate here:

http://www.tmssoftware.com/site/blog.asp?post=195
(note that some change in the blog css styles rendered the images too small, but you can still see them by dragging them and dropping them in a new tab)

But note that this post was for FlexCel.NET. For some reason I don't remember (I think it was OS/X related (!)), you can't call the APIMate delivered with Delphi from the command line. And by default, it will only show one sheet, not all sheets as you would need to diff the full file, not just the active sheet.

The good news is: the support for doing it is all there, so it is easy to modify APIMate in delphi to show all sheets.

If you open the APIMate project, UApiMate.pas, and search for the text:

  edData.Text := Api.Process(SelectedLang, edXlsFileName.Text, cbUsing.Checked);

And change it to:
  edData.Text := Api.Process(SelectedLang, edXlsFileName.Text, cbUsing.Checked, true);

And APIMate will show all sheets. Of course, in your final code you'll want to call this line directly from your code, not using APIMate.exe, but the library instead. (All the code is in a library, APIMate.exe is just a wrapper that calls it). But when you call that line, make sure to remember to output all the sheets.

I don't know, this is what I can think of. As said, Automated testing in FlexCel is a little lower level, but it all boils down to having a big set of "reference" files, and seeing if the new files are still the same. The method is quite robust at least for me, and finds sometimes bugs introduced that I wouldn't have though in a million year. Due to the big number of reference files and the fact that it finds all kind of stuff, it is that I am very sure when I release a new version that we aren't breaking anything. If FlexCel can still generate the thousands of reference files correctly it is very likely that it can also generate the files from its users. And I don't remember the last time I saw a regression bug.
3

Adrian,

Thanks! I looked a bit at APIMate last night, and although I had not gone far enough yet to discover the issues about processing all pages, I was sure this would be a good foundation. Obviously, I knew it would need to be  reworked for command line use, but I had assumed you must use it for testing, also, and figured it would not be a huge effort.

Obviously, the first problem will be that we must collect  those "gold standard" sheets. But this will be a continuing project. We are coming from D2007 and XLSReadWriteII4 to XE5 (soon XE6) and FlexCel. Big changes. But I am looking forward to using FlexCel. We also are going to work from datasets of formatted strings, so I will be replicating in Excel the values to present the same appearance. This approach allows the same datasets to be used for reports. And for some time yet, we must also support the D2007 version, so we will make a DLL with Flexcel, which we can call from D2007. Since we will pass the dataset from D2007, it is logical to have also a library of "gold" datasets.

I am not yet sure about producing logs of the commands to FlexCel. That would provide another check-point, but may not be necessary, since you have so many reference tests you run before release. I am still considering on that point. However, it could also be possible to reshape those logs to resemble the APIMate output, which would be useful.

Again, thanks for your reply. I appreciate the pointers and suggestions.
4

Just one thing to mention, that will become obvious once you start implementing it, but might not be obvious now and can add extra work if not considered:


Note that APIMate output might change with new FlexCel versions. We are constantly adding new stuff, and APIMate will start outputting more things as it learns new tricks. We can't guarantee and it makes no sense to keep APIMate output stable.

So you should't store the "golden standard" files as an APIMate output. Store the reference files as xls or xlsx, and then, when doing the comparison convert them both at the same time: The golden standard and the currently generated file. So if APIMate changed because of a FlexCel new version, it will change for both and still work. If you pre-converted the reference files to APIMate and stored the apimate output in a database, then a new FlexCel change might break all the tests, as the golden standard files will be created with Apimate 6.3 and the new files with APIMAte 6.4. By generating both with the latest version, you'll avoid this issue.

About how to create the "gold" datasets, well, you start by the beginning :)  Start by adding some files, and then whenever there is a bug make sure to add a file so that bug doesn't happen again. We've been doing this for more than 10 years (reference files are shared between FlexCel .NET and VCL), and when you realize, the list is huge :)