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

Source: Internet
Author: User

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
  1. 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 ()
  2. 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 ()
  3. 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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.