Why Beta Software Is Hard To Use, and How A Huge Company Still Listens to the Little Guy

T

his is the umpteenth post in my series on porting a .NET-laden, grid-enabled spreadsheet to run on Excel Services. In my last post (Adapting a Spreadsheet to Excel Services), I discussed the process that took me from a complex spreadsheet with .NET code-behind to an Excel Services spreadsheet calculating in SharePoint 2007.

After I had calculation happening (including running a job on the Digipede Network, gathering results, and bringing the data back into Excel), I just had to pretty up my spreadsheet with some pretty colors and some charts. Sounded easy enough, but it took two agonizing days.

Betas Have Bugs
The first problem I had turned out to be an honest-to-Pete bug in Excel Services. As I detailed last time, my User Defined Function was returning an 8x264 array of data into my spreadsheet. My plan was to have 2 charts, just like the spreadsheet I was modeling. In my original spreadsheet the 2 charts were each of dynamically updated while the job ran on the grid. Unfortunately, due to the architecture of Excel Services, I couldn't have dynamically updating graphs, but that was no big deal.

Using Excel 2007, I made a spreadsheet look just like my original spreadsheet--the charts used the return data from my User Defined Method as their source data. When my spreadsheet looked passable, I published it to SharePoint. It looked pretty good! I entered a number of bonds and pressed the "Apply" button. This launched a job on the grid as expected.

I waited for the results to come back. It should have taken 20 or 30 seconds, and I had been waiting for several minutes. I checked Digipede Control to see the status of the job--and I noticed that there were several jobs in the system. There was only one job running, but as soon as it completed, another job started. I cancelled it--and another job started. I cancelled it--and another job started.

My spreadsheet was out of control! It was starting jobs over and over again, as if it were stuck in a loop. I had to bounce IIS on the SharePoint server to get it to stop submitting jobs.

I was flummoxed. The logic in my spreadsheet had been working fine--I had just added some charts and a "pretty" front page. Why was it launching jobs repeatedly?

I dashed an e-mail off to Shahar Prish, a member of the Excel Services team whose Cum Grano Salis blog had been very informative when I was designing this whole thing. As I tried to get more information, I realized that my User Defined Method was getting called exactly 2,112 times. What? Some Rush fan's idea of an Easter Egg? No--I was returning an 8x264 array: exactly 2,112 values. My method was getting called for each cell in the array. Definitely bad.

To my surprise, Shahar e-mailed me back within minutes asking for more details and a sample. I couldn't believe it! This is a 60,000 employee company, and a product that's used by millions of people! I had e-mailed a developer and within minutes he was corresponding with me. Very, very impressive. I credit Scoble for helping to create such an open, blog-friendly, customer-friendly environment.

I packaged up a sample that exhibited the behavior (pulling out all Digipede code so he could run this on his machine). While I was pulling this together, I realized something very strange: my method was only getting called multiple times only if a cell that preceded it in the spreadsheet was dependent on the results.

Let me repeat that. If cell A1 was dependent on my results (say they were in A2:H264), my method was called repeatedly. If cell J:10 were dependent on my results, the method would only get called once (as expected). This was very strange behavior.

I sent the sample to Shahar for him to investigate. But, in packaging up the sample, I had found the workaround to my problem: don't have anything before the array be depedent on the array! Easy enough--I dragged my "results" sheet to the front of my workbook. Like magic, everything was working again!

[Note: I received an e-mail from Shahar the next morning. I had indeed found a bug in Excel 2007 beta 2! Funny thing, though: the developers had found that bug the same night I had. Full story here on Shahar's blog.]

Not all bugs are easy bugs
From there on out, I used an iterative process of tweaking my spreadsheet (just UI stuff), publishing it to SharePoint, and tweaking it again. And I soon experienced more bad Excel Services bevavior.

Periodically, when editing my workbook, when I would publish it to Excel Services I would get the following message:

Unable to open Workbook
The file you selected cannot be opened because it is corrupt, protected by Information Rights Management, or in a file format not supported by Excel Services. Excel 2007 may be able to open this file. Would you like to try and open this file in Excel 2007?

Similarly, at periodic intervals when publishing, I would see this message:

Excel Web Access
An error has occurred. Please contact your system administrator if this problem persists.

Over time, this grew infuriating. I was only editing these files in Excel--there was no reason for them to be "corrupt." Each time, they could still be edited in Excel 2007, but Excel Services/SharePoint would balk. I would have to start over from scratch designing my workbook.

Eventually, I kind of gave up on ExcelServices's ability to serve as a repository for my workbook. Instead, I worked from my local copy. I would make an edit, then publish it up to Excel Services. I'd look to see what needed tweaking. But instead of editing the copy in SharePoint, I'd edit the local copy on my hard disk (making sure I made a copy first, every single time). Then, I'd publish it to Excel Services, overwriting the previous version. If I published it and got an error, I'd just go back one revision.

It was tedious, and it took me 50 copies to get things right, but in the end the spreadsheet looked terrific. I even threw in some conditional formatting to show off some of the Excel 2007 features.

All in all, it was a great experience. I knew I was working with beta software, so having problems didn't really bother me. The demo looked great, and I got it to my partner on time. I loved Excel 2007. I was fairly impressed with Excel Services, although there are clearly still some bumps in the road.

And I was very impressed with the responsiveness of the Excel team!

Technorati tags: grid computing, excel services, excel2007