Adding a Formula

How to Add a Formula

ExcelWriter supports almost all major math, string, boolean, time, statistical, database, lookup and reference formulas or functions that are part of Excel. Formulas can be inserted into any cell in a worksheet by using the Cell.Formula property. They can be inserted either by hard coding the cell address or by dynamic coding using a variable, such as NumRows (the return value of the CopyFromRecordset method which indicates where the last row of the pasted recordset will be positioned in the worksheet) to provide a sum at the bottom of a column.

In ExcelWriter, formulas can reference data across worksheets, as follows:

ws3.Cells(1,1).Formula = "=sum(sheet1!A1,Sheet2!A2)"
ws3.Cells(2,1).Formula = "=sum(sheet1!A1:A3)"

When applying a formula to a cell, always include the entire string in double quotation marks ("") and begin the string with an equal sign (=), as you would when creating a spreadsheet in Microsoft Excel.

ExcelWriter does not execute formulas on the server. Therefore, you cannot retrieve a formula's result in script. All formulas are executed when the file is opened in Microsoft Excel. Note: If a downloaded spreadsheet is opened with Microsoft Excel Viewer, "0" will be displayed in all cells that contained formulas.

The only functions available in ExcelWriterSE are sum and average. ExcelWriterEE includes all functions listed in Formula Functions.

Example 1: Adding Formulas

Formulas are added using the Cell object's Formula property. The Formula property is used to explicitly say that this is a formula to be interpreted rather than literal text. The formula is specified as a simple text string. Most of the commonly used functions in Microsoft Excel are supported by ExcelWriter. For the complete list of the currently supported functions, see see the Formula Functions.

To specify a range of cells, use the format starting-cell:ending-cell. Many more complex combinations of functions can be specified in a formula.

<%@ Language=VBScript %>
<!--METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<% 
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set ws = xlw.Worksheets(1)

'--- Create header row
ws.Cells("A1").Value = "Name"
ws.Cells("B1").Value = "Count"
ws.Cells("C1").Value = "Dollar"

'--- Add values
ws.Cells("A2").Value = "Fred Smith"
ws.Cells("B2").Value = 10
ws.Cells("C2").Value = 37.5

ws.Cells("A3").Value = "John Doe"
ws.Cells("B3").Value = 20
ws.Cells("C3").Value = 62.85

'--- Add formulas
ws.Cells("B5").Value = "Total:"
ws.Cells("B6").Formula = "=sum(b2:b3)"

ws.Cells("C5").Value = "Average:"
ws.Cells("C6").Formula = "=average(c2:c3)"

ws.Cells("E5").Value = "Total x Average:"
ws.Cells("E6").Formula = "=b6*c6"

'--- Open the workbook in the browser
xlw.Save "formula.xls", saOpenInPlace

Set xlw = nothing
Response.end
%>

Example 2: Accessing Values in a Different Worksheet

ExcelWriter allows you to use values from a different sheet when assigning a formula to a cell.

ExcelWriter does not support the following formulations:

• Sheet1:Sheet2!A1:C3
• Sheet1!A1:C3;Sheet4!A1:C3

<%@ Language=VBScript %>
<!--METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<% 
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set ws = xlw.Worksheets(1)

'--- Create header row
ws.Cells("A1").Value = "Name"
ws.Cells("B1").Value = "Count"
ws.Cells("C1").Value = "Dollar"

'--- Add values
ws.Cells("A2").Value = "Fred Smith"
ws.Cells("B2").Value = 10
ws.Cells("C2").Value = 37.5

ws.Cells("A3").Value = "John Doe"
ws.Cells("B3").Value = 20
ws.Cells("C3").Value = 62.85

'--- Add formulas
ws.Cells("B5").Value = "Total:"
ws.Cells("B6").Formula = "=sum(b2:b3)"

ws.Cells("C5").Value = "Average:"
ws.Cells("C6").Formula = "=average(c2:c3)"

ws.Cells("E5").Value = "Total x Average:"
ws.Cells("E6").Formula = "=b6*c6"

Set ws = nothing

Set ws2 = xlw.Worksheets(2)
ws2.Cells("B5").Value = "Total:"
ws2.Cells("B6").Formula = "=sum(Sheet1!b2:b3)"

ws2.Cells("C5").Value = "Average:"
ws2.Cells("C6").Formula = "=average(Sheet1!c2:c3)"

ws2.Cells("E5").Value = "Total x Average:"
ws2.Cells("E6").Formula = "=Sheet1!b6*Sheet1!c6"

'--- Open the workbook in the browser
xlw.Save "formula.xls", saOpenInPlace

Set xlw = nothing
Response.end
%>

Section Summary

1. To specify a formula, use the Cell object's Formula property.
2. A formula is a string starting with an equal sign (=). It may contain functions and operators.
3. A formula can be applied to data from a single cell, or from a range of cells.
4. A formula can use values from any worksheet in the workbook.
5. Formulas cannot be executed on the server to retrieve values for use in the script. All formulas are executed when the file is downloaded and opened in Excel.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.