Plug in wings and let Excel fly--xlwings (i)

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

2. 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 ()
4. 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 ()
6. 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

1. Reference workbook, note that the workbook should be opened first
   wb.=xw.books[' Workbook name ']
2. Referencing the active workbook
   Wb=xw.books.active
3. 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]
4. Reference Activity Sheet
   Sht=xw.sheets.active
5. 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 ')
6. 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

1. Store a single value
   # note ". Value"
   Sht.range (' A1 '). value=1
2. 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

1. Reading a single value
   # The value of A1 is read into the A variable
   A=sht.range (' A1 '). Value
2. 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)