Web developers can produce a Microsoft Excel file and pass that file to the client in a variety of ways. To introduce you to some of your options, we’ll examine a couple of methods here. Although this tutorial is written in VBScript, it should be simple to convert it into any other scripting language you want to use.
First, we’ll look at a simple approach in which we pass over an HTML table and then just change the Content Type we’re sending to the browser to that of Excel. In the second, more complex and powerful solution, we’ll use an ActiveX component to directly link between our code and the local instance of MS Excel.
A simple approach
If you want to use only a single Excel worksheet, you can use this approach. We create a standard HTML table and pass it back from our Server Side Script—in this case, a VBScript Active Server Pages (ASP) script—with the MIME type of Excel and not HTML. This call is then managed by the browser.
The first thing we do in our script is to set the Content Type we are going to return to the client browser:
Response.ContentType = “application/vnd.ms-excel”
Now that we’ve told the browser that we’re sending back an Excel file, we simply create our standard HTML table and send that back. The table in Listing A could be generated from the results of a database query, for example, or simply from static HTML.
We can also include some simple Excel commands in the data. For instance, we could create three columns: first value, second value, and a total column, as shown in Listing B.
The results you get depend on how your Web browser is set up to handle .XLS files. In some cases, it will render in the Web browser; in others, it will open Excel and load the file in there. This activity is determined by the local client machine settings for MIME types and is outside the control of a Web page.
This technique should suffice many developers. But if you need a little more flexibility or complexity, you will have to use an ActiveX component.
An ActiveX approach
If you require more than a single worksheet, or if you require access to some of the VBA functions, you need to use ActiveX to create your file. In this approach, we’ll create an instance of Excel on the client and then pass data and function calls into it.
For this to work, end users must have their ActiveX settings in Internet Explorer set to Prompt or Enable for the correct zone. (At present, this functionality works only in Internet Explorer.)
Set Xcl = CreateObject(“Excel.Application”)
Xcl.Visible = true
Set newBook = Xcl.Workbooks.Add
First, we create our Excel object ,Xcl. This will be the link between our code and Excel. The rest of our code will reside within a VBScript subroutine called StartExcel. Next, we use the Server.CreateObject function to create a link to Excel and assign that to our object Xcl. Then, we set the visibility of the object to true and add a Workbook to our object:
A standard Excel Workbook has three worksheets. For each additional sheet you require, you will need to call the Add function. For our example, we require only the standard number of sheets.
As shown in Listing C, we will activate the first worksheet in the workbook so that it becomes the active sheet. We set the width of the first two columns to 50 characters and also enable word wrapping on these columns. We set a format mask for numeric data and set the cell alignment to left for the range of cells from A1 to B1000. The reason we use the actual values—such as -4131—and not the Excel Constant of xlLeft is that as we are interacting via ActiveX, so we don’t have the visibility of these constants.
Next, we set the background color of the first cell and enter some values into the worksheet to represent the data you may want to add. The last thing we do in this section is name the current worksheet My First Worksheet.
Finally, we call the Save As dialog, passing in the suggested file name of Testing Excel Extraction. If the user selects another filename, that’s fine too. But if he or she cancels the dialog, we detect that and use our preferred name. We then destroy our object by setting its value to nothing and redirect the use to another page. To get the code to run, we set the function to run when the HTML page has loaded by using the onLoad handler.
In some cases, we may need to pass Excel function calls, such as SUM, in this way. We can do this by passing the full string just as we would type it into Excel. We can also use VBA code to achieve the same results, as shown in Listing D.
In this example, we sum the values in the first two cells in the second column and place the results in the third row of the second column. In the forth row, we multiply that total value by 2.
As you can see, there is little difference between this and the VBScript version. The main changes involve setting some of the layout values in our VBA code.
Creating the file on the server side
If your output data file is large or complex, getting a client computer to do all the work may not be the best option. You can also produce the workbook on the file server side and then either provide a link to it or begin downloading the file automatically on its creation. This method allows you to process the file using the server, which is likely to be a more powerful computer.
To demonstrate, we’ll create a comma-separated values (CSV) file and provide a link to it. Step one is to get all of our data out of the data source. For the example, shown in Listing F, we use the getString function, which returns a recordset as a single string. For more complex processes, you may need to extract each element of the recordset in turn to perform an action on it, such as adding to a total or formatting.
This gives us the file created on the server and allows us to return a link to that file to the user. One of the possible downsides with this approach is that the user is required to run the code to produce the file and then click on a link to begin the file download. The other main problem with this approach is that it will, over time, fill up disk space on your server. So you need to either manually clean up the files at some point or create a batch job to do this for you.
Ready to create
I hope you found this tutorial helpful. You should now be able to create an Excel file on the client browser containing your information, as well as to perform at least some simple functions on that data. Here are some of the sites I find most useful for this type of application: