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
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
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
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
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
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)
lblMessage.Text = "Monthly Payment: " & (sp.Range("B1:B4").Cells(4).Value
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. |