TOLAPGrid Conditional Formating
Posted by - NA - on 05 March 2009 01:16 PM
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Conditional formatting provides a user-friendly interface for visual cell selection depending on its contents. The way of selection and the method of analysis can vary greatly. Access to the possibilities of conditional formatting is fulfilled either via a pop-up menu (appears on the screen after the selection area has been changed), or via a context menu. The results of conditional formatting are available for printing, exporting and saving. Ways of formattingThere are two ways for a user to assign conditional formatting.
Using the context menu. After selecting some data area, you can call the context menu by right-clicking and choose there the "Conditional formatting" item and then go on like you do in case 1.
Conditional formatting menu
How to hide menu itemsSome menu items can be hidden by the appropriate settings in the Grid.OLAPGridSettings.MindFormat property. These are:
Selection modeFor your convenience, you can assign the conditional formatting preview when changing selection. The area can be assigned by either lift-clicking (but the standard selection with the left mouse key will be impossible in that case) or right-clicking (with the opportunity to use standard selection). The command selected for conditional formatting in the menu will be highlighted with gradient and accompanied by a mouse icon on the right. The figure “The view of conditional formatting menu” shows a selection of an item from the menu: "Color ranking" -> "Green-yellow-red". You can choose this command by right-clicking it. You can change the selection mode programmatically by assigning the property value: // set the conditional selection mode by the left-clicking Conditional formatting settingUsing conditional formatting slows down the work of the component since it has to make additional analysis of data, that's why if you don’t need it, you can simply disable it.
A finer setting of conditional formatting is possible through overriding the OnFillConditionalMenu and OnDrawCellFormat events. Export of conditional formatting resultsThe results of conditional formatting can be exported to a majority of the supported formats; however, for some of them there are certain restrictions.
Options of displaying the results of conditional formatting in different formats + supported The OnFillConditionalMenu eventThe event is executed upon filling the menu of conditional formatting and allows you to control its set of commands. Properties of the OnFillConditionalMenu event argument:
Upon access to the OnFillConditionalMenu event handler the lists are initialized by commands and menu items used by default. By changing these lists a programmer can specify what menu will be built. You can either create custom commands or use commands from the e.Commands list. Note 1. Note 2. A programmer can create custom commands similar to the already existing ones. For that use the constructor: public TConditionalCommand( ToolStripMenuItem AMenu, // a menu item where a command is to be placed TFormatKind AFormatKind, // mode which is to be used during cell selection TActivityKind AActivityKind, // mode of cell analysis TGradientColor AGradient, // gradient for formatting double APart // a numeric parameter ) A value of the APart parameter is used only when AActivityKind == TFormatKind. MoreThan and AActivityKind == TFormatKind.BelowThan. In other cases it can be any. Example 1. C#: // create a menu item for commands ToolStripMenuItem MI = new ToolStripMenuItem("As standard"); // add a menu item to the list e. Menus.Add(MI); // a command based on a standard one // create a command to fill the cell background // the green color will correspond to the minimum value // the red color - to the maximum TConditionalCommand cc = new TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.Gradient, new TGradientColor("", Color.Green, Color.Red), 0); // set the command's name cc.Text = "Rainbow gradient"; // add the command to the list e.Commands.Add(cc); VB: ' create a menu item for commands Dim MI As New ToolStripMenuItem("As standard") ' add a menu item to the list e.Menus.Add(MI) ' a command based on a standard one ' create a command to fill the cell background ' the green color will correspond to the minimum value ' the red color - to the maximum Dim cc As New TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.Gradient, New TGradientColor("",New Color() { Color.Green, Color.Red }), 0) cc.Text = "Rainbow gradient" e.Commands.Add(cc) To create arbitrary user commands, use the following constructor: public TConditionalCommand( ToolStripMenuItem AMenu, // a menu item where a command is to be placed TFormatKind AFormatKind, // mode which is to be used during cell selection TEventConvertTo AEventConvertTo // the OnEventConvertTo event handler ) The user can assign the parameters of formatting cells through the OnEventConvertTo event. For its description see OnEventConvertTo. Example 2. First of all, create a list of commands that will not make the top two menu items. Delete them from the e.Commands list. Create the "Example 1 - from standard" menu item and add it to the e.Menus list. All the commands, created on the basis of those already existing, will be connected to it. Create and add to the e.Commands list the commands similar to the standard ones. Create the "Example 2 - gradients" menu item and add it to the e.Menus list. Create the commands for this menu item that will paint with gradient filling according to the linear and logarithmic dependence. Create the "Example 3 - chart bars" menu item and add it to the e.Menus list. Create commands for this item that will paint a rectangle with the width defined by the linear and logarithmic dependence. Create the "Example 4 - more than value" menu item and add it to the e.Menus list. Create commands for this item that will paint a rectangle with the width of 30 % of the cell’s width, if the condition set in the command is met, and of 10 %, if not.
C# private void Grid_OnFillConditionalMenu(object sender, TEventFillConditionalMenuArgs e) { // the list of commands to be deleted List<TConditionalCommand> list = new List<TConditionalCommand>(); foreach (TConditionalCommand command in e.Commands) { if (e.Menu.IndexOf(command.ParentMenu) > 1) list.Add(command); } foreach (TConditionalCommand command in list) { e.Commands.Remove(command); } ToolStripMenuItem MI = new ToolStripMenuItem("Example 1 - from standard"); e. Menus.Add(MI); // // Adding an item similar to standard // // adding the gradient assigning command TConditionalCommand cc = new TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.Gradient, new TGradientColor("", Color.Green, Color.Yellow, Color.Yellow, Color.Red), 0); cc.Text = "Rainbow gradient"; e.Commands.Add(cc); // the command for painting the cells, whose values are close to the average, yellow, // paint the rest of the cells red cc = new TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.Gradient, new TGradientColor("", Color.Red, Color.Yellow, Color.Red), 0); cc.Text = "Red-yellow-red"; e.Commands.Add(cc); // the command for painting the cells, whose values do not exceed 25% of max cc = new TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.BelowThan, new TGradientColor("", Color.Red, Color.Yellow), 0.25); cc.Text = "Less than 25 % max"; e.Commands.Add(cc); // the command for painting the cells, whose values do not exceed 50% of max cc = new TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.BelowThan, new TGradientColor("", Color.Red, Color.Yellow), 0.50); cc.Text = "Less than 50 % max"; e.Commands.Add(cc); // the command for painting the cells, whose values are less than average cc = new TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.BelowAverage, new TGradientColor("", Color.Blue, Color.LightCyan), 0); cc.Text = "Less average"; e.Commands.Add(cc); // add user menu commands MI = new ToolStripMenuItem("Example 2 - gradient's"); e. Menus.Add(MI); // the command for filling the cell background with gradient according to the ExampleConvertTo event handler cc = new TConditionalCommand(MI, TFormatKind.BackColor, new TEventConvertTo(ExampleConvertTo)); cc.Text = "Rainbow gradient"; e.Commands.Add(cc); // the command for filling the cell background with gradient according to the ExampleLog event handler cc = new TConditionalCommand(MI, TFormatKind.BackColor, new TEventConvertTo(ExampleLog)); cc.Text = "Logarithm gradient"; e.Commands.Add(cc); // add user menu commands MI = new ToolStripMenuItem("Example 3 - chart bar's"); e. Menus.Add(MI); // the command for painting a rectangle according to the ExampleConvertTo event handler cc = new TConditionalCommand(MI, TFormatKind.ChartBar, new TEventConvertTo(ExampleConvertTo)); cc.Text = "Linear chart"; e.Commands.Add(cc); // the command for painting a rectangle according to the ExampleLog event handler cc = new TConditionalCommand(MI, TFormatKind.ChartBar, new TEventConvertTo(ExampleLog)); cc.Text = "Logarithm chart"; e.Commands.Add(cc); // add user menu commands MI = new ToolStripMenuItem("Example 4 - more than value"); e. Menus.Add(MI); // adding the commands for threshold selection of cells by value levels // of 1000, 3000, 5000 cc = new TConditionalCommand(MI, TFormatKind.ChartBar, new TEventConvertTo(ExampleMore1000)); cc.Text = "More 1000"; e.Commands.Add(cc); cc = new TConditionalCommand(MI, TFormatKind.ChartBar, new TEventConvertTo(ExampleMore3000)); cc.Text = "More 3000"; e.Commands.Add(cc); cc = new TConditionalCommand(MI, TFormatKind.ChartBar, new TEventConvertTo(ExampleMore5000)); cc.Text = "More 5000"; e.Commands.Add(cc); // assigning the command to be used when choosing the mode of conditional selection e.SelectCommand = cc; } // the event handler for obtaining a color from the gradient set private void ExampleConvertTo(object sender, TEventConvertToArgs e) { e.Result = TGradientColor.ColorBetween(new Color[] { Color.Green, Color.Yellow, Color.Red }, e.Part); } // the event handler for obtaining a color from the gradient set by the logarithmic dependence. private void ExampleLog(object sender, TEventConvertToArgs e) { e.Part = Math.Log((49 * e.Part + 1))/Math.Log(50); e.Result = TGradientColor.ColorBetween(new Color[] { Color.Green, Color.Yellow, Color.Red }, e.Part); } // threshold restriction by the cell values exceeding 1000 private void ExampleMore1000(object sender, TEventConvertToArgs e) { if (e.Value > 1000) e.Part = 0.3; else e.Part = 0.1; e.Result = Color.Red; } // threshold restriction by the cell values exceeding 3000 private void ExampleMore3000(object sender, TEventConvertToArgs e) { if (e.Value > 3000) e.Part = 0.3; else e.Part = 0.1; e.Result = Color.Red; } // threshold restriction by the cell values exceeding 5000 private void ExampleMore5000(object sender, TEventConvertToArgs e) { if (e.Value > 5000) e.Part = 0.3; else e.Part = 0.1; e.Result = Color.Red; } VB: Private Sub Grid_OnFillConditionalMenu(ByVal sender As Object, ByVal e As TEventFillConditionalMenuArgs) Handles Grid1.OnFillConditionalMenu ' the list of commands to be deleted Dim list As New List(Of TConditionalCommand) Dim command As TConditionalCommand For Each command In e.Commands If (e.Menus.IndexOf(command.ParentMenu) > 1) Then list.Add(command) End If Next For Each command In list e.Commands.Remove(command) Next Dim MI As New ToolStripMenuItem("Example 1 - from standard") e.Menus.Add(MI) ' Adding an item similar to standard ' adding the gradient assigning command Dim cc As New TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.Gradient, New TGradientColor("", New Color() {Color.Green, Color.Yellow, Color.Yellow, Color.Red}), 0) cc.Text = "Rainbow gradient" e.Commands.Add(cc) ' the command for painting the cells, whose values are close to the average, yellow, ' paint the rest of the cells red cc = New TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.Gradient, New TGradientColor("", New Color() {Color.Red, Color.Yellow, Color.Red}), 0) cc.Text = "Red-yellow-red" e.Commands.Add(cc) ' the command for painting the cells, whose values do not exceed 25% of max cc = New TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.BelowThan, New TGradientColor("", New Color() {Color.Red, Color.Yellow}), 0.25) cc.Text = "Less than 25 % max" e.Commands.Add(cc) ' the command for painting the cells, whose values do not exceed 50% of max cc = New TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.BelowThan, New TGradientColor("", New Color() {Color.Red, Color.Yellow}), 0.5) cc.Text = "Less than 50 % max" e.Commands.Add(cc) ' the command for painting the cells, whose values are less than average cc = New TConditionalCommand(MI, TFormatKind.BackColor, TActivityKind.BelowAverage, New TGradientColor("", New Color() {Color.Blue, Color.LightCyan}), 0) cc.Text = "Less average" e.Commands.Add(cc) ' add user menu commands MI = New ToolStripMenuItem("Example 2 - gradient's") e.Menus.Add(MI) ' the command for filling the cell background with gradient according to the ExampleConvertTo event handler cc = New TConditionalCommand(MI, TFormatKind.BackColor, New TEventConvertTo(AddressOf Me.ExampleConvertTo)) cc.Text = "Rainbow gradient" e.Commands.Add(cc) ' the command for filling the cell background with gradient according to the ExampleLog event handler cc = New TConditionalCommand(MI, TFormatKind.BackColor, New TEventConvertTo(AddressOf Me.ExampleLog)) cc.Text = "Logarithm gradient" e.Commands.Add(cc) ' add user menu commands MI = New ToolStripMenuItem("Example 3 - chart bar's") e.Menus.Add(MI) ' the command for painting a rectangle according to the ExampleConvertTo event handler cc = New TConditionalCommand(MI, TFormatKind.ChartBar, New TEventConvertTo(AddressOf Me.ExampleConvertTo)) cc.Text = "Linear chart" e.Commands.Add(cc) ' the command for painting a rectangle according to the ExampleLog event handler cc = New TConditionalCommand(MI, TFormatKind.ChartBar, New TEventConvertTo(AddressOf Me.ExampleLog)) cc.Text = "Logarithm chart" e.Commands.Add(cc) ' add user menu commands MI = New ToolStripMenuItem("Example 4 - more than value") e.Menus.Add(MI) cc = New TConditionalCommand(MI, TFormatKind.ChartBar, New TEventConvertTo(AddressOf Me.ExampleMore1000)) cc.Text = "More 1000" e.Commands.Add(cc) ' adding the commands for threshold selection of cells by value levels ' of 1000, 3000, 5000 cc = New TConditionalCommand(MI, TFormatKind.ChartBar, New TEventConvertTo(AddressOf Me.ExampleMore3000)) cc.Text = "More 3000" e.Commands.Add(cc) cc = New TConditionalCommand(MI, TFormatKind.ChartBar, New TEventConvertTo(AddressOf Me.ExampleMore5000)) cc.Text = "More 5000" e.Commands.Add(cc) ' assigning the command to be used when choosing the mode of conditional selection e.SelectCommand = cc End Sub ‘the event handler for obtaining a color from the gradient set Private Sub ExampleConvertTo(ByVal sender As Object, ByVal e As TEventConvertToArgs) e.Result = TGradientColor.ColorBetween(New Color() {Color.Green, Color.Yellow, Color.Red}, e.Part) End Sub ‘the event handler for obtaining a color from the gradient set by the logarithmic dependence. Private Sub ExampleLog(ByVal sender As Object, ByVal e As TEventConvertToArgs) e.Part = (Math.Log(((49 * e.Part) + 1)) / Math.Log(50)) e.Result = TGradientColor.ColorBetween(New Color() {Color.Green, Color.Yellow, Color.Red}, e.Part) End Sub ‘ threshold restriction by the cell values exceeding 1000 Private Sub ExampleMore1000(ByVal sender As Object, ByVal e As TEventConvertToArgs) If (e.Value > 1000) Then e.Part = 0.3 Else e.Part = 0.1 End If e.Result = Color.Red End Sub ‘threshold restriction by the cell values exceeding 3000 Private Sub ExampleMore3000(ByVal sender As Object, ByVal e As TEventConvertToArgs) If (e.Value > 3000) Then e.Part = 0.3 Else e.Part = 0.1 End If e.Result = Color.Red End Sub ‘threshold restriction by the cell values exceeding 5000 Private Sub ExampleMore5000(ByVal sender As Object, ByVal e As TEventConvertToArgs) If (e.Value > 5000) Then e.Part = 0.3 Else e.Part = 0.1 End If e.Result = Color.Red End Sub The OnEventConvertTo eventArises when obtaining the parameters of cell formatting. The data for analysis are passed in this event argument properties. The handler allows setting the formatting function and the resulting parameter for painting.
The value of the Part property allows setting the method of data analysis. It is taken into account when building a rectangle. In the cell background filling mode (FormatKind == TFormatKind.BackColor) this value doesn't influence the result. The value of the Result property allows setting the formatting color. Example 1.Assigning the resulting color. C#: private void ExampleConvertTo(object sender, TEventConvertToArgs e) { e.Result = TGradientColor.ColorBetween(new Color[] { Color.Green, Color.Yellow, Color.Red }, e.Part); } VB: Private Sub ExampleConvertTo(ByVal sender As Object, ByVal e As TEventConvertToArgs) e.Result = TGradientColor.ColorBetween(New Color() { Color.Green, Color.Yellow, Color.Red }, e.Part) End Sub Example 2.Assigning the logarithmic function. Using the logarithmic dependence allows "expanding" diapason of minor cell values, that is by minor changes of the Part entry parameter will result in significant changes in its output value (see the chart).
The next picture shows that in case using the linear function, the set of values close to minimum (to the left) is painted with virtually indistinguishable tints of green. The application of the logarithmic function allows "expanding" this diapason.
Comparative results of the standard and logarithmic OnEventConvertTo event handler with different variants of FormatKind filling. C#: // the linear OnEventConvertTo event handler private void ExampleConvertTo(object sender, TEventConvertToArgs e) { e.Result = TGradientColor.ColorBetween(new Color[] { Color.Green, Color.Yellow, Color.Red }, e.Part); } // the logarithmic OnEventConvertTo event handler private void ExampleLog(object sender, TEventConvertToArgs e) { e.Part = Math.Log((49 * e.Part + 1))/Math.Log(50); e.Result = TGradientColor.ColorBetween(new Color[] { Color.Green, Color.Red }, e.Part); } VB: ' the linear OnEventConvertTo event handler Private Sub ExampleConvertTo(ByVal sender As Object, ByVal e As TEventConvertToArgs) e.Result = TGradientColor.ColorBetween(New Color() { Color.Green, Color.Yellow, Color.Red }, e.Part) End Sub ' the logarithmic OnEventConvertTo event handler Private Sub ExampleLog(ByVal sender As Object, ByVal e As TEventConvertToArgs) e.Part = (Math.Log(((49 * e.Part) + 1)) / Math.Log(50)) e.Result = TGradientColor.ColorBetween(New Color() { Color.Green, Color.Yellow, Color.Red }, e.Part) End Sub The OnDrawCellFormat eventExecuted right away upon cell formation. Values necessary for custom formatting are passed in the event argument.
Example 1.The actions of the example handler duplicate the standard formatting command. Note that depending on the type of formatting the default action should be called either prior to or after the painting the background. The sequence of operators described in this handler can be substituted with a single call of the e.DoDefaultDraw() command. C#: private void Grid_OnDrawCellFormat(object sender, TEventDrawCellFormatArgs e) { // if the painting background parameters are changed, this should be done // PRIOR to painting the background if (e.FormatKind == TFormatKind.BackColor) e.DoDefaultActivity(); e.DrawBackground(); // if the drawing is fulfilled above the background, this should be done // AFTER painting the background if (e.FormatKind == TFormatKind.ChartBar) e.DoDefaultActivity(); e.DrawText(); } VB: Private Sub Grid_OnDrawCellFormat(ByVal sender As Object, ByVal e As TEventDrawCellFormatArgs) ' if the painting background parameters are changed, this should be done ' PRIOR to painting the background If (e.FormatKind Is TFormatKind.BackColor) Then e.DoDefaultActivity() End If e.DrawBackground() ' if the drawing is fulfilled above the background, this should be done ' AFTER painting the background If (e.FormatKind Is TFormatKind.ChartBar) Then e.DoDefaultActivity() End If e.DrawText() End Sub Example 2.The example handler shows how to substitute the standard drawing of conditional formatting for custom. The maximum value of a number in a cell corresponds to a semicircle. The less values are displayed as parts of it.
C#: private void Grid_OnDrawCellFormat(object sender, TEventDrawCellFormatArgs e) { if (e.Command.Text != "Rainbow gradient") { e.DoDefaultDraw(); } else { e.DrawBackground(); // data preparation float angle = Convert.ToInt32(e.Part * 180); SolidBrush brush = new SolidBrush(Color.FromArgb(200, Color.Red)); Rectangle rect = e.RectangleCell; rect.Width = Convert.ToInt32(rect.Height * 1.7); rect.Height = rect.Width; rect.Inflate(-4, -4); e.Graphics.SmoothingMode = SmoothingMode.HighQuality; // painting a segment // a semicircle corresponds to the maximum value e.Graphics.FillPie(brush, rect, - 180.0f, angle); e.Graphics.DrawPie(Pens.Blue, rect, -180.0f, angle); e.Graphics.SmoothingMode = SmoothingMode.Default; e.DrawText(); } } VB: Private Sub Grid_OnDrawCellFormat(ByVal sender As Object, ByVal e As TEventDrawCellFormatArgs) If (Not e.Command.Text Is "Rainbow gradient") Then e.DoDefaultDraw() Else e.DrawBackground() ' data preparation Dim angle As Single = Convert.ToInt32(CDbl((e.Part * 180))) Dim brush As New SolidBrush(Color.FromArgb(200, Color.Red)) Dim rect As Rectangle = e.RectangleCell rect.Width = Convert.ToInt32(CDbl((rect.Height * 1.7))) rect.Height = rect.Width rect.Inflate(-4, -4) e.Graphics.SmoothingMode = SmoothingMode.HighQuality ' painting a segment ' a semicircle corresponds to the maximum value e.Graphics.FillPie(brush, rect, -180.0!, angle) e.Graphics.DrawPie(Pens.Blue, rect, -180.0!, angle) e.Graphics.SmoothingMode = SmoothingMode.Default e.DrawText() End If End Sub | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|