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

https://www.kancloud.cn/gnefnuy/xlwings-docs/1127474

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():

xw.apps[10559].books['FileName.xlsx']
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'     
  os.system(i)

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

app.quit()

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

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

6. Get all the chart collections in the sheet

sheet.charts

7. Clear all data and formats in the table.

sheet.clear()

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

sheet.clear_contents()

9. Delete worksheet

sheet.delete()

10. Return table index (same as excel)

sheet.index

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

sheet.autofit(axis=None) 
# 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
rng=xw.Range('A1')
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
xw.apps[0].books[0].sheets[0].range('A1')
The swift way to reference a cell is
sht=xw.books['name'].sheets['name']
# A1 cell
rng=sht['A1']
rng=sht['a1']
# A1:B5 cells
rng=sht['A1:B5']
# The first column of the first row is a1
rng=sht[0,0]
# B1 cell 
rng=sht[0,1]

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
 slice 
fst_col = sht[ 0 ,:ncols].value

Eight, commonly used functions and methods

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

# new workbook
xw.books.add()
# Reference the currently active workbook
xw.books.active
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
wb=xw.books.acitve
# Return the absolute path of the workbook
x=wb.fullname
# return the name of the workbook
x=wb.name
# 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
x=wb.save(path=None)
# Close the workbook 
x=wb.close()

2, sheet commonly used api

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

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
rng.address
rng.get_address()
# clear the contents of the range
rng.clear_contents()
# clear formatting and content
rng.clear()
# Get the background color of the range and return the RGB value as a tuple
rng.color
# 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
rng.column
# Return the data of the cells in the range
rng.count
# return current_region
rng.current_region
# Return ctrl + direction 
rng.end( 'down' )
 # Get the formula or enter the formula 
rng.formula= '=SUM(B1:B5)' 
# Array formula
rng.formula_array
# 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
rng.column_width
# return the total width of the range
rng.width
# Get the hyperlink of the range
rng.hyperlink
# Get the last cell in the lower right corner of the range
rng.last_cell
# 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
rng.row
# The height of the row, all rows are the same height, return row height, different return None
rng.row_height
# return the total height of the range
rng.height
# Return the number of rows and columns of the range
rng.shape
# Return the sheet where the range is located
rng.sheet
#return all rows of range
rng.rows
# The first row of the range 
rng.rows[ 0 ]
 # The total number of rows in the range
rng.rows.count
# return all columns of range
rng.columns
# Returns the first column of the range 
rng.columns[ 0 ]
 # Returns the number of columns in the range
rng.columns.count
# The size of all ranges is adaptive
rng.autofit()
# All column widths are adaptive
rng.columns.autofit()
# All row widths are adaptive 
rng.rows.autofit()

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

References:

https://docs.xlwings.org/en/stable/index.html

https://www.kancloud.cn/gnefnuy/xlwings-docs/1127450

You may also like...

Leave a Reply

Your email address will not be published.