Let’s go straight to the video
Tabular
• column to be accessed (integer), my_col
The function presents a dialog containing a PyQt QtWidgets.QTableView. When the user selects a row, the value at column my_col is placed on the clipboard. A row is selected by either the Select button or, much simpler, just Enter. The user may continue selecting, and therefore copying, or may cancel the dialog. Note that the user may paste anywhere, not just Excel.
My idea has two parts:
• the generality of using one dialog, by varying only the column to be picked.
The function presents a dialog containing a PyQt QtWidgets.QMenu. The user may select from an ‘outline’ form of dropdown.
This resembles how one might navigate a book or a helpfile. In my documentation below, I access my user_dict.json idea from a recent blog post; the hierarchy approach is simpler and more succinct. I think I prove that the list length has a practical limit.
Many uses come to mind.
The widgets are built using straight Python and PyQT.
my_table = [ ["Callout", "Diameter", "Weight", "Area"], ["#3", 0.375, 0.376, 0.11], ["#4", 0.5, 0.668, 0.20], ["#5", 0.625, 1.043, 0.31], ["#6", 0.75, 1.502, 0.44], ["#7", 0.875, 2.044, 0.60], ["#8", 1.000, 2.67, 0.79], ["#9", 1.128, 3.4, 1.00], ["#10", 1.27, 4.30, 1.27], ["#11", 1.41, 5.31, 1.56], ["#14", 1.693, 7.65, 2.25], ["#18", 2.257, 13.60, 4.00], ]
{ "cars": [ "Alfa Romeo", "Bentley", "Citroen", "Dacia", "Ferrari", "Hyundai", "Infiniti", "Jeep", "Kia", "Lotus", "MG", "Nissan", "Porsche", "Renault", "Subaru", "Tesla", "Volkswagen", "Weltmeister", "XPeng", "Yulong", "Zastava" ], "family": { "Jackson": [ "Rebbie", "Jackie", "Tito", "Jermaine", "La Toya", "Marlon", "Brandon", "Michael", "Randy", "Janet" ], "heroes": [ "superman", "wonder woman" ],
Code Excerpts
# I programmed the table model as a class class MyTableModel(PySide2.QtCore.QAbstractTableModel): def __init__(self, parent, mylist, header, *args): PySide2.QtCore.QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header ... # I programmed the dialog as a class # Notice I have one form for Python or PyXLL class FormKLH_DDT(PySide2.QtWidgets.QDialog): def __init__(self, pyxll_flag=False, col_input=0, parent=None): super().__init__(parent) path_fn = "E:/my_pyxll_modules/user_dict.json" if pyxll_flag: ... # function to be called from straight Python def tabledrop(col_input=0): # make sure Qt has been initialized app = get_qt_app() pyxll_flag = False dlg = FormKLH_DDT(pyxll_flag, col_input) dlg.exec_() # function to be called from PyXLL within Excel @xl_macro() def tabledrop_pyxll(col_input=0): # make sure Qt has been initialized app = get_qt_app() xl = xl_app() active_sheet = xl.ActiveSheet parent = None try: my_column = int(active_sheet.Range("my_column").Value) except: my_column = col_input pyxll_flag = True dlg = FormKLH_DDT(pyxll_flag, my_column) dlg.exec_() if __name__ == "__main__": app = PySide2.QtWidgets.QApplication(sys.argv) # demo a call from Python col_input = 3 tabledrop(col_input)
Martin Fitzpatrick
Couldn’t have done it without that lambda !!
Brilliant.
To anyone considering doing any detail at all with PyQt, this is your reference store.
I bought the electronic copy in August 2020. Martin followed up with an emailed update in November 2021 and then again in June 2024 — both at no charge.