A customer requirement is the ability to offline manage business data within a downloaded excel sheet, and upload for processing into the business administration. Strong demand is that the input and actions in the excel sheet must be user-prescribtive as well as restricting. Microsoft Excel supports this via capabilities as input cell validation, cell format, protecting sheets and so on. We provided our customer with an example Excel 2010 sheet as functional specification. After we reached agreement on the Excel sheet behaviour, next step is to realize the runtime Excel sheet generation, and bind it to the user-selected data retrieved from the business administration.
The Open XML SDK can be used for server-based Excel sheet generation. A problem however is that (usage of the) the Open XML API / language is not very well documented. Setting up the generation of a simple Excel sheet is not a problem [as there are also sufficient code examples online]. But when it comes to including capabilities like data validation, the situation changes. The Open XML generation is very fragile, and you easily end up with an incorrect Open XML structure. Due the badly documented Open XML API, fixing the programmatically generation is a frustrating and time-consuming task.
In case of a complexer Excel sheet, a better approach is to utilize Open XML Productivity Tool. Just open the Excel sheet in this tool, and then export the Open XML code generation for the imported sheet. Mind you, typically you will want to refactor the generated code to improve on its maintainability. Also in our example we bind it to the data retrieved from the business administration.
