The Microsoft Office Web Components

Have you ever wanted to incorporate Microsoft Office Technology into your web and windowsapplications? The Microsoft Office Web Components are a suite of controls released by Microsoft thatmake this possible. The latest version, O.W.C. version 11, of the components was released with MicrosoftOffice 2003. Version 10 was released with Microsoft Office XP and Version 9 was released with MicrosoftOffice 2000.The O.W.C. suite provides all the functionality of the desktop version of Microsoft Office Excel but it islighter 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 thedesktop version. It supports more rows and columns that the desktop version and its calculation engine issuitable for server-side applications. The chart component can plot over 50 types of charts by default. Thechart can render static images or interactive 3D charts that are feature rich and easy to create. The pivottable object can pivot thousands of records inside a web page efficiently. It can source its data fromrelational 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. Italso 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 alsoavailable as a free download from the Microsoft Office website. O.W.C. version 10 for Microsoft OfficeXP 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 availablehere: 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 installationmedia. After the installation process, add the chart, pivot table, and excel objects to the toolbox of VisualStudio 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 isfairly easy to port to another language. To compile this example, you will need to import the OWC11assembly.

//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 goodapproach since it is free for use and easy to program. The rich programming feature set allows thedeveloper to get creative while maintaining a polished appearance. The O.W.C. is fully supported byMicrosoft 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 thecontrol into a web form by copying the object class id into the html portion of the page. If your designersupports drag and drop, then simply drop the O.W.C. Excel spreadsheet unto the web form from thetoolbox. 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 tocode. However, if you wanted to customize any of these behaviors, you can simply extend the toolbarfunctionality and then provide your own custom code. Finally, you should note that this type of applicationis different from, say, a data grid application that exports data to Excel because this application isinteractive; 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 streamand loaded in the browser. Finally, the pivot table attempts to load data by accessing a table in theNorthwind 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 andexport the details of the on-demand report. Notice that this functionality is already built in and requiresabsolutely no coding effort. A lot more functionality is available via the toolbar control which is a staple ofevery O.W.C. component.

There are several reasons to consider the Office Web Components when designing and building nextgeneration 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-inwindows functionality such as copy and paste. The components contain a built-in help system that isthorough and up-to-date. The API is extensive enough to allow the developer to customize or supplementnative 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 atMicrosoft.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 atwww.lulu.com/owc.

Most Commented Articles :

• C# Tutorial For Beginners
• C# Operator Overloading
• Objects & Classes in C#
• C# Editable ListView
• C# – Static Members
• Drag and Drop Files with C#
• Using the Parallel Class in C# – Processing Data in Parallel across Multiple Processors or Cores
• Talk to SharePoint Through its Web Services
• ComboBox With Images
• GDI+ A Higher Level API