Robust Spreadsheet for State Agency Application

Executive Summary

Desktop applications are becoming more robust and the State of Washington Department of Labor and Industries (L&I) has very intense data crunching demands. Along with the requirements for user interface ease, the agency needs back-end calculation power for its applications. L&I requires reporting a growing amount of data that must be easily managed and read by several personnel in several sections of the department.

Working with Excel files, providing an easy interface to learn and use, and handling large amounts of data quickly are only some of the requirements that the project team faced in application development.

The team found a solution that provides incredible spreadsheet capability. The solution is more robust and customizable than Excel, but remains familiar to users. The tool integrates seamlessly with L&I's Visual Studio development environment. The engine behind much of the power of our data analysis is the spreadsheet capability of Spread for Windows Forms from FarPoint Technologies.

Background

The State of Washington Department of Labor and Industries (L&I) is a diverse State agency dedicated to the safety, health and security of Washington's 3.2 million workers. L&I helps employers meet safety and health standards by inspecting workplaces when alerted to hazards. As administrators of the State's workers' compensation system, L&I is similar to a large insurance company, providing medical and limited wage-replacement coverage to workers who suffer job-related injuries and illness. L&I's rules and enforcement programs also help ensure workers are paid what they are owed, that children's and teens' work hours are limited, and that consumers are protected from unsound building practices.

Employers in the State of Washington must pay industrial insurance premiums to L&I based on the type of work performed and the total number of hours of that work performed by their employees. L&I has a Field Audit section that performs audits of the premiums paid and hours reported to L&I and has the authority to levy fines. The Field Audit section is also responsible for performing audits and collecting fines against business who fail to register or otherwise operate illegally in the State of Washington.

The agency goal, approved by the State Legislature, was to replace the existing system - a collection of non-integrated applications and utilities, some of which were over ten years old. The name for the application is FACT - Field Audit Computer Technology. This application supports field audit personnel in the collection, collation, reporting, and dissemination of data in support of audit activities. It pulls together information from servers and mainframes from both within the Department itself and from other State of Washington agencies, thus providing auditors with a complete picture of what a business has reported (or failed to report) to the appropriate State agencies. The application allows auditors to add data based on the employers' own records, timecards, checkbook, etc. From this data, fairly complete assessments can be made. In addition, this application would support the legal concerns of the department by providing litigation audit summary information and audit tracking.

Original Application Design

The first version of the application was written in Microsoft Visual Basic .NET (2005) and consisted of many parts that included two front-ends (one Windows Forms, the other browser-based), a back-end data assimilation engine (which packages data for the front ends), and a back-end data integration engine (which provides access to data without the front-end knowing or caring whether the data came from a local SQL server, a network SQL server, a mainframe, message queues, or a Web service).

The primary data entry mechanism for the application was Microsoft Excel operating as a hosted component. It should be noted that Microsoft does not recommend this method. Microsoft Consulting Services was brought in and recommended an unsupported C++ component (DSOFramer) to aid in the hosting.

The application was released to production, but the Windows Forms front-end did not perform adequately. Excel did not function as expected in the hosted environment. Events were not propagated to the hosting application, recalculations were not properly triggering, and the performance was very slow compared to required levels. For example, a typical first-cut audit, which included very little data, was taking nearly 40 seconds to load. This was a limitation that required a different solution. The ability to crunch data was the primary purpose of the application as a whole.

HIS Consulting Services was contracted to investigate the performance and user interface interaction issues and assist in the development of a solution. Beyond simply getting something that could crunch data, we strove to make the application work so that the users would accept it and would be productive with it, as well. This was not an easy task: we were dealing with over 200 users whose primary concern was the collection of data from employers, not the nuts and bolts of storing the data. They were accustomed to storing the data disconnected and in free-form via Excel, and had never had an integrated application to support their end-to-end business process.

A Better Solution

From the start of this next phase, the project team determined that the Excel integration must be replaced with a more robust solution. The existing .NET GridView control would not perform as needed without an extreme amount of sub-classing and rebuilding. This determination is what started us down the road to finding a third-party component. At the minimum, the component had to be able to read and write Excel files, support formulas, support multiple cell types, provide for customizations such as colorization, font, cell spanning, and be able to expose data change events to the hosting class without clumsy work-arounds and interop overrides. In addition, the component had to be easy to learn because it had to be implemented very quickly once it was acquired. L&I has very strict requirements regarding what can be assimilated into its application infrastructure. Based on these requirements, we narrowed down the research area to four leading spreadsheet controls on the market. After a two-week evaluation period, it was clear the FarPoint Spread for Windows Forms was the hands-down winner. No one else came close to the ease of use, ease of implementation, cost, flexibility, documentation and Web-based support for the control.

The next thing to be done was to remove Excel from the application and create a new View class that contained multiple FarPoint Spread controls. The removal of Excel took approximately three days, while the initial "rough-in" of the FarPoint Spread took an additional two days. The initial development included the control layout, activation, event hooking, and preliminary colorization. Once the rough-in was done, we fired up the application just to see what kind of loading performance we might expect. We were more than a little surprised to see a typical 40-second load time drop to under 4 seconds. Further, we loaded one of the our largest audits which was averaging about 90+ seconds load time, and we were delighted to see it load in less than 10 seconds. This told us that the largest overhead in the control was our custom initialization, not the loading of data.

Functional modifications that used to take a day or more now took only a few minutes to code and a few more to test. Since FarPoint Spread is a true .NET component, there were no "unexpected" interactions, unknown events, or failed event firings.

Each page of the user interface is implemented by an instance (sheet) of Spread. Screen shots of some of the sheets that make up the user interface are shown below.

Figure 1. Adjustment Report Sheet of the Audit Workbook

Three sheets are used for data entry, three for summarization of entered data, six for summary assessment, one for legend display, two for titling, and one for report preview packaging. The three data entry sheets incorporate computations as well as event processing for database look-ups based on multiple-cell contents, while the nine summary sheets are all tied together through event processing on any data entry change.

Figure 2. Reported Hours Sheet of the Audit Workbook

Figure 3. Check Register Sheet of the Audit Workbook

Cut and paste (Ctrl+C, Ctrl+V), calendar pop-up, and calculator pop-up are supported intrinsically in the control, saving on code production and both development and testing time. Drag-and-drop from external sources is supported with specialized drop handlers in order to pre-validate data before incorporation into each data entry sheet. To speed the drag-and-drop processing, event handlers are disabled during the drop operation and re-enabled only after the data has been validated, applied, and summarized. During testing, a block of 1,400 rows of data was dropped on the "Other Audited Hours" sheet, and the entire drop, verify, incorporate, and summarize process took less than one second! Have we mentioned how fast this control is?

Figure 4. Other Audited Hours Sheet of the Audit Workbook

Figure 5. Print Preview of Summary Adjustment Sheet

All of this was accomplished while protecting data integrity, minimizing application modifications, and responding to (sometimes aggravating) user requests, in less than 50 work days. Our production roll-out was a raving success and moved the application from the cutting-block stack to the "here's a fine addition to our portfolio" stack.

Conclusions

FarPoint Spread saved the agency extensive hours in on-going development and maintenance, as well as shielded the staff from potential and unknown incompatibilities when Microsoft Office 2007 would be rolled out to all department machines. Microsoft gave no indication that hosting Excel would work any better with the 2007 version, and no guarantees that Excel 2007 would function anything like Excel 2003 as a hosted component. Between the upcoming Office 2007 deployment and the pending Microsoft Windows Vista deployment within the agency, the use of FarPoint Spread has successfully mitigated the risk of a work stoppage for the agency audit staff, while empowering the auditors, and changing conversations from " ... and this just doesn't work ..." to "... this is great. Now we want to add ...". The choice of Spread fulfilled our performance requirements and accelerated application development.

This success is being propagated as the agency is now examining FarPoint Spread for ASP.NET, the Web Forms component, for another project with a public-facing data-acquisition requirement.

On a final note, we would like to point out that we are not using anywhere near all of the capabilities of FarPoint Spread. With our new application incorporating Spread, we have only scratched the surface of what is one of the most impressive, flexible, and capable components I have come across.

Finding More Information

You can easily download a free trial evaluation from the FarPoint Web site and see for yourself how easy it is to develop and application with spreadsheet capabilities.

• Spread for Windows Forms Main Product Page
• Spread for Windows Forms Product Tour
• Spread for Windows Forms Download Trial
• Spread for Windows Forms Online Support Forums

About FarPoint

Whether you are developing for the Web or stand-alone Windows applications, FarPoint has a version of Spread that is right for you and that deploys royalty-free in the environment you need. FarPoint delivers enterprise spreadsheet solutions. Visit the FarPoint Technologies website for more information.