You can add additional cells to a Worksheet with text, formulas, results from SQL queries and bitmap images¹⁾ 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

¹⁾ 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:
  
  
  
  
  

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

The following substitution variables can be used for positioning the extra cells:

{MINROWID}   : Substituted with the minimum row ID for the main query of the Worksheet
{MAXROWID}   : Substituted with the maximum row ID for the main query of the Worksheet
{LMAXROWID}  : Substituted with the last maximum row ID.                                    (added in version 2.7.7)
{LMINROWID}  : Substituted with the last minimum row ID.                                    (added in version 2.7.7)
{OAMAXROWID} : Substituted with the overall maximum row ID, up to this point.               (added in version 2.7.7)
{OAMINROWID} : Substituted with the overall minimum row ID, up to this point.               (added in version 2.7.7)

Downloadable example illustrating the use of {LMAXROWID} etc :

• rowid_example.zip
  
  Unzip the file, install the XML file using the Workbook Import utility in SQl 2 Excel Pro.

Comments (5)

Subscribe to this comment's feed

Peter Geier said:

	... So how do I now format the total? All previous cells are formatted correctly as: $16,456.77 (I used as range rows 1-99 and the output is only 29 rows). But the results cell of =SUM(C{MINROWID}:C{MAXROWID}) now shows ugly: 776550.26
	report abuse vote down vote up

June 29, 2010
Votes: +1

Mats @ Joomla-R-Us said:

	... @Peter Geier: Not sure why it's not formatted correctly. You could try adding one extra formatting entry, for just the SUM cell. If you can't figure it out, can you please upload an example Workbook definition (XML) to the support forum so I can take a look at it. -Mats
	report abuse vote down vote up

June 29, 2010
Votes: +0

This e-mail address is being protected from spambots. You need JavaScript enabled to view it said:

	... Is it possible to and an extra cell to all Rows? Like {ALLROWS} this would be handy..
	report abuse vote down vote up

October 06, 2010
Votes: +0

Cesar said:

	... Just wondering, can you be a bit more clear on the image path thing? I uploaded the file to my site, its visible from anyone, but the excel book, when opened, it instantly tells me that the "some resources were lost" and no image appears. I tried full path, website path, short web path. and so far nothing seems to work.
	report abuse vote down vote up

October 13, 2010
Votes: +0

admin said:

	... @Cesar: The path to the image is the is the full path on disk (not URL), for example: /var/www/html/images/test.bmp The webserver needs to have read access to the file/folder. Also, the format of the BMP file needs to be 24bit encoding, 8 bits per color. It won't work otherwise. Try with the sample bitmap first: http://joomla-r-us.com/images/sql2excel/logo.bmp
	report abuse vote down vote up

October 13, 2010
Votes: +0