Create dynamic Web-based spreadsheets using Excel

Create dynamic Web-based spreadsheets using Excel

Most Windows developers know that Word and Excel 2000/XP provide the ability to save dynamic spreadsheet documents as Web pages. However, what if you want to create a dynamic spreadsheet that generates data on the fly through a Web interface--without adding additional tools to the server side? It's actually quite simple, assuming the client machine has Office XP or 2000 installed.

Let's say you want to create an Excel spreadsheet that contains deposit and withdrawal information in which the data is displayed in accounting format:

The negative values are in red.

There are five columns for the spreadsheet: Date, Group, Description, Amount, and Balance.

The column headers are bolded and centered in their individual cells.

The first row of data is the beginning balance, subsequent rows represent the individual transactions on that account, and the balance column is a sum of the previous balance and the current amount column.

To organize your spreadsheet in this format, start by creating a new spreadsheet. Then fill the first row with the appropriate column headers and format the columns to be displayed as necessary. Create the remaining rows dynamically based on the underlying data. So create just one row to represent a row of data, format the cells as necessary, and save the document as a Web page, making sure to select the check box Add Interactivity.

Next, open the saved document in an editor in which you can view the source code, such as Notepad. The document will contain an OBJECT tag with several PARAM tags. If you're using Office XP, the PARAM name to watch is XMLData. This PARAM tag has a value attribute set to a large HTML-encoded string. This value is the HTML-encoded XML that Excel uses to create the spreadsheet. Here's an example of the OBJECT tag with the associated PARAMs (This code has been truncated to save space.):

Copy the XMLData parameter value and paste it into a new document. Save this new document as an .htm file, because you'll want to open it in IE to copy the HTML-decoded text. When you view this document in IE, you'll see a page that looks like this:

<?xml version="1.0"?> <Workbook xmlns="urn:schemas-microsoft-
com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas
-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC
-html40">...

This is the XML that you'll reconfigure to create the appropriate output.

Save this document as an .xls file and open it in Excel to check the formatting. Here's where Office XP and 2000 differ: XP has an extended schema for providing extra functionality with additional tags for columns, rows, and cells. 2000 maintains a TABLE-TR-TD interface with styles defined for each component.

Now try to remove the ambiguity of style names, delete most of the default styles for the different tags, and normalize the styles. Style tags make that easy to do in XP, while in 2000, the styles are created in one STYLE block within the document. You can also put individual styles to the particular components with the style attribute in 2000 or by using the ss:[style_attribute] attribute in XP. You'll also want to delete some of the unnecessary tags from the XML, including PublishObjects and children, LastSaved, WindowHeight, and WindowWidth.

After you clean up your XML, try it in Excel again to make sure it works. If it does, then proceed to the next step.

Create a new ASP page and set either the Expires or ExpiresAbsolute properties on the Response object to ensure that the page will always be updated. Change the ContentType to application/vnd.ms-excel. Add a header called Content-Disposition and set the value to inline; filename=\"filename.xls\" if you want the spreadsheet displayed within the browser; otherwise, set it to attachment; filename=\"filename.xls\" to display outside the browser.

You can take out the static XML (such as the column header and worksheet information) and put that directly into your ASP page. Add a script block to take care of the XSL transformation of your dynamic data XML:

Dim oDOM, oXSL

Set oDOM = Server.CreateObject("MSXML.DOMDocument")

Set oXSL = Server.CreateObject("MSXML.DOMDocument")

oDOM.async = false;

oXSL.async = false;

If oDOM.loadXML(GetDataXML()) Then

If oXSL.load("xsldoc.xsl") Then

Response.Write oDOM.transformNode(oXSL)

End If

The last step is to create the XSL that will perform the transformation on the XML data that will be supplied through the GetDataXML() routine. Simply create your XSL to produce the rows that will complete the spreadsheet.