ASP.NET, SQL Server, WPF, C#, VB.NET

I've talked about using Excel, OWC SpreadSheet and the OWC Chart class from ASP.NET in several other articles here. There's one last item I never covered, and it's an important one, now that Office XP (2002) is in more widespread use.

The Office Web Components are lightweight COM components that are free-threaded and designed to be used on a web server. One important use, however, does not involve showing a visible Spreadsheet in your ASP.NET web applications. This is when you may decide that you have an Excel workbook that does some pretty heavy-duty calculations, and you would prefer to keep the formulas and all your hard work in Excel, and leverage the power of Excel to do the calculations for the web from an ASP.NET WebForms page.

Perhaps, for example, you have a bunch of input data that comes from a database (maybe it was a survey or other WebForm - based application), and now the entered data are stored in a database. You may wish to bring this data out, plug it into the appropriate places in your marvelous Excel spreadsheet creation, calculate results, and then bring out the results and display them to the web user on demand.

With Office Web Components (OWC) you do not have to incur the heavy penalty of instantiating a very large COM Server (Microsoft Excel) through Interop and then deal with all the marshalling constructs and finally (if you are lucky) get the little unhoustrained puppies to unload when you are done and not have your systems admin come to you the next day asking why he had to reboot a production web server because there were 123 separate instances of "Excel.exe" showing in the task manager.... In fact, you don't need Excel to be installed on the server at all, just the lightweight OWC package which takes about 2 minutes to install and has no drag on performance whatever in a production webserver unless it is actually used.

To show you how simple it can be to use an Excel spreadsheet with OWC, I created a simple "Loan Calculator" spreadsheet with input cells for Principal, Term, and Interest Rate, and an output cell that uses the Excel PMT() function to compute the monthly payment. We then save this workbook using the "XML Spreadsheet" option available on Excel 2002 and above. This is the only format that the OWC Spreadsheet Component can read and bring in all the formulas, etc. Do not attempt to load a regular XLS file in OWC, it can't be done, period.

Then, in my VB.NET ASP.NET application's button click , I have this simple code to enter the user's WebForm entries into the OWC Spreadsheet class , perform the calculation, and pull out the result and display it to the user:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sp As OWC10.SpreadsheetClass = New SpreadsheetClass
sp.XMLURL = MapPath("test.xml")
sp.Range("B1:B4").Cells(1).Value = Convert.ToInt32(txtPrincipal.Text)
sp.Range("B1:B4").Cells(2).Value = Convert.ToInt32(txtTerm.Text)
sp.Range("B1:B4").Cells(3).Value = Convert.ToInt32(txtRate.Text)
sp.CalculateFull()
lblMessage.Text = "Monthly Payment: " & (sp.Range("B1:B4").Cells(4).Value * -1)
End Sub

Pretty simple, wouldn't you agree? And guess what - even though I didn't even bother to explicitly call the ReleaseCOMObject methods, there is no errant copy of anything left around in memory. These components are all free-threaded. You don't even have to set "ASPCOMPAT" on your page, so there's no performance drain. I haven't tried it, but I suspect that since it's freethreaded, you could even store a fully instantiated Spreadsheet class instance holding a complete workbook in Application scope.

Obviously, this is a very simple example. However, there is nothing to stop you from loading the most complex Workbooks that have been saved as XML Spreadsheets and performing heavy-duty calculations in your web reporting pages! OWC Spreadsheet class supports all the formulas, everything except Pivot Tables and charts.

The downloadable Solution, which is in Visual Studio.NET 2003 format, includes a copy of the test.xml spreadsheet used above.

Download the code accompanying this article

 

Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform. Pete's samples at GotDotNet.com have been downloaded over 41,000 times. You can read Peter's UnBlog Here.  --><--NOTE: Post QUESTIONS on FORUMS!