In this article, I propose a change to this workflow: take a basic sample of an Excel workbook, containing data and formulae, and use it as a template for dynamic workbooks where the data may be retrieved from a database. No new logic will need to be added to perform the business analytics. Instead, the logic that is already present in the template workbook is exploited.
The workbook in Figure 1 is composed with Excel and saved out as an XML file called default.xml. For the sake of this article, you'll retrieve the information for the fields within the balance sheet from a flat XML file called input.xml.
|Figure 1: An Excel Workbook containing desired presentation and business analytics.
It is very straightforward to change the application to read the data from your specific back-end data source either by URL or via a data-access technology.
Save the default.xml and input.xml files from the download to your "C:\" directory. If you want to use a different directory, take note of it, and change the references in the source code as necessary.
Using the New Project wizard within Visual Studio.Net, select 'Visual J# Projects' and choose 'ASP.NET Web Service'. Set the location to be 'http://localhost/OfficeDoc' and choose 'OK'.
Replace the wizard-generated code for OfficeDoc.asmx.jsl with the code from the download.
What Does the Web Service Do?
The Web service code in the download produces a very simple output, as you can see in the mock up shown in Figure 1.
Using an input parameter (which is the ticker of the company for whom you are producing the balance sheet), the code builds the location of the balance sheet data within the XML file containing the balance sheet information as an XPath.
The XPath below will return the revenue information for IBM as a nodelist.
The code then parses this nodelist, and puts the information into the XML document describing the spreadsheet at the proper cell locations.