Microsoft KB Archive/288130

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

Article ID: 288130

Article Last Modified on 5/14/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components
 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Active Server Pages 4.0

-



This article was previously published under Q288130



SUMMARY
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



MORE INFORMATION
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 = &quot;text/xml&quot;

Dim NumOrders, NumProds, r   NumOrders = 300 NumProds = 10 Dim oSS Dim oOrdersSheet Dim oTotalsSheet Dim oRange Dim c   Set oSS = CreateObject(&quot;OWC10.Spreadsheet&quot;) Set c = oSS.Constants

'Rename Sheet1 to &quot;Orders&quot;, rename Sheet2 to &quot;Totals&quot; and remove Sheet3 Set oOrdersSheet = oSS.Worksheets(1) oOrdersSheet.Name = &quot;Orders&quot; Set oTotalsSheet = oSS.Worksheets(2) oTotalsSheet.Name = &quot;Totals&quot; 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(&quot;A1:F1&quot;) oRange.Value = Array(&quot;Order Number&quot;, &quot;Product ID&quot;, &quot;Quantity&quot;, &quot;Price&quot;, &quot;Discount&quot;, &quot;Total&quot;) oRange.Font.Bold = True oRange.Interior.Color = &quot;Silver&quot; oRange.Borders(c.xlEdgeBottom).Weight = c.xlThick oRange.HorizontalAlignment = c.xlHAlignCenter 'Apply formatting to the columns oOrdersSheet.Range(&quot;A:A&quot;).ColumnWidth = 20 oOrdersSheet.Range(&quot;B:E&quot;).ColumnWidth = 15 oOrdersSheet.Range(&quot;F:F&quot;).ColumnWidth = 20 oOrdersSheet.Range(&quot;A2:E&quot; & NumOrders + 1 _       ).HorizontalAlignment = c.xlHAlignCenter oOrdersSheet.Range(&quot;D2:D&quot; & NumOrders + 1).NumberFormat = &quot;0.00&quot; oOrdersSheet.Range(&quot;E2:E&quot; & NumOrders + 1).NumberFormat = &quot;0 % &quot; oOrdersSheet.Range(&quot;F2:F&quot; & NumOrders + 1).NumberFormat = &quot;$ 0.00&quot; '&quot;_($* #,##0.00_)&quot; '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(&quot;A2:E&quot; & NumOrders + 1).Value = aOrderData 'Add a formula to calculate the order total for each row and format the column oOrdersSheet.Range(&quot;F2:F&quot; & NumOrders + 1).Formula = &quot;=C2*D2*(1-E2)&quot; oOrdersSheet.Range(&quot;F2:F&quot; & NumOrders + 1).NumberFormat = &quot;_( $* #,##0.00   _)&quot;

'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 &quot;5&quot; oOrdersSheet.AutoFilter.Apply 'Add a Subtotal at the end of the usedrange oOrdersSheet.Range(&quot;F&quot; & NumOrders + 3).Formula = &quot;=SUBTOTAL(9, F2:F&quot; & NumOrders + 1 & &quot;)&quot; '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 = &quot;Product ID&quot; oSS.Windows(1).ColumnHeadings(2).Caption = &quot;Total&quot; oSS.Windows(1).DisplayRowHeadings = False 'Add the product IDs to column 1 Dim aProductIDs aProductIDs = GetProductIDs oTotalsSheet.Range(&quot;A1:A&quot; & NumProds).Value = aProductIDs oTotalsSheet.Range(&quot;A1:A&quot; & NumProds).HorizontalAlignment = c.xlHAlignCenter

'Add a formula to column 2 that computes totals per product from the Orders Sheet oTotalsSheet.Range(&quot;B1:B&quot; & NumProds).Formula = _ &quot;=SUMIF(Orders!B$2:B$&quot; & NumOrders + 1 & &quot;,A1,Orders!F$2:F$&quot; & NumOrders + 1 & &quot;)&quot; oTotalsSheet.Range(&quot;B1:B&quot; & NumProds).NumberFormat = &quot;_( $* #,##0.00   _)&quot;

'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.End

Function 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) = &quot;'&quot; & String(7-Len(CStr(r+1)), &quot;0&quot;) & 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 = aOrderInfo End Function

Function GetProductIDs ReDim aPIDs(NumProds, 1) For r = 0 To NumProds-1 aPIDs(r, 0) = r+1 Next GetProductIDs = aPIDs End 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:

 

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   tag. You could also set the XMLURL property at run-time if desired, by using the following code. Spreadsheet1.XMLURL = &quot;http://YourWebServer/xmlss.asp&quot;

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:  Open XMLSS.asp in a text editor.  Change the following line in the script: Response.ContentType = &quot;text/xml&quot; Change the line of code to resemble the following: Response.ContentType = &quot;application/vnd.ms-excel&quot;  Save your changes to XMLSS.asp, and then start Windows Internet Explorer. Browse to http://YourWebServer/XMLSS.asp. The XML Spreadsheet is rendered in Microsoft Excel hosted in-place in the browser.

