To read the article online, visit http://www.4GuysFromRolla.com/webtech/051502-1.shtml

Creating Multisheet Excel Spreadsheets with Office Web Components (OWC) for XP

By J. Christian Reed



Creating Excel Spreadsheets through an ASP Page
By Scott Mitchell
This article discusses how to create a multisheet Excel spreadsheet using Office Web Components (OWC) for Office XP. Before reading this article you may wish to know of the various ways one can have an ASP page create an Excel application, so I thought it might be worthwhile to list some of the more popular techniques.

One technique is to simply set the Response.ContentType property to application/vnd.ms-excel and sending simple HTML that Excel can interpret. This technique is rather limiting in that the generated Excel spreadsheet is bound to be very simple (that is you cannot use formatting or insert formulas, etc.). Read Dynamic Excel Reports with ASP for more information on this technique. This option is nice, though, because it only requires that the client have Excel installed on their machine, not the server.

Another approach, in which only the client must have Excel installed, is to use SoftArtisan's ExcelWriter component. This component generates an Excel spreadsheet that can have the full spreadsheet formatting and function support without requiring Excel be installed on the Web server. However this component is not free, costing either $199 or $299 for the standard a professional versions (as of May 2002).

If Excel is installed on the Web server you have another two (free) options. One is to use syntax like CreateObject("Excel.Application"), which uses Office Automation to create an Excel spreadsheet that you can then manipulate through your ASP page. While this approach gives you full access to the many features of Excel, it is slow, especially if you expect any sort of audience attempting to simultaneously create spreadsheets. (For more information on this technique check out this code example.

A better approach is to use Office Web Components (OWC), which is highlighted at Creating Excel Spreadsheets with Office Web Components (OWC). For Excel 2000 I was able to create worksheets with many of Excel's advanced features, but the documentation is anything but ample. While I've not read it, I've heard a good starting place is Programming Microsoft Office 2000 Web Components. Furthermore, I've not tried the OWC for Office XP, but they appear to support many more features, as this article shows.

Now, without further delay, here is the article, showing how to create multisheet spreadsheets using OWC for Office XP. As is the case with OWC, for this article's examples to work both the client and server will need to have Office XP installed on it...


Introduction
When I started my last project I thought it would be a cake walk because every requirement seemed so commonplace and everyday. Basically I needed to allow my users (through a Web page) to select parameters (from combo boxes) that drove a select statement which in term returned its output on a formatted multi-sheeted Excel spreadsheet. I had been there and done that in VB and so I assumed that porting this knowledge to ASP would be no big deal. Well it wasn't as straight forward as I had hoped.

I learned quickly from personal trial and error that using the usual Excel ActiveX component of CreateObject("Excel.Application") was not going to work server-side and definitely not client-side (my reports were too big). It was unstable and very slow. After reading on what Microsoft had to say about this in an article titled Microsoft Office Development with Visual Studio, I was convinced that I needed something else. I looked at the 4Guys article Creating Excel Spreadsheets with Office Web Components (OWC) and I thought I had a solution until I discovered that OWC only allowed for one worksheet in an Excel workbook! So I went back to CreateObject("Excel.Application") but was not pleased with its creepingly slow speed.

Office Web Components for Office XP to the Rescue
After reading Microsoft's many discouragements and disclaimers regarding the use of server-side automation of office applications I happened to discover that the Office Web Components of Microsoft Office XP (OWC10) allow you to create server-side spreadsheet objects with multiple sheets! Read more about Office XP Web Components at http://www.microsoft.com/Office/developer/platform/webcomp.asp.

So after locating an example and playing around with it a little, I was able to complete my project. Instead of boring you with a heavy example taken form that code I made up this little ASP OWC10 example. Because you are compiling an Excel Workbook here you will want to buffer your server output until it has completed the spreadsheet. To get Excel to open up this XML spreadsheet in the client's browser we switch the ContentType to the Excel MIME type.

<%@ Language=VBScript %>
<%
  Response.Buffer = True
  Response.ContentType = "application/vnd.ms-excel"

Declare your variables set up your Excel Workbook OWC object. Most documentation and examples refers to this object as a Spreadsheet or Worksheet. I stay away from this name because it gets me confused about the difference between this object and the sheets objects that I am going to use in my code. Notice that we use OWC10 here (Office XP Web Components); you will have to have Office XP installed on your Web server for this code to work.

  Dim objWorkbook, c, objSheet1, objSheet2, intCol, intRow
  Set objWorkbook = CreateObject("OWC10.Spreadsheet")

Set up your Worksheet objects that are in your Workbook. I rename the sheets here and I also delete the third sheet just to show that it can be done. The method .add works here too, but your objWorkbook will start with the default three Worksheets already in it so there is no need to add any if you need three or less. The other nice thing about OWC is that it let's you use the Excel constants. When I played around with Excel.Application in ASP I didn't have that luxury.

  Set objSheet1 = objWorkbook.Worksheets(1)
  objSheet1.Name = "One"
  Set objSheet2 = objWorkbook.Worksheets(2)
  objSheet2.Name = "Two"
  objWorkbook.Worksheets(3).Delete

  Set c = objWorkbook.Constants

Now I am going to use a Sub to write to my two Excel Worksheets. Nothing too fancy here:

Sub WriteData(pintDivisor, pstrColor)
   With objWorkbook.ActiveSheet
      For intRow = 1 To 100
         For intCol = 1 To 10
            .Cells(intRow, intCol).Value = (intRow - intCol) / pintDivisor
            If .Cells(intRow, intCol).Value Mod 3 = 0 Then
               .Cells(intRow, intCol).Interior.Color = pstrColor
            End If
         Next
         .Cells(intRow, 11).Value = "= I" & CStr(intRow) & "+J" & CStr(intRow)
         If intRow Mod 2 = 0 Then .Cells(intRow, 11).Interior.Color = "LightGray"
      Next
      .Columns("A:D").AutoFilter
   End With
End Sub

We can then activate our Worksheets and write some data to the worksheets by calling the WriteData Sub, like so:

  objSheet1.Activate
  Call WriteData(3, "Red")
      
  objSheet2.Activate
  Call WriteData(7, "Blue")

Finally we need to have the user's browser load Office XP and display the Excel worksheet. We do this by simply dumping out the XML data. (Recall that at the top of our ASP page we set the Response.ContentType property to the Excel MIME type, so the browser knows to launch Excel.)

  objWorkbook.DisplayToolbar = False
  objWorkbook.AutoFit = True
  objSheet1.Activate
  
  'Write the XML data to the client    
  Response.Write objWorkbook.XMLData
  Response.End
      
  'Clean up your variables!
  Set objWorkbook = Nothing
  Set c = Nothing
  Set objSheet1 = Nothing
  Set objSheet2 = Nothing
  Set objSheet3 = Nothing

The client will have to have Excel XP installed on their computer or they will just see an Excel Spreadsheet full of XML output that is completely unusable. If they have Excel XP installed they will be prompted once the spreadsheet is created to open or save it. If they open it or save it they can later use Excel to save it as an Excel Workbook format (.xls) and they will have all of the formatting that you originally showed them.

Also for displaying straight from the server to client upon completion of the Excel Workbook the following properties are available: HTMLData, CSVData, and XMLData. XMLData is the only one that saves successfully with all formatting straight to Excel Workbook file format. The HTMLData isn't bad but it is just HTML and so AutoFilter and other Excel specific things won't work.

Conclusion
This worked great for me. This is the best thing I have seen from Office XP as of yet. I never could figure out how to get the FreezePanes property to work (please email me if you figure it out), but everything else seemed to work just as posted.

If you are serious about using Office Web Components for Office XP I highly recommend that you download and install the Office XP Web Component Toolpack. It comes with an invaluable Office XP Web Components Object Model Reference and some great examples that you won't find elsewhere.

Good luck and Happy Programming!

By J. Christian Reed


Attachments

  • Download the complete source code (in text format)


  • Article Information
    Article Title: Creating Multisheet Excel Spreadsheets with Office Web Components (OWC) for XP
    Article Author: J. Christian Reed
    Published Date: Wednesday, May 15, 2002
    Article URL: http://www.4GuysFromRolla.com/webtech/051502-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers