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
"criteria for AISC members"
"Reverse order (True or False)"
"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"]
..
..
