与Excel对象模型有关的常用操作

本页目录

本节介绍后面内容可能会用到的一些与Excel对象模型有关的操作，包括获取文件当前路径、对象的引用、给参数指定常数、扩展单元格区域和改变单元格区域的属性等。

获取文件的当前路径

4.2节中利用工作簿对象的open方法可以打开指定路径的Excel文件。很多时候，希望这个路径是工作簿文件或py文件的当前路径。

【Excel VBA】

Excel VBA中获取工作簿文件的当前路径，使用ThisWorkbook对象的Path属性。下面打开Excel，保存在D盘下，名称和格式为dqexcel.xlsm。打开Excel VBA编程环境，添加一个模块，在代码编辑器中输入下面的代码。示例文件的存放路径为Samples\ch04\Excel VBA\当前路径.xlsm。

code.vba 
Sub Test()
  Debug.Print ThisWorkbook.Path
End Sub

运行过程，在立即窗口中输出文件所在路径"D:"。

【Python xlwings】

Python中获取py文件的当前路径，需要导入os模块，使用模块中的getcwd方法进行获取。假设下面是某py文件内的部分代码，使用os模块的getcwd方法获取该py文件的路径，用工作簿对象的open方法打开该路径下的Excel数据文件，返回一个工作簿对象。示例文件的存放路径为Samples\ch04\Python\当前路径.py。

code.python 
import xlwings as xw  #导入xlwings包
import os  #导入os包
root = os.getcwd()  #获取本py文件的当前路径
#创建Excel应用对象，可见，不添加工作簿
app=xw.App(visible=True, add_book=False)
#打开数据文件，可写
bk=app.books.open(fullname=root+\
                 r'\dqexcel.xlsx',read_only=False)
sht1=bk.sheets(1)  #获取第1个工作表
print(sht1.name)  #输出第1个工作表的名称
bk.close()  #关闭bk
app.quit()  #退出应用

运行脚本，打开当前路径中的Excel文件dqexcel.xlsx，输出工作簿中第1个工作表的名称。

对象的引用

操作对象之前需要首先找到对象并选择它。4.2节曾介绍，Excel中创建的所有Workbook(book)对象都保存在Workbooks(books)集合中，所有Worksheet(sheet)对象都保存在Worksheets(sheets)集合中。当需要获取集合中的某个对象时，首先要把它从集合中找出来。查找的方法，一种是使用索引号，每个对象添加到集合中时都有一个唯一的索引号，另一种是使用对象的名称。Excel VBA代码示例文件的存放路径为Samples\ch04\Excel VBA\对象的引用.xlsm。

可以用索引号和名称引用工作簿。

【Excel VBA】

code.vba 
Set bk=Workbooks(1)
Set bk=Workbooks("dqexcel")

【Python xlwings】

code.python 
>>> bk=app.books[0]
>>> bk=app.books(1)
>>> bk=app.books["dqexcel"]

可以用索引号和名称引用工作表。

【Excel VBA】

code.vba 
Set sht=Worksheets(1)
Set sht=Worksheets("dqexcel")

【Python xlwings】

code.python 
>>> sht=bk.sheets[0]
>>> sht=bk.sheets(1)
>>> sht=bk.sheets("dqexcel")

单元格的引用需要指定单元格对应的行坐标和列坐标。

【Excel VBA】

code.vba 
sht.Range("A1").Select
sht.Cells(1, "A").Select
sht.Cells(1,1).Select

【Python xlwings】

code.python 
>>> sht.range("A1").select()
>>> sht.range(1,1).select()
>>> sht["A1"].select()
>>> sht.cells(1,1).select()
>>> sht.cells(1,"A").select()

引用单元格区域需要指定区域左上角单元格和右下角单元格的坐标。

【Excel VBA】

code.vba 
sht.Range("A3:C8").Select
sht.Range("A3","C8").Select
sht.Range(sht.Range("A3"), sht.Range("C8")).Select
sht.Range(sht.Cells(3,1),sht.Cells(8,3)).Select

【Python xlwings】

code.python 
>>> sht.range("A3:C8").select()
>>> sht.range("A3","C8").select()
>>> sht.range(sht.cells(3,1),sht.cells(8,3)).select()
>>> sht.range((3,1),(8,3)).select()

单元格和单元格区域的引用还有很多特殊的情况，参见第13章的介绍。

获取末行行号-给参数指定常数值

从Excel工作表读取数据常需要获取数据区末行的行号。数据区末行如图4-4中选中行所示。引用末行有两种方法，一是从上往下找，数据区的末行即最后一个非空行；二是从工作表的底部往上找，为指定列遇到的第一个非空行。这里用到单元格对象的End(end)方法。

Document Image

图4-4 获取数据区末行的行号

Excel VBA和Python xlwings两种情况下引用末行的代码如下所示，各语句有相同的运行效果。注意End(end)方法的参数设置常数值时方式的区别。Excel VBA代码示例文件的存放路径为Samples\ch04\Excel VBA\末行行号.xlsm。

【Excel VBA】

code.vba 
intR=sht.Range("A1").End(xlDown).Row
intR=sht.Cells(1,1).End(xlDown).Row
intR=sht.Range("A" & CStr(sht.Rows.Count)).End(xlUp).Row
intR=sht.Cells(sht.Rows.Count,1).End(xlUp).Row

【Python xlwings】

code.python 
>>> sht.range("A1").end("down").row
>>> sht.cells(1,1).end("down").row
>>> sht.range("A"+str(sht.api.Rows.Count)).end("up").row
>>> sht.cells(sht.api.Rows.Count,1).end("up").row
>>> sht.api.Range("A1").End(xw.constants.Direction.xlDown).Row
>>> sht.api.Cells(1,1).End(xw.constants.Direction.xlDown).Row
>>> sht.api.Range("A"+str(sht.api.Rows.Count)).\
End(xw.constants.Direction.xlUp).Row
>>> sht.api.Cells(sht.api.Rows.Count,1).\
End(xw.constants.Direction.xlUp).Row

扩展单元格区域

向工作表中写入数据时常需要将单元格扩展到行区域、列区域或多行多列区域。使用单元格对象的Resize(resize)方法进行扩展。

下面演示通过对指定单元格C2进行上下、左右和行列三个方向扩展来得到新的区域，并选择它们。Excel VBA代码示例文件的存放路径为Samples\ch04\Excel VBA\扩展单元格区域.xlsm。

【Excel VBA】

code.vba 
sht.Range("C2").Resize(3).Select  ' C2:C4，单列
sht.Range("C2").Resize(1, 3).Select  ' C2:E2，单行
sht.Range("C2").Resize(3, 3).Select  ' C2:E4，多行多列

【Python xlwings】

code.python 
>>> sht.range("C2").resize(3).select()   # C2:C4，单列
>>> sht.range("C2").resize(1, 3).select()    # C2:E2，单行
>>> sht.range("C2").resize(3, 3).select()    # C2:E4，多行多列

修改单元格区域的属性

使用Excel VBA和Python xlwings可以修改单元格区域的属性如背景色、字体大小、样式等。下面将A2单元格的背景色设置为绿色，字体大小设置为20，加粗倾斜。Excel VBA代码示例文件的存放路径为Samples\ch04\Excel VBA\修改单元格区域属性.xlsm。

【Excel VBA】

code.vba 
sht.Range("A2").Interior.Color=RGB(0,255,0)
sht.Range("A2").Font.Size=20
sht.Range("A2").Font.Bold=True
sht.Range("A2").Font.Italic=True

【Python xlwings】

code.python 
>>> sht.range("A2").color=(0,255,0)
>>> sht.api.Range("A2").Font.Size=20
>>> sht.api.Range("A2").Font.Bold=True
>>> sht.api.Range("A2").Font.Italic=True