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
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
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"
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()">
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
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";
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.