Well, I’m on the verge of this project that really puzzled me.
The situation is, in our office, we have a department that collect data about the corporate performance indicators from all across the departments and branches.
The method they used before is, they distribute a macro Excel file, which include some worksheets with certain tables to fill by all the departments and branches. Each worksheet was referenced to certain indicator value/score. When filled out, the indicator score will be automatically filled and evaluated. So they came across with many excel files to work with.
Now, we want to move that process into an application, which takes us to this problem. The indicators and the worksheet format itself was subject to change every year. So to incorporate this very dynamic structure, we design the db to store every rows, cells, and formulas, on the database, and come up with a very complex design.
Well my question is, did we do this OK and efficient? I mean is there any suggestions to this kind of problem? We are considering our option to use Google Docs API for the spreadsheet and extract the data from it too. What do you guys think?
5
No not really.
The problem is that with every variation in the spreadsheet you end up with a different interpretation of the indicators.
So given the same set of inputs each sheet will come up with different recomendations.
Either determine what is the “correct” set of indicators and formulas to be used in the application.
- or –
If you really want to leave well alone then excel has a perfectly good ODBC interface which would allow the application to read the original data. Alternatively the POI java library can read spreadsheet data directly. Trying to model the dynamism and general weirdness of a spreadsheet is just going to cause you problems.