The dataframe is my AISC table of structural steel shapes. This was built from a fundamental set of properties used by all structural engineers. There are approximately 2000 shapes with about 90 properties each.
In day-to-day work, a structural engineer is selecting beams, columns, etc from the AISC shapes and, often, restricting the selection to set dimensions or to set properties — use of multiple criteria would be the norm.
The function uses only three parameters: the criteria range, the reverse sort toggle, and the upper limit on rows of results.
Note that conditional formatting is applied using the PyXLL formatter option.
Database Filter in Action — Video
Full Screenshot
Active Filters
The shape area (‘A’) is restrained:
9 ≤ A ≤ 10
A = 9.13 does match
The shape depth (‘DDET’) is restrained:
DDET = 8
DDET = 8 does match
The shape name (‘AISC_MANUAL_LABEL’) is restrained:
^W[0-9]+x.*$
This restricts the shape name to this pattern:
Starts with ‘W’
Then one or more digits
Then an ‘X’
Then zero or more of any characters
‘W8X31’ does match
Criteria
I offer a code snippet
..
..
from pyxll import async_call
from pyxll import xl_app
from pyxll import xl_func
from pyxll import XLCell
from pyxll import xl_macro
from pyxll import xl_menu
from pyxll import xlcAlert
from pyxll import xlfCaller
from pyxll import schedule_call
from pyxll import get_type_converter
from pyxll import DataFrameFormatter, ConditionalFormatter, Formatter
..
..
blue_formatter = Formatter(text_color=Formatter.rgb(0, 0, 0xff))
bf = Formatter.rgb(0, 0, 0xff)
light_yellow_formatter = Formatter(text_color=bf,
interior_color=Formatter.rgb(255, 242, 204))..
..
#this next line is the PyXLL decorator
@xl_func("range critstrinp,
bool reverseboolinp,
int ibxintinp: var",
formatter=light_yellow_formatter)
def AISC_mult_filt_sort(critstrinp,
reverseboolinp=True,
ibxintinp=40):
"""FUNCTION to place filtered sorted
(multiple criteria) steel shape list
<critstrinp>
"criteria for AISC members"
<reverseboolinp>
"Reverse order (True or False)"
<ibxintinp>
"Upper limit on output rows count
(leave blank for default limit of 40)"
"""
..
..
crit_string = ""
# CT is for 'contains'
# as in search_string in str(getattr(db[x], field_name))
# SW is for 'starts with'
# as in getattr(db[x], field_name).startswith(search_string)
# RE is for 'regular expression'
# as in re.search(regex, str(getattr(db[x], field_name)))
crits = ["==", ">", "<", ">=", "<=", "!=", "<>",
"EE", "EQ", "E", "GT", "LT", "GE", "LE",
"NE", "CT", "SW", "RE"]
critsbetween = ["<<", "<<=", "<=<", "<=<=",
"LTLT", "LTLE", "LELT", "LELE"]
..
..
