Interop and Gembox

We recently began working on a project for a customer that required taking database information and spitting it into an Excel workbook.  The original project was created utilizing a process Microsoft provides called Microsoft Interop. Here is a very brief description - MS Interop allows the programmer to pull data from a database and run a server side Office application such as Excel to generate a spreadsheet.  Basically you request the information and a session of Excel is launched, data is pumped into it then its saved. Think of it as a scripted event.  Microsoft recommends not using this due to security issues, speed issues, and memory issues.

Our customer had complained repeatedly that they were having issues with speed, big issues.  One report they run involving 30 days of data was taking 15 minutes or more, if it ran at all.  Most of the time they were picking up the phone and having the developer run the report locally to avoid the problem. Many smaller reports were taking 5-10 minutes.  These reports do have a lot of information but frankly we didn't believe the story the developer was feeding us that the problem was how much information was being requested and pumped into this Excel Workbook. 

During the process of researching this issue we wound up taking on future development of this project.  The first thing we did (after stumbling through the existing code and getting it working on a new server) was to research and determine the cause of the "slowness" of this Excel Interop.  During our research we looked at server memory, processor power, hard drive speed, internet and network connection types as well as many other sources that could slow down the process. We found that each of these potential bottlenecks was not actually responsible for the problem. That left us with one possible problem - Microsoft Excel Interop.

So now the solution - We began researching third party applications that go about the same work.  We found numerous other ideas to fix the issue. The one we finally settled on is GemBox.Spreadsheet. GemBox essentially does the same thing as Interop, in fact our programmer was able to repurpose much of the code and change the calls to Interop to GemBox.Spreadsheet in order to get the new process working.  GemBox describes their product as being easy to incorporate, fast and 100% non dependent on Excel. We found all of this to be true.

The results?  No less than breath taking. Our reports that were taking well over 15 minutes if they ran at all now take around 2-3 seconds. Yes you read that correctly. 2 as in 1....2...   We were stunned with what we got.  There of course is a catch to this.  GemBox does provide a free version, but larger projects require a license. Prices begin at $480 which honestly we felt was a steal for what it gave us.  The free version is actually quite flexible and in many cases it is all you need - it supports 150 rows per sheet with a max of 5 sheets per workbook. We recommend buying it to support the company that creates it, just as we do with any software you enjoy using.

Rating - 10/10