You can write Excel formulas with SQL 2 Excel. There are 4 special substitution variables that you can use in your formulas:

{ROWID}    : Substituted with current row ID
{COLID}    : Substituted with current column ID
{MINROWID} : Substituted with the minimum row ID, e.g. first data row   (added in version 1.1.5)
{MAXROWID} : Substituted with the maximum row ID, e.g. last data row    (added in version 1.1.5)

You can use simple expressions, like below, to reference rows or columns relative to the current.

{ROWID-1} : Will be substituted with current row ID - 1
{ROWID+1} : Will be substituted with current row ID + 1.

Here's an example illustrating how to write the SQL query:

SELECT ID, '=A{ROWID}*2' AS FORMULA
FROM jos_content

You need to specify which column(s) that does have formulas in them in the Worksheet configuration. In this case it's column 2 which will have a formula. We specify that in the Worksheet configuration, like this:

If more then one column has formulas in them, specify the columns with a comma delimted list.

The {ROWID} will be substituted with the current row ID when the cell formula is inserted into the Worksheet. The resulting spreadsheet will contain formulas, like this:

Comments (0)

Subscribe to this comment's feed

Name

Email

Comment

smaller | bigger

Subscribe via email (registered users only)

I have read and agree to the Terms of Usage.

Write the displayed characters

Add Comment