Python xlwings - API

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) # 通过excel显示df
lst = [1,2,3,4,5]
xw.view(lst) # 显示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.

# 将xw打开的books数据转成dataframe形式
a = [1,2,3,4,5]
xw.view(a)
b = xw.load(0,0) # 指定index=0,header=0,默认都是1开始,
print(b) # <class 'pandas.core.frame.DataFrame'>
'''
     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) # Apps([<Excel App 6392>, <Excel App 6664>])

2. xw.active

Returns the active app.

print(xw.apps.active) # <Excel App 3172>

3. xw.apps.add()

Creates a new App. The new App becomes the active one. Returns an App object.

xw.apps.add() # 新建一个apps
print(xw.apps.add()) # 在新建的同时, 拿到返回值 <Excel App 5296>

4. xw.apps.count

print(xw.apps.count) # 4

5. xw.apps.keys()

Provides the PIDs of the Excel instances that act as keys in the Apps collection.

print(xw.apps.keys()) # [1532, 2704, 8092, 7504]

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()

# 实例化excel对象
>>> import xlwings as xw
>>> app1 = xw.App()
>>> app2 = xw.App(visible=False,add_book=False) # visible 是否可见, add_book 是否增加一个页面

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) # <Excel App 4216>
app1.activate()
print(xw.apps.active) # <Excel App 2556>

3. 实例对象.api

Returns the native object (pywin32 or appscript obj) of the engine being used.

print(app1.api) # <win32com.gen_py.Microsoft Excel 16.0 Object Library._Application instance at 0x39494032>

4. 实例对象.books

A collection of all Book objects that are currently open.

print(app1.books) # Books([<Book [工作簿1]>])

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) # automatic

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 # 类似VBA中的display_alerts

8. 实例对象.hwnd

Returns the Window handle (Windows-only).

print(app1.hwnd) # 1115562

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) # 7504

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')) # <Range [工作簿1]Sheet1!$A$1>

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) # <Range [工作簿1]Sheet1!$A$1:$A$3>

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) # C:\Users\Administrator\AppData\Roaming\Microsoft\Excel\XLSTART

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) # 16.0

19. 实例对象.visible

print(app1.visible) # True

Books - class xlwings.main.Books(impl)

A collection of all book objects:

1. xw.books

print(xw.books) # Books([<Book [工作簿1]>])

2. 实例对象.active

import xlwings as xw

app1 = xw.App(visible=True,add_book=True)
wb = app1.books
print(wb) # Books([<Book [工作簿2]>])
print(wb.active) # <Book [工作簿2]>

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) # Books([<Book [工作簿1]>, <Book [工作簿2]>])
print(app1.books[0]) # <Book [工作簿1]>

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) # visible=False不可见,add_book=False不会新增一个workbook
app1.books.open(r'C:\Users\Administrator\Desktop\1.xlsx') # 打开绝对地址上的一个文件
print(app1.books) # Books([<Book [1.xlsx]>])
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() # 创建一个空的workbook
xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx') # 打开一个workbook

2. 实例对象.activate()

wb = xw.Book() # 创建一个空的workbook
print(xw.books.active) # <Book [工作簿2]>
wb2 = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx') # 打开一个workbook
wb2.activate()
print(xw.books.active) # <Book [1.xlsx]>

3. 实例对象.api

print(wb2.api) # <win32com.gen_py.None.Workbook>

4. 实例对象.app

Returns an app object that represents the creator of the book.

print(wb2.app) # <Excel App 3788>

5, classmethodcaller()

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() # 会留下一个excel的对象

7. 实例对象.fullname

print(wb2.fullname) # C:\Users\Administrator\Desktop\1.xlsx

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) # 1.xlsx

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') # save as

12. 实例对象.selection

wb2.sheets[0].range('a3').select()
print(wb2.selection) # <Range [1.xlsx]Sheet1!$A$3>

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) # Sheets([<Sheet [1.xlsx]Sheet1>, <Sheet [1.xlsx]Sheet2>])

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']) # 不需要加后缀.pdf, exclude去掉不需要打的sheet,include可以包含隐藏的sheet

Sheets - class xlwings.main.Sheets(impl)

A collection of all sheet objects:

1. xw.sheets

返回激活状态的workbook里的sheet

wb = xw.Book() # 创建一个空的workbook
wb2 = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx') # 打开一个workbook
print(xw.sheets) # 返回wb2的sheets对象 Sheets([<Sheet [1.xlsx]Sheet1>, <Sheet [1.xlsx]Sheet2>])

2. 实例对象.active

import xlwings as xw

wb = xw.Book(r'C:\Users\Administrator\Desktop\1.xlsx')
sht = wb.sheets
print(sht) # Sheets([<Sheet [1.xlsx]Sheet1>, <Sheet [1.xlsx]Sheet2>])
print(sht.active) # <Sheet [1.xlsx]Sheet1>

3. 实例对象.add(name=None, before=None, after=None)

Creates a new Sheet and makes it the active sheet.

sht.add('hello',before='Sheet1') # 在Sheet1 之前插入一张hello的sheet

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) # <Sheet [1.xlsx]Sheet1>
sht.add('hello',before='Sheet1')
sht[-1].activate()
print(sht.active) # <Sheet [1.xlsx]Sheet2>

2. 实例对象.api

print(sht.api) # <win32com.gen_py.Microsoft Excel 16.0 Object Library.Sheets instance at 0x34971952>

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) # 根据sheet返回workbook_name <Book [1.xlsx]>

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) # <Range [1.xlsx]hello!$1:$1048576>

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() # 清空所有content和format

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

# Create two books and add a value to the first sheet of the first book
first_book = xw.Book()
second_book = xw.Book()
first_book.sheets[0]['A1'].value = 'some value'

# Copy to same Book with the default location and name
first_book.sheets[0].copy()

# Copy to same Book with custom sheet name
first_book.sheets[0].copy(name='copied') 

# Copy to second Book requires to use before or after
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) # 1 
print(sht[0].index) # 1

12. 实例对象.name

Gets or sets the name of the Sheet.

print(sht[0].name) # hello_pandas
sht[0].name = 'hello' # 修改了sheet的name
print(sht[0].name) # hello

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')) # <Range [2.xlsx]hello!$A$1:$A$10>

16 . 实例对象.select()

Selects the Sheet. Select only works on the active book.

sht[0].select()

17. 实例对象.shapes

print(sht[0].shapes) # Shapes([<Shape 'Picture 2' in <Sheet [2.xlsx]hello_pandas>>, <Shape 'Picture 4' in <Sheet [2.xlsx]hello_pandas>>])

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) # <Range [2.xlsx]hello_pandas!$A$1:$E$4>

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')) # <Range [工作簿1]Sheet1!$A$1>
print(xw.Range('A1:C3')) # <Range [工作簿1]Sheet1!$A$1:$C$3>
print(xw.Range((1, 1))) # <Range [工作簿1]Sheet1!$A$1>
print(xw.Range((1, 1), (3, 3))) # <Range [工作簿1]Sheet1!$A$1:$C$3>
# print(xw.Range('NamedRange')) # 如果有命名的range的话是可以找打的
print(xw.Range(xw.Range('A1'), xw.Range('B2'))) # <Range [工作簿1]Sheet1!$A$1:$B$2>

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) # $A$1
print(rng.get_address()) # $A$1

3. 实例对象.api

Returns the native object (pywin32 or appscript obj) of the engine being used.

print(rng.api) # <win32com.gen_py.Microsoft Excel 16.0 Object Library.Range instance at 0x39725040>

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) # None 空白
print(rng.color is None) # True
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) # 3
print(rng.row) # 3

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 <= column_width <= 255

Note: If the Range is outside the used range of the Worksheet, and columns in the Range have different widths, returns the width of the first column.

wb = xw.Book()
rng = xw.Range('C3')
print(rng.column)
print(rng.column_width) # 8.38
rng.column_width = 17
print(rng.column_width) # 17
rng.row_height = 52
print(rng.row_height) # 52

10. 实例对象.columns / rows

Returns a RangeColumns object that represents the columns in the specified range.

rng = xw.Range('C3:E10')

print(rng.columns) # RangeColumns(<Range [工作簿1]Sheet1!$C$3:$E$10>)
print(rng.rows) # RangeRows(<Range [工作簿1]Sheet1!$C$3:$E$10>)

11. 实例对象.copy(destination=None)

Copy a range to a destination range or clipboard.

wb = xw.Book()
rng = xw.Range('C3:E3')
rng.value = 100
rng.copy(xw.Range('D4')) # 复制到D4

12. 实例对象.count

Returns the number of cells.

13. 实例对象.current_region

This property returns a Range object representing a range bounded by (but not including) any combination of blank rows and blank columns or the edges of the worksheet. It corresponds to Ctrl-* on Windows and Shift-Ctrl-Space on Mac.

wb = xw.Book()
rng = xw.Range('C3:E3')
rng.select()
print(rng.current_region) # <Range [工作簿1]Sheet1!$C$3>
print(rng.current_region.address) # $C$3

14. 实例对象.delete(shift=None)

shift (str*,* default None) – Use left or up. If omitted, Excel decides based on the shape of the range.

wb = xw.Book()
rng = xw.Range('C3:D6')
rng.value = 100
xw.Range('C3').delete(shift='left')
xw.Range('C3').delete(shift='up')

15. 实例对象.end(direction)

Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing Ctrl+Up, Ctrl+down, Ctrl+left, or Ctrl+right.

# 选择到这个有值的区域里面的边界位置
import xlwings as xw
wb = xw.Book()
xw.Range('A1:B2').value = 1
xw.Range('A1').end('down').select()
xw.Range('B2').end('right')

wb = xw.Book()
xw.Range('A1:C10').value = 100
rng = xw.Range('a1').end('right')
print(rng.end('down').row) # 最大行

16. 实例对象.expand(mode=‘table’)

Expands the range according to the mode provided. Ignores empty top-left cells (unlike Range.end()).

wb = xw.Book()
xw.Range('A1').value = [[None, 1], [2, 3]]
print(xw.Range('A1').expand('table').address) # $A$1:$B$2
print(xw.Range('A1').expand('right').address) # $A$1:$B$1
print(xw.Range('A1').expand('down').address) # $A$1:$A$2

wb = xw.Book()
xw.Range('A1').value = [[None, 1], [2, 3]]
xw.Range('A1').expand('table').value = 100 # 将$A$1:$B$2赋值为100

17. 实例对象.formula

Gets or sets the formula for the given Range.

wb = xw.Book()
xw.Range('A1').value = [[100, 1], [2, 3]]
xw.Range('C1').formula = "= A1 + B1" # C1 = 101
print(xw.Range('C1').formula) # = A1 + B1

18. 实例对象.formula2

…

19. 实例对象.formula_array

…

20. 实例对象.get_address(row_absolute=True, column_absolute=True, include_sheetname=False, external=False)

Returns the address of the range in the specified format. address can be used instead if none of the defaults need to be changed.

# 如果不需要设置一些参数,与address的返回值是一样的
import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range((1,1)).get_address()
'$A$1'
>>> xw.Range((1,1)).get_address(False, False) # 取消绝对值输出
'A1'
>>> xw.Range((1,1), (3,3)).get_address(True, False, True) # 加上sheet_name
'Sheet1!A$1:C$3'
>>> xw.Range((1,1), (3,3)).get_address(True, False, external=True) # 外部可以引用的格式
'[Book1]Sheet1!A$1:C$3'

21. 实例对象.hyperlink

Returns the hyperlink address of the specified Range (single Cell only)

22. 实例对象.insert(shift=None, copy_origin=‘format_from_left_or_above’)

Insert a cell or range of cells into the sheet.

wb = xw.Book()
rng = xw.Range('A1:C3')
rng.value = 100
xw.Range('A1').insert(shift='right') # 插入一格, 原数据往右
xw.Range('A1').insert(shift='down') # # 插入一格, 原数据往下

23. 实例对象.last_cell

wb = xw.Book()
rng = xw.Range('A1:C3')
rng.value = 100
print(rng.last_cell)	# <Range [工作簿1]Sheet1!$C$3>
print(rng.last_cell.row) # 3
print(rng.last_cell.column) # 3

24. 实例对象.left

Returns the distance, in points, from the left edge of column A to the left edge of the range. Read-only.

wb = xw.Book()
rng = xw.Range('B1:E3')
rng.value = 100
print(rng.left) # 54 一格54的宽度

25 . 实例对象.merge(across=False)

Creates a merged cell from the specified Range object.

wb = xw.Book()
rng = xw.Range('A1:E6')
rng.value = 100
xw.Range('A1:B3').merge(across=True) # across=True 只合并column,row不合并
xw.Range('A4:C6').merge(across=False) # across= False 合并row和column

26. 实例对象.merge_area

Returns a Range object that represents the merged Range containing the specified cell. If the specified cell isn’t in a merged range, this property returns the specified cell.

wb = xw.Book()
rng = xw.Range('A1:E6')
rng.value = 100
xw.Range('A1:B3').merge(across=True) # across=True 只合并column,row不合并
xw.Range('A4:C6').merge(across=False) # across= False 合并row和column
print(xw.Range('A1').merge_area) # 返回这个单元格涉及到的merge区域, <Range [工作簿1]Sheet1!$A$1:$B$1>
print(xw.Range('E1').merge_area) # 若没有merge区域,返回自己 <Range [工作簿1]Sheet1!$E$1>

27. 实例对象.merge_cells

Returns True if the Range contains merged cells, otherwise False

wb = xw.Book()
rng = xw.Range('A1:E6')
rng.value = 100
xw.Range('A1:B3').merge(across=True) # across=True 只合并column,row不合并
xw.Range('A4:C6').merge(across=False) # across= False 合并row和column
print(xw.Range('A1:B1').merge_cells) # True 本身就是一个merge
print(xw.Range('A1:E1').merge_cells) # None 中间包括了merge
print(xw.Range('F1:G1').merge_cells) # False 不是merge

28. 实例对象.name

wb = xw.Book()
rng = xw.Range('A1:E6')
print(rng.name) # None
rng.name = 'xx'
print(rng.name) # <Name 'xx': =Sheet1!$A$1:$E$6>

29. 实例对象.number_fromat

wb = xw.Book()
rng = xw.Range('A1:E6')
rng.value = 1.11111
print(rng.number_format) # G/通用格式
rng.number_format = '0.00%'
print(rng.number_format) # 0.00%

30. 实例对象.offset(row_offset=0, column_offset=0)

Returns a Range object that represents a Range that’s offset from the specified range.

wb = xw.Book()
rng = xw.Range('A1')
print(rng.offset(row_offset=2, column_offset=2).address) # $C$3

31. 实例对象.options()

Allows you to set a converter and their options. Converters define how Excel Ranges and their values are being converted both during reading and writing operations. If no explicit converter is specified, the base converter is being applied, see Converters and Options.

https://docs.xlwings.org/en/stable/converters.html#converters

1. ndim - 将数字转为1 / 2维

wb = xw.Book()
rng = xw.Range('A1')
rng.value = [[1, 2], 
             [3, 4]]
print(rng.options(ndim=1).value) # [1.0]
print(rng.options(ndim=2).value) # [[1.0]]
print(xw.Range('A1:B1').options(ndim=1).value) # [1.0, 2.0]
print(xw.Range('A1:B1').options(ndim=2).value) # [[1.0, 2.0]]

2. numbers - 转数字类型为float / int

与number_format的区别, number_format会转变excel里显示的值, options不转变

wb = xw.Book()
rng = xw.Range('A1')
rng.value = 1
print(rng.options(numbers=int).value) # 1
print(rng.options(numbers=float).value) # 1.0

3. dates - 转时间

By default cells with dates are read as datetime.datetime, but you can change it to datetime.date:

import datetime
import xlwings as xw

wb = xw.Book()
rng = xw.Range('A1')
rng.value = '2020-1-1'
print(rng.options(dates=datetime.datetime).value) # 2020-01-01 00:00:00
print(rng.options(dates=datetime.date).value) # 2020-01-01

4. empty - 转空白

wb = xw.Book()
rng = xw.Range('A1')
print(rng.options().value) # None
print(rng.options(empty='NA').value) # NA
print(rng.options(empty=123).value) # 123

5. transpose - 转置

wb = xw.Book()
rng = xw.Range('A1')
rng.options(transpose=True).value = [1,2,3,4,5] # 将list存在row上

6. expand - 扩大活动范围

This works the same as the Range properties table, vertical and horizontal but is only evaluated when getting the values of a Range:

>>> import xlwings as xw
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = [[1,2], [3,4]]
>>> rng1 = sht.range('A1').expand()
>>> rng2 = sht.range('A1').options(expand='table')
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> sht.range('A3').value = [5, 6]
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

7. Dictionary converter - 转字典

The dictionary converter turns two Excel columns into a dictionary. If the data is in row orientation, use transpose:

wb = xw.Book()
rng = xw.Range('A1')
rng.value = [['a',1],['b',2]]
# A 1
# B 2
print(xw.Range('A1:B2').options(dict).value)
rng = xw.Range('D1')
rng.value = [['a','b'],[1,2]]
# A B
# 1 2
print(xw.Range('D1:E2').options(dict,transpose=True).value)

8. Numpy array converter

…

9. Pandas Series converter

…

10. Pandas DataFrame converter

…

32. 实例对象.paste(paste=None, operation=None, skip_blanks=False, transpose=False)

wb = xw.Book()
rng = xw.Range('A1')
rng.value = [['a',1],['b',2]]
rng.expand().copy()
xw.Range('E3').paste()

33. 实例对象.row_value

…

34. 实例对象.resize(row_size=None, column_size=None)

wb = xw.Book()
rng = xw.Range('A1')
rng1 =rng.resize(2,2)
print(rng1.address) # $A$1:$B$2

35. 实例对象.select()

36. 实例对象.shape()

Tuple of Range dimensions.

wb = xw.Book()
rng = xw.Range('A1')
print(rng.shape) # (1,1)

37. 实例对象.sheet

wb = xw.Book()
rng = xw.Range('A1')
print(rng.sheet) # <Sheet [工作簿1]Sheet1>

38. 实例对象.size

wb = xw.Book()
rng = xw.Range('A1')
print(rng.size) # 1

39. 实例对象.table

40. 实例对象.top

Returns the distance, in points, from the top edge of row 1 to the top edge of the range. Read-only.

# 地址的top距离第一行的距离
wb = xw.Book()
rng = xw.Range('E3')
print(rng.height * 2) # 28.5
print(rng.top) # 28.5

41. 实例对象.unmerge()

import xlwings as xw
import time

wb = xw.Book()
rng = xw.Range('A1:E3')
rng.merge()
time.sleep(2)
rng = xw.Range('A1:B2')
rng.unmerge()

42. 实例对象.value

43. 实例对象.width

RangeRows / RangeColumns - class xlwings.RangeRows(rng) / class xlwings.RangeColumns(rng)

Represents the rows of a range. Do not construct this class directly, use Range.rows instead.

Represents the columns of a range. Do not construct this class directly, use Range.columns instead.

wb = xw.Book()
rng = xw.Range('A1:C4')
print(rng.rows[0]) # <Range [工作簿1]Sheet1!$A$1:$C$1>
print(rng.rows[1]) # <Range [工作簿1]Sheet1!$A$2:$C$2>
print(rng.rows[2]) # <Range [工作簿1]Sheet1!$A$3:$C$3>
print(rng.rows[-1]) # <Range [工作簿1]Sheet1!$A$4:$C$4>
print(rng.rows[-2]) # <Range [工作簿2]Sheet1!$A$3:$C$3>

columns对象雷同

1. 实例对象.autofit()

Autofits the height of the rows / columns.

wb = xw.Book()
rng = xw.Range('A1:C4')
rng.rows.autofit() # 对象素有的行自动行高
rng.columns.autofit()

2. 实例对象.count

wb = xw.Book()
rng = xw.Range('A1:C4')
print(rng.rows.count) # 4
print(rng.columns.count) # 4

Shapes - class xlwings.main.Shapes(impl)

A collection of all shape objects on the specified sheet:

1. 实例对象.api

2. 实例对象.count

Shape

Pictures - class xlwings.main.Pictures(impl)

A collection of all picture objects on the specified sheet:

1. add(image, link_to_file=False, save_with_document=True, left=0, top=0, width=None, height=None, name=None, update=False, scale=1)

Adds a picture to the specified sheet.

# 插入单张图片,插入后修改图片内的位置和大小
import xlwings as xw
import os

wb = xw.Book() # 创建一个空的workbook
xw.Range('C1').column_width = 17
xw.Range('C1:C2').row_height = 52
wb.sheets[0].pictures.add(r'C:\Users\Administrator\Desktop\12345.jpg',left=17,top=10,width=100,
                          height=100,name='hello')
wb.sheets[0].pictures['hello'].left = wb.sheets[0].range('c1').left + 2
wb.sheets[0].pictures['hello'].top = wb.sheets[0].range('c1').top + 2
wb.sheets[0].pictures['hello'].width = wb.sheets[0].range('c1').width - 4
wb.sheets[0].pictures['hello'].height = wb.sheets[0].range('c1').height - 4

# 循环插入图片
# 需求在C1:C2重插入图片1,2
sht = wb.sheets[0]
for i in range(1,3):
    pic_address = os.path.join(r'C:\Users\Administrator\Desktop', str(i) + '.jpg')
    print(pic_address)
    sht.pictures.add(pic_address , left=17, top=10, width=100,height=100,name=str(i))
    sht.pictures[str(i)].left = sht.range('C' + str(i)).left + 2
    sht.pictures[str(i)].top = sht.range('C' + str(i)).top + 2
    sht.pictures[str(i)].width = sht.range('C' + str(i)).width - 4
    sht.pictures[str(i)].height = sht.range('C' + str(i)).height - 4

2. 实例对象.api

3. 实例对象.count

Picture - class xlwings.Picture(impl=None)

The picture object is a member of the pictures collection:

1. 实例对象.api

2. 实例对象.delete()

3. 实例对象.height / left / name / parent / top / width

4. 实例对象.update(image)

Replaces an existing picture with a new one, taking over the attributes of the existing picture.

Font - class xlwings.main.Font(impl)

The font object can be accessed as an attribute of the range or shape object.

• mysheet['A1'].font
• mysheet.shapes[0].font

1. 实例对象.api

2. 实例对象.bold

Returns or sets the bold property (boolean).

>>> sheet['A1'].font.bold = True
>>> sheet['A1'].font.bold
True

3. 实例对象.color

Returns or sets the color property (tuple).

>>> sheet['A1'].font.color = (255, 0, 0) # RGB tuple
>>> sheet['A1'].font.color
(255, 0, 0)

4. 实例对象.italic

Returns or sets the italic property (boolean).

>>> sheet['A1'].font.italic = True
>>> sheet['A1'].font.italic
True

5. 实例对象.name

Returns or sets the name of the font (str).

>>> sheet['A1'].font.name = 'Calibri'
>>> sheet['A1'].font.name
Calibri

6. 实例对象.size

Returns or sets the size (float).

>>> sheet['A1'].font.size = 13
>>> sheet['A1'].font.size
13