How to make a macro (Python or VBA) that makes a column filter by color, both background color or text color? as in the image below:
it’s a recent option in libreoffice, I didn’t find anything about it in the forum
I found this reference in the api, but I don’t know how it applies (FilterFieldValue Struct Reference):
https://api.libreoffice.org/docs/idl/ref/structcom_1_1sun_1_1star_1_1sheet_1_1FilterFieldValue.html
It is possible to implement, but you have to provide an interface for selecting options. So we should talk about filtering data by the parameters set by the user on the sheet. Or what?
You can probably programmatically set two properties at the same time (not tested).
com.sun.star.sheet.FilterFieldType.TEXT_COLOR
com.sun.star.sheet.FilterFieldType.BACKGROUND_COLOR
This is the python macro to apply a conventional Standard filter, with EQUAL operator.
But how could I modify it to apply a filter by text color/background color ?
import uno
from com.sun.star.sheet import TableFilterField
from com.sun.star.sheet.FilterOperator import EMPTY, EQUAL, GREATER_EQUAL, GREATER, TOP_VALUES, BOTTOM_VALUES, NOT_EQUAL, NOT_EMPTY
def apply_filter(*args):
REF_CELL = 'A1'
doc = XSCRIPTCONTEXT.getDocument()
sheets = doc.Sheets
my_sheet = sheets[0]
cell = my_sheet[REF_CELL]
my_range = get_my_range(cell)
filter_by(my_range, 6)
def get_my_range(ref_cell):
sheet = ref_cell.Spreadsheet
cursor = sheet.createCursorByRange(ref_cell)
cursor.collapseToCurrentRegion()
range = sheet[cursor.AbsoluteName]
return range
def filter_by(range, ref_field):
NAME = 'data_tmp'
doc = XSCRIPTCONTEXT.getDocument()
db_ranges = doc.DatabaseRanges
if db_ranges.hasByName(NAME):
db_ranges.removeByName(NAME)
db_ranges.addNewByName(NAME, range.RangeAddress)
data = db_ranges.getByName(NAME)
data.AutoFilter = True
ff = TableFilterField()
ff.Field = ref_field
ff.Operator = EQUAL
ff.NumericValue = 5
ff.IsNumeric = True
fd = data.getFilterDescriptor()
fd.ContainsHeader = True
fd.FilterFields = ((ff,))
data.refresh()
return
The »my_« (pre|in)fix is a stupid habit of Basic programmers, unless you also use a prefix »your_« »their_« or »our_« its absolutely meaningless!
»range« is the name of a built-in python function!
and you dont need the indirections, do “return cursor” right away!
…
def get_range(ref_cell):
sheet = ref_cell.Spreadsheet
cursor = sheet.createCursorByRange(ref_cell)
cursor.collapseToCurrentRegion()
return cursor
good catch - but this is the official documentation… that must! 
btw. its nice to see others to read the python-docs
The name of the variables I can correct…
But could you guys help me to solve the main problem of the topic, filter a column by text color / background color using macro?
As far as I understand, the interface currently implemented allows you to select the color of the cell’s text or background, but not both.
Maybe I expressed myself badly
Yes that’s right I need a macro that is able to filter one or the other (text color or background color) not both at the same time, but I don’t know how to do it.
Only now the task has become clearer…
To my surprise, it looks like a macro can set several conditions of different types at the same time (starting from LO 7.2).
Option Explicit
' Show red font color, red background color and string "red".
Sub SetFilterRed()
Dim oDoc, oDBRange, oFilterDesc
Dim FFValue(2) As New com.sun.star.sheet.FilterFieldValue
Dim FFields(0) As New com.sun.star.sheet.TableFilterField3
oDoc = ThisComponent
oDBRange = oDoc.DataBaseRanges.getByName("d_color")
FFValue(0).FilterType = com.sun.star.sheet.FilterFieldType.BACKGROUND_COLOR
FFvalue(0).ColorValue = RGB(255, 0, 0) ' Red background
FFValue(1).FilterType = com.sun.star.sheet.FilterFieldType.TEXT_COLOR
FFvalue(1).ColorValue = RGB(255, 0, 0) ' Red font color
FFValue(2).FilterType = com.sun.star.sheet.FilterFieldType.STRING
FFvalue(2).StringValue = "red"
FFields(0).Field = 0
FFields(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
FFields(0).Values = FFValue
oFilterDesc = oDBRange.getFilterDescriptor()
oFilterDesc.FilterFields3 = FFields
oDBRange.refresh()
End Sub
Colors.ods (12.4 KB)
The same vba macro above created by @sokol92 , converted to python. It really filtered different types at the same time, although in the graphical interface it only shows the background color radiobutton is selected.
Thanks a lot for the help
import uno
from com.sun.star.sheet import TableFilterField3, FilterFieldValue
from com.sun.star.sheet.FilterFieldType import BACKGROUND_COLOR, TEXT_COLOR, STRING
from com.sun.star.sheet.FilterOperator import EQUAL
def rgb_to_long(rgb):
"""Convert an RGB color tuple to libreoffice long integer format
Args:
rgb (tuple): (255,0,0)
Returns:
int: LO long int
"""
r = rgb[0] * 256 * 256
g = rgb[1] * 256
b = rgb[2]
return r+g+b
def str_hex_to_long(str_hex):
"""Converts a hexadecimal color string to libreoffice long integer format
Args:
str_hex (str): 'ff0000' or '#ff0000'
Returns:
int: LO long int
"""
return int(str_hex.replace('#', ''), 16)
def apply_filter(*args):
REF_CELL = 'A1'
doc = XSCRIPTCONTEXT.getDocument()
sheets = doc.Sheets
my_sheet = sheets[0]
cell = my_sheet[REF_CELL]
range = get_range(cell)
filter_by(range, 0)
def get_range(ref_cell):
sheet = ref_cell.Spreadsheet
cursor = sheet.createCursorByRange(ref_cell)
cursor.collapseToCurrentRegion()
return cursor
def filter_by(range, ref_field):
NAME = 'data_tmp'
doc = XSCRIPTCONTEXT.getDocument()
db_ranges = doc.DatabaseRanges
if db_ranges.hasByName(NAME):
db_ranges.removeByName(NAME)
db_ranges.addNewByName(NAME, range.RangeAddress)
data = db_ranges.getByName(NAME)
data.AutoFilter = True
#ffvalue = [FilterFieldValue() for _ in range(3)] #<class 'TypeError'>: 'pyuno' object is not callable
ffvalue0 = FilterFieldValue()
ffvalue1 = FilterFieldValue()
ffvalue2 = FilterFieldValue()
ffvalue0.FilterType = BACKGROUND_COLOR
#ffvalue0.ColorValue = rgb_to_long((255,0,0))
ffvalue0.ColorValue = str_hex_to_long('ff0000')
#ffvalue0.ColorValue = -1 #No fill
ffvalue1.FilterType = TEXT_COLOR
#ffvalue1.ColorValue = rgb_to_long((255,0,0))
ffvalue1.ColorValue = str_hex_to_long('ff0000')
#ffvalue1.ColorValue = -1 #Automatic
ffvalue2.FilterType = STRING
ffvalue2.StringValue = 'red'
ff = TableFilterField3()
ff.Field = ref_field
ff.Operator = EQUAL
#ff.Values = (ffvalue0,)
ff.Values = (ffvalue0, ffvalue1, ffvalue2)
fd = data.getFilterDescriptor()
fd.ContainsHeader = True
fd.FilterFields3 = ((ff,))
data.refresh()
Note1: is there any native way to convert tuple rgb to libreoffice long int in Python macros? I made two custom functions for this.
Note2: I tried to generate the FilterFieldValue objects by loop, but it displays the error message:
ffvalue = [FilterFieldValue() for _ in range(3)]
<class ‘TypeError’>: ‘pyuno’ object is not callable
>>> r, g, b = (111, 121, 131)
>>> packed = int('%02x%02x%02x' % (r, g, b), 16)
use f-string formatting instead:
r, g, b = (111, 121, 131)
packed = int(f"{r:02x}{g:02x}{b:02x}", 16)
It remains only to explain to viewers who are not knowledgeable in Python why the expression
int(f"{r:02x}{g:02x}{b:02x}", 16)
more efficient than
r * 65536 + g * 256 + b
from the post above. 
It isn’t!
Its roundabout 6times slower than the calculated
But does it matter if we compare 2.65 microseconds versus 495 nanoseconds??
for future inquiries
import uno
from unicodedata import normalize
from com.sun.star.sheet import TableFilterField3, FilterFieldValue
from com.sun.star.sheet.FilterFieldType import BACKGROUND_COLOR, DATE, NUMERIC, STRING, TEXT_COLOR
from com.sun.star.sheet.FilterOperator import EQUAL
def rgb_to_long(rgb_color):
"""Convert an RGB color tuple to libreoffice long integer format
https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03010306.html
return => red*256*256 + green*256 + blue
Args:
rgb_color (tuple): (255,0,0)
Returns:
int: LO long int
"""
r, g, b = rgb_color
#return int(f'{r:02x}{g:02x}{b:02x}', 16)
return r * 65536 + g * 256 + b
def str_hex_to_long(str_hex_color):
"""Converts a hexadecimal color string to libreoffice long integer format
Args:
str_hex_color (str): 'ff0000' or '#ff0000'
Returns:
int: LO long int
"""
return int(str_hex_color.replace('#', ''), 16)
def get_range(ref_cell):
"""Expands the cursor into the region containing the cells to which it currently points.
A region is a cell range bounded by empty cells.
Args:
ref_cell (str): String with the name of a reference cell belonging to the data range to be analyzed
Returns:
LO pyuno cursor: range of cells that contain information
"""
sheet = ref_cell.Spreadsheet
cursor = sheet.createCursorByRange(ref_cell)
cursor.collapseToCurrentRegion()
return cursor
def filter_by(range, db_range_name, ref_field):
"""Apply filter by background color, text color, string, number, date ...
https://api.libreoffice.org/docs/idl/ref/structcom_1_1sun_1_1star_1_1sheet_1_1FilterFieldValue.html
Args:
range (LO pyuno range): LO data range - ex: doc.Sheets[0]['A1:B10'] or cursor
db_range_name (str): name for the database range
ref_field (int): index of the column to be applied the filter - ex: 0 = column A
"""
name_dbr = normalize('NFKD', db_range_name).encode('ASCII','ignore').decode('ASCII')
name_dbr = name_dbr.replace(' ', '_').lower()
doc = XSCRIPTCONTEXT.getDocument()
db_ranges = doc.DatabaseRanges
if db_ranges.hasByName(name_dbr):
db_ranges.removeByName(name_dbr)
db_ranges.addNewByName(name_dbr, range.RangeAddress)
data = db_ranges.getByName(name_dbr)
data.AutoFilter = True
#ffvalues = [FilterFieldValue() for _ in range(3)] #<class 'TypeError'>: 'pyuno' object is not callable
ffvalues = [FilterFieldValue(), FilterFieldValue(), FilterFieldValue()]
ffvalues[0].FilterType = BACKGROUND_COLOR
#ffvalues[0].ColorValue = rgb_to_long((255,0,0))
ffvalues[0].ColorValue = str_hex_to_long('#FF0000')
#ffvalues[0].ColorValue = -1 #No fill
ffvalues[1].FilterType = TEXT_COLOR
ffvalues[1].ColorValue = str_hex_to_long('#FF0000')
#ffvalues[1].ColorValue = -1 #Automatic
ffvalues[2].FilterType = STRING
ffvalues[2].StringValue = 'red'
tablesff = [TableFilterField3()]
tablesff[0].Field = ref_field
tablesff[0].Operator = EQUAL
tablesff[0].Values = ffvalues
fd = data.getFilterDescriptor()
fd.ContainsHeader = True
fd.FilterFields3 = (tablesff)
data.refresh()
def main(*args):
doc = XSCRIPTCONTEXT.getDocument()
sheets = doc.Sheets
main_sheet = sheets[0]
reference_cell = main_sheet['A1']
range = get_range(reference_cell)
db_range_name = f'dbr_{main_sheet.Name}'
filter_by(range, db_range_name, 0)