By Alvin Bruney
Have you ever wanted to incorporate Microsoft Office Technology into your web and windows
applications? The Microsoft Office Web Components are a suite of controls released by Microsoft that
make this possible. The latest version, O.W.C. version 11, of the components was released with Microsoft
Office 2003. Version 10 was released with Microsoft Office XP and Version 9 was released with Microsoft
Office 2000.
The O.W.C. suite provides all the functionality of the desktop version of Microsoft Office Excel but it is
lighter and optimized for internet development. The O.W.C. package contains a spreadsheet, pivot table,
chart and data source component. The spreadsheet exactly mirrors the functionality and performance of the
desktop version. It supports more rows and columns that the desktop version and its calculation engine is
suitable for server-side applications. The chart component can plot over 50 types of charts by default. The
chart can render static images or interactive 3D charts that are feature rich and easy to create. The pivot
table object can pivot thousands of records inside a web page efficiently. It can source its data from
relational databases, XML islands and OLAP cubes. The data source component does not contain a user-
interface piece. Instead, it is used by the components for sophisticated data-binding behind the scenes. It
also exposes a rich event model and may be used as a stand-alone component.
These next few examples get you up to speed on writing applications based on the Microsoft Office. First,
you will need to install the components from the Microsoft Office setup disk. The components are also
available as a free download from the Microsoft Office website. O.W.C. version 10 for Microsoft Office
XP is available here: http://www.microsoft.com/downloads/details.aspx?FamilyID=982B0359-0A86-
4FB2-A7EE-5F3A499515DD&displaylang=EN. O.W.C. version 11 for Microsoft Office 2003 is available
here: http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-
E0FD290D4B76&displaylang=en. O.W.C. 9 is only available on the Microsoft Office 2000 installation
media. After the installation process, add the chart, pivot table, and excel objects to the toolbox of Visual
Studio or your favorite IDE.
Creating charts
Here is some simple code to create a chart for display in any browser. The example is written in C# but it is
fairly easy to port to another language. To compile this example, you will need to import the OWC11
assembly.
//First create a ChartSpace object to hold the chart
ChartSpace objCSpace = new ChartSpaceClass ();
//Add a chart and provide a type
ChChart objChart = objCSpace.Charts.Add (0);
objChart.Type = ChartChartTypeEnum.chChartTypePie3D;
//add chart titles and legend
objChart.HasTitle = true;
objChart.Title.Caption = "ASP.NET Charts";
objChart.HasLegend = true;
objChart.Legend.Border.DashStyle = OWC11.ChartLineDashStyleEnum.chLineDash;
objChart.Legend.Position = OWC11.ChartLegendPositionEnum.chLegendPositionRight;
//Populate with contrived data
string strCategory = "Cars, Trucks, Vans, Big Rigs, Motorcycles, Mopeds";
string strValue = "13,12,31,43,23,15";
//Add a series to the chart's series collection
objChart.SeriesCollection.Add(0);
//load the category and value data
objChart.SeriesCollection[0].SetData (ChartDimensionsEnum.chDimCategories,
(int)ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
objChart.SeriesCollection[0].SetData (ChartDimensionsEnum.chDimValues,
(int)ChartSpecialDataSourcesEnum.chDataLiteral, strValue);
//show the chart on the client
Response.ContentType= "image/gif";
Response.BinaryWrite((byte[])objCSpace.GetPicture("gif",500,400));
Response.End();
Here is what the chart application looks like.
If you would like to add charting capabilities to your application, the O.W.C. chart component is a good
approach since it is free for use and easy to program. The rich programming feature set allows the
developer to get creative while maintaining a polished appearance. The O.W.C. is fully supported by
Microsoft Product support so you can write your applications in confidence.
Excel spreadsheets
This example loads data into a spreadsheet application. To build the application, you must first embed the
control into a web form by copying the object class id into the html portion of the page. If your designer
supports drag and drop, then simply drop the O.W.C. Excel spreadsheet unto the web form from the
toolbox. Your html file should look like this. I've removed the Excel object for brevity.
<body MS_POSITIONING="GridLayout">
<script>
function LoadData(){
document.all.sp.HTMLURL= "http://localhost/test.htm";
}
</script>
<form id="Form1" method="post" runat="server">
<INPUT onclick = "LoadData()" style="Z-INDEX: 102; LEFT: 232px; POSITION:
absolute; TOP: 312px" type="button" value="View Data">
</form>
</body>
Here is what the application looks like.
Honestly, that's not a lot of code to build commercial-grade software. You will notice that auto-calculation,
export, copy and paste, range manipulation and sorting are all natively implemented. There is nothing to
code. However, if you wanted to customize any of these behaviors, you can simply extend the toolbar
functionality and then provide your own custom code. Finally, you should note that this type of application
is different from, say, a data grid application that exports data to Excel because this application is
interactive; that is, the application can respond to end-user events.
Pivot Tables.
This example creates a Pivot table object at run-time. The object is then embedded into the response stream
and loaded in the browser. Finally, the pivot table attempts to load data by accessing a table in the
Northwind database on the client. Here is how we proceed.
private void Page_Load(object sender, System.EventArgs e)
{
//determine whether or not the browser supports OWC
if(Request.Browser.ActiveXControls)
{
Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:
502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'
classid='clsid:0002E55A-0000-0000-C000-
000000000046'VIEWASTEXT></OBJECT>");
//cause the object to load data on the client
Response.Write(@"<script>document.all.pTable.ConnectionString =
'Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=\'DSN=MS
Access Database;DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;\';Initial Catalog=C:\Program
Files\Microsoft Office\OFFICE11\SAMPLES\Northwind'</script>");
Response.Write("<script>document.all.pTable.DataMember =
'orders'</script>"); }
}
Here is what the application looks like.
Pivot tables are a great way to put the end-user in the driver's seat. The pivot table object can print, sort and
export the details of the on-demand report. Notice that this functionality is already built in and requires
absolutely no coding effort. A lot more functionality is available via the toolbar control which is a staple of
every O.W.C. component.
There are several reasons to consider the Office Web Components when designing and building next
generation applications. The components are industrial strength and based on a familiar interface that end-
users have grown accustomed to using. The components are feature rich and provide the user with built-in
windows functionality such as copy and paste. The components contain a built-in help system that is
thorough and up-to-date. The API is extensive enough to allow the developer to customize or supplement
native behavior. Finally, the Microsoft Office Web Components are available as a free download.
There are a number of resources on the web covering simple to advance aspects of the O.W.C. In addition,
the new book The Microsoft Office Web Components with .NET is now available on www.lulu.com/owc,
Amazon, Barnes & Noble and most online retailers. The public newsgroup may be found at
Microsoft.public.office.developer.web.components.
Credits
Alvin Bruney is an ASP.NET MVP. He has been involved in .NET technology since the beta version.
His new book The Microsoft Office Web Components Black Book with .NET is available at
www.lulu.com/owc.
