In my last blog entry here, I started a discussion of the technical challenges of using Spreadsheets as electronic business documents. I want to continue that discussion, but instead of focusing on how data is located predictably in the spreadsheet, I want to talk about separating the formatting from the business data.
As you might imagine, consuming Spreadsheets and producing them presents two different sets of challenges. When consuming them, the problem of locating the data in the spreadsheet predictably is the most significant challenge, next to data content concerns. The formatting (fonts, colors, shading, graphics, etc.) generally does not matter when consuming the Spreadsheets, whereas when producing them, it can matter almost as much as the data content.
We found it to be a real challenge to solve the problem of producing a spreadsheet that not only has the business data in it, but also the formatting that makes it something that’s appealing to look at and useful in terms of formulas that reference business data. There are a number of APIs that are available for creating and manipulating spreadsheets, but we haven’t found anything we could use that was as good as Microsoft Excel. So, what we decided to do was to provide a mechanism where we could capture an example of what the user wanted to produce and then use it as a template at runtime. This provides us with a couple of advantages:
- Microsoft has already invested lots of money in making an editor that’s good at providing formatting tools – that wheel has already been invented.
- Our users can focus on mapping business data, and not on fonts, colors, graphics, and formulas. We excel (no pun intended) in the area of mapping, and someone else excels in the area of Spreadsheet formatting.
- If formatting changes are required that do not affect the location of the business data, a user can make those modifications to the example spreadsheet using Microsoft Excel – without touching the previously built integration process.
The challenge of merging business data into a template that ultimately becomes the final product is tricky because of the common presence of formulas that are usually dependent on the location and amount of business data. The main culprit is the data location problem again:
- Accounting for totalizing formulas that often reference rows and cells that start in a fixed location, but end when the data runs out;
- Sparsely populating rows so as to not overwrite formulas that reference other cells in the row.
- Multiple-row line items (with either or both combinations of the two items listed above).
- And, of course, spreadsheets that contain both form-type and list-type areas.
Of course, we’re always on the lookout for good examples of Spreadsheets that will challenge us, or perhaps stump us. If you have particularly challenging examples, we’d like to see them. Send them to email@example.com.