|
You can add additional cells to a Worksheet with text, formulas, results from SQL queries and bitmap images1) in SQL 2 Excel Pro. You can position the cells wherever you want, as long as you don't overwrite the table data the SQL query generates.
Adding Text and Formulas
Here's a simple example where we add a cell which computes the sum of all the Hits for articles in the list.
Setup the Worksheet:
Results in this spreadheet:
This feature allows very fine control of the data and formulas in the generated spreadsheet.
Many times you don't know how many rows exactly the SQL query returned. If you want to sum a whole column using an Excel Formula you can use the {MINROWID} and {MAXROWID} formula variables. Those will be replaced dynamically with the min and max row id's for the current query/sheet.
Example:
=SUM(C{MINROWID}:C{MAXROWID})
Adding data from SQL queries
You can add data from additional SQL queries as "Extra Cells". The start position (row/col) for the data is the upper left corner. If your query returns more than one row then it will be populated downward. If your query returns multiple columns of data then it will be filled in the cells to the right.
See Create Worksheets based on multiple SQL queries for more info.
Adding Bitmap Images
1) Bitmap images (*.bmp) has to be using 24-bit color encoding (8 bits per color). Sample bitmap in the supported format.
- Upload your bitmap file to your server manually (ftp, cPanel etc etc) and put it in some folder where the webserver can read it.
- In the spreadsheet, define the cell (row,col) where you wish to insert the bitmap and the path to the file:
- The resulting spreadsheet:
You can add as many images you want.
Smart Positioning
The row and column can be specified for each Extra Celll entry. You can specify it with an absolute number, or you can position it relative to the top or bottom of the data rows.
Example 1:
Positioning your extra cell 3 rows below the last datarow in the first column:
Row = {MAXROWID+3}
Col = 1
Example 2:
Positioning your extra cell on the same row as the first data row in column 7
Row = {MINROWID}
Col = 7
 |
Subscribe to this comment's feed
