Functions

ExcelWriter supports almost all major math, string, boolean, time, statistical, database, lookup and reference formulas or functions that are part of Excel.

With ExcelWriter, formulas cannot be executed server-side to retrieve values for use in the script. All formulas are executed at the time that the file is downloaded and opened in Excel. (Please note: If the downloaded files are opened client side with the free Microsoft Excel Viewer, all formulas will be rendered with "0" as the resultant value.)

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.

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 Microsoft Excel spreadsheet.

Any formula that is not mentioned in this list can be inserted into an existing spreadsheet or template and used in ExcelWriter. This list is comprised of formulas that can be added into your ExcelWriter script to be included at run-time.

The following example demonstrates using a formula.

ws.Cells("b6").Formula = "=sum(b2:b3)" 

ExcelWriter 2 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)"

Note that ExcelWriter does not support the following formulations:

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

ExcelWriter Supported Functions