With big data’s rise, Python has gained popularity in China as a key alternative to Excel for data analysis. Python processes Excel data via specialized packages.
Python Packages for Excel Data Processing
Key Python packages for Excel data processing are listed in Table 1-1:
| Python Package | Description |
|---|---|
| xlrd | Reads .xls and .xlsx files |
| xlwt | Writes .xls files |
| OpenPyXL | Reads/writes .xlsx/.xlsm/.xltx/.xltm; supports Excel object model; no Excel dependency |
| XlsxWriter | Writes .xlsx files; supports VBA |
| win32com | Encapsulates all Excel object models used in VBA |
| comtypes | Encapsulates all Excel object models used in VBA |
| xlwings | Re-encapsulates Win32COM; supports mixed VBA-Python programming and data type conversion |
pandas |
Reads/writes .xls/.xlsx; provides concise, fast data processing functions |
These packages vary: some are lightweight, others feature-rich (rivaling VBA’s object model); some depend on Excel, others do not; some are efficient, others less so. This book focuses on pandas, win32com, xlwings, and OpenPyXL.
Processing Data via pandas
Like Power Query, pandas is designed for large data. Built on NumPy, it processes data quickly and encapsulates algorithms, enabling concise code (via function/method calls).
pandas does not support the Excel object model—users cannot read/write specific cell ranges directly. Since large data is impractical to display in worksheets (and loading/refreshing risks crashes), pandas prioritizes speed over worksheet interaction. It does not require Excel installation.
Processing Data via win32com and xlwings
win32com encapsulates Excel’s VBA object model, giving Python users access to most VBA capabilities. xlwings re-encapsulates win32com, providing a second layer of abstraction for VBA-like functionality.
win32com also supports object models for Word, PowerPoint, WPS, and AutoCAD. For Excel data, xlwings is recommended. Both win32com and xlwings require Excel installation and handle only small/medium-sized data.
Processing Data via OpenPyXL
Functionally, OpenPyXL is a lightweight version of xlwings. It supports the Excel object model but incompletely (many features are missing).
Its key advantage: no Excel installation required—ideal for software development. It handles only small/medium-sized data.
Comparing Python Packages
Sections 1.3.2–1.3.4 outline package traits. In practice, they are often combined: use pandas for data processing, and OpenPyXL/xlwings for worksheet operations (e.g., reading/writing data, formatting cells).
Correspondence Between Python Packages and Microsoft Products
Two product lines exist for data analysis:
Microsoft’s Excel, VBA, and Power BI tools.
Python packages.
Their correspondence is clear:
Excel VBA ↔ win32com/xlwings (small/medium data).
Power Query ↔ pandas (large data).