constants ValueFormatEnum
Defines how the cell's value is shown. The CellValueFormat property indicates the way the cell displays its content. The Def(exCellValueFormat) property indicates the format for all cells within the column. The CellValue property indicates the cell's value,  content or formula. The ComputedField property indicates the formula to compute all cells in the column. The FormatColumn property indicates the format to be applied for cells in the columns.The ValueFormatEnum type supports can be a combination of the following values:

 NameValueDescription 
   exText0 Standard text. No HTML tags are displayed  
   exHTML1 The control uses built-in HTML tags to display the caption using HTML format. The control supports the following HTML tags: 
  • <b>  bold </b> bolds a part of the caption.
  • <u> underline </u> specifies that the portion should appear as underlined. 
  • <s> strikeout </s> specifies that the portion should appear as strikeout. 
  • <i> italic </i> specifies that the portion should appear as italic. 
  • <fgcolor=FF0000> fgcolor </fgcolor> changes the foreground color for a portion.
  • <bgcolor=FF0000> bgcolor </bgcolor> changes the background color for a portion.
  • <br> breaks a line. Set the CellSingleLine property on False, to let the cell's content to be displayed on multiple lines. 
  • <solidline> draws a solid line. If has no effect for a single line caption.
  • <dotline> draws a dotted line. If has no effect for a single line caption.
  • <upline> draws the line to the top of the text line
  • <r> aligns the rest of the text line to the right side. It has no effect if the caption contains a single line.
  • <img>number[:width]</img> inserts an icon inside the cell's caption. The number indicates the index of the icon being inserted. The last 7 bits in the high significant byte of the number expression indicates the identifier of the skin being used to paint the object. Use the Add method to add new skins to the control. If you need to remove the skin appearance from a part of the control you need to reset the last 7 bits in the high significant byte of the color being applied to the part. The width is optional and indicates the width of the icon being inserted. Using the width option you can overwrite multiple icons getting a nice effect. By default, if the width field is missing, the width is 18 pixels.
  • <img>key[:width]</img> inserts a custom size picture being loaded using the HTMLPicture property. The Key parameter indicates the key of the picture being displayed. The Width parameter indicates a custom size, if you require to stretch the picture, else the original size of the picture is used.  
  • <font face;size>text </font> displays portions of text with a different font and/or different size. For instance, the <font Tahoma;12>bit</font> draws the bit text using the Tahoma font, on size 12 pt. If the name of the font is missing, and instead size is present, the current font is used with a different size. For instance, <font ;12>bit</font> displays the bit text using the current font, but with a different size.
  • <a id;options> anchor </a>, specifies hyperlinks in your text. An anchor is a piece of text or some other object (for example an image) which marks the beginning and/or the end of a hypertext link.The <A> element is used to mark that piece of text (or inline image), and to give its hypertextual relationship to other documents. The control fires the AnchorClick event when the user clicks an anchor element. Use the FormatAnchor property to specify the visual effect for anchor elements. For instance, if the user clicks the anchor <a1>anchor</a>, the control fires the AnchorClick event, where the AnchorID parameter is 1, and the Options parameter is empty. Also, if the user clicks the anchor <a 1;yourextradata>anchor</a>, the AnchorID parameter of the AnchorClick event is 1, and the Options parameter is "yourextradata". Use the AnchorFromPoint property to retrieve the identifier of the anchor element from the cursor. Use the ShowToolTip method to display a custom tooltip.
  • & glyph characters as &amp ( & ), &lt ( < ), &gt ( > ), &qout ( " ), &#number, For instance, the &#8364 displays the EUR character, in UNICODE configuration. The & ampersand is only recognized as markup when it is followed by a known letter or a # character and a digit. For instance if you want to display <b>bold</b> in HTML caption you can use &lt;b&gt;bold&lt;/b&gt;
Newer HTML format supports subscript and superscript like follows:
  • <off offset> ... </off> defines the vertical offset to display the text/element. The offset parameter defines the offset to display the element. This tag is inheritable, so the offset is keep while the associated </off> tag is found. You can use the <off offset> HTML tag in combination with the <font face;size> to define a smaller or a larger font to be displayed. For instance: "Text with <font ;7><off 6>subscript" displays the text such as: Text with subscript The "Text with <font ;7><off -6>superscript" displays the text such as: Text with subscript

Also, newer HTML format supports decorative text like follows:

  • <gra rrggbb;mode;blend> ... </gra> defines a gradient text. The text color or <fgcolor> defines the starting gradient color, while the rr/gg/bb represents the red/green/blue values of the ending color, 808080 if missing as gray. The mode is a value between 0 and 4, 1 if missing, and blend could be 0 or 1, 0 if missing. The <font> HTML tag can be used to define the height of the font. Any of the rrggbb, mode or blend field may not be specified. The <gra> with no fields, shows a vertical gradient color from the current text color to gray (808080). For instance the "<font ;18><gra FFFFFF;1;1>gradient-center</gra></font>" generates the following picture:

  • <out rrggbb;width> ... </out> shows the text with outlined characters, where rr/gg/bb represents the red/green/blue values of the outline color, 808080 if missing as gray, width indicates the size of the outline, 1 if missing. The text color or <fgcolor> defines the color to show the inside text. The <font> HTML tag can be used to define the height of the font. For instance the "<font ;31><out 000000><fgcolor=FFFFFF>outlined</fgcolor></out></font>" generates the following picture:

  • <sha rrggbb;width;offset> ... </sha> define a text with a shadow, where rr/gg/bb represents the red/green/blue values of the shadow color, 808080 if missing as gray, width indicates the size of shadow, 4 if missing, and offset indicates the offset from the origin to display the text's shadow, 2 if missing. The text color or <fgcolor> defines the color to show the inside text. The <font> HTML tag can be used to define the height of the font.  For instance the "<font ;31><sha>shadow</sha></font>" generates the following picture:

    or  "<font ;31><sha 404040;5;0><fgcolor=FFFFFF>outline anti-aliasing</fgcolor></sha></font>" gets:

 

 
   exComputedField2 Indicates a computed field. The CellValue property indicates the formula to compute the field. A computed field can display its content using the values from any other cell in the same item/row. For instance %1 + %2 indicates that the cell displays the addition from the second and third cells in the same item ( cells are 0 based ). For instance, if the cells are of numeric format the result is the sum of two values, while if any of the cell is of string type it performs a concatenation of the specified cells. The ComputedField property indicates the formula to compute all cells in the column. The exComputedField can be combined with exText or exHTML. For instance, the exComputedField + exHTML indicates that the computed field may display HTML tags. 

The syntax for the CellValue property should be: formula where %n indicates the cell from the n-index. The operation being supported are listed bellow.

For instance %1 + %2 indicates the sum of all cells in the second and third column from the current item.

 
   exTotalField4 Indicates a total/subtotal field. The CellValue property indicates the formula for total field that includes an aggregate function such as: sum, min, max, count, avg. The exTotalField can be combined with exText or exHTML. For instance, the exTotalField + exHTML indicates that the total field may display HTML tags. 

The syntax for the CellValue property should be: aggregate(list,direction,formula) where:

aggregate must be one of the following: 

  • sum - calculates the sum of values.
  • min - retrieves the minimum value.
  • max - retrieves the maximum value.
  • count - counts the number of items.
  • avg - calculates the average of values.

list must be one of the following:

  • a long expression that specifies the index of the item being referred.
  • a predefined string expression as follows:
    • all - indicates all items, so the formula is being applied to all items. The direction has no effect.
    • current - the current item.
    • parent - the parent item.
    • root - the root item.

direction must be one of the following:

  • dir - collects the direct descendents. 
  • rec - collects the leaf descendents ( leaf items ). A leaf item is an item with no child items.
  • all - collects all descendents.

Currently, the following items are excluded by aggregate functions:

  • not-sortable items. The SortableItem property specifies whether the item can be sorted ( a sortable item can change its position after sorting, while a not-sortable item keeps its position after sorting. 
  • not-selectable items. The SelectableItem property specifies whether the user can selects/focus the specified item.
  • divider items. The ItemDivider property specifies whether the item displays a single cell, instead displaying whole cells.

In conclusion, aggregate functions counts ONLY items that are:

Shortly, by setting to a different value to any of these properties, makes the item to be ignored by the aggregate functions.

For instance 

  • count(current,dir,1) counts the number of child items ( not implies recursively child items ).
  • count(current,all,1) counts the number of all child items ( implies recursively child items ).
  • count(current,rec,1) counts the number of leaf items ( implies recursively leaf items ).
  • count(current,rec,1) counts the number of leaf items ( a leaf item is an item with no child items ).
  • sum(parent,dir,%1=0?0:1) counts the not-zero values in the second column (%1)
  • sum(parent,dir,%1 + %2) indicates the sum of all cells in the second (%1) and third (%2) column that are directly descendent from the parent item. 
  • sum(all,rec,%1 + %2) sums all leaf cells in the second (%1) and third (%2) columns.
 

The formula on the CellValue property ( if the CellValueFormat property indicates the exComputedField or exTotalField ) may include the formatting operators as follows:

The expression supports cell's identifiers as follows:

This property/method supports predefined constants and operators/functions as described here.

Usage examples:

  1. "1", the cell displays 1
  2. "%0 + %1", the cell displays the sum between cells in the first and second columns.
  3. "%0 + %1 - %2", the cell displays the sum between cells in the first and second columns minus the third column.
  4. "(%0 + %1)*0.19", the cell displays the sum between cells in the first and second columns multiplied with 0.19.
  5. "(%0 + %1 + %2)/3", the cell displays the arithmetic average for the first three columns.
  6. "%0 + %1 < %2 + %3", displays 1 if the sum between cells in the first  two columns is less than the sum of third and forth columns.
  7. "proper(%0)'" formats the cells by capitalizing first letter in each word
  8. "currency(%1)'" displays the second column as currency using the format in the control panel for money
  9. "len(%0) ? currency(dbl(%0)) : ''" displays the currency only for not empty/blank cells.
  10. "int(date(%1)-date(%2)) + 'D ' + round(24*(date(%1)-date(%2) - floor(date(%1)-date(%2)))) + 'H''" displays interval between two dates in days and hours, as xD yH
  11. "2:=((1:=int(0:= date(%1)-date(%0))) = 0 ? '' : str(=:1) + ' day(s)') + ( 3:=round(24*(=:0-floor(=:0))) ? (len(=:2) ? ' and ' : '') + =:3 + ' hour(s)' : '' )" displays the interval between two dates, as x day(s) [and y hour(s)], where the x indicates the number of days, and y the number of hours. The hour part is missing, if 0 hours is displayed, or nothing is displayed if dates are identical.

 

 

 


Send comments on this topic.
1999-2017 Exontrol.COM, Software. All rights reserved.