How we Incorporate Excel Features in Designing Custom Web Applications

By Hasitha Gunasinghe, Team Manager at Improvement Interactive

Financial controllers and accountants love excel. Its functionality is near and dear to their heart. It’s no wonder that when we, as their software development team, meet for a requirements gathering session for a budgeting application, they request many excel-like features for their custom web application.

Improvement interactive has been building large enterprise software for over 20 years. The application our team built processes over $20 billion in transactions annually for one of the fortune 500 companies. This is by all means a complex enterprise application. The budgeting module is one of the more critical modules in this financial application, where over 17000 locations across the US do all of their budgeting and forecasting for the business.

The financial controllers (users) wanted excel like features such as

  • Copy/paste cell values
  • Ability to add formulas on cells by users
  • Conditional formatting
  • Ability to add comments on each cell
  • Lock certain cells

After analyzing the requirements, we decided we needed to have excel like features on the custom web application. We did not try to re-invent the wheel. There were 3rd party tools out there which would upload an Excel file on the web. We utilized one of those libraries to load the excel file into our custom web page, but we wanted additional functionality such as:

  • Populate the excel cells with data that is stored in the database
  • Update the database transactional tables with updated cell values so that users could run reports.
  • Excel Macro features on the web

In order to include the above features, we built a unique framework that uses Cycligent.js framework(which is available for you to download, free!), which mapped the excel cells and columns to unique database tables and columns. To mimic the macro features we developed JavaScript functions. One such functionality is to copy over formulas when a custom formula is activated by a dropdown selection.

After all of the above was implemented, we still had to overcome a main obstacle. We had noticed that the file generated by the 3rd party tool was too large and that it would not be acceptable to have users download such a large file when the web page loads. After carefully analyzing the output of this large file, our team built a compression routine which reduced the file size by over 80%.

After all the hard work, we have created a product that we believe will

  • Make us and our clients happy
  • Will provide business improvements to our client
  • Provide our client with a great user experience

Which directly aligns with our organizations mission statement:

  • To be Happy
  • creating Enterprise Applications
  • that produce Business Improvements
  • while providing customers with a Great Experience.