Spreadsheet Programming: MS Excel as Component Development Environment

preadsheet programming is a natural evolution of component-oriented development. Its basic philosophy is to use MS Excel as a development environment for creating business logic components. These components can then be orchestrated to create vastly complex and sophisticated business processing systems that are still easy to manage and maintain. Furthermore, because the business rules are developed in Excel, business users—who typically drive system requirements—can easily review, verify, and even modify them.

Keep in mind that Excel is used only as a development environment. At runtime, spreadsheet engines can execute the business components without Excel, allowing for a high degree of scalability. Several vendors offer products that enable this technique on Java and .NET.

Spreadsheet programming is a natural evolution of component-oriented development.

This article explains how to begin leveraging spreadsheet programming for higher productivity. It covers the design, building, testing, and maintenance of applications with spreadsheet programming. It also provides the full source code for a sales tax calculator component built in Excel and deployed as either a Java applet or an ASP.NET page.

MS Excel as Business Component Development Environment
An Excel workbook consists of one or more worksheets, each of which contains many rows and columns of cells. Each cell can contain data (a number or a string) or a formula. Cells can be grouped into ranges containing one or more cells, and ranges can be named and referred to by name.

Under the paradigm of spreadsheet programming, a workbook corresponds to a business component, a cell containing data is an input to the business component, and cells with formulas make up the output behavior of the business component (see Table 1).

Traditional Component-oriented Development	Spreadsheet Programming
Component	Workbook
Public input data member	Data Cell that is populated at runtime through the API
Public output method	Formula Cell that is queried at runtime through the API
Private method	Formula Cell that is not queried at runtime
Private constant data member	Data Cell that is not changed at runtime

Cell formulas in Excel can be composed of Excel's built-in Functions, which include basic math operations as well as about 300 other functions. Some of the most useful functions for building business components are: IF, OR, AND, LOOKUP, OFFSET, COUNTIF, SUMIF. Experiment with these functions if you're not familiar with them.

At design time, you work in Excel to interactively develop and test the business component's functionality. But Excel cannot scale to perform calculations on a server for multiple clients. This is where spreadsheet engines come in. At runtime, your application communicates with the spreadsheet engine via an API, input parameters are passed in via a 'SetCellValue(. . .)' method, and the results of cell formulas are retrieved with a 'GetCellValue(. . .)' method.

Several powerful design patterns can be leveraged when using Excel to develop business components. This article discusses them further, but first jumps right into developing a simple business component with spreadsheet programming.