Python operation Excel module, the module mentioned on the net: xlwings, Xlrd, XLWT, OPENPYXL, PYXLL, they provide the function summed up there are two: first, read and write Excel file with Python, is actually read and write formatted text file, operation Excel file and operation text, CSV file no difference, Excel file is only used to store data. In addition to operating data, you can also adjust the Excel file table width, font color and so on. Another thing to mention is that using COM to invoke Excel's API to manipulate Excel documents is also possible, rather cumbersome and basic without distinction between VBA.
Features of Xlwings
- Xlwings is very easy to read and write data in Excel files, and can be modified in cell format.
- Seamless connection to Matplotlib and pandas
- You can call a program written in VBA in an Excel file, or you can have VBA invoke a program written in Python.
- Open source is free and has been updated
Basic Operations Xlwings Basic objects
- Open a saved Excel document
# import xlwings module, open Excel program, default settings: program visible, only open not new workbook, screen update off
Import Xlwings as XW
App=xw. APP (Visible=true,add_book=false)
App.display_alerts=false
App.screen_updating=false
# File Location: filepath, open test document, then save, close, end program
Filepath=r ' G:\Python scripts\test.xlsx '
Wb=app.books.open (filepath)
Wb.save ()
Wb.close ()
App.Quit ()
- Create a new Excel document named Test.xlsx, and save it in the D drive.
Import Xlwings as XW
App=xw. APP (Visible=true,add_book=false)
Wb=app.books.add ()
Wb.save (R ' d:\test.xlsx ')
Wb.close ()
App.Quit ()
- Enter a value in a cell
Create a new test.xlsx, enter "life" in the first cell of Sheet1, then save the close and exit the Excel program.
Import Xlwings as XW
App=xw. APP (Visible=true,add_book=false)
Wb=app.books.add ()
# WB is the new Workbook (workbook), which assigns values to the A1 cell of the WB Sheet1
Wb.sheets[' Sheet1 '].range (' A1 '). Value= ' Life '
Wb.save (R ' d:\test.xlsx ')
Wb.close ()
App.Quit ()
Open the saved test.xlsx, enter "bitter short" in the second cell of Sheet2, then save Close, exit the Excel program
Import Xlwings as XW
App=xw. APP (Visible=true,add_book=false)
Wb=app.books.open (R ' d:\test.xlsx ')
# WB is the new Workbook (workbook), which assigns values to the A1 cell of the WB Sheet1
Wb.sheets[' Sheet1 '].range (' A1 '). value= ' Bitter short '
Wb.save ()
Wb.close ()
App.Quit ()
Master the above code, you can use Excel as a txt text for data storage, you can read the Excel file data, after the calculation, and save the results in Excel.
referencing workbooks, worksheets, and cells
- Reference workbook, note that the workbook should be opened first
wb.=xw.books[' Workbook name ']
- Referencing the active workbook
Wb=xw.books.active
- Referencing a sheet in a workbook
sht=xw.books[' Workbook name '].sheets[' sheet's name ']
# or
wb=xw.books[' Workbook name ']
Sht=wb.sheets[sheet's name]
- Reference Activity Sheet
Sht=xw.sheets.active
- Referencing A1 cells
rng=xw.books[' Workbook name '].sheets[' sheet's name ']
# or
sht=xw.books[' Workbook name '].sheets[' sheet's name ']
Rng=sht.range (' A1 ')
- Referencing cells on the active sheet
# Note the range first letter capitalization
Rng=xw. Range (' A1 ')
It is important to note that the full reference path to the cell is:
# first Sheet first cell of first workbook in Excel program
Xw.apps[0].books[0].sheets[0].range (' A1 ')
The way to quickly refer to a cell is
sht=xw.books[' name '].sheets[' name ']
# A1 Cell
rng=sht[' A1 ']
# a1:b5 Cell
rng=sht[' A1:b5 ']
# in line i+1, cell of column j+1
# B1 Cell
rng=sht[0,1]
# A1:J10
RNG=SHT[:10,:10]
PS: For cells can also be referenced by a tuple representing a row and column
# A1 Reference to cell
Xw. Range (+)
#A1: reference to Cell C3
Xw. Range ((+), (3,3))
Storing data
- Store a single value
# note ". Value"
Sht.range (' A1 '). value=1
- Storage List
# Store the list [A1:C1] in the
Sht.range (' A1 '). value=[1,2,3]
# Store the list [A1:A3] in the
Sht.range (' A1 '). Options (Transpose=true). value=[1,2,3]
# The 2x2 table, the two-dimensional array, is stored in the A1:B2, such as the first row, and the second row 3,4
Sht.range (' A1 '). Options (expand= ' table ') =[[1,2],[3,4]
Reading data
- Reading a single value
# The value of A1 is read into the A variable
A=sht.range (' A1 '). Value
- To read a value into the list
#将A1到A2的值, reading to the A list
A=sht.range (' a1:a2 '). Value
# reads the data of the first row and the second row as a two-dimensional array
A=sht.range (' a1:b2 '). Value
Luckyfrog
Links: https://www.jianshu.com/p/e21894fc5501
Source: Pinterest
Plug in wings and let Excel fly--xlwings (i)