Archive

Other Common Properties of Application Object

Method

#Refresh screen #xlwings app.screen_updating=False #Show warnings #xlwings #app.display_alerts=False #Call worksheet functions #xlwings #app.api.WorksheetFunction.CountIf(app.api.Range(‘B2:F5′),’>8′)

Sample Code

#Other commonly used properties

import xlwings as xw    #Import the xlwings package

app=xw.App()

#Refresh screen
#xlwings
app.screen_updating=False

#Show warnings
#xlwings
#app.display_alerts=False

#Call worksheet functions
#xlwings
#app.api.WorksheetFunction.CountIf(app.api.Range('B2:F5'),'>8')

#app.kill()

Application Object: Properties for Position, Size, Title, Visibility, and Status

Method

#xlwings API

app.api.Left    #Location

#app.api.Top

#app.api.Width    #Size

#app.api.Height

#app.api.Caption    #Title

#app.api.Visible    #Visibility

#app.api.WindowState    #Status

#app.api.WindowState=xw.constants.WindowState.xlMaximized

Sample Code

#Location, size, title, visibility, and status properties

import xlwings as xw    #Import the xlwings package

app=xw.App()

#xlwings API
app.api.Left    #Location
#app.api.Top
#app.api.Width    #Size
#app.api.Height
#app.api.Caption    #Title
#app.api.Visible    #Visibility
#app.api.WindowState    #Status
#app.api.WindowState=xw.constants.WindowState.xlMaximized

app.kill()

Two Ways to Use xlwings

Method

xlwings method, using new syntax

sht.range(‘A1’).value=10

 

xlwings API method, using VBA-style class syntax

sht.api.Range(‘A1’).Value=10

sht.api returns a COM object.

Sample Code

#Two Programming Approaches with the xlwings Package  

import xlwings as xw   #Import the xlwings package 

app=xw.App()    #Create an Excel application
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht.range('A1').value=10

#xlwings API
#sht.api.Range('A1').Value=10

bk.close()
app.kill()

General Process of Using xlwings

Method

Import xlwings package

import xlwings as xw    

Create or get main objects

app=xw.App()

bk=app.books.active    #Get the active workbook

sht=bk.sheets.active    #Get the active sheet

Data processing

sht.range(‘A1’).value=10

Save the workbook, close it and exit

bk.close()

app.kill()

Sample Code

#General Process of Using the xlwings Package  

import xlwings as xw    #Import the xlwings package 

app=xw.App()    #Create an Excel application  
bk=app.books.active    #Get the active workbook 
sht=bk.sheets.active    #Get the active worksheet  

#Process data 
sht.range("A1").value=10

#Close the workbook
bk.close()
#Exit the application
app.kill()

xlwings Packages and It's Installation

Comparison of Python Packages Related to Excel

OpenPyXl（Does not depend on Excel)

win32com：xlwings predecessor, encapsulates VBA object model, Windows platform

xlwings：Powerful, platform-independent

pandas：Based on NumPy, fast, data cleaning

Excel Objects and Their Hierarchy

Application Object

Workbooks Collection Object

Workbook Object

Worksheets Collection Object

Worksheet Object

Range Obejct

Installation of xlwings Package

• Install directly online. In PowerShell, type:

pip install xlwings

Press Enter.

• For offline installation, visit the URL:

https://pypi.org/

Download the corresponding version and 32/64-bit installation file, then install it.