OWC Meets ASP.NET

CoverStory

LANGUAGES:C#

ASP.NETVERSIONS: 1.x

OWC Meets ASP.NET

Leverage Microsoft Office Technology in Your WebApplications

By Alvin Bruney

The Microsoft Office Web Components (OWC) are a group ofActiveX controls authored by Microsoft that allow developers to harness thepower of Microsoft Office technology in Web applications. The initial releaseof the OWC (version 9) shipped with Microsoft Office 2000. Since then, versions10 and 11 followed with Microsoft Office XP and Office 2003, respectively. Thecomponents provide Microsoft Excel and Access functionality only; otherMicrosoft Office technology, such as Word and PowerPoint, are not provided. Thecomponents are built to closely mirror the fit and finish of the desktopcounterpart, 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 animpressive number of charts in 2D, as well as 3D. The Chart control supports a richevent model, as well as drill-through capabilities when bound to an underlying DataSource control. The PivotTable control contains a potent auto-calculationengine and can pivot large amounts of data efficiently and easily. The DataSource component allows the ActiveX controls to bind to a wide variety of datasources to include XML, OLAP cubes, relational databases, and even other OWCcontrols. For instance, the Chart control can easily source its data fromeither the desktop or the OWC version of Excel. The Excel Spreadsheet control containsa powerful calculation engine capable of handling complex computations in realtime. The Excel Spreadsheet control also provides most of the functionalityavailable in the desktop version. With the exception of the Data Source, eachcontrol contains a client interface and a server-side piece.

To run the next few examples you must install thecomponents from the Microsoft Office setup disk. The components are alsoavailable as a free download from the Microsoft Office Web site. OWC version 10for 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. Afterthe installation process, add the Chart, PivotTable, and Excel objects to thetoolbox 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 theserver using the ASP.NET code-behind approach (see Figure 1). The image is thensent to the requesting browser for display (see Figure 2).

private void Page_Load(object sender, System.EventArgs e)

{

  //First create aChartSpace object to hold the chart

  ChartSpace objCSpace =new ChartSpaceClass ();

  //Add a chart andprovide a type

  ChChart objChart =objCSpace.Charts.Add (0);

  objChart.Type =ChartChartTypeEnum.chChartTypeLineStacked3D;

  //customize the look andfeel

objChart.PlotArea.Interior.SetTextured(

 OWC11.ChartPresetTextureEnum.chTextureGreenMarble,

 OWC11.ChartTextureFormatEnum.chTile,10,

 OWC11.ChartTexturePlacementEnum.chEnd);

  //add chart titles andlegend

  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 contriveddata

  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 thechart's series collection

  objChart.SeriesCollection.Add(0);

  //assign series

objChart.SeriesCollection[0].SetData (

 ChartDimensionsEnum.chDimSeriesNames,

  (int)ChartSpecialDataSourcesEnum.chDataLiteral,

 strSeriesName);

  //load the category andvalue 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 theclient

  Response.ContentType="image/gif";

  Response.BinaryWrite((byte[])objCSpace.GetPicture("gif",500,400));

  Response.End();

}

Figure 1: Create achart 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 surfacesand a legend to create visual impact, but these options are not strictlyrequired. Charts produced in this manner are not normally interactive andcannot respond to user events. If you require interactivity, examine the nextexample.

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 locatedon the client (see Figure 3).

private void Page_Load(object sender, System.EventArgs e)

{

   //determine whether ornot the browser supports OWC

   if(Request.Browser.ActiveXControls)

    {

Response.Write("");

 //send the load instructions

Response.Write(@" 'Provider=MSDASQL.1;PersistSecurity Info=False;Extended Properties= 'DSN=sampledb;''");

 Response.Write("");

   }

}

Figure 3: Create aPivotTable object at run time, stream it to the client, and load data fromAccess.

Although some .NET developers may find this interface abit awkward when compared to a data grid, knowledge workers are very familiarwith it and are particularly versed in massaging the data to producesophisticated reports. The data massaging or slicing starts with the end-userdragging the items in the PivotTable Field List object on the right andreleasing these items on the PivotTable form on the left (see Figure 4). Theintersection of rows and columns produces a data report that can be filteredand probed, sorted, printed, or exported. The PivotTable object can also fireSQL queries at relational databases through its CommandText property. Theresults of these queries form part of the Field List object. It also allows thedeveloper 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 togenerate rich, interactive data and analytical services for ASP.NETapplications. The approach may also be used with Web services or remotingenvironments to provide reports to a variety of clients across disparateplatforms. For instance, it is possible for a Windows client to source itspivot table data via a Web service that connects to a database on a Unixmachine.

Loading Excel Spreadsheets from the Client with ASP.NET

This example loads contrived data into a spreadsheetapplication. The data can then be submitted to the server for processing. Here show. Embed the ActiveX Excel Spreadsheet object into an ASPX Web form bydragging the Excel Spreadsheet control from the toolbox onto the design surfacein Microsoft Visual Studio. Next, map the click event of an html button to aclient-side loadData function. When the button is clicked, the spreadsheet willbe filled with data. Here is the bare-bones code:

//html button is mapped to this function

function loadData(){

 //CSVDATA may be used toload or unload data

 //into the spreadsheet

 document.all.sp.CSVDATA ="1,2,3,45,6,7";

}

Notice that the new line character has been inserted inthe string to force items 5, 6, and 7 to load in a new row. The CSVDATAproperty provides only basic formatting capabilities. If your applicationrequires sophisticated data presentation such as text formatting and rowhighlighting, you should use HTML or XML formats instead of CSV. See Figure 5for an illustration of what the application looks like (additional frills, suchas a panel with a couple of text boxes, simulate a real-world application).

Figure 5: Load data into aspreadsheet application.

For a real-world application, the Save button would simplyunload the data using the CSVDATA property and submit that data for processingon the server. This implementation is simple enough to be left as an exercise forthe reader. Figure 6 shows some rudimentary server-side processing code toparse the contents of the spreadsheet.

//assume PostedData contains the spreadsheet data from the client

if(PostedData.Value != string.Empty)

{

 string[] rows =PostedData.Value.Split('');

 if(rows != null&& rows.Length > 0)

 {

   foreach(string columnsin rows)

   {

     //collect columncontents for processing

   }

 }

}

Figure 6: Parsethe contents of the spreadsheet.

The spreadsheet wraps a world of functionality behind afamiliar interface. Some of that functionality is exposed neatly through the Commandsand Options dialog box, as shown in Figure 7. The dialog box can be called fromthe toolbar or the built-in context menu of the spreadsheet. All thefunctionality shown in the dialog box may be performed through code, as well.Additionally, programmatic access to other user-interface pieces, such as thecontext menu and toolbar control, requires only a moderate amount ofdevelopment effort. For instance, the export button on the toolbar nativelyimplements the export of data from the control into Excel. But it is stillpossible to customize and refine the export behavior to suit your needs.

Figure 7: The Commands and Optionsdialog box.

Conclusion

We ve barely scraped the surface in this introductoryarticle 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 onloading and unloading data into the controls. The data massaging portion ishandled entirely by the control itself so that the developer doesn t have tobuild this functionality by hand.

The Office Web Components are based on a provenarchitecture that is flexible enough to withstand the weight of enterprisesoftware requirements. In addition, the suite sits behind a front-end that isfamiliar to end-users and knowledge workers because so many companies alreadyuse Microsoft Excel. These characteristics cannot be understated because theyreduce training time and help build confidence in the use of software based onthe OWC.

Although the technology is not new Access and VBprogrammers have been using it for years ASP.NET does bring this technologyto the forefront so that all developers can use these components in new andexciting ways to create a new breed of commercial-grade, interactive softwarefor the Web that integrates seamlessly with core Microsoft Office products. Ifyou ve ever considered leveraging Microsoft Office technology in your Web and Windowsapplications, the Office Web Components may be a suitable candidate.

The sample code accompanyingthis article is available for download.

Alvin Bruney is anASP.NET MVP. He has been involved in .NET technology since the beta version.His new book, The Microsoft Office WebComponents Black Book with .NET, is available at http://www.lulu.com/owc.

Learn More about OWC

Resources in the form of articles, codewalkthroughs, and white papers for the Office Web Components may be found onMSDN. The public newsgroup may be found atMicrosoft.public.office.developer.web.components. Books dedicated to thecomponents may be found at http://www.lulu.com/owc,Amazon, Barnes & Noble, and most online book merchants.