xlwings 使用手册
初始设置
# visible 用来设置程序是否可见,True表示可见(默认),Flase不可见
# add_book 用来设置是否自动创建工作簿
app = xw.App(visible=False, add_book=False)
app.display_alerts = False # 关闭一些提示信息,可以加快运行速度。 默认为 True。
app.screen_updating = False # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度
wps 中的调整
wps 要把 xlwings 包里 _xlwindows.py
中的
self._xl = COMRetryObjectWrapper(DispatchEx("Excel.Application"))
替换为
self._xl = COMRetryObjectWrapper(DispatchEx("KET.Application"))
range 取值方式
单个单元格
- 字符串形式:
sheet.range('A1')
表示第一列第一行的单元格、sheet.range('B2')
表示第二列第二行的单元格 - 行号和列号形式(注意行号和列号起始都是 1):
sheet.range(1, 1)
或sheet.range((1, 1))
表示第一列第一行的单元格、sheet.range(2, 2)
表示第二列第二行的单元格
单元格范围
- 字符串形式:
sheet.range('B2:C4')
表示从第二列第二行到第三列第四行的单元格范围 - 行号和列号形式:
sheet.range((2,3), (5,6))
表示从第二行第三列到第五行第六列的单元格范围 - 行列标识形式:
sheet.range('1:5')
表示从第一行到第五行的行范围,sheet.range('A:C')
表示从第一列到第三列的列范围 获取一列数据:sht.range('B3').options(transpose=True)
表示从第二列第三行的单元格开始的这一列范围
非连续性单元格范围(暂不支持)
列表形式:使用包含多个单元格地址的列表来指定非连续的单元格范围。列表中的每个元素都表示一个单元格地址,如
sheet.range(['A1', 'B2', 'C3'])
表示由三个单元格组成的范围。cells = ['A1', 'B2', 'C3'] sheet.range(cells)
二维列表形式:使用包含多个行的二维列表来指定非连续的单元格范围。每个行都包含起始单元格地址和结束单元格地址,如
sheet.range([['A1', 'B2'], ['C3', 'D4']])
表示由四个单元格组成的范围。cells = [['A1', 'B2'], ['C3', 'D4']] sheet.range(cells)
sheet 取值方式
sheet 取值方式和 range 取值方式基本一样,不同的是,sheet 的索引起始为 0,而 range 的起始为 1,且 sheet 方式多了一个切片区域取值的方式。
sheet1.range("A1")
sheet1.range("A1:C3")
sheet1.range((1,1))
sheet1.range((1,1), (3,3))
sheet1.range("NamedRange")
# 或者使用索引、切片表示法
sheet1["A1"] # 单个单元格
sheet1["A1:C3"] # 单元格范围
sheet1[0, 0] # 单个单元格;行索引,列索引,从 0 开始
sheet1[0:4, 0:4] # 单元格范围;行索引、列索引切片,与 python 列表切片一致,start:end,从 start 开始,不包含 end
sheet1["NamedRange"]
切片示例:
# 从第二行(1)到第四行(4),从第二列(B)到第三列(C)
sheet[1:4, 1:3].address
# 输出结果
$B$2:$C$4
options 参数及说明
options 方法允许用户设定转换器和相关的选项。转换器定义了 Excel 的区域及其值在读写过程中如何转换。通过 Range
对象的 options()
方法可以设置一些选项,如转置、转换为 DataFrame 等。
expand
参数:指定如何扩展范围以适应数据。可选值为'table'
或'down'
。默认为'table'
,表示扩展为包含整个表格;'down'
表示只扩展垂直方向。# 将范围的数据扩展为垂直方向 sheet.range('A1:B5').options(expand='down').value
numbers
参数:指定如何处理数字类型。可选值为'numbers'
、'formatted'
或'text'
。默认为'numbers'
,表示将数字读取为 Python 中的数值类型;'formatted'
表示读取为 Excel 中的格式化字符串;'text'
表示读取为纯文本字符串。# 设置选项,将数值类型转换为整型 int_values = sheet.range('B1:B3').options(numbers=int).value
dates
参数:指定如何处理日期类型。可选值为'dates'
或'formatted'
。默认为'dates'
,表示将日期读取为 Python 中的 datetime 对象;'formatted'
表示读取为 Excel 中的格式化字符串。# 设置选项,将日期类型转换为指定格式的字符串 date_values = sheet.range('C1:C3').options(dates='%Y-%m-%d').value
empty
参数:指定是否允许空单元格。默认为'raise'
,表示如果遇到空单元格将引发异常;'skip'
表示跳过空单元格;'zero'
表示将空单元格视为零值。# 设置选项,将空单元格填充为指定的值 filled_values = sheet.range('D1:D3').options(empty='NA').value
transpose
参数:指定是否转置数据。默认为False
,表示不转置;True
表示转置数据。# 设置选项,将 A 列向量转换为行向量 row_values = sheet.range('A1:A3').options(transpose=True).value
header=True
:将第一行作为 Pandas DataFrame 的表头。# 设置选项,将第一行作为表头 df = sheet.range('A1:D3').options(header=True).options(numbers=int).options(dates='%Y-%m-%d').options(empty='NA').options(pd.DataFrame).value
expand 参数及说明
Range
对象的 expand
方法可以动态地扩展范围,以适应包含数据的区域。它会自动根据包含数据的单元格的行和列来扩展范围,使其包含所有数据。可以使用以下参数:
down
:只扩展范围的行,直到遇到空行;right
:只扩展范围的列,直到遇到空列;left
:只扩展范围的列,直到遇到空列,并向左扩展;up
:只扩展范围的行,直到遇到空行,并向上扩展。table
:(=down + right)
示例代码:
# 设置初始范围
range1 = sheet.range('A1:C3')
print("初始范围:")
for row in range1.rows:
for cell in row:
print(cell.address, end=" ")
print()
print()
# 调用 expand() 方法扩展范围
range2 = range1.expand('down').expand('right', 1)
print("扩展后的范围:")
for row in range2.rows:
for cell in row:
print(cell.address, end=" ")
print()
输出:
初始范围:
$A$1 $B$1 $C$1
$A$2 $B$2 $C$2
$A$3 $B$3 $C$3
扩展后的范围:
$A$1 $B$1 $C$1 $D$1
$A$2 $B$2 $C$2 $D$2
$A$3 $B$3 $C$3 $D$3
$A$4 $B$4 $C$4 $D$4
$A$5 $B$5 $C$5 $D$5
expand 和 options 扩展区域的区别
expand
会直接给出扩展后的区域对象,而 options
方法在调用区域的是才计算区域扩展。
>>> sheet['A1'].value = [[1,2], [3,4]]
>>> range1 = sheet['A1'].expand('table') # or just .expand()
>>> range2 = sheet['A1'].options(expand='table')
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> sheet['A3'].value = [5, 6]
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]
used_range 属性
Sheet
对象的 used_range
属性可以返回当前工作表中包含数据的最大范围。它返回一个 Range
对象,即包含数据的单元格范围。也即工作表中用过的区域。
# 获取包含数据的最大范围
used_range = sheet.used_range
# 获取包含数据的最大行数和列数
rows, cols = used_range.shape
rows = used_range.shape[0]
cols = used_range.shape[1]
rows = used_range.last_cell.row
cols = used_range.last_cell.column
# 获取包含数据的单元格范围的值
values = used_range.value
# 获取 used_range,并扩展范围以去除空白单元格
used_range = sheet.used_range.expand('table')
print('used_range 范围:')
for row in used_range.rows:
for cell in row:
print(cell.address, end=" ")
print()
输出:
used_range 范围:
$A$1 $B$1 $C$1
$A$2 $B$2 $C$2
$A$3 $B$3 $C$3
二维数据结构
[['a1', 'b1', 'c1'], ['a2', 'b2', 'c2'], ['a3', 'b3', 'c3'], ['a4', 'b4', 'c4']]
二维结构数据,是从起始单元格逐行逐列读取的。
单元格常见属性和操作
range().options() 方法可以用于设置和读取单元格的各种属性和选项。其常见参数及用法如下:
- value:设置或读取单元格的值。
- 设置单元格的值:
sheet.range('A1').options(value=10)
- 读取单元格的值:
value = sheet.range('A1').value
- formula:设置或读取单元格的公式。
- 设置单元格的公式:
sheet.range('B1').options(formula='=A1 + 10')
- 读取单元格的公式:
formula = sheet.range('B1').formula
- number_format:设置或读取单元格的数字格式。
- 设置单元格的数字格式:
sheet.range('C1').options(number_format='0.00%')
- 读取单元格的数字格式:
number_format = sheet.range('C1').number_format
- font:设置或读取单元格的字体格式。
- 设置单元格的字体格式:
sheet.range('A1').options(font={'bold': True})
- 读取单元格的字体格式:
font = sheet.range('A1').font
- color:设置或读取单元格的背景色。
- 设置单元格的背景色:
sheet.range('A1').options(color=(255, 255, 0))
- 读取单元格的背景色:
color = sheet.range('A1').color
- row_height:设置或读取单元格所在行的行高。
- 设置单元格所在行的行高:
sheet.range('A1').options(row_height=20)
- 读取单元格所在行的行高:
row_height = sheet.range('A1').row_height
- column_width:设置或读取单元格所在列的列宽。
- 设置单元格所在列的列宽:
sheet.range('A1').options(column_width=15)
- 读取单元格所在列的列宽:
column_width = sheet.range('A1').column_width