CoverStory
LANGUAGES: C#
ASP.NET VERSIONS: 1.x
OWC Meets ASP.NET
Leverage Microsoft Office Technology in Your Web Applications
By Alvin Bruney
The Microsoft Office Web Components (OWC) are a group of ActiveX controls authored by Microsoft that allow developers to harness the power of Microsoft Office technology in Web applications. The initial release of the OWC (version 9) shipped with Microsoft Office 2000. Since then, versions 10 and 11 followed with Microsoft Office XP and Office 2003, respectively. The components provide Microsoft Excel and Access functionality only; other Microsoft Office technology, such as Word and PowerPoint, are not provided. The components are built to closely mirror the fit and finish of the desktop counterpart, but they are easy to program and are tuned for Web development.
The OWC package contains the Chart, Data Source, PivotTable, and Excel Spreadsheet controls. The Chart control can render an impressive number of charts in 2D, as well as 3D. The Chart control supports a rich event model, as well as drill-through capabilities when bound to an underlying Data Source control. The PivotTable control contains a potent auto-calculation engine and can pivot large amounts of data efficiently and easily. The Data Source component allows the ActiveX controls to bind to a wide variety of data sources to include XML, OLAP cubes, relational databases, and even other OWC controls. For instance, the Chart control can easily source its data from either the desktop or the OWC version of Excel. The Excel Spreadsheet control contains a powerful calculation engine capable of handling complex computations in real time. The Excel Spreadsheet control also provides most of the functionality available in the desktop version. With the exception of the Data Source, each control contains a client interface and a server-side piece.
To run the next few examples you must install the components from the Microsoft Office setup disk. The components are also available as a free download from the Microsoft Office Web site. OWC version 10 for Microsoft Office XP is available at http://www.microsoft.com/downloads/details.aspx?FamilyID=982B0359-0A86-4FB2-A7EE-5F3A499515DD&displaylang=EN; OWC version 11 for Microsoft Office 2003 is available at http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en. OWC 9 is only available on the Microsoft Office 2000 installation media. After the installation process, add the Chart, PivotTable, and Excel objects to the toolbox of Visual Studio (or your favorite IDE).
Creating Server-side Charts with the code-behind in ASP.NET
This first example creates a chart as an image on the server using the ASP.NET code-behind approach (see Figure 1). The image is then sent to the requesting browser for display (see Figure 2).
private void Page_Load(object sender, System.EventArgs e)
{
//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.chChartTypeLineStacked3D;
//customize the look and feel
objChart.PlotArea.Interior.SetTextured(
OWC11.ChartPresetTextureEnum.chTextureGreenMarble,
OWC11.ChartTextureFormatEnum.chTile,10,
OWC11.ChartTexturePlacementEnum.chEnd);
//add chart titles and legend
objChart.HasTitle = true;
objChart.Title.Caption = "ASP.NET Charts";
objChart.HasLegend = true;
objChart.Legend.Position =
OWC11.ChartLegendPositionEnum.chLegendPositionRight;
objChart.Axes[0].HasTitle = true;
objChart.Axes[0].Title.Caption = "Date";
//Populate with contrived data
string strSeriesName = "Chart Series";
string strCategory = "1/1/2000,2/2/2000,3/3/2000,4/4/2000,5/5/2000";
string strValue = "1,2,-3,5,2";
//Add a series to the chart's series collection
objChart.SeriesCollection.Add(0);
//assign series
objChart.SeriesCollection[0].SetData (
ChartDimensionsEnum.chDimSeriesNames,
(int)ChartSpecialDataSourcesEnum.chDataLiteral,
strSeriesName);
//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();
}
Figure 1: Create a chart as an image on the server using the ASP.NET code-behind approach.
Figure 2: The charting application.
The code adds some cheap frills, such as textured surfaces and a legend to create visual impact, but these options are not strictly required. Charts produced in this manner are not normally interactive and cannot respond to user events. If you require interactivity, examine the next example.
Loading Pivot Tables with Data Using ASP.NET
This example creates a PivotTable object at run time, streams it to the client, and loads data from an Access database file located on the client (see Figure 3).
private void Page_Load(object sender, System.EventArgs e)
{
//determine whether or not the browser supports OWC
if(Request.Browser.ActiveXControls)
{
Response.Write("");
//send the load instructions
Response.Write(@"");
Response.Write("");
}
}
Figure 3: Create a PivotTable object at run time, stream it to the client, and load data from Access.
Although some .NET developers may find this interface a bit awkward when compared to a data grid, knowledge workers are very familiar with it and are particularly versed in massaging the data to produce sophisticated reports. The data massaging or slicing starts with the end-user dragging the items in the PivotTable Field List object on the right and releasing these items on the PivotTable form on the left (see Figure 4). The intersection of rows and columns produces a data report that can be filtered and probed, sorted, printed, or exported. The PivotTable object can also fire SQL queries at relational databases through its CommandText property. The results of these queries form part of the Field List object. It also allows the developer to exercise precise control over the items in the field list.
Figure 4: Our PivotTable example.
Embedding live objects in a stream provides the ability to generate rich, interactive data and analytical services for ASP.NET applications. The approach may also be used with Web services or remoting environments to provide reports to a variety of clients across disparate platforms. For instance, it is possible for a Windows client to source its pivot table data via a Web service that connects to a database on a Unix machine.
Loading Excel Spreadsheets from the Client with ASP.NET
This example loads contrived data into a spreadsheet application. The data can then be submitted to the server for processing. Here s how. Embed the ActiveX Excel Spreadsheet object into an ASPX Web form by dragging the Excel Spreadsheet control from the toolbox onto the design surface in Microsoft Visual Studio. Next, map the click event of an html button to a client-side loadData function. When the button is clicked, the spreadsheet will be filled with data. Here is the bare-bones code:
//html button is mapped to this function
function loadData(){
//CSVDATA may be used to load or unload data
//into the spreadsheet
document.all.sp.CSVDATA = "1,2,3,4\n5,6,7";
}
Notice that the new line character has been inserted in the string to force items 5, 6, and 7 to load in a new row. The CSVDATA property provides only basic formatting capabilities. If your application requires sophisticated data presentation such as text formatting and row highlighting, you should use HTML or XML formats instead of CSV. See Figure 5 for an illustration of what the application looks like (additional frills, such as a panel with a couple of text boxes, simulate a real-world application).
Figure 5: Load data into a
spreadsheet application.
For a real-world application, the Save button would simply unload the data using the CSVDATA property and submit that data for processing on the server. This implementation is simple enough to be left as an exercise for the reader. Figure 6 shows some rudimentary server-side processing code to parse the contents of the spreadsheet.
//assume PostedData contains the spreadsheet data from the client
if(PostedData.Value != string.Empty)
{
string[] rows = PostedData.Value.Split('\n');
if(rows != null && rows.Length > 0)
{
foreach(string columns in rows)
{
//collect column contents for processing
}
}
}
Figure 6: Parse the contents of the spreadsheet.
The spreadsheet wraps a world of functionality behind a familiar interface. Some of that functionality is exposed neatly through the Commands and Options dialog box, as shown in Figure 7. The dialog box can be called from the toolbar or the built-in context menu of the spreadsheet. All the functionality shown in the dialog box may be performed through code, as well. Additionally, programmatic access to other user-interface pieces, such as the context menu and toolbar control, requires only a moderate amount of development effort. For instance, the export button on the toolbar natively implements the export of data from the control into Excel. But it is still possible to customize and refine the export behavior to suit your needs.
Figure 7: The Commands and Options
dialog box.
Conclusion
We ve barely scraped the surface in this introductory article but it should be enough to spark your interest in these components. In particular, you should realize that the bulk of the coding effort centers on loading and unloading data into the controls. The data massaging portion is handled entirely by the control itself so that the developer doesn t have to build this functionality by hand.
The Office Web Components are based on a proven architecture that is flexible enough to withstand the weight of enterprise software requirements. In addition, the suite sits behind a front-end that is familiar to end-users and knowledge workers because so many companies already use Microsoft Excel. These characteristics cannot be understated because they reduce training time and help build confidence in the use of software based on the OWC.
Although the technology is not new Access and VB programmers have been using it for years ASP.NET does bring this technology to the forefront so that all developers can use these components in new and exciting ways to create a new breed of commercial-grade, interactive software for the Web that integrates seamlessly with core Microsoft Office products. If you ve ever considered leveraging Microsoft Office technology in your Web and Windows applications, the Office Web Components may be a suitable candidate.
The sample code accompanying this article is available for download.
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 http://www.lulu.com/owc.
Resources in the form of articles, code walkthroughs, and white papers for the Office Web Components may be found on MSDN. The public newsgroup may be found at Microsoft.public.office.developer.web.components. Books dedicated to the components may be found at http://www.lulu.com/owc, Amazon, Barnes & Noble, and most online book merchants.
