工作表对象

相关对象

跟工作表有关的对象，xlwings API使用方式下主要有Worksheet, Worksheets, Sheet和Sheets等，xlwings方式下只有sheet和sheets两种。复数形式的类表示集合，所有单数形式的对象都在对应集合中存储和管理。

Worksheet和Sheet都表示工作表，它们有什么区别呢？在Excel主界面中，右键单击工作表选项卡下面的标题处，在弹出式菜单中单击“插入…”选项，如图2-19所示。弹出图2-20所示的对话框，在该对话框中选择一种工作表类型，单击“确定”按钮，可以插入一个新的工作表。

图2-19 工作表选项卡右击菜单 图2-20 选择一种工作表类型

从图2-20中可以看出，在xlwings API使用方式下，工作表主要有4种类型，即普通工作表、图表工作表、宏工作表和对话框工作表。最常用的工作表类型是普通工作表。所以，上面提到的Worksheet对象和Sheet对象之间的区别就在于：Worksheet对象表示普通工作表，Worksheets集合对象中保存的是所有普通工作表；而Sheet对象可以是4种工作表类型中的任何一种，Sheets集合对象中包含所有类型的工作表。xlwings使用方式下则没有这种区分，sheet对象和sheets集合对象只是针对普通工作表。

创建和引用工作表

使用集合对象的add(Add)方法创建新的工作表。在xlwings使用方式下，使用sheets对象的add方法创建，在另外两种使用方式下，使用Worksheets对象或Sheets对象的Add方法进行创建。

新创建的工作表自动放到集合中进行存储，按照存放的先后顺序，每个工作表都有一个索引号。当需要对集合中的某个工作表进行操作时，首先要把它从集合中找出来，这个查找的操作就是工作表的引用。可以使用索引号或工作表的名称进行引用。

一、xlwings使用方式

使用sheets对象的add方法创建工作表，语法格式如下所示：

bk.sheets.add(name=None, before=None, after=None)

其中，bk表示指定工作簿。该方法有3个参数：

• name - 新工作表的名称。如果不指定，会使用Sheet加数字的方式自动命名。数字按照添加顺序自动累加。

• before -指定在该工作表之前插入新表。

• after - 指定在该工作表之后插入新表。

默认时，创建的新工作表自动成为活动工作表。

下面使用不带参数的add方法在bk工作簿中插入一个新的普通工作表。注意，在xlwings使用方式下，默认时，使用add方法创建的新工作表是放在所有已有工作表的后面的。

>>> bk.sheets.add()

可以用before参数和after参数给新建的工作表指定位置。新建的工作表sht在已有的第2个工作表之前插入：

>>> bk.sheets.add(before=bk.sheets(2))

新建的工作表sht在已有的第2个工作表之后插入：

>>> bk.sheets.add(after=bk.sheets(2))

新创建的工作表自动放到集合中进行存储，并且每个工作表都有一个唯一的索引号。可以用索引号对工作表进行引用。在xlwings方式下，可以用方括号进行引用，也可以用小括号进行引用。前者引用的基数为0，即集合中第1个工作表对象的索引号为0；后者引用的基数为1，即集合中第1个工作表对象的索引号为1。

>>> bk.sheets[0]
<Sheet [test.xlsx]MySheet>
>>> bk.sheets(1)
<Sheet [test.xlsx]MySheet>

也可以使用工作表的名称进行引用。

>>> sht=bk.sheets["Sheet1"]
>>> sht.name="MySheet"

二、xlwings API使用方式

使用Worksheets对象的Add方法创建新工作表，其语法格式为：

【xlwings API】

bk.api.WorkSheets.Add(Before, After, Count, Type)

其中，bk表示指定的工作簿。Add方法有4个参数，皆为可选：

• Before -指定在该工作表之前插入新表。

• After - 指定在该工作表之后插入新表。

• Count – 插入工作表的个数。

• Type – 插入工作表的类型。

可见，在API使用方式下，可以指定工作表的类型，可以一次插入多张工作表。

Type参数的取值如表2-7所示。

表2-7 Type参数的取值

下面使用不带参数的Add方法创建新的普通工作表。此时创建的工作表自动添加到所有工作表的最前面。注意，xlwings使用方式下是放在最后面的，与此不同。默认时，新工作表的名称为Sheet后面添加数字的形式，例如Sheet2, Sheet3等。数字的大小是从2开始连续累加的。

【xlwings API】

>>> bk.api.Worksheets.Add()

新工作表在第2个工作表之前插入：

【xlwings API】

>>> bk.api.Worksheets.Add(Before=bk.api.Worksheets(2))

一次插入3个工作表，放在最前面。注意，这3个工作表中，后生成的表始终在最前面插入。

【xlwings API】

>>> bk.api.Worksheets.Add(Count=3)

下面指定新工作表的类型，创建一个新的图表工作表。

【xlwings API】

>>> bk.api.Worksheets.Add(Type=xw.constants.SheetType.xlChart)

也可以组使用参数设置。

【xlwings API】

>>> bk.api.Worksheets.Add(Before=bk.api.Worksheets(2), Count=3)

创建新工作表后，可以用工作表对象的Name属性修改工作表的名称。

【xlwings API】

>>> sht=bk.api.Worksheets.Add()
>>> sht.Name= "MySheet"

也可以使用Sheets对象的Add方法创建新的工作表，在语法上跟使用Worksheets.Add()完全相同。

【xlwings API】

>>> sht=bk.api.Sheets.Add()
>>> sht=bk.api.Sheets.Add(Before=bk.api.Worksheets(2))
>>> sht=bk.api.Sheets.Add(Count=3)
>>> sht=bk.api.Sheets.Add(Type=xw.constants.SheetType.xlChart)

可以使用索引号和名称两种方式引用工作表。

>>> sht=Worksheets(1)
>>> sht=Worksheets("Sheet1")

激活、复制、移动和删除工作表

使用工作表对象的activate(Activate)方法或select(Select)方法激活指定工作表，激活以后的工作表就是活动工作表。

在xlwings使用方式下用工作表对象的activate方法或select方法激活第2个工作表，在另两种使用方式下用工作表对象的Activate方法或Select方法激活：

【xlwings】

>>> bk.sheets[1].activate()
>>> bk.sheets[1].select()

【xlwings API】

>>> bk.api.Worksheets(2).Activate()
>>> bk.api.Worksheets(2).Select()

激活以后，它就成为当前工作簿的活动工作表。在xlwings使用方式下用sheets对象的active属性获取当前活动工作表，另两种使用方式下用ActiveSheet引用活动工作表。

【xlwings API】

>>> bk.sheets.active.name
'Sheet1'

【xlwings API】

>>> bk.api.ActiveSheet.Name
'Sheet1'

复制工作表，在xlwings API使用方式下使用Copy方法。

使用不带参数的Copy方法，会复制一个工作表并在新工作簿中打开。

【xlwings API】

>>> bk.api.Sheets("Sheet1").Copy()

也可以在使用Copy方法时指定位置参数，确定将生成的新工作表放在指定工作表的前面或后面。注意参数名称有大小写区分。

【xlwings API】

>>> bk.api.Sheets("Sheet1").Copy(Before=bk.api.Sheets("Sheet2"))
>>> bk.api.Sheets("Sheet1").Copy(After=bk.api.Sheets("Sheet2"))

可以跨工作簿复制。假设bk2是另一个工作簿。将当前工作簿bk中的第1个表复制到bk2工作簿中第2个表的前面或后面。

【xlwings API】

>>> bk.api.Sheets("Sheet1").Copy(Before=bk2.api.Sheets("Sheet2"))
>>> bk.api.Sheets("Sheet1").Copy(After=bk2.api.Sheets("Sheet2"))

移动工作表与复制工作表类似，使用工作表的Move方法。

使用不带参数的Move方法，会创建一个新工作簿并将指定工作表移动到该工作簿打开。

【xlwings API】

>>> bk.api.Sheets("Sheet1").Move()

也可以在使用Move方法时指定位置参数，确定将工作表移动到指定工作表的前面或后面。

【xlwings API】

>>> bk.api.Sheets("Sheet1").Move(Before=bk.api.Sheets("Sheet3"))
>>> bk.api.Sheets("Sheet1").Move(After=bk.api.Sheets("Sheet3"))

也可以跨工作簿移动工作表，只需在赋位置参数时指定目标工作簿对象。

【xlwings API】

>>> bk.api.Sheets("Sheet1").Move(Before=bk2.api.Sheets("Sheet2"))

使用列表，可以同时移动多个工作表。下面将工作表Sheet2和Sheet3移动到工作表Sheet1前面。

【xlwings API】

>>> bk.api.Sheets(["Sheet2", "Sheet3"]).Move(Before=bk.api.Sheets(1))

删除工作表使用sheets对象的Delete方法，使用列表，可以一次删除多个工作表。

【xlwings】

>>> bk.sheets("Sheet1").delete()
>>> bk.sheets(["Sheet2", "Sheet3"]).delete()

【xlwings API】

>>> bk.api.Sheets("Sheet1").Delete()
>>> bk.api.Sheets(["Sheet2", "Sheet3"]).Delete()

隐藏和显示工作表

通过设置工作表对象的visible(Visible)属性，可以隐藏或显示工作表。

在xlwings使用方式下，设置工作表对象的visible属性的值为False或0，隐藏工作表，设置为True或1，显示工作表。下面隐藏工作簿bk中的工作表Sheet1。

>>> bk.sheets("Sheet1").visible = False
>>> bk.sheets("Sheet1").visible = 0

在xlwings API使用方式下，使用工作表对象的Visible属性显示或隐藏工作表。下面三行代码作用一样，用于隐藏工作簿bk中的工作表Sheet1。

【xlwings API】

>>> bk.api.Sheets("Sheet1").Visible = False
>>> bk.api.Sheets("Sheet1").Visible = xw.constants.SheetVisibility.xlSheetHidden
>>> bk.api.Sheets("Sheet1").Visible = 0

对于这种方法隐藏的工作表，在图2-21所示的弹出式菜单中单击“取消隐藏…”选项，在打开的对话框中可以找到对应的工作表名称，选择它可以取消隐藏。

使用xlwings API方式，还有一种隐藏叫深度隐藏。深度隐藏的工作表，无法通过菜单取消隐藏，只能通过在属性窗口设置或者用代码取消隐藏。使用下面的代码对工作表进行深度隐藏：

【xlwings API】

>>> bk.api.Sheets("Sheet1").Visible=xw.constants.SheetVisibility.xlSheetVeryHidden
>>> bk.api.Sheets("Sheet1").Visible=2

无论以何种方式隐藏了工作表，都可以用下面代码中的任意一句显示它：

【xlwings】

>>> bk.sheets("Sheet1").visible = True
>>> bk.sheets("Sheet1").visible = 1

【xlwings API】

>>> bk.api.Sheets("Sheet1").Visible = True
>>> bk.api.Sheets("Sheet1").Visible = xw.constants.SheetVisibility.xlSheetVisible
>>> bk.api.Sheets("Sheet1").Visible = 1
>>> bk.api.Sheets("Sheet1").Visible = -1

选择行和列

选择一个单行，先引用该单行，然后用Select方法选择即可。下面选择第1行：

【xlwings】

>>> sht["1:1"].select()

【xlwings API】

>>> sht.api.Rows(1) .Select()
>>> sht.api.Range("1:1").Select()
>>> sht.api.Range("a1").EntireRow.Select()

选择多行，先引用该多行，然后用Select方法选择。下面选择第1至5行：

【xlwings】

>>> sht["1:5"].select()
>>> sht[0:5,:].select()

【xlwings API】

>>> sht.api.Rows("1:5").Select()
>>> sht.api.Range("1:5").Select()
>>> sht.api.Range("A1:A5").EntireRow.Select()

选择不连续行，先引用该不连续行，然后用Select方法选择。下面选择第1-5行和第7-10行：

【xlwings】

>>> sht.range("1:5,7:10").select()

【xlwings API】

>>> sht.api.Range("1:5,7:10").Select()

选择一个单列，先引用该单列，然后用Select方法选择即可。下面选择第1列：

【xlwings】

>>> sht.range("A:A").select()

【xlwings API】

>>> sht.api.Columns(1).Select()
>>> sht.api.Columns("A").Select()
>>> sht.api.Range("A:A").Select()
>>> sht.api.Range("A1").EntireColumn.Select()

选择多列，先引用该多列，然后用Select方法选择。下面选择B-C列：

【xlwings】

>>> sht.range("B:C").select()
>>> sht[:,1:3].select()

【xlwings API】

>>> sht.api.Columns("B:C").Select()
>>> sht.api.Range("B:C").Select()
>>> sht.api.Range("B1:C2").EntireColumn.Select()

选择不连续列，先引用该不连续列，然后用Select方法选择。下面选择第C-E行和第G-I行：

【xlwings】

>>> sht.range("C:E,G:I").select()

【xlwings API】

>>> sht.api.Range("C:E,G:I").Select()

2.3.6复制/剪切行和列

在xlwings API使用方式下，引用行和列后，用单元格对象的Copy方法和Cut方法复制和剪切行和列。

进行复制时，首先用Copy方法将源数据复制到剪贴板，选择要粘贴的目标位置，然后将工作表对象的Paste方法进行粘贴。下面将第2行的内容复制到第7行：

【xlwings API】

>>> sht.api.Rows("2:2").Copy()
>>> sht.api.Range("A7").Select()
>>> sht.api.Paste()

进行剪切时，首先用Cut方法将源数据剪切到剪贴板，选择要粘贴的目标位置，然后将工作表对象的Paste方法进行粘贴。剪切与复制的区别在于，剪切后源数据就清空了，而复制不会清空源数据。剪切相当于移动操作。下面将第2行的内容剪切到第7行：

【xlwings API】

>>> sht.api.Rows("2:2").Cut()
>>> sht.api.Range("A7").Select()
>>> sht.api.Paste()

也可以一次剪切多行。首先选择多行，然后用Selection对象的Cut方法进行剪切。注意两种方式获取Selection对象的方法不一样。下面将第2~3行的内容剪切到第7~8行：

【xlwings API】

>>> sht.api.Rows("2:3").Select()
>>> bk.selection.api.Cut()
>>> sht.api.Range("A7").Select()
>>> sht.api.Paste()

列的复制和剪切跟行的类似，只是引用的是列。下面将第A列的内容复制到第E列：

【xlwings API】

>>> sht.api.Columns("A:A").Copy()
>>> sht.api.Range("E1").Select()
>>> sht.api.Paste()

将第1列的内容剪切到第5列：

【xlwings API】

>>> sht.api.Columns("A:A").Cut()
>>> sht.api.Range("E1").Select()
>>> sht.api.Paste()

将B, C列的内容剪切到F, G列：

【xlwings API】

>>> sht.api.Columns("B:C").Select()
>>> bk.selection.api.Cut()
>>> sht.api.Range("F1").Select()
>>> sht.api.Paste()

插入行和列

4.3.9小节介绍了使用单元格对象的Insert(insert)方法插入单元格和区域，引用行或列后，调用同样的方法，可以实现插入行或列。

对于图2-21所示的工作表数据，设置第2行的格式，设置A2单元格的背景色为绿色，C2为兰色，E2为红色，在第3行上面插入行，复制第2行的格式。编写代码如下：

【xlwings】

>>> sht.range("A2").color=(0,255,0)
>>> sht.range("C2").color=(0,0,255)
>>> sht.range("E2").color=(255,0,0)
>>> sht["3:3"].insert(shift="down",copy_origin="format_from_left_or_above")

【xlwings API】

>>> sht.api.Range("A2").Interior.Color=xw.utils.rgb_to_int((0, 255, 0))
>>> sht.api.Range("C2").Interior.Color=xw.utils.rgb_to_int((0, 0, 255))
>>> sht.api.Range("E2").Interior.Color=xw.utils.rgb_to_int((255, 0, 0))
>>> sht.api.Rows(3).Insert(Shift=xw.constants.InsertShiftDirection.xlShiftDown,CopyOrigin=xw.constants.InsertFormatOrigin.xlFormatFromLeftOrAbove)

定义第2行的格式并在第3行上面插入行后的效果如图2-22所示。插入的第3行复制了第2行的格式，原来位置的行及以下数据依次往下移。

图2-21 原工作表数据 图2-22 定义格式并插入行后的工作表

使用循环，可以连续插入多行。下面在第3行上方插入4个空白行。

【xlwings API】

>>> for i in range(4):
sht.api.Rows(3).Insert()

下面在活动工作表中先选择1个行，然后在该行上方插入1个空白行。

【xlwings API】

>>> bk.sheets.active.api.Rows(bk.selection.row).Insert()

实际应用中，常需要遍历多个行，在其中找到满足条件的行，然后在它上面插入空白行。下面遍历工作表sht中第3列的各行，找到值为“雷婷”的单元格时，在它所在的行上面插入一行。

【xlwings API】

>>> for i in range(10,2,-1):
if sht.cells(i, 3).value == '雷婷':
sht.api.Cells(i, 3).EntireRow.Insert()

插入列的操作与插入行的基本相同，只是区域的引用方式和Insert(insert)方法的参数设置不一样。

【xlwings】

>>> sht['B:B'].insert()

【xlwings API】

>>> sht.api.Columns(2).Insert()

使用循环可以连续插入多列：

【xlwings API】

>>> for i in range(1,3):
sht.cells(1, 2).select()
bk.selection.api.EntireColumn.Insert()

使用循环隔列插入列，可以将循环时计数变量的步长设置为2，或在循环体中对单元格进行引用时间隔引用列。下面使用第2种方法隔列插入列：

【xlwings API】

>>> for i in range(1,9):
sht.cells(1, 2*i).select()
bk.selection.api.EntireColumn.Insert()

删除行和列

引用行或列后，用工作表对象的Delete(delete)方法删除行或列。该方法在4.3.11小节有详细介绍，请参阅。

一、删除单/多/不连续行和列

单行单列、多行多列和不连续行和列的删除，请参见2.3.5小节行列选择的内容，引用方式相同，把Select(select)方法换成Delete(delete)方法即可，不再赘述。

二、删除空行

删除空行可以有多种方法，下面介绍两种。

第1种是使用4.3.7小节介绍的SpecialCells方法，先找到空格，然后删除空格所在的行。

【xlwings API】

>>> sht.api.Columns("A:A").SpecialCells(xw.constants.CellType.xlCellTypeBlanks).EntireRow.Delete()

第2种方法使用工作表函数，这里要用到后面介绍的Application对象，使用该对象的WorksheetFunction属性，继续引用其CountA方法。该方法的参数为工作表的行，如果行为空行，则返回0。据此可以删除所有空行。

【xlwings API】

>>> a= sht.used_range.rows.count
>>> for i in range(a,1,-1):
		if app.api.WorksheetFunction.CountA(sht.api.Rows(i))==0:
			sht.api.Rows(i).Delete()

三、删除重复行

删除重复行，首先要把重复行找出来。使用工作表函数COUNTIF可以找出重复行。图2-23中第1列是给定的数据，在单元格B1中添加公式"=COUNTIF($A$1:$A$7,A1)"，下拉填充，结果如图中第2列所示，列中每个数据表示左侧数据重复的次数，大于1的即表示有重复。据此可以找出重复行。

图2-23 用COUNTIF函数查找重复行

编写如下代码，对工作表中第1列数据用COUNTIF函数进行判断，如果返回值大于1，表示为重复行，删除。

【xlwings API】

>>> a=sht.cells(sht.api.Rows.Count, 1).end("up").row
>>> for i in range(a,1,-1):
if app.api.WorksheetFunction.CountIf(sht.api.Columns(1), sht.api.Cells(i,1))>1:
sht.api.Rows(i).Delete()

设置行高和列宽

在xlwings API使用方式下，用单元格对象的RowHeight属性设置和获取行高，用ColumnWidth属性设置和获取列宽。

下面设置第3行的行高为30，第5行的行高为40，最后设置全部行的行高为30。

【xlwings API】

>>> sht.api.Rows(3).RowHeight = 30
>>> sht.api.Range("C5").EntireRow.RowHeight = 40
>>> sht.api.Range("C5").RowHeight = 40
>>> sht.api.Cells.RowHeight = 30

下面设置第2列的列宽为20，第4列的列宽为15，最后设置全部列的列宽为10。

【xlwings API】

>>> sht.api.Columns(2).ColumnWidth = 20
>>> sht.api.Range("C4").ColumnWidth = 15
>>> sht.api.Range("C4").EntireColumn.ColumnWidth = 15
>>> sht.api.Cells.ColumnWidth = 10

在xlwings使用模式下，使用工作表对象的autofit方法，将在整个工作表上自动调整行、列或两者的高度和宽度。该方法的语法格式为：

sht.autofit(axis=None)

其中，sht为需要设置的工作表。参数axis的值为"rows"或"r"时，自动调整行, 值为"columns"或"c"时，自动调整列，不带参数时，自动调整行和列。

>>> sht.autofit("c")

图2-24和图2-25为自动调整工作表列宽前后的效果。[大谦Excel，dqexcel点com]

图2-24 自动调整列宽前的工作表 图2-25 自动调整列宽后的工作表