Most applications, albeit web-based or otherwise, incorporate some resemblance of business logic – “the part of the program that encodes the real-world business rules that determine how data can be created, displayed, stored, and changed.”[1] Not only do these rules vary significantly from program to program, company to company, and industry to industry, but – with respect to the entirety of the application – they also tend to change the most frequently.
For this particular reason, it is always advised that the layer of the application that maintains the software’s business logic is separated from the core application code. Traditional applications handle this decoupling through the development of a “business logic layer”, where the integrity of the rest of the application is not affected by constant modifications to the underlying business logic. More modern architectures may separate business logic into one or more web services, wherein the exposed interfaces remain constant while the code on the service side can be modified with less risk to the core application.
While these are perfectly valid means of abstracting away business logic from the core application, these resolutions still demand the allocation of development resources to maintain and update the application’s business logic. Ideally, developer time could be spent working on the core application while the logic associated with the business could be maintained by the users most familiar with that business: the business users and analysts.
Of course, business-oriented professionals typically have limited programming experience and should not be expected to maintain the business logic programmatically. In turn, the objective would be to allow business users to maintain and update business logic in a familiar platform with little-to-no programming expertise required. These requirements isolate Excel as the perfect platform for the task: many business users already utilize Excel to generate their business models and have the experience and know-how to maintain and update these models.
The concept of connecting business-oriented spreadsheet models with applications is not the mark of a new endeavor: there are a number of spreadsheet components currently on the market that allow integration with external applications. They are often dependent on particular interface components (typically written in Java, .NET, and COM) and require learning those libraries. In terms of deployment, they also require that the components are packaged with each application, imposing more complexity and creating additional dependencies within the application.
Dedicated business logic services are the preferable approach since they solidify the decoupling without generating any additional complexity:
- A central service endpoint exists to support all requests for spreadsheet-based business logic. All spreadsheets can be handled through the same system. Different spreadsheets containing varying business logic can be accessed by any consuming application.
- The business users upload their spreadsheets, providing the development team with the corresponding input and output fields (I/O).
- The developers connect the front-end to the business logic service using the I/O provided by the business team. This requires no actual knowledge of the business logic itself.
Let’s take a simple example to walk through the process: estimating shipping costs. The process of calculating estimated shipping costs can be easily replicated in an Excel spreadsheet, incorporating various lookup tables, discount rules, distance and weight based calculations. This logical model can be built entirely by business experts using worksheet formulas in Excel.
There is no reason to go into the details of the business logic itself since it is irrelevant from the perspective of the developer. Despite any of these complex calculations and table structures (signifying the business logic layer of the application), the user input section is very simple:
From the developer’s perspective, this is the significant portion of the application since it constitutes the user interface that needs to be developed in order to interface with the back-end business logic. All that the developer truly needs to know is that there are four significant fields:
| Type (I/O) | Name (Identifier) | Label |
| Input | originZip | Origination ZIP |
| Input | destinationZip | Destination ZIP |
| Input | weight | Weight (lb) |
| Output | cost | Cost ($) |
To convert this workbook into a web service, we simply need to upload the Excel file to the SpreadsheetWEB server and obtain an application key:
At this point, the process of submitting the user input and retrieving the output is incredibly simple, effectively a mapping exercise from the I/O of the user interface to the web service:
For the purposes of this demonstration, we have created a simple WinForms application in C# to correspond with this I/O, but there are also readily-available integration libraries for interfacing with the SpreadsheetWEB API from your Java, JavaScript, and PHP applications. This web service can be consumed by any type of application, web-based or otherwise.
We now have a fully functioning application that’s entirely decoupled from the underlying business logic:
Figure 3 - Simple sample application consuming the SpreadsheetWEB API for all business logic.
As shipping rates are adjusted, a business user can freely update the underlying logic with new data, modified rules, and revised calculations. Then, they simply re-upload the Excel file to the SpreadsheetWEB server, replacing the existing one and – so long as the basic input and output fields are not modified – none of these business logic changes will require modification to the core application’s code.
We can also expose this web service to our registered partners by providing them with the application key and a set of security credentials: the entire security model is already built into SpreadsheetWEB.
[1] https://en.wikipedia.org/wiki/Business_logic
Repost from LinkedIn.
