xlwings tutorial

Hits: 0

[]Introduction to [xlwings]

Python operates Excel modules. The modules mentioned on the Internet are roughly: xlwings, xlrd, xlwt, openpyxl, [pyxll] , etc. The functions they provide can be summarized into two types: 1. Using python to read and write Excel files, in fact, is to read and write. Formatted text files, there is no difference between operating excel files and text and csv files. Excel files are only used to store data. Second, in addition to operating data, you can also adjust the table width, font color, etc. of the Excel file. In addition, it should be mentioned that it is also feasible to use COM to call Excel’s API to operate Excel documents, which is quite troublesome and basically no different from VBA.

xlwings Chinese Documentation


Features of xlwings

  • xlwings can easily read and write data in Excel files, and can modify cell formats
  • Can be seamlessly connected with matplotlib and pandas
  • You can call the program written in VBA in the Excel file, or let VBA call the program written in Python.
  • Open source free, always updated

Basic operation

1. Import the library

import xlwings as xw

2. Open excel

# Open the Excel program, the default settings: the program is visible, only open the workbook without creating a new workbook, and close the screen update 
app =xw.App(visible= True ,add_book= False )
 app.display_alerts = False 
app.screen_updating = False 
# Other operations: 
# app.screen_updating = False   
# : The screen is updated, which means that you can see the operation of the code for excel. Turning off the real-time update can speed up the script running. Default is True. 
# app.pid #App process pid 
# app.books #Returns a list of all open workbooks. Python opened and manually opened are not interoperable 
# Terminate the process and force quit. 
# app.quit() #Exit the excel program without saving

3. Workbook

Note that the workbook should be opened first

1. Create a new Excel document

wb = app.books.add() #Create a new book 
wk = xw.Book()
 wk = xw.books.add()

2. Open the Excel document

# Support absolute path and relative path 
wb = app.books.open( 'filepath' )
 wk = xw.Book( 'filepath' )
 wk = xw.books.open( 'filepath' )
 # It is recommended to use the following directly when practicing Article 
# wb = xw.Book('example.xlsx') 
# This way you won't open new Excel frequently

3. Open an unsaved or unclosed excel instance

wk = xw.Book( 'Book1' )    
 wk = xw.books[ 'Book1' ]   #You can also use indexes

If you have the same file open in two instances of Excel, you need to fully qualify it and include the application instance. You will find your app instance key (PID) via xw.apps.keys():

View all instance processes:
xw.apps.keys() #output list

kill all instance processes:
 for i in xw.apps.keys():
  i = 'taskkill/pid ' + str(i) + ' -t -f'     

4. Open the active workbook

wb = xw.books.active

5, save

wb.save(path= None )
 # Save the workbook, if it is the specified path, save it in the current working directory.

6. Close

wk.close() #Close   without saving.

7. Exit Excel


4. Worksheet

1. Open the worksheet

# Can use name or index 
sheet = xw.books[ 'name of workbook' ].sheets[ 'name of sheet' ]
 sheet = xw.books[ 'name of workbook' ].sheets[ 0 ]

2. Open the activity sheet

sheet = xw.sheets.active

3. Return to the book specified by sheet

book_name = sheet.book

4. Returns a range object representing all the cells on the sheet

sheet_cells = sheet.cells

5. Get or set the name of the Sheet

 # Returns all sheet specific names. 
sheet_names_list = sheet.names

6. Get all the chart collections in the sheet


7. Clear all data and formats in the table.


8. Clear the contents of the worksheet, but keep the formatting


9. Delete worksheet


10. Return table index (same as excel)


11. Create a new Sheet and make it the active sheet

wb.sheets.add(name= None , before= None , after= None ) 
 #Parameters : name(str,default None) - The name of the new sheet. If None, it defaults to Excel's name.before (Sheet, default None) - an object specifying what to add before a new sheet is added.after (Sheet, default None) - an object specifying the sheet after the sheet added.

12. Automatically adjust the width of columns, rows or both on the entire worksheet

# Arguments: axis ( string , default None) – To autoscale rows, use one of the following: rows or r, to autoscale columns, use one of the following: columns hc, to autoscale rows and columns, no arguments are provided

13. Get the number of rows and columns of the excel sheet

app = xw.App(visible=False, add_book=False)
xls = app.books.open(excel_file)
sheet = xls.sheets[0]
info = sheet.used_range
nrows = info.last_cell.row
ncols = info.last_cell.colum

5. Cells

1. Reference cell A1

rng = xw.books[ 'name of workbook' ].sheets[ 'name of sheet' ]
 # or 
sheet =xw.books[ 'name of workbook' ].sheets[ 'name of sheet' ]
 rng =sheet. range( 'A1' )

2. Reference a cell on the active worksheet

# Note that the first letter of Range is capitalized
One of the things to note is that the fully referenced path to the cell is:
# The first cell of the first sheet of the first workbook of the first Excel program
The swift way to reference a cell is
# A1 cell
# A1:B5 cells
# The first column of the first row is a1
# B1 cell 

3. Reference area cells

# A1:J10 
rng =sht[: 10 ,: 10 ]
 rng =sht.range( 'a1:a5' )
 #rng = sht['a1:a5'] 
#rng = sht[:5,0]

PS: Cells can also be referenced with tuple representing rows and columns

# A1 cell reference
 xw .Range (1,1)
 #A1 :C3 cell reference
 xw .Range ((1,1),(3,3))

6. Write data

1. Write a single value

# Note ".value“ 
sht.range( 'A1' ) .value = 1

2. Write the list

Insert by default

# Store the list [1,2,3] in A1:C1 
sht.range( 'A1' ) .value =[ 1 , 2 , 3 ]
 # Equivalent to 
sht.range( 'A1:A3' ) .value = [ 1 , 2 , 3 ]

Insert by column

# Store the list [1,2,3] in A1:A3 
sht.range( 'A1' ).options(transpose=True) .value =[ 1 , 2 , 3 ]

For multi-line input, use a two-dimensional list

# Store a 2x2 table, a two-dimensional array, in A1:B2, such as the first row 1, 2, the second row 3, 4 
sht.range( 'A1' ).options(expand= 'table' ) .value =[[ 1 , 2 ],[ 3 , 4 ]]

Seven, read data

1. Read a single value

# Read the value of A1 into the a variable 
a =sht.range( 'A1' ).value

2. The value of the read range

The returned value is in the form of a list, with multiple rows and multiple columns as a two-dimensional list, but one thing to note is that the returned value is a floating-point number by default

#Read the values ​​from A1 to A2 into the a list 
a =sht.range( 'A1:A2' ).value
 # Read the data in the first and second rows as a two-dimensional array 
a =sht .range( 'A1:B2' ).value

Read the first column of excel, first count the number of rows in the cell

rng = sht.range('a1').expand('table')
nrows = rng.rows.count

Then you can read the exact range

a = sht.range(f'a1:a{nrows}').value

The same is true for selecting a row of data

ncols = rng.columns.count #Use
fst_col = sht[ 0 ,:ncols].value

Eight, commonly used functions and methods

[1. APIs] commonly used in Book workbooks[]

# new workbook
# Reference the currently active workbook
wb=xw.books['workbook name']
wb.activate() activates as the current workbook
wb.fullname returns the absolute path of the workbook
wb.name returns the name of the workbook
wb.save(path=None) Save the workbook, the default path is the original path of the workbook, if not, it is the path where the script is located
-wb.close() closes the workbook
Code example:
# Refer to the current workbook in the Excel program
# Return the absolute path of the workbook
# return the name of the workbook
# Save the workbook, the default path is the original path of the workbook, or the path where the script is located if it is not saved
# Close the workbook 

2, sheet commonly used api

# Create a new worksheet 
xw.sheets.add(name= None ,before= None ,after= None )
 # Refer to the current active sheet
# Refer to a specified sheet 
sht=xw.books[ 'workbook name' ].sheets[ 'sheet name' ]
 # Activate sheet as the active worksheet
# Clear the content and formatting of the sheet
# Clear the contents of the sheet
# Get the name of the sheet
# delete sheet 

3. Range commonly used api

# Refer to the cell of the current active worksheet 
rng=xw.Range( 'A1' )
 # Add a hyperlink 
# rng.add_hyperlink(r'www.baidu.com','Baidu','Tips: Click to link to Baidu' ) 
# Get the address of the current range
# clear the contents of the range
# clear formatting and content
# Get the background color of the range and return the RGB value as a tuple
# Set the color of the range 
rng.color=( 255 , 255 , 255 )
 # Clear the background color of the range 
rng.color= None 
# Get the first column of the range
# Return the data of the cells in the range
# return current_region
# Return ctrl + direction 
rng.end( 'down' )
 # Get the formula or enter the formula 
rng.formula= '=SUM(B1:B5)' 
# Array formula
# Get the absolute address of the cell 
rng.get_address(row_absolute= True , column_absolute= True , include_sheetname= False , external= False )
 # Get the column width
# return the total width of the range
# Get the hyperlink of the range
# Get the last cell in the lower right corner of the range
# Range translation 
rng.offset(row_offset= 0 ,column_offset= 0 )
 #Resize the range to change the size of the range 
rng.resize(row_size= None ,column_size= None )
 # The first row of the range
# The height of the row, all rows are the same height, return row height, different return None
# return the total height of the range
# Return the number of rows and columns of the range
# Return the sheet where the range is located
#return all rows of range
# The first row of the range 
rng.rows[ 0 ]
 # The total number of rows in the range
# return all columns of range
# Returns the first column of the range 
rng.columns[ 0 ]
 # Returns the number of columns in the range
# The size of all ranges is adaptive
# All column widths are adaptive
# All row widths are adaptive 

Author: LuckyFrog
Link: /p/e21894fc5501
Source : Jianshu The
copyright belongs to the author.




You may also like...

Leave a Reply

Your email address will not be published.