You are currently offline, waiting for your internet to reconnect

How to use ASP to build spreadsheet XML for client-side display

This article was previously published under Q288130
This article illustrates Microsoft Active Server Pages (ASP) script that builds a spreadsheet in the XML Spreadsheet (XMLSS) format by using the Office Spreadsheet component. The XMLSS can be displayed client-side in one of several ways:
  • In the Spreadsheet component on a Web page
  • In Microsoft Excel shown in-place in the browser
  • Opened directly in Microsoft Excel
Using the Spreadsheet component in server-side code to build spreadsheets provides more scalability and better performance compared to using server-side Automation of Microsoft Excel. We do not recomment Automation of Office applications, including Excel, on the server. This method should be avoided when other alternatives for achieving the same results are available to you. XMLSS can persist many of the features common to both the Spreadsheet component and Microsoft Excel. Multi-sheet workbooks, cell formatting, Autofilter, cell formulas, and re-calculation represent a handful of those features. The Spreadsheet component has an object model that closely matches the object model for Microsoft Excel. Therefore, if you are familiar with the Excel object model, you can easily apply some of your existing Excel code, with modification, for use with the Spreadsheet component.

The following sample demonstrates how to generate a multi-sheet workbook in XMLSS using the Spreadsheet component with ASP. The sample also discusses how you can display the resulting XMLSS client-side on a Web page or in Microsoft Excel.

ASP script to build XMLSS by using the Spreadsheet component

Save the following ASP as XMLSS.asp in the virtual root directory of your Web server. By default, the root directory is C:\inetpub\wwwroot.
<% Language=VBScript %><%    Response.Buffer = True    Response.ContentType = "text/xml"    Dim NumOrders, NumProds, r    NumOrders = 300    NumProds = 10            Dim oSS    Dim oOrdersSheet    Dim oTotalsSheet    Dim oRange    Dim c        Set oSS = CreateObject("OWC10.Spreadsheet")    Set c = oSS.Constants    'Rename Sheet1 to "Orders", rename Sheet2 to "Totals" and remove Sheet3    Set oOrdersSheet = oSS.Worksheets(1)    oOrdersSheet.Name = "Orders"    Set oTotalsSheet = oSS.Worksheets(2)    oTotalsSheet.Name = "Totals"    oSS.Worksheets(3).Delete        '=== Build the First Worksheet (Orders) ==============================================            'Add headings to A1:F1 of the Orders worksheet and apply formatting    Set oRange = oOrdersSheet.Range("A1:F1")    oRange.Value = Array("Order Number", "Product ID", "Quantity", "Price", "Discount", "Total")    oRange.Font.Bold = True    oRange.Interior.Color = "Silver"    oRange.Borders(c.xlEdgeBottom).Weight = c.xlThick    oRange.HorizontalAlignment = c.xlHAlignCenter       'Apply formatting to the columns    oOrdersSheet.Range("A:A").ColumnWidth = 20    oOrdersSheet.Range("B:E").ColumnWidth = 15    oOrdersSheet.Range("F:F").ColumnWidth = 20    oOrdersSheet.Range("A2:E" & NumOrders + 1 _        ).HorizontalAlignment = c.xlHAlignCenter    oOrdersSheet.Range("D2:D" & NumOrders + 1).NumberFormat = "0.00"    oOrdersSheet.Range("E2:E" & NumOrders + 1).NumberFormat = "0 % "    oOrdersSheet.Range("F2:F" & NumOrders + 1).NumberFormat = "$ 0.00" '"_($* #,##0.00_)"            'Obtain the order information for the first five columns in the Orders worksheet    'and populate the worksheet with that data starting at row 2    Dim aOrderData    aOrderData = GetOrderInfo    oOrdersSheet.Range("A2:E" & NumOrders + 1).Value = aOrderData        'Add a formula to calculate the order total for each row and format the column    oOrdersSheet.Range("F2:F" & NumOrders + 1).Formula = "=C2*D2*(1-E2)"        oOrdersSheet.Range("F2:F" & NumOrders + 1).NumberFormat = "_(  $* #,##0.00   _)"    'Apply a border to the used rows    oOrdersSheet.UsedRange.Borders(c.xlInsideHorizontal).Weight = c.xlThin    oOrdersSheet.UsedRange.BorderAround , c.xlThin, 15        'Turn on AutoFilter and display an initial criteria where    'the Product ID (column 2) is equal to 5    oOrdersSheet.UsedRange.AutoFilter    oOrdersSheet.AutoFilter.Filters(2).Criteria.FilterFunction = c.ssFilterFunctionInclude    oOrdersSheet.AutoFilter.Filters(2).Criteria.Add "5"    oOrdersSheet.AutoFilter.Apply        'Add a Subtotal at the end of the usedrange    oOrdersSheet.Range("F" & NumOrders + 3).Formula = "=SUBTOTAL(9, F2:F" & NumOrders + 1 & ")"        'Apply window settings for the Orders worksheet    oOrdersSheet.Activate   'Makes the Orders sheet active    oSS.Windows(1).ViewableRange = oOrdersSheet.UsedRange.Address    oSS.Windows(1).DisplayRowHeadings = False    oSS.Windows(1).DisplayColumnHeadings = False    oSS.Windows(1).FreezePanes = True    oSS.Windows(1).DisplayGridlines = False        '=== Build the Second Worksheet (Totals) ===========================================        'Change the Column headings and hide row headings    oTotalsSheet.Activate    oSS.Windows(1).ColumnHeadings(1).Caption = "Product ID"    oSS.Windows(1).ColumnHeadings(2).Caption = "Total"    oSS.Windows(1).DisplayRowHeadings = False        'Add the product IDs to column 1    Dim aProductIDs    aProductIDs = GetProductIDs    oTotalsSheet.Range("A1:A" & NumProds).Value = aProductIDs    oTotalsSheet.Range("A1:A" & NumProds).HorizontalAlignment = c.xlHAlignCenter    'Add a formula to column 2 that computes totals per product from the Orders Sheet    oTotalsSheet.Range("B1:B" & NumProds).Formula = _        "=SUMIF(Orders!B$2:B$" & NumOrders + 1 & ",A1,Orders!F$2:F$" & NumOrders + 1 & ")"    oTotalsSheet.Range("B1:B" & NumProds).NumberFormat = "_(  $* #,##0.00   _)"    'Apply window settings for the Totals worksheet    oSS.Windows(1).ViewableRange = oTotalsSheet.UsedRange.Address        '=== Setup for final presentation ==================================================        oSS.DisplayToolbar = False    oSS.AutoFit = True    oOrdersSheet.Activate    Response.Write oSS.XMLData    Response.EndFunction GetOrderInfo()    ReDim aOrderInfo(NumOrders,5)    Dim aPrice, aDisc    aPrice = Array(10.25, 9.5, 2.34, 6.57, 9.87, 4.55, 6, 13.05, 3.3, 5.5)    aDisc = Array(0, 0.1, 0.15, 0.2)    For r = 0 To NumOrders-1        aOrderInfo(r, 0) = "'" & String(7-Len(CStr(r+1)), "0") & r+1 'Col 1 is Order Number        aOrderInfo(r, 1) = Int(Rnd() * NumProds) + 1                 'Col 2 is Product ID        aOrderInfo(r, 2) = Int(Rnd() * 20) + 1                       'Col 3 is Quantity        aOrderInfo(r, 3) = aPrice(aOrderInfo(r, 1)-1)                'Col 4 is Price        aOrderInfo(r, 4) = aDisc(Int(Rnd() * 4))                     'Col 5 is Discount    Next    GetOrderInfo = aOrderInfoEnd FunctionFunction GetProductIDs()    ReDim aPIDs(NumProds, 1)    For r = 0 To NumProds-1        aPIDs(r, 0) = r+1    Next    GetProductIDs = aPIDsEnd Function%>				

Display the XMLSS on a Web page

To display the sample XMLSS on a Web page, you must only set the XMLURL property for a Spreadsheet Component to the URL for the ASP, as follows:
<html><body><object classid="clsid:0002E551-0000-0000-C000-000000000046" id="Spreadsheet1">  <param name="XMLURL" value="http://YourWebServer/xmlss.asp"></object></body></html>				
Note If you are using Microsoft Office 2003, you may have to change the classid in the above code as applicable.

In the preceding HTML, the XMLURL property is set by using a <param> tag. You could also set the XMLURL property at run-time if desired, by using the following code.
   Spreadsheet1.XMLURL = "http://YourWebServer/xmlss.asp"				

Display the XMLSS in Microsoft Excel

The XMLSS that is created with the Spreadsheet component can be opened in Microsoft Excel. The formatting and features you implement in the Spreadsheet component can be shared with Microsoft Excel. The Spreadsheet component supports some features that Excel does not. Additionally, Excel supports some features that the Spreadsheet component does not. Any XML tags or attributes that Excel does not implement are ignored when the XMLSS is opened.

To view the results of the sample ASP script in Microsoft Excel, follow these steps:
  1. Start Microsoft Excel.
  2. On the File menu, click Open.
  3. In the File Name box, type http://YourWebServer/xmlss.asp, and then click Open.
Examine the workbook, and note that the data and formats that were applied at run time are all present in the workbook when it is opened in Excel. There is one exception: The heading captions that are created in the Spreadsheet component do not carry over to Excel because this is a feature of the Spreadsheet component that Microsoft Excel does not share. If you create XMLSS by using the Spreadsheet component for the purpose of displaying the file in Excel, be aware of the different features that each supports.

Another way to open the ASP-created XMLSS in Microsoft Excel is to supply the Excel Multipurpose Internet Mail Extensions (MIME) type as the ContentType in your ASP. When you use the Excel MIME type and browse to your ASP, the XMLSS can be rendered in Microsoft Excel in-place in the browser. To do this, follow these steps:
  1. Open XMLSS.asp in a text editor.
  2. Change the following line in the script:
    Response.ContentType = "text/xml"					
    Change the line of code to resemble the following:
    Response.ContentType = "application/"					
  3. Save your changes to XMLSS.asp, and then start Windows Internet Explorer.
  4. Browse to http://YourWebServer/XMLSS.asp. The XML Spreadsheet is rendered in Microsoft Excel hosted in-place in the browser.
For more information, visit the Office Web Components topic at the following Microsoft Web site:For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
285891 How to use Visual Basic or ASP to create an XML spreadsheet for Excel 2002 and Excel 2003
278976 How to use XSL to transform Excel XML spreadsheet for server-side use
257757 Considerations for server-side Automation of Office
XL2003 XL2007

Article ID: 288130 - Last Review: 05/14/2007 21:51:06 - Revision: 5.2

  • Microsoft Office 2003 Web Components
  • Microsoft Office XP Web Components
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Active Server Pages 4.0
  • kbhowto kbofficewebspread KB288130