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
- Common APIs for book workbooks
wb=xw.books[' Workbook name ']
- Sheet Common APIs
# 引用某指定sheet sht=xw.books[‘工作簿名称‘].sheets[‘sheet的名称‘] # 激活sheet为活动工作表 sht.activate() # 清除sheet的内容和格式 sht.clear() # 清除sheet的内容 sht.contents() # 获取sheet的名称 sht.name # 删除sheet sht.delete
- 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 ()
- Books API for Workbook collections
# 新建工作簿 xw.books.add() # 引用当前活动工作簿 xw.books.active
- 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)