Python操作excel文件的第三方库有很多，小爬就常用openPyxl库来操作已有的excel文件，它对xlsx、xlsm等格式的支持都较好。可openPyxl也有不足，它难以实习VBA中的很多功能。如果我们平日里对VBA语法很熟悉，则可以通过win32com.client来操纵excel文件，语法非常类似。

之所以不直接使用VBA，是因为VBA擅长跟excel打交道，不擅长跟外部应用打交道。小爬最近就遇到这样一个自动化场景：先利用python爬虫的方法，获取到服务器端的多个excel文件，然后对这些excel文件进行跨表操作，单纯的VBA实现起来比较繁琐，用python和VBA各实现一部分不利于脚本的封装，割裂感较强。

话不多说，我们看看如何用win32com来控制excel，首先我们需要用pip安装pywin32库，之后就可以使用了：

import win32com.client

下面的代码演示了一些常规的语法操作，与vba如出一辙，只是需要代码pythonic：

import win32com.client
import os
base_dir=os.path.dirname(os.path.abspath(__file__)) # 获取当前路径
xlApp = win32com.client.Dispatch('Excel.Application')
xlApp.Visible=1 # 显示excel界面
filename="test.xlsx"
fullPath=os.path.join(base_dir,filename) # 得到完整的filepath
xlBook = xlApp.Workbooks.Open(fullPath, ReadOnly = False) #打开对饮的excel文件
sht = xlBook.Worksheets('Sheet1') # 打开对应名称的sheet
sht.UsedRange.ClearContents() # 对当前使用区域清除内容
nrows=sht.UsedRange.Rows.Count # 获取使用区域的行数
sht.UsedRange.Copy() #复制
sht.Activate() # 激活当前工作表

光这些还不够，比如我们希望实现excel的复制&粘贴值操作，vba的语法类似这样：

Sub 宏1()
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

这段VB风格的代码如何pythonic且不出错呢，你可能首先会想这样改造：

    Range("A1").Select()
    Selection.CurrentRegion.Select()
    Selection.Copy()
    Selection.PasteSpecial(Paste=xlPasteValues, Operation=xlNone, SkipBlanks=False, Transpose=False)

VBA中默认你操作的当前worksheet，所以可以直接使用Range对象，Selection对象，但是python中不能直接这样简写，改造和简化后应该是：

    sht.Range("A1").CurrentRegion.Copy()
    sht.Range("A1").CurrentRegion.PasteSpecial(Paste=xlPasteValues, Operation=xlNone, SkipBlanks=False, Transpose=False)

其实这样还是会报错，因为python并无法知道xlPasteValues、xlNone这些常量到底为多少，因为我们没有提前定义它。如何查询这些常量实际的值，一个简单的方法还是通过VBA的对象浏览器，打开excel，按住快捷键Alt + F11进入VBE界面，见下图：

有了这些，上面的伪代码就可以改成如下形式，成功运行通过：

sht.cells(1,1).PasteSpecial(Paste=-4163, Operation=-4142, SkipBlanks=False, Transpose=False)

小爬知道了这些后，想用分列功能将excel某列文本型数值转为常规的数值格式，操作如动态所示，

vba代码示例如下：

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

我们故技重施，可以查到xlDelimited、xlDoubleQuote 这些常量的值，但是这个Array(1, 1) 怎么转化为python语法，小爬目前还没找到合适的方法，有知道的童鞋，可以留言区告诉我，谢谢~

上面的例子说明，上文提到的方法有一定局限性。其实我们可以利用选择性粘贴（乘以1）来达到同样的效果：文本型数字转常规数值，演示动图如下：

这段方法，需要借助一个辅助单元格，将其赋值1，待操作完毕后，再清空该辅助单元格的值即可，这个用VBA代码示例如下：

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B2").Select
    Selection.Copy
    Range("A2:A20").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False

这次，我们可以用上面的方法得到xlPasteAll、xlMultiply等常量的值，将上面的代码python化即可；

利用这个方法，我们还可以很轻易得到某一列的最后一行的行号，比如A列的，可以这样写：

max_rowA=sht.cells(sht.Rows.Count,1).End(-4162).Row

这回，你应该能看出来上面代码中的-4162　是怎么来的了。

希望上面列举的示例，能给各位希望用python win32com.client来操作excel文件的童鞋，一点点借鉴和提示！还不赶紧动手试试？

=============================================================================================

（2020-08-27日更新）

再次查阅了微软的官方文档后，找到关于数据分列（TextToColumns)实现文本型数值转为常规数值的解决方法，见网址： https://docs.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns

详细内容如下：

Parameters

可以看到 关于excel录屏得到的VBA分列的代码中，“ FieldInfo :=Array(1, 1) ”，FieldInfo 字段是可选的，当我们并不准备把数据分成几列的时候，可以不需要该字段。比如我想要对F列进行分列，将文本型数字转为数字，pythonic（win32com.client)的代码是这样的，亲测可用：

workNumSht.Columns("F:F").TextToColumns(Destination=workNumSht.Range("F1"), DataType=2, TrailingMinusNumbers=True)

=============================================================================================

（2020-12-21日更新）

上文的方法中，当时小爬还没能找到VBA中的Array类型如何利用python win32com模块来表达，现已找到解决办法。我们知道在VBA中，如下代码就可以轻松将某一行值写入一个一维数组：

dim arr() as string
arr=sheet.Range("A1:M1").Value

我们就可以假定vba中的 sheet.Range("A1:M1").Value 属性可以得到一个类似于Array(1,1)的数组对象。我们可以这样去验证这个vba数组对象在python win32com中是如何实现的。我试着利用win32com模块来 print(type(sht.Range("A1:B1").Value)) ,结果系统返回了一个Tuple元组类型。

这个时候，我们VBA中录制得到的分列功能（TextToColumns）就可以随意python化了。比如下图中，我们希望将B列的字母和数字分开成两列：

VBA录制宏得到这段分列的代码如下：

Sub 宏1()
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="_", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub

我们在python　win32com.client中可以这样表达：

xlDelimited=1
xlDoubleQuote=1
sht.Columns("B:B").TextToColumns(Destination=sht.Range("B1"), DataType=xlDelimited, 
TextQualifier=xlDoubleQuote, ConsecutiveDelimiter=False, Tab=False, Semicolon=False, 
Comma=False, Space=False, Other=True, OtherChar ="_", FieldInfo=((1, 1),(2,1)),
 TrailingMinusNumbers=True)

该方法亲测可用，特此奉上～～～

快来扫码关注我的公众号 获取更多爬虫、数据分析的知识！