[Solved] Coloring results

[dreamquartz]

Hi All,

Is there any information available about having a resultset showing different colors when they are not in range?
Example
Positive balance: Black
Negative balance: Red
or
Less than 30 days: Blue
More than 30 days and less than 6 months: Orange
More than 6 months: Black

I would like to be able to show it in a listbox or a table control.

Dream

[Villeroy]

The number in a formatted field can be formatted like a spreadsheet cell with conditional font colours. See "number formats" in the help index.
You may use Calc as a report engine and dump record sets into a preformatted spreadsheet with conditional number formats, conditional formatting by cell styles and using the STYLE spreadsheet function: [Tutorial] Using registered datasources in Calc

[longi]

Hi!
I got the same thing you want, but in a report.
Maybe it could be useful for you if you can adapt it.

Code: Select all

Sub FormatoCondicionalMonotabla
    'By JohnSUN-Pensioner (The author of the first macro I used to get this)
    Dim oTextTables As Variant   ' All tables of document
    Dim oTextTable As Variant   ' One table
    Dim oCell As Variant      ' One cell
    ocontroller = Thisdatabasedocument.currentController
    if not ocontroller.isconnected then ocontroller.connect
    oreportdoc = Thisdatabasedocument.reportdocuments.getbyname("My Report").open
    oTexttable = oreportdoc.Texttables(0)
    REM or   oTextTable = oTextTables.getByName("Table2")
    orows = oTexttable.rows
    Columnas= oTexttable.Columns.Count
    for i = 1 to  orows.count-1
    for a = 0 to 0
    oCell = oTextTable.getCellByPosition(a, i)
    oText = oCell.Text
    oCurs = oText.createTextCursor()
    oCurs.gotoEND(True)
    if oCurs.GetString ="(the text you want to have the condition)" Then 
    oCurs.CharFontName = "Arial" '(Noun of the source)
    oCurs.CharUnderline = com.sun.star.awt.FontUnderline.SINGLE '( Emphasises the text )
    oCurs.CharWeight = com.sun.star.awt.FontWeight.BOLD '( Bold )   
    oCurs.CharColor = RGB(255,0,255)
    oCurs.CharHeight = 8
    oCurs.CharPosture = com.sun.star.awt.FontSlant.ITALIC '(Italic)
    oCell.setPropertyValue("BackColor", RGB(255, 255, 0))   ' or any other
    oTexttable.rows(0)
    End if
    Next a
    Next i
End Sub

I'll try to use it into forms as you want. It could be a god thing.
I hope it were useful for you.
Bye!

[dreamquartz]

Still looking for a solution for a Query.

Dream

[Villeroy]

It is definitively impossible for single rows in any kind of grid view. It is possible to change color of a stand-alone controls and an entire grid control as you navigate through the records.

[longi]

Hi!

As the teacher said, it seems imposible to get a background colour in a cell as in a text table or a calc table, because the control is all the control table.
However, and I suppose you have already tried, you can change the type of field in the column. So you can have a formatted field, and you can have the colour of the numbers inside the column, and you can have different colours deppending on the value.
I couldn't change the font height, or put in bold the numbers, but I could change their colour.
Maybe is not the best solution, but it could be useful.

The example could be similar to [<4][RED]#.##0;[>=4][BLUE]#.##0;Estandar

Bye!

[longi]

Hello one more time!

I've been googling for a bit, and I found a beautiful macro to build a grid into a dialog. Unfortunately, I've never used dialogs in my databases, so I don't know how to deal with them.
The code is really different than the code I've been using lately, so I'm not able to translate this into a form with a grid inside it.
It seems that you can have each row in a different colour (like a zebra), and can change some others parameters, but I don't know how to do it.
I put the code here, and if somebody knows how to work with it, and explain us a bit..., it could be appreciated.
Thanks in advance!

Code: Select all

Sub GridControlTest()
   ' By Oliver, the author I copied

	Dim oColumnModel as Object
	Dim oColumn1 as Object
	Dim oColumn2 as Object
	Dim oDataModel as Object
	Dim oGridModel as Object
	 Dim oGridControl as Object
	Dim oDialogModel as Object
	Dim oDialogControl as Object
 	Dim i as Integer

	 Dim rBounds as new com.sun.star.awt.Rectangle
	Dim oContWin as Object
	 Dim oFrame as Object
 	Dim oToolkit as Object
	 Dim wd as new com.sun.star.awt.WindowDescriptor
	
 	Dim oListener

	oColumnModel = createUnoService("com.sun.star.awt.grid.DefaultGridColumnModel")

   	oColumn1 = createUnoService("com.sun.star.awt.grid.GridColumn")
	oColumn1.Title = "City"
	oColumn1.ColumnWidth = 30
 	oColumn2 = createUnoService("com.sun.star.awt.grid.GridColumn")
	oColumn2.Title = "Country"
	oColumn2.ColumnWidth = 50

	oColumnModel.addColumn(oColumn1)
	oColumnModel.addColumn(oColumn2)

	oDataModel = createUnoService("com.sun.star.awt.grid.DefaultGridDataModel")
	For i = 0 To 20
		oDataModel.addRow (""&i, Array(Chr(97+i), Chr(65+i)))
	Next i

	oDialogModel = createUnoService("com.sun.star.awt.UnoControlDialogModel")
	oDialogModel.Title = "GridControl Test"
	oDialogControl = createUnoService("com.sun.star.awt.UnoControlDialog")
	oDialogControl.setModel( oDialogModel )
	oDialogControl.setPosSize( 100, 100, 400, 500, com.sun.star.awt.PosSize.POSSIZE)

	oGridModel = oDialogModel.createInstance("com.sun.star.awt.grid.UnoControlGridModel")
	oGridModel.Name = "MyGrid"
	oGridModel.GridDataModel = oDataModel
	oGridModel.ColumnModel = oColumnModel
	oGridModel.ShowColumnHeader = True
	oGridModel.ShowRowHeader = True
	oGridModel.HScroll = True
	oGridModel.VScroll = True
	oGridModel.Sizeable = True

	oGridControl = createUnoService("com.sun.star.awt.grid.UnoControlGrid")
	oGridControl.setModel(oGridModel)

	oDialogControl.addControl("MyGrid", oGridControl)
	oGridControl.setPosSize(10, 10, 380, 380, com.sun.star.awt.PosSize.POSSIZE)

	oToolkit = createUnoService("com.sun.star.awt.Toolkit")

	rBounds.X = oDialogControl.PosSize.X
	rBounds.Y = oDialogControl.PosSize.Y
	rBounds.Width  = oDialogControl.PosSize.Width
	rBounds.Height = oDialogControl.PosSize.Height

	wd.Type = com.sun.star.awt.WindowClass.TOP
	wd.Bounds = rBounds
  	With com.sun.star.awt.WindowAttribute
	wd.WindowAttributes = .BORDER + .MOVEABLE + .SIZEABLE + .CLOSEABLE
	End With
	wd.WindowServiceName = "window"
	
	oContWin = oToolkit.createWindow(wd)

	oFrame = createUnoService("com.sun.star.frame.Frame")
  	oFrame.initialize(oContWin)
	StarDesktop.getFrames().append(oFrame)
	oFrame.Name = "TestGridFrame"
	oFrame.Title = "TestGridTitle"

	oGridControl.createPeer(oToolkit, oContWin)
	oContWin.setVisible(True)

	oListener = CreateUnoListener("XGridSelection_", "com.sun.star.awt.grid.XGridSelectionListener")
	oGridControl.addSelectionListener(oListener)

End Sub

Sub XGridSelection_selectionChanged(oEvt)
	MsgBox "selected row: " & oEvt.Row
End Sub

Sub XGridSelection_disposing(oEvt)
End Sub

It doesn't matter if you put the macro in a writer document nor in a database. It works in the same way.

Bye!

[dreamquartz]

Hi!

As the teacher said, it seems imposible to get a background colour in a cell as in a text table or a calc table, because the control is all the control table.
However, and I suppose you have already tried, you can change the type of field in the column. So you can have a formatted field, and you can have the colour of the numbers inside the column, and you can have different colours deppending on the value.
I couldn't change the font height, or put in bold the numbers, but I could change their colour.
Maybe is not the best solution, but it could be useful.

The example could be similar to [<4][RED]#.##0;[>=4][BLUE]#.##0;Estandar

Bye!

Thanks for this one.
I was using this, and am using it as follows:
[<4][RED]#.##0;[>=4][<10][BLUE]#.##0;REGULAR.

The biggest challenge I have now trying to do something similar with dates.
I did not find anything so far.

Dream

[dreamquartz]

Hello one more time!

I've been googling for a bit, and I found a beautiful macro to build a grid into a dialog. Unfortunately, I've never used dialogs in my databases, so I don't know how to deal with them.
The code is really different than the code I've been using lately, so I'm not able to translate this into a form with a grid inside it.
It seems that you can have each row in a different colour (like a zebra), and can change some others parameters, but I don't know how to do it.
I put the code here, and if somebody knows how to work with it, and explain us a bit..., it could be appreciated.
Thanks in advance!

Code: Select all

Sub GridControlTest()
   ' By Oliver, the author I copied

	Dim oColumnModel as Object
	Dim oColumn1 as Object
	Dim oColumn2 as Object
	Dim oDataModel as Object
	Dim oGridModel as Object
	 Dim oGridControl as Object
	Dim oDialogModel as Object
	Dim oDialogControl as Object
 	Dim i as Integer

	 Dim rBounds as new com.sun.star.awt.Rectangle
	Dim oContWin as Object
	 Dim oFrame as Object
 	Dim oToolkit as Object
	 Dim wd as new com.sun.star.awt.WindowDescriptor
	
 	Dim oListener

	oColumnModel = createUnoService("com.sun.star.awt.grid.DefaultGridColumnModel")

   	oColumn1 = createUnoService("com.sun.star.awt.grid.GridColumn")
	oColumn1.Title = "City"
	oColumn1.ColumnWidth = 30
 	oColumn2 = createUnoService("com.sun.star.awt.grid.GridColumn")
	oColumn2.Title = "Country"
	oColumn2.ColumnWidth = 50

	oColumnModel.addColumn(oColumn1)
	oColumnModel.addColumn(oColumn2)

	oDataModel = createUnoService("com.sun.star.awt.grid.DefaultGridDataModel")
	For i = 0 To 20
		oDataModel.addRow (""&i, Array(Chr(97+i), Chr(65+i)))
	Next i

	oDialogModel = createUnoService("com.sun.star.awt.UnoControlDialogModel")
	oDialogModel.Title = "GridControl Test"
	oDialogControl = createUnoService("com.sun.star.awt.UnoControlDialog")
	oDialogControl.setModel( oDialogModel )
	oDialogControl.setPosSize( 100, 100, 400, 500, com.sun.star.awt.PosSize.POSSIZE)

	oGridModel = oDialogModel.createInstance("com.sun.star.awt.grid.UnoControlGridModel")
	oGridModel.Name = "MyGrid"
	oGridModel.GridDataModel = oDataModel
	oGridModel.ColumnModel = oColumnModel
	oGridModel.ShowColumnHeader = True
	oGridModel.ShowRowHeader = True
	oGridModel.HScroll = True
	oGridModel.VScroll = True
	oGridModel.Sizeable = True

	oGridControl = createUnoService("com.sun.star.awt.grid.UnoControlGrid")
	oGridControl.setModel(oGridModel)

	oDialogControl.addControl("MyGrid", oGridControl)
	oGridControl.setPosSize(10, 10, 380, 380, com.sun.star.awt.PosSize.POSSIZE)

	oToolkit = createUnoService("com.sun.star.awt.Toolkit")

	rBounds.X = oDialogControl.PosSize.X
	rBounds.Y = oDialogControl.PosSize.Y
	rBounds.Width  = oDialogControl.PosSize.Width
	rBounds.Height = oDialogControl.PosSize.Height

	wd.Type = com.sun.star.awt.WindowClass.TOP
	wd.Bounds = rBounds
  	With com.sun.star.awt.WindowAttribute
	wd.WindowAttributes = .BORDER + .MOVEABLE + .SIZEABLE + .CLOSEABLE
	End With
	wd.WindowServiceName = "window"
	
	oContWin = oToolkit.createWindow(wd)

	oFrame = createUnoService("com.sun.star.frame.Frame")
  	oFrame.initialize(oContWin)
	StarDesktop.getFrames().append(oFrame)
	oFrame.Name = "TestGridFrame"
	oFrame.Title = "TestGridTitle"

	oGridControl.createPeer(oToolkit, oContWin)
	oContWin.setVisible(True)

	oListener = CreateUnoListener("XGridSelection_", "com.sun.star.awt.grid.XGridSelectionListener")
	oGridControl.addSelectionListener(oListener)

End Sub

Sub XGridSelection_selectionChanged(oEvt)
	MsgBox "selected row: " & oEvt.Row
End Sub

Sub XGridSelection_disposing(oEvt)
End Sub

It doesn't matter if you put the macro in a writer document nor in a database. It works in the same way.

Bye!

I am with Villeroy in relation to Macros. They are "Evil". I had to convince our Client to use one Macro, and that took a long time.
See: viewtopic.php?f=13&t=79079.
So, thank you for this solution, and by the looks of it it would work.

The solution you gave above, is doing the trick for what we were looking for.

Dream

[dreamquartz]

It is definitively impossible for single rows in any kind of grid view. It is possible to change color of a stand-alone controls and an entire grid control as you navigate through the records.

Can you elaborate?
Using HSQLDB 2.3.3 these days....

Dream

[Villeroy]

The API offers to do this:
MyFormControl.Model.BackgroundColor = cLng(ColorCode)

but not
MyGridControl.Model.Row(x).BackgroundColor = cLng(ColorCode)

Rule of thumb: If there is no way to do it manually in the GUI, then it can't be done with a macro.

[dreamquartz]

The API offers to do this:
MyFormControl.Model.BackgroundColor = cLng(ColorCode)

but not
MyGridControl.Model.Row(x).BackgroundColor = cLng(ColorCode)

Rule of thumb: If there is no way to do it manually in the GUI, then it can't be done with a macro.

K