SQL 2 Excel Pro has a formatting feature which allows you to control formatting of eny cell/range in your sheet.

You can control:

• Font Type
• Font Size
• Font Color
• Font Weight
• Background Color
• Border
• Border Color
• Number/Date Format
• Alignment
• Vertical Alignment
• Word Wrap
• Conditional formatting (see below)
  
  

The Format tab on the Worksheet page allows you to you to add as many cells/ranges as you need.



In the example above, we have defined two ranges. The first range defines 5 rows in column 1 where we set the number format to percent, and the font color to orange.  The second range is defined as a purple thin border aorund all cells.

Note how the border is added to the cells defined in range 1 as well. The orange numbers also have the border. The formatting is added sequentially. You can define overlapping ranges which makes it easier to define complex fromatting of irregular areas/cells.

Specifying the range

You can specify the cell range as start row & col and stop row & col. Use integer numbers only - do not use letters.

You can use the {MINROWID} {MAXROWID} syntax (same as in formulas) - when defining the start/stop rows for smart allocation of the formatting.

Number/Date Format

The following number/date format codes can be used to specify the cell number/date format.

Conditional Formatting

You can define conditions for each format. The format will only be applied to the cells that matches the condition you specify. The following conditions can be specified:

• Even Row Numbers
• Odd Row Numbers
• Even Column Numbers
• Odd Column Numbers
• Cell value is string
• Cell value is not string
• Cell value is numeric
• Cell value is not numeric
• Cell value is integer
• Cell value is not integer
• Cell value is empty
• Cell value is not empty
• Cell value = {some value}
• Cell value >= {some value}
• Cell value > {some value}
• Cell value <= {some value}
• Cell value < {some value}
• Cell value <> {some value}
• Cell value = {Result of SQL query}
• Cell value >= {Result of SQL query}
• Cell value > {Result of SQL query}
• Cell value <= {Result of SQL query}
• Cell value < {Result of SQL query}
• Cell value <> {Result of SQL query}

Here's a simple example showing how to highlight the cell with the highest and the lowest value in a column. To do that we use two formats, one to highlight the maximum value with red text font and another format to highlight the minimum with blue. For both formats we use the Cell value = {Result of SQL query} condition. Click on the thumbnail image below to see how the formats are defined:



The resulting spreadsheet is shown below. Note the third column, where the cells with minumum and maximum hits are highlighted in blue and red color.

In this simple example we only used text coloring for the conditional formatting. But any type of formatting can be controlled by the conditional rule, background color, border etc etc.

Known Issues

• Using a lot of formatting requires more memory on the server and creates larger file sizes. You may run out of memory on the server if using formatting on very large spreadsheets. Use it with caution / check the PHP max process size configuration if you run into problems.
  
  
• Only cells which have content can be formatted.
  
  Tip! You can an add extra cell with just a space in it if you want to apply formatting.
  
  

Comments (1)

Subscribe to this comment's feed

Roland said:

	... having trouble with UK currency am getting an A in front of the currency symbol?
	report abuse vote down vote up

February 04, 2010
Votes: +0

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