Python xlwings – API
https://docs.xlwings.org/en/stable/api.html # 官方文档
xlwings中各个类的关系图 :
xw – apps(app) – books(book) – sheets(sheet) – range(range_row,range_column)
; 顶级方法
1. xlwings.view(obj, sheet=None, table=True)
Opens a new workbook and displays an object on its first sheet by default. If you provide a sheet object, it will clear the sheet before displaying the object on the existing sheet.
>>> import xlwings as xw
>>> import pandas as pd
>>> import numpy as np
>>> df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
>>> xw.view(df)
xw.view(df)
lst = [1,2,3,4,5]
xw.view(lst)
2. xlwings.load(index=1, header=1)
Loads the selected cell(s) of the active workbook into a pandas DataFrame. If you select a single cell that has adjacent cells, the range is auto-expanded and turned into a pandas DataFrame. If you don’t have pandas installed, it returns the values as nested lists.
a = [1,2,3,4,5]
xw.view(a)
b = xw.load(0,0)
print(b)
'''
0 1 2 3 4
0 1.0 2.0 3.0 4.0 5.0
'''
对象方法
Apps – class xlwings.main.Apps(impl)
A collection of all app objects:
1. xw.apps
import xlwings as xw
app1 = xw.App()
app2 = xw.App()
print(xw.apps)
2. xw.active
Returns the active app.
print(xw.apps.active)
3. xw.apps.add()
Creates a new App. The new App becomes the active one. Returns an App object.
xw.apps.add()
print(xw.apps.add())
4. xw.apps.count
print(xw.apps.count)
5. xw.apps.keys()
Provides the PIDs of the Excel instances that act as keys in the Apps collection.
print(xw.apps.keys())
App – class xlwings.App(visible=None, spec=None, add_book=True, impl=None)
An app corresponds to an Excel instance. New Excel instances can be fired up like so:
1. xw.App()
>>> import xlwings as xw
>>> app1 = xw.App()
>>> app2 = xw.App(visible=False,add_book=False)
2. 实例对象.activate()
Activates the Excel app.
app1 = xw.App(visible=True,add_book=True)
app2 = xw.App(visible=True,add_book=True)
print(xw.apps.active)
app1.activate()
print(xw.apps.active)
3. 实例对象.api
Returns the native object (pywin32
or appscript
obj) of the engine being used.
print(app1.api)
4. 实例对象.books
A collection of all Book objects that are currently open.
print(app1.books)
5. 实例对象.calculate()
Calculates all open books.
app1.calculate()
6. 实例对象.calculaction
Returns or sets a calculation value that represents the calculation mode. Modes: 'manual'
, 'automatic'
, 'semiautomatic'
print(app1.calculation)
7. 实例对象.display_alerts
The default value is True. Set this property to False to suppress prompts and alert messages while code is running; when a message requires a response, Excel chooses the default response.
app1.display_alerts = False
8. 实例对象.hwnd
Returns the Window handle (Windows-only).
print(app1.hwnd)
9. 实例对象.kill()
Forces the Excel app to quit by killing its process.
app1.kill()
10. 实例对象.macro()
Runs a Sub or Function in Excel VBA that are not part of a specific workbook but e.g. are part of an add-in.
Examples
This VBA function:
Function MySum(x, y)
MySum = x + y
End Function
can be accessed like this:
>>> import xlwings as xw
>>> app = xw.App()
>>> my_sum = app.macro('MySum')
>>> my_sum(1, 2)
3
See also: Book.macro()
11. 实例对象.pid
Returns the PID of the app.
print(app1.pid)
12. 实例对象.quit()
Quits the application without saving any workbooks.
app1.quit()
13. 实例对象.range()
Range object from the active sheet of the active book, see Range()
.
print(app1.range('A1'))
14. 实例对象.screen_updaing()
Turn screen updating off to speed up your script. You won’t be able to see what the script is doing, but it will run faster. Remember to set the screen_updating property back to True when your script ends.
app1.screen_updating = False
15. 实例对象.selection
app1.range('A1:A3').select()
print(app1.selection)
16. 实例对象.startup_path
Returns the path to XLSTART
which is where the xlwings add-in gets copied to by doing xlwings addin install
.
print(app1.startup_path)
17. 实例对象.status_bar
Gets or sets the value of the status bar. Returns False
if Excel has control of it.
18. 实例对象.version
Returns the Excel version number object.
print(app1.version)
19. 实例对象.visible
print(app1.visible)
Books – class xlwings.main.Books(impl)
A collection of all book
objects:
1. xw.books
print(xw.books)
2. 实例对象.active
import xlwings as xw
app1 = xw.App(visible=True,add_book=True)
wb = app1.books
print(wb)
print(wb.active)
3. 实例对象.add()
Creates a new Book. The new Book becomes the active Book. Returns a Book object.
app1 = xw.App(visible=True,add_book=True)
wb = app1.books
wb.add()
print(app1.books)
print(app1.books[0])
4. 实例对象.open()
open
( fullname, update_links=None, read_only=None, format=None, password=None, write_res_password=None, ignore_read_only_recommended=None, origin=None, delimiter=None, editable=None, notify=None, converter=None, add_to_mru=None, local=None, corrupt_load=None)
Opens a Book if it is not open yet and returns it. If it is already open, it doesn’t raise an exception but simply returns the Book object.
import xlwings as xw
app1 = xw.App(visible=False,add_book=False)
app1.books.open(r'C:\Users\Administrator\Desktop\1.xlsx')
print(app1.books)
app1.kill()
Book – class xlwings.Book
A book object is a member of the books
collection:
1. xw.Book
The easiest way to connect to a book is offered by xw.Book
: it looks for the book in all app instances and returns an error, should the same book be open in multiple instances. To connect to a book in the active app instance, use xw.books
and to refer to a specific app, use:
import xlwings as xw
wb = xw.Book()
xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx')
2. 实例对象.activate()
wb = xw.Book()
print(xw.books.active)
wb2 = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx')
wb2.activate()
print(xw.books.active)
3. 实例对象.api
print(wb2.api)
4. 实例对象.app
Returns an app object that represents the creator of the book.
print(wb2.app)
5, classmethod caller
()
References the calling book when the Python function is called from Excel via RunPython
. Pack it into the function being called from Excel, e.g.:
import xlwings as xw
def my_macro():
wb = xw.Book.caller()
wb.sheets[0].range('A1').value = 1
To be able to easily invoke such code from Python for debugging, use xw.Book.set_mock_caller()
.
6. 实例对象.close()
Closes the book without saving it.
wb.close()
7. 实例对象.fullname
print(wb2.fullname)
8. 实例对象.macro(name)
Runs a Sub or Function in Excel VBA.
Parameters:
name
(Name of Sub or Function with or without module name, e.g. 'Module1.MyMacro'
or 'MyMacro'
) –
Examples
This VBA function:
Function MySum(x, y)
MySum = x + y
End Function
can be accessed like this:
>>> import xlwings as xw
>>> wb = xw.books.active
>>> my_sum = wb.macro('MySum')
>>> my_sum(1, 2)
3
See also: App.macro()
9. 实例对象.name
print(wb2.name)
10. 实例对象.names
Returns a names collection that represents all the names in the specified book (including all sheet-specific names).
11. 实例对象.save(path=None)
Saves the Workbook. If a path is being provided, this works like SaveAs() in Excel. If no path is specified and if the file hasn’t been saved previously, it’s being saved in the current working directory with the current filename. Existing files are overwritten without prompting.
wb2.save()
wb2.save(r'C:\Users\Administrator\Desktop\123.xlsx')
12. 实例对象.selection
wb2.sheets[0].range('a3').select()
print(wb2.selection)
13. 实例对象.set_mock_caller()
Sets the Excel file which is used to mock xw.Book.caller()
when the code is called from Python and not from Excel via RunPython
.
Examples
This code runs unchanged from Excel via RunPython and from Python directly
import os
import xlwings as xw
def my_macro():
sht = xw.Book.caller().sheets[0]
sht.range('A1').value = 'Hello xlwings!'
if __name__ == '__main__':
xw.Book('file.xlsm').set_mock_caller()
my_macro()
14. 实例对象.sheets
print(wb2.sheets)
15. 实例对象.to_pdf(path=None, include=None, exclude=None)
Exports the whole Excel workbook or a subset of the sheets to a PDF file. If you want to print hidden sheets, you will need to list them explicitely under include
.
wb2.to_pdf(r'C:\Users\Administrator\Desktop\222',exclude=['Sheet2'])
Sheets – class xlwings.main.Sheets(impl)
A collection of all sheet
objects:
1. xw.sheets
返回激活状态的workbook里的sheet
wb = xw.Book()
wb2 = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx')
print(xw.sheets)
2. 实例对象.active
import xlwings as xw
wb = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx')
sht = wb.sheets
print(sht)
print(sht.active)
3. 实例对象.add(name=None, before=None, after=None)
Creates a new Sheet and makes it the active sheet.
sht.add('hello',before='Sheet1')
Sheet – class xlwings.Sheet(sheet=None, impl=None)
A sheet object is a member of the sheets
collection:
1. 实例对象.activate()
wb = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx')
sht = wb.sheets
print(sht)
print(sht.active)
sht.add('hello',before='Sheet1')
sht[-1].activate()
print(sht.active)
2. 实例对象.api
print(sht.api)
3. 实例对象.autofit(axis=None)
Autofits the width of either columns, rows or both on a whole Sheet.
>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.sheets['Sheet1'].autofit('c')
>>> wb.sheets['Sheet1'].autofit('r')
>>> wb.sheets['Sheet1'].autofit()
4. 实例对象.book
print(sht[0].book)
5. 实例对象.cells
Returns a Range object that represents all the cells on the Sheet (not just the cells that are currently in use).
print(sht[0].cells)
6. 实例对象.charts
…
7. 实例对象.clear()
Clears the content and formatting of the whole sheet.
wb = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx')
sht = wb.sheets
sht[-1].clear()
8. 实例对象.clear_contents()
Clears the content of the whole sheet but leaves the formatting.
9. 实例对象.copy(before=None, after=None, name=None)
如果excel打开的时候是已共享状态是无法copy给自己的
Copy a sheet to the current or a new Book. By default, it places the copied sheet after all existing sheets in the current Book. Returns the copied sheet.
Examples
first_book = xw.Book()
second_book = xw.Book()
first_book.sheets[0]['A1'].value = 'some value'
first_book.sheets[0].copy()
first_book.sheets[0].copy(name='copied')
first_book.sheets[0].copy(after=second_book.sheets[0])
wb = xw.Book(r'C:\Users\Administrator\Desktop\2.xlsx')
sht = wb.sheets
sht[-1].copy(after=sht[-1],name='hello')
10. 实例对象.delete()
sht[-1].delete()
11. 实例对象.index
Returns the index of the Sheet (1-based as in Excel).
print(sht[-1].index)
print(sht[0].index)
12. 实例对象.name
Gets or sets the name of the Sheet.
print(sht[0].name)
sht[0].name = 'hello'
print(sht[0].name)
13. 实例对象.names
Returns a names collection that represents all the sheet-specific names (names defined with the “SheetName!” prefix).
…
14. 实例对象.pictures
15. 实例对象.range(cell1, cell2=None)
Returns a Range object from the active sheet of the active book, see Range()
.
print(sht[0].range('A1:A10')) #
16 . 实例对象.select()
Selects the Sheet. Select only works on the active book.
sht[0].select()
17. 实例对象.shapes
print(sht[0].shapes)
18. 实例对象.tables
…
19. 实例对象.to_pdf(path=None)
Exports the sheet to a PDF file.
sht[0].to_pdf(r'C:\Users\Administrator\Desktop\2')
20. 实例对象.used_range
print(sht[0].used_range)
21. 实例对象.visible
Gets or sets the visibility of the Sheet (bool).
Range – class xlwings.Range(cell1=None, cell2=None, **options)
Returns a Range object that represents a cell or a range of cells.
wb = xw.Book()
print(xw.Range('A1'))
print(xw.Range('A1:C3'))
print(xw.Range((1, 1)))
print(xw.Range((1, 1), (3, 3)))
print(xw.Range(xw.Range('A1'), xw.Range('B2')))
1. 实例对象.add_hyperlink(address, text_to_display=None, screen_tip=None)
Adds a hyperlink to the specified Range (single Cell)
2. 实例对象.address
Returns a string value that represents the range reference. Use get_address()
to be able to provide paramaters.
wb = xw.Book()
rng = xw.Range('A1')
print(rng.address)
print(rng.get_address())
3. 实例对象.api
Returns the native object (pywin32
or appscript
obj) of the engine being used.
print(rng.api)
4. 实例对象.autofit()
Autofits the width and height of all cells in the range.
- To autofit only the width of the columns use
xw.Range('A1:B2').columns.autofit()
- To autofit only the height of the rows use
xw.Range('A1:B2').rows.autofit()
rng.value = 'AAAAAAAAAAAAAAAAAA1'
rng.autofit()
5. 实例对象.clear()
Clears the content and the formatting of a Range.
6. 实例对象.clear_contents()
Clears the content of a Range but leaves the formatting.
7. 实例对象.color
Gets and sets the background color of the specified Range.
To set the color, either use an RGB tuple (0, 0, 0)
or a color constant. To remove the background, set the color to None
, see Examples.
Examples
>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').color = (255,255,255)
>>> xw.Range('A2').color
(255, 255, 255)
>>> xw.Range('A2').color = None
>>> xw.Range('A2').color is None
True
wb = xw.Book()
rng = xw.Range('A1')
print(rng.color)
print(rng.color is None)
rng.color = (100,255,100)
print(rng.color)
8. 实例对象.column / row
Returns the number of the first column in the in the specified range. Read-only.
wb = xw.Book()
rng = xw.Range('C3')
print(rng.column)
print(rng.row)
9. 实例对象.column_width / row_height
Gets or sets the width, in characters, of a Range. One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.
If all columns in the Range have the same width, returns the width. If columns in the Range have different widths, returns None.
column_width must be in the range: 0
Original: https://blog.csdn.net/zhaoleiedu/article/details/115416224
Author: EricZHAOedu
Title: Python xlwings – API
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/753898/
转载文章受原作者版权保护。转载请注明原作者出处!