An approach using ActiveX

If you require more than a single worksheet or you require access to some of the VBA functions for example, you need to use ActiveX to create your file. In this approach we create an instance of Excel on the client and then pass data and function calls into it.

For this to work, the end user MUST have their ActiveX settings in Internet Explorer (IE) set to Prompt or Enable for the correct zone. At present this functionality only works in IE - Microsoft Internet Explorer.

<script language='VBScript'>
Dim Xcl
 
Sub StartExcel()
    Set Xcl = CreateObject("Excel.Application")
    Xcl.Visible = true
    Set newBook = Xcl.Workbooks.Add

Firstly, we create our MS 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. We then use the Server.CreateObject function to create an a link to Excel and assign that to our object Xcl, then we set the visibility of the object to true, then we add a Workbook to our object.

    newBook.Worksheets.Add

A standard Excel Workbook has 3 worksheets, for each additional one that you require, you will need to call the Add function of the Worksheet once for each worksheet more than 3 you require - in this case we only require 1.

    newBook.Worksheets(1).Activate
    newBook.Worksheets(1).Columns("A").columnwidth=50
    newBook.Worksheets(1).Columns("A").WrapText = True
    newBook.Worksheets(1).Columns("B").columnwidth=50
    newBook.Worksheets(1).Columns("B").WrapText = True
    newBook.Worksheets(1).Range("A1:B1000").NumberFormat = "0"
    newBook.Worksheets(1).Range("A1:B1000").HorizontalAlignment = -4131

We will Active the first worksheet in the book so that we it becomes the 'active' worksheet. We set the width of the first two columns to 50 characters and also enable wordwrapping on these columns. We set the format mask for numeric data to a specified set and the 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 we don't have visibility of these constants, a full list of the real value of the constants is also available.

    newBook.Worksheets(1).Cells(1,1).Interior.ColorIndex="15"
    newBook.Worksheets(1).Cells(1,1).value="First Column, First Cell"
    newBook.Worksheets(1).Cells(2,1).value="First Column, Second Cell"
    newBook.Worksheets(1).Cells(1,2).value="Second Column, First Cell"
    newBook.Worksheets(1).Cells(2,2).value="Second Column, Second Cell"
    newBook.Worksheets(1).Name="My First WorkSheet"

Set the background colour of the first cell to the colour represented by the value 15 - actually gray, then we shall place some values on to the worksheet to represent the data you may wish to add. The last thing we do in this section is to name the current worksheet as My First Worksheet.

    fname=Xcl.GetSaveAsFilename("Testing Excel Extraction.xls")
    if fname = "False" then
        fname="Testing Excel Extraction.xls"
    end if
    newBook.SaveAs fname
    Set Xcl = nothing
    Location.Href="http://www.greggriffiths.org/index.html"
end Sub
</script>
<body onload="StartExcel()">

Finally, we call the Save As dialog passing in the suggested file name of Testing Excel Extraction, if the user selects another filename thats fine too, but if they cancel the dialog then we catch that that has happened and use our preferred name. We then destroy our object by setting its value to nothing, then we redirect the use to another page - in this case http://www.greggriffiths.org/index.html. To get the code to run, we set the function to be run once the HTML page has loaded by using the onLoad handler. Click here to see this code running.

In some cases we may need to pass Excel function calls such as SUM in this way, this can be done by simply passing the full string as we would type into Excel, or the VBA code to achieve the same results, as shown in the following code :

Dim Xcl
Sub StartExcel()
    Set Xcl = CreateObject("Excel.Application")
    Xcl.Visible = true
    Set newBook = Xcl.Workbooks.Add
    newBook.Worksheets(1).Activate
    newBook.Worksheets(1).Range("A1:B1000").NumberFormat = "0"
    newBook.Worksheets(1).Cells(1,1).value="Value1"
    newBook.Worksheets(1).Cells(2,1).value="Value2"
    newBook.Worksheets(1).Cells(1,2).value="20"
    newBook.Worksheets(1).Cells(2,2).value="10"
    vnewBook.Worksheets(1).Cells(3,1).value="Total"
    newBook.Worksheets(1).Cells(3,2).value="=Sum(B1:B2)"
    newBook.Worksheets(1).Cells(4,1).value="Total Times 2"
    newBook.Worksheets(1).Cells(4,2).value=CInt(newBook.Worksheets(1).Cells(3,2).value * 2)
    newBook.Worksheets(1).Name="My First WorkSheet"
end Sub

In this example, I am summing the values in the first two cells in the second column and placing the results in the third row of the second column, in the forth row I am multiplying that total value by two. A demonstration of this is also available.

Following a post on the Wrox P2P Javascript list, I began working on converting some of the functionality into Javascript in response to a request from a fellow developer. The conversion to Javascript is quite simple - almost as simple as replacing the Dim's with Var's and adding a ; at the end of each line, so we have something like :

var xls = new ActiveXObject ( "Excel.Application" );
xls.visible = true;
var newBook = xls.Workbooks.Add;
newBook.Worksheets.Add;
newBook.Worksheets(1).Activate;
xls.ActiveWorkBook.ActiveSheet.PageSetup.Orientation = 2;
xls.ActiveWorkBook.ActiveSheet.PageSetup.PaperSize = 5;
newBook.Worksheets(1).Columns("A").columnwidth=50;
newBook.Worksheets(1).Columns("A").WrapText = true;
newBook.Worksheets(1).Columns("B").columnwidth=50;
newBook.Worksheets(1).Columns("B").WrapText = true;
newBook.Worksheets(1).Range("A1:B1000").NumberFormat = "0";
newBook.Worksheets(1).Range("A1:B1000").HorizontalAlignment = -4131;
newBook.Worksheets(1).Cells(1,1).Interior.ColorIndex="15";
newBook.Worksheets(1).Cells(1,1).value="First Column, First Cell";
newBook.Worksheets(1).Cells(2,1).value="First Column, Second Cell";
newBook.Worksheets(1).Cells(1,2).value="Second Column, First Cell";
newBook.Worksheets(1).Cells(2,2).value="Second Column, Second Cell";
newBook.Worksheets(1).Name="My First WorkSheet";

As you can see, there is very little difference between this and the VBScript version presented above. The main changes being that we are also setting some of the layout values in our VBA code as well - in this case setting the print layout to Landscape and the paper size to Legal. A working demo of the Javascript version is also available.

It is certainly possible to extend the functionality, recently I worked on a method of pulling data from a datasource into a form, allowing the user to amend the data - with the field resizing to cope with the data entered - and then export the data plus their changes into Excel. This was achived by simply using Client Side Javascript to move through the form for its data rather than have it preset in a function, a sample of this is available, try running the export, then amending some of the data and then rerunning the export to see the differences.

In some cases you may want to create the file on the Server Side and then provide a link to the file back to the client or simply just have the 'trigger' for the files creation come from the client side, we will look a this in the next section.

Website Designed by Adservio Consulting      Bookmark and Share Valid HTML 4.01 Strict    Valid CSS!    Level A conformance icon, W3C-WAI Web Content Accessibility Guidelines 1.0