You are correct in that xlwings relies on pywin32, whereas openpyxl does not.
openpyxl
A ".xlsx" excel file is essentially a zip-file containing multiple XML files formatted according to Microsoft's OOXML specification. With this specification it's possible to create a program capable of directly reading/writing excel files in just about any programming language. This is the approach applied in openpyxl: it uses python code to read/write excel files directly.
xlwings
A Microsoft Excel application can be started and controlled by an external program through the Win32 COM API. The pywin32 package provides an interface between Win32 COM and Python. Through a python script with the right pywin32 commands you can fully control an Excel Application (open excel files, query data from cells, write data to cells, save excel files, etc.). The pywin32 commands that you can use mirror the Excel VBA commands, albeit with python syntax.
xlwings is (among other things) a user-friendly wrapper around pywin32. It introduces several concise-yet-powerful methods. An example would be the methods for direct conversion of an excel cell range to a numpy array or pandas dataframe (and vice versa).
Summary
A fundamental difference between xlwings and openpyxl is that the former requires that MS Excel is installed on your machine, whereas the latter does not.
Python and xl wings
python - Differences between xlwings vs openpyxl Reading Excel Workbooks - Stack Overflow
By curiosity, what do you think about xlwings?
xlwings: The easiest way to deploy your Python powered Excel Spreadsheets on Windows.
Hi all! I've previously written about why I wasn't a big fan of Microsoft's "Python in Excel" solution for using Python with Excel, see the Reddit discussion. Instead of just complaining, I have now published the "xlwings Lite" add-in, which you can install for free for both personal and commercial use via Excel's add-in store. I have made a video walkthrough, or you can check out the documentation.
xlwings Lite allows analysts, engineers, and other advanced Excel users to program their custom functions ("UDFs") and automation scripts ("macros") in Python instead of VBA. Unlike the classic open-source xlwings, it does not require a local Python installation and stores the Python code inside Excel for easy distribution. So the only requirement is to have the xlwings Lite add-in installed.
So what are the main differences from Microsoft's Python in Excel (PiE) solution?
-
PiE runs in the cloud, xlwings Lite runs locally (via Pyodide/WebAssembly), respecting your privacy
-
PiE has no access to the excel object model, xlwings Lite does have access, allowing you to insert new sheets, format data as an Excel table, set the color of a cell, etc.
-
PiE turns Excel cells into Jupyter notebook cells and introduces a left to right and top to bottom execution order. xlwings Lite instead allows you to define native custom functions/UDFs.
-
PiE has daily and monthly quota limits, xlwings Lite doesn't have any usage limits
-
PiE has a fixed set of packages, xlwings Lite allows you to install your own set of Python packages
-
PiE is only available for Microsoft 365, xlwings Lite is available for Microsoft 356 and recent versions of permanent Office licenses like Office 2024
-
PiE doesn't allow web API requests, whereas xlwings Lite does.
You are correct in that xlwings relies on pywin32, whereas openpyxl does not.
openpyxl
A ".xlsx" excel file is essentially a zip-file containing multiple XML files formatted according to Microsoft's OOXML specification. With this specification it's possible to create a program capable of directly reading/writing excel files in just about any programming language. This is the approach applied in openpyxl: it uses python code to read/write excel files directly.
xlwings
A Microsoft Excel application can be started and controlled by an external program through the Win32 COM API. The pywin32 package provides an interface between Win32 COM and Python. Through a python script with the right pywin32 commands you can fully control an Excel Application (open excel files, query data from cells, write data to cells, save excel files, etc.). The pywin32 commands that you can use mirror the Excel VBA commands, albeit with python syntax.
xlwings is (among other things) a user-friendly wrapper around pywin32. It introduces several concise-yet-powerful methods. An example would be the methods for direct conversion of an excel cell range to a numpy array or pandas dataframe (and vice versa).
Summary
A fundamental difference between xlwings and openpyxl is that the former requires that MS Excel is installed on your machine, whereas the latter does not.
in-term of simultaneous opening of workbook while using xlwings, you can actually stop it with this statement line1 app=xw.apps.active, line2 if app is none: line3 app=xw.app(visible=False)if you are not comfortable with it. The limitation is that u cannot use xlwing without installing Excel in your local machine, but since you are working with excel you need excel app. but generally xlwing covers a lot as a learner you see the effect of code in Realtime. both of are mostly use your choice depend on your project.
I found this blog post showing basics on how to use xlwings.
But after reading the post, I was a bit confuse. Why should someone use this package, when you can do everything with pandas. A part, from the fact they have a nice interface that works with jupyter.
You guys, what do you think about that? Have you some good exemples or reasons to use it?