The two packages openpyxl and PyXLL are fundamentally different, and suited to different use cases.
- openpyxl is a Python package for reading and writing files in the Excel file formats.
- PyXLL is an Excel add-in for running Python inside the live Excel application.
If you need to read and write Excel files from Python without running Excel (e.g. as part of a batch process) then most likely you will want to use openpyxl.
To expose Python functions in Excel as worksheet functions, macros, ribbon tool bar and so on then PyXLL will enable you to do that.
PyXLL is commonly used to expose Python functions to Excel users so they can be called interactively, either as part of adhoc analysis or to build interactive Excel dashboards. The ability to run Python code integrated into Excel means you can use Excel as a user facing user interface (UI) to your Python code.
Excel can be scripted (or automated) using Python functions running in Excel itself using PyXLL. With PyXLL, Python can be used as a replacement for VBA with all of the advantages that come with using Python. Additionally, unlike VBA, the Python code is not coupled to any particular workbook and can be reused easily between workbooks and as well as other Python scripts or applications.
openpyxl has no capabilities to integrate Python into Excel, as that is not its purpose! Instead, you can use it for reading and writing files saved by Excel.