巧用python win32com模块操作excel文件
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
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Destination | Optional | Variant | A Range object that specifies where Microsoft Excel will place the results. If the range is larger than a single cell, the top left cell is used. |
| DataType | Optional | XlTextParsingType | The format of the text to be split into columns. |
| TextQualifier | Optional | XlTextQualifier | Specifies whether to use single, double, or no quotes as the text qualifier. |
| ConsecutiveDelimiter | Optional | Variant | True to have Excel consider consecutive delimiters as one delimiter. The default value is False. |
| Tab | Optional | Variant | True to have DataType be xlDelimited and to have the tab character be a delimiter. The default value is False. |
| Semicolon | Optional | Variant | True to have DataType be xlDelimited and to have the semicolon be a delimiter. The default value is False. |
| Comma | Optional | Variant | True to have DataType be xlDelimited and to have the comma be a delimiter. The default value is False. |
| Space | Optional | Variant | True to have DataType be xlDelimited and to have the space character be a delimiter. The default value is False. |
| Other | Optional | Variant | True to have DataType be xlDelimited and to have the character specified by the OtherChar argument be a delimiter. The default value is False. |
| OtherChar | Optional | Variant | Required if Other is True; the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored. |
| FieldInfo | Optional | Variant | An array containing parse information for the individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed. |
| DecimalSeparator | Optional | Variant | The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting. |
| ThousandsSeparator | Optional | Variant | The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting. |
| TrailingMinusNumbers | Optional | Variant | Numbers that begin with a minus character. |
可以看到 关于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)
该方法亲测可用，特此奉上～～～
快来扫码关注我的公众号 获取更多爬虫、数据分析的知识！
