RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Spreadsheet Programming: MS Excel as Component Development Environment

Spreadsheet programming is an emerging technique for building complex business logic in MS Excel at a fraction of the effort, cost, and cycle time of hand coding such logic in a programming language. Programmers can deploy the business logic built in the spreadsheet into interactive client applications and highly scalable server applications.

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
Table 1. Component-oriented Development Analogies in Spreadsheet Programming

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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date