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
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"
'=== 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
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.AutoFilter.Filters(2).Criteria.FilterFunction = c.ssFilterFunctionInclude
'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
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
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
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
GetOrderInfo = aOrderInfo
ReDim aPIDs(NumProds, 1)
For r = 0 To NumProds-1
aPIDs(r, 0) = r+1
GetProductIDs = aPIDs
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:
<object classid="clsid:0002E551-0000-0000-C000-000000000046" id="Spreadsheet1">
<param name="XMLURL" value="http://YourWebServer/xmlss.asp">
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:
- Start Microsoft Excel.
- On the File menu, click Open.
- 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:
Change the line of code to resemble the following:
Response.ContentType = "text/xml"
Response.ContentType = "application/vnd.ms-excel"
- 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.
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:
How to use Visual Basic or ASP to create an XML spreadsheet for Excel 2002 and Excel 2003
How to use XSL to transform Excel XML spreadsheet for server-side use
Considerations for server-side Automation of Office