代替VBA！用Python轻松实现Excel编程(文末赠书)

小F

发布于 2023-01-03 20:26:38

5.3K0

发布于 2023-01-03 20:26:38

大家好，我是小F～

面向Excel数据处理自动化的脚本编程，目前主要有VBA和Python两种语言可供选择。

从上世纪90年代到目前，VBA一直是Excel脚本编程的主要工具。VBA语言具有简单易学、功能强大的特点，在长达几十年的时间里为提高Excel工作效率作出了贡献，也积累了海量的代码和学习资料。在这段时间里，Basic语言也一直是国内中学到大学教学首选的计算机语言。

随着网络时代的全面到来，以及大数据、人工智能等的兴起，Python语言在国内异军突起。Python语言已经成为目前最受欢迎的计算机语言之一，近年来在TIOBE和IEEE等编程语言排行榜上长期占据前三位。在国内，Python也在逐步代替原来的Basic语言，成为小学、中学和大学学生入门学习计算机编程的首选语言。所以，当前使用Python进行Excel脚本编程以提高工作效率的朋友越来越多。

PART 01

需求与选择：VBA还是Python?

所以，目前使用或准备使用VBA和Python进行Excel脚本编程，或者说进行Excel数据处理自动化的人主要有3种。

第1种是懂Python不懂VBA的，他们有办公自动化和数据分析的需求。就目前可以找到的图书和网络课程来看，主要是结合Python的xlrd, xlwt和OpenPyXL等包进行介绍。这几个包小巧灵活，但是功能上有明显的不足，无法与VBA媲美。使用win32com和xlwings等包，VBA能做的Python都能做，但是这方面的资料很少，特别是语言参考这样一些最基本的文档缺乏。所以，Python用户在了解Excel对象模型的过程中需要参阅大量VBA的资料，即他们有快速掌握VBA语言的需求和获取xlwings基础性文档和使用手册的需求。
第2种是原来对VBA比较熟悉的朋友因为各种原因要学习和使用Python进行Excel脚本编程。VBA本身是很强大的，但是Python崛起以后，它通过win32com和xlwings可以使用VBA使用的Excel对象模型，即可以代替VBA；通过pandas等包可以快速方便地处理大型数据。所以，在数据处理自动化方面Python相较于VBA有明显的优势。这也是很多VBA老用户与时俱进，开始学习Python，学习用Python处理Excel数据的主要原因。
第3种是没有计算机语言基础但有Excel编程需求的同学。他们面临的是诸如“学VBA好还是学Python好”，“先学VBA还是先学Python”这样的问题。

那么，

究竟是学VBA好还是学Python好呢？

有没有可能同时学好两门语言？

有没有让VBA用户和Python用户快速掌握另外一门语言的捷径？

答案是：有！

PART 02

双语对照学习：快速学习语言的捷径

对照学习就是快速学习语言的有效捷径！

所谓的对照学习，不是将两种语言机械地放在一起，自说自话，而是先将两门语言的语法全部打碎，然后实现语法知识点点对点的对照、融合和重建，在自己熟悉的语境中快速理解和掌握另一门语言。

下面的代码使用VBA和Python实现用空格分割给定的字符串。

【Excel VBA】

Sub Test()
  Dim strL As String
  Dim strArray() As String
  strL = "Hello python VBA"
  strArray = Split(strL, " ")  '分割字符串
  Debug.Print strArray(0)
  Debug.Print strArray(1)
  Debug.Print strArray(2)
End Sub

【Python】

>>> 'Hello python VBA'.split(' ')
['Hello', 'python', 'VBA']

下面用VBA和Python实现用多分支if判断结构判断给定成绩的等级。

【Excel VBA】

Sub Test1()
  Dim intSC As Integer
  intSC = InputBox("请输入一个数字：")
  If intSC >= 90 Then
    Debug.Print "优秀"
  ElseIf intSC >= 80 Then
    Debug.Print "良好"
  ElseIf intSC >= 70 Then
    Debug.Print "中等"
  ElseIf intSC >= 60 Then
    Debug.Print "及格"
  Else
    Debug.Print "不及格"
  End If
End Sub

【Python】

sc= int(input('请输入一个数字：'))
if(sc>=90):
    print('优秀')
elif(sc>=80):
    print('良好')
elif(sc>=70):
    print('中等')
elif(sc>=60):
    print('及格')
else:
    print('不及格')

PART 03

Python使用xlwings真的能代替VBA吗？

能！因为xlwings间接封装了VBA使用的Excel对象模型。

Excel脚本编程的核心主要有2个，一个是脚本语言，另一个是对象模型。

语言方面Python可以代替VBA，现在xlwings封装了VBA使用的Excel对象模型，即对象模型是一样的，所以Python使用xlwings可以代替VBA进行Excel脚本编程，VBA能做的，Python也能做。

xlwings包将Excel对象模型中一些常用的功能使用新语法进行封装，其他功能用API方式进行调用。但实际上，使用API方式可以以类VBA的语法完成几乎所有的编程。所以，实际上xlwings包提供了两种编程方式，用封装后的新语法进行编程称为xlwings方式，使用API的称为xlwings API方式。

目前出版的图书和网络资料主要介绍新语法。就Excel对象模型提供的功能而言，新语法封装的这部分只是冰山水面上的一小部分，水面下的大部分需要通过API方式实现。

下面举例说明两种使用方式的区别。例如要选择工作表中的A1单元格，可以使用这两种方式进行编程：

【xlwings】

>>> sht=bk.sheets(1)
>>> sht.range("A1").select()

【xlwings API】

>>> sht=bk.sheets(1)
>>> sht.api.Range('A1').Select()

可见，在xlwings方式下，range属性和select方法都是小写的，是重新封装后的写法。在xlwings API方式下，在sht对象后面引用api，后面就可以使用VBA中的引用方式，Range属性和Select方法首字母都是大写。所以使用API方式可以使用大多数VBA的编程代码，懂VBA编程的同学可以很快就能上手。当然，使用xlwings方式会有一些编码、效率方面的好处，有一些扩展的功能。

PART 04

用VBA和Python操作Excel工作表

由于xlwings封装了VBA使用的Excel对象模型，所以，Python使用xlwings可以操作Excel表格，可以从表格读取数据、将数据写入表格，或者设置表格的属性等，就像VBA的操作一样。

下面分别用VBA和Python获取工作表中数据区域末行的行号。其中，sht为指定的工作表对象。

【Excel 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】

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

下面分别用VBA和Python修改单元格区域的属性，将A2单元格的背景色设置为绿色，单元格中文本字体的大小设置为20，加粗并倾斜。其中，sht为指定的工作表对象。

【Excel 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】

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

单元格A2的属性设置效果如图1所示。

图1 单元格属性设置

PART 05

用VBA和Python创建Excel图表

目前的图书和网络教程介绍Python自动化办公时主要介绍用Matplotlib创建图表，然后将图表导入到Excel表格。实际上，Python使用xlwings可以创建Excel自己的图表并进行编辑设置。

Excel自己的图表相较于Matplotlib创建的图表主要有几个方面的优势。

第1个优势是表格中的绘图数据与图表是关联的，修改数据时图表会即时改变；
第2个优势是可以创建透视图这样一些特殊图表；
第3个优势是Excel自己的三维图表效果比Matplotlib创建的三维图表好，可以添加光照，设置材质、纹理等。

下面分别用VBA和Python，使用Excel工作表中的数据创建嵌入式图表。

【Excel VBA】

Sub CreateCharts()
  Dim cht As ChartObject
  '生成ChartObject对象，指定位置和大小
  Set cht = ActiveSheet.ChartObjects.Add(50, 200, 355, 211)
  With cht
    With .Chart   'Chart属性返回Chart对象，用它设置图表属性
      '绑定数据
      .SetSourceData Source:=Sheets("Sheet1").Range("A1:H7"), PlotBy:=xlRows
      .ChartType = xlColumnClustered        '图表类型
      .SetElement msoElementChartTitleCenteredOverlay    '标题居中显示
      .ChartTitle.Text = "部分省2011—2016年的GDP数据"    '标题文本
    End With
  End With
End Sub

【Python xlwings】

import xlwings as xw      #导入xlwings包
import os         #导入os包
root = os.getcwd()       #获取当前路径
app = xw.App(visible=True, add_book=False)  #创建Excel应用，不添加工作簿
#打开与本文件相同路径下的数据文件，可写
wb=app.books.open(root+r'/GDP数据.xlsx',read_only=False)
sht=wb.sheets(1)       #获取工作表对象
cht=sht.charts.add(50, 200)       #添加图表
cht.set_source_data(sht.range('A1').expand())   #图表绑定数据
cht.chart_type='column_clustered'      #图表类型
cht.api[1].HasTitle=True        #图表有标题
cht.api[1].ChartTitle.Text='部分省2011—2016年的GDP数据'  #标题文本

运行程序后生成的图表如图2所示。

图2 创建嵌入式图表

PART 06

用VBA和Python创建Excel数据透视表

通过编程，可以使用向导和缓存两种方式创建数据透视表。

下面分别用VBA和Python，使用缓存方式创建数据透视表。Excel会为数据透视表建立一个缓存，通过该缓存，可以实现对数据源中数据的快速读取。先使用PivotCaches集合的Create方法可以创建PivotCache对象，即缓存对象，然后使用缓存对象的CreatePivotTable方法创建数据透视表。

【Excel VBA】

Sub CreatePivotTable()
Dim shtData As Worksheet
Dim shtPVT As Worksheet
Dim rngData As Range
Dim rngPVT As Range
Dim pvc As PivotCache
Dim PVT As PivotTable
'数据所在的工作表
Set shtData = Worksheets("数据源")
'数据所在的单元格区域
Set rngData = shtData.Range("A1").CurrentRegion
'新建数据透视表所在的工作表
Set shtPVT = Worksheets.Add()
shtPVT.Name = "数据透视表"
'放数据透视表的位置
Set rngPVT = shtPVT.Range("A1")

'创建数据透视表关联的缓存
Set PVC= ActiveWorkbook.PivotCaches.Create( _
             SourceType:=xlDatabase, SourceData:=rngData)
'创建数据透视表
Set PVT =PVC.CreatePivotTable(TableDestination:=rngPVT, _
             TableName:="透视表")


'设置字段
With PVT
  .PivotFields("类别").Orientation = xlPageField   '页字段
  .PivotFields("类别").Position = 1
  .PivotFields("产品").Orientation = xlColumnField   '列字段
  .PivotFields("产品").Position = 1
  .PivotFields("产地").Orientation = xlRowField   '行字段
  .PivotFields("产地").Position = 1
  .PivotFields("金额").Orientation = xlDataField   '值字段
End With

End Sub

【Python】

import xlwings as xw   #导入xlwings包
import os      #导入os包
root = os.getcwd()    #获取当前路径
#创建Excel应用，可见，不添加工作簿
app=xw.App(visible=True, add_book=False)
#打开数据文件，可写
bk=app.books.open(fullname=root+r'\创建透视表.xlsx',read_only=False)
#获取数据源工作表
sht_data=bk.sheets.active
rng_data=sht_data.api.Range('A1').CurrentRegion
#新建数据透视表所在的工作表
sht_pvt=bk.sheets.add()
sht_pvt.name='数据透视表'


#放透视表的位置
rng_pvt=sht_pvt.api.Range('A1')
#创建透视表关联的缓冲区
pvc=bk.api.PivotCaches().Create(\
          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
          SourceData=rng_data)
#创建透视表
pvt=pvc.CreatePivotTable(\
          TableDestination=rng_pvt,\
          TableName='透视表')
#设置字段
pvt.PivotFields('类别').Orientation=\
    xw.constants.PivotFieldOrientation.xlPageField    #页字段
pvt.PivotFields('类别').Position=1        #页字段中的第1个字段
pvt.PivotFields('产品').Orientation=\
    xw.constants.PivotFieldOrientation.xlColumnField   #列字段
pvt.PivotFields('产品').Position=1        #列字段中的第1个字段
pvt.PivotFields('产地').Orientation=\
    xw.constants.PivotFieldOrientation.xlRowField    #行字段
pvt.PivotFields('产地').Position=1        #行字段中的第1个字段
pvt.PivotFields('金额').Orientation=\
    xw.constants.PivotFieldOrientation.xlDataField    #值字段

运行程序，生成的数据透视表如图3所示。