SoftArtisans ExcelWriter Guide

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.

With ExcelWriter, formulas cannot be executed on the server to retrieve values for use in the script. All formulas are executed when the file is downloaded to the client. Note: If a downloaded spreadsheet is opened with Microsoft Excel Viewer, "0" will be displayed in all cells that contained formulas.

Top

Example 1: Adding Formulas

Building on the first example, several formulas will be added to the spreadsheet.

tutorial/formula.asp

[View Source]

A spreadsheet is created, with the same values as in the first exercise.

	set xlw = Server.Create("SoftArtisans.ExcelWriter")
	...
	ws.Cells("A2").Value = "Fred Smith"
	ws.Cells("B2").Value = 10
	ws.Cells("C2").Value = 37.5

Formulas are added using the Cell'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 SoftArtisans ExcelWriter. For the complete list of the currently supported functions, see the Programmer's Reference Section. If there are particular functions you require that are not currently supported, please let us know.

	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"

Users who are familiar with Microsoft Excel formulas will recognize this format:

	=Function-name(Cell-references)
	=Cell-reference simple-math-function(+,-,*,/) Cell-reference

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

Top

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, as in the following examples.

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

tutorial/formula2.asp

[View Source]

Note that ExcelWriter does not support the following formulations:

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

Top

Section Summary

To specify a formula, use the Cell object's Formula property.
A formula is a string, starting with an equal sign (=). It may contain functions and operators.
A formula can be applied to data from a single cell, or from a range of cells.
A formula can use values from any worksheet in the workbook.
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.

Top