Automatically analyze excel files

Question

I have to replicate a manual generation of a large number of excel files. I started to manually track the relations between cells ( files, formulas, etc). I also had a talk with the person which generates those files. For now I have a general understanding about how the excel files are generated, but "devil is in the details".

I assume that I can write a script which will generate the hierarchy between cells and files, but this might require the same effort as manually noticing the relations. Also, I'm afraid that I'm not too experienced and my app is more prone to error approach than a manual analyze.

How to handle this problem? Do you know about an open source project which analyze the excel files in a recursive mode following the formulas ?

Answer 1

Write a lot of tests. Use the manual output from the people who have been generating those files up to now as the results that must come out of your tests; on the other hand, present very simple situations to those people and collect their results as the first tests on which you start developing. (I can't emphasize enough the words "very simple situations").

The more tests you write, the more confident you will be that all output from your app matches what is expected from it. If some output is still wrong, then you did not write all possible tests, or the expected behavior was not completely detailed to you.

Let me remark this is a common situation in software development, because software is very good at repeating instructions, but precisely this amount of repetition can make you 'blind' as a human analyst, i.e. you can't keep an overview perspective of the results. Testing is the best technique to regain that overview.