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

In the previous article plug in the wings, let Excel fly up--xlwings (a) mentioned using the Xlwings module, using Python to operate Excel has the following advantages:

• 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

This article describes some common APIs for the Xlwings module, as described above

Common functions and methods of xlwings basic objects

2. Common APIs for book workbooks
   wb=xw.books[' Workbook name ']
   • Wb.activate () active as current workbook
   • Wb.fullname returns the absolute path to the workbook
   • Wb.name returns the name of the workbook
   • Wb.save (Path=none) Saves the workbook, the default path is the original path of the workbook, and if not saved, the path where the script is located
     -WB. Close () closes the workbook
     code example:

     # 引用Excel程序中，当前的工作簿wb=xw.books.acitve# 返回工作簿的绝对路径x=wb.fullname# 返回工作簿的名称x=wb.name# 保存工作簿，默认路径为工作簿原路径，若未保存则为脚本所在的路径x=wb.save(path=None)# 关闭工作簿x=wb.close()

4. Sheet Common APIs

    # 引用某指定sheet sht=xw.books[‘工作簿名称‘].sheets[‘sheet的名称‘] # 激活sheet为活动工作表 sht.activate() # 清除sheet的内容和格式 sht.clear() # 清除sheet的内容 sht.contents() # 获取sheet的名称 sht.name # 删除sheet sht.delete

5. Range Common APIs

    # refers to cell Rng=xw of the currently active worksheet. Range (' A1 ') # Add Hyperlink # Rng.add_hyperlink (R ' www.baidu.com ', ' Baidu ', ' hint: Click that link to Baidu ') # get the current range's address rng.address rng.get_ Address () # Clears the contents of range rng.clear_contents () # Clears the format and content rng.clear () # Gets the background color of range, returns the RGB value in tuples Rng.color # Sets the color of range Rng.col Or= (255,255,255) # Clears the background color of range Rng.color=none # Gets the first column of the Range column label Rng.column # returns the data for the cell in range Rng.count # return current_region rng  . current_region # Return CTRL + Direction rng.end (' down ') # get formula or input Formula Rng.formula= ' =sum (b1:b5) ' # array formula Rng.formula_array # get absolute address of cell Rng.get_address (Row_absolute=true, Column_absolute=true,include_sheetname=false, External=False) # Get column width rng.column_ Width # Returns the total width of range Rng.width # get a range Hyperlink Rng.hyperlink # Get the last cell in the lower-right corner of Range Rng.last_cell # Range pan Rng.offset (row_offs et=0,column_offset=0) #range进行resize改变range的大小 rng.resize (row_size=none,column_size=none) # Range The first row of row labels Rng.row # The height of the row, all rows as high returns the row height, not the same returns the total height of the range Rng.height # returns the number of rows and columns in range Rng.shape # returns the sheet Rng.sheet # where range is located # rng.row_height Returns all lines of range Rng.rows # rAnge first line Rng.rows[0] # range Total number of rows Rng.rows.count # returns all columns of range Rng.columns # returns the first column of range Rng.columns[0] # Returns the number of columns in range rng.co Lumns.count # Size of all range Adaptive rng.autofit () # All column width Adaptive rng.columns.autofit () # All row width Adaptive rng.rows.autofit ()

7. Books API for Workbook collections

    # 新建工作簿 xw.books.add() # 引用当前活动工作簿 xw.books.active

9. Sheets a collection of worksheets

    # 新建工作表 xw.sheets.add(name=None,before=None,after=None) # 引用当前活动sheet xw.sheets.active

Instance

Big Z teacher, teach a little z classmate how to use Python to manipulate Excel, using the first and second article of Knowledge, wrote a python small script, to small z students to demonstrate how to use Python to adjust cell line width, column width and background color, do some interesting things.

The works of the Great Z teacher. png

Little Z classmate after looking at so cliche but fun things, self-determined to modify the code, changed the color of the cell, and in the sheet inside the inscription, and then, there are new works:

Works of Little Z. png

The next lesson has the opportunity to teach small Z students, using Python's own time module, so that the static drawings and characters in Excel to move to become a gif-like picture.

Resources:
Xlwings Official documents

Luckyfrog
Links: Http://www.jianshu.com/p/b534e0d465f7
Source: Pinterest
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

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