pywin32+excel（一）——Python使用win32com/pywin32操作excel

1. 正式使用

一千个读者一千个哈姆雷特，单纯 save()表 close()工作薄 还有quit()Excel APP，这三个写法就有很多不同的解释。。。

不喜欢解释太多，直接看代码吧，给一个简单的读取某个excel文件 修改后保存的例子。

1.0 代码demo

excel = win32.Dispatch('Excel.Application')
excel.Visible = False  # 如果是True  会打开excel程序（界面）
excel.DisplayAlerts = 0  # 不显示警告信息
wb = excel.Workbooks.open('./xxx.xls')  # 打开一个excel文件 最好使用绝对路径
# office的函数调用时候不是特别在意大小写（有些函数大小写错了照样可以运行，有些就不行。。） wps有严格的大小写限制
ws = wb.Worksheets('Sheet1')  # 具体工作簿里的哪张工作表 一般默认Sheet1 操作都是对工作表操作的


c_column = [15, 16, 18, 41]  # 需要写入的数据
xing = 10  # 某一行，这里指第十行
ws.Cells(11, 5).Value = 2  # Cells(row,col) 先行后列 Cells(11,5)就指的是 E11单元格
ws.Cells(11, 5).offset(3,2).Value =1   # E11 偏移后 到了 F13
ws.Range('D' + str(xing)).value = c_column[0]  # 这里指对D10写入数据 15
ws.Range('E' + str(xing)).value = c_column[1]  # 这里指对E10写入数据 16
ws.Range('F' + str(xing)).value = c_column[2]  # 这里指对F10写入数据 18
ws.Range('G' + str(xing)).value = c_column[3]  # 这里指对G10写入数据 41
wb.save  # 保存表格（将修改保存到当前正在修改的表格）  保存当前工作簿
wb.SaveAs('xxx.xls')  # 另存为为另一个表格 路径也要是绝对路径（如果不想修改原表格 可以注销掉上一句 另存为即可）   将工作簿另存为
wb.Close(False)  #  True就是关闭该文件，并保存。不保存就是False  关闭工作簿
excel.Quit()  # 关闭excel操作环境。

1.1关于offset的说明

参考Excel VBA中Offset函数的用法和自己实测

• Offset函数实为定位函数。首先要找到中心点，如果以单元格A1为中心点，则A1的位置就是Offset(0,0)。
  
• 上图以A1为中心，Offset两个参数分别是行和列。以0为起点。
• 同理，E11 （11,5）偏移（3,2）后 （11+3-1,5+2-1）得到（13,6）即 F13
• E11偏移（1,0）到D11 (11,5)偏移（1,0） 即 （11+1-1，5+0-1）=（11,4）=11D 即 D11
• 所以offset(0,0)反倒会向左上移动一个。想要保持不变，应该是offset(1,1)。。。实测
• 上 左 是 负向 下 右 是正向
• 这一结论和网上搜到的VBA代码结论不一致，可能是pywin32库的问题
• 此外，注意到如果写入单元格的内容很长，不会自动拉长单元格，需要自己打开excel之后手动缩放（也就是保持格式不变啦）

1.2 range使用

1.2.1 合并单元格的搜索问题

如果想定义Range（比如在A1-D10这个范围内的单元格搜索“报账单”），则需要使用Range对象，根据
Application.Range property (Excel)，可知，定义Range的方式有两种：

//续上面的代码：
range=ws.Range("A1:D10").Find('报账单')  #返回的对象仍然是一个range 
或者
range=ws.Range(ws.Cell(1,1),ws.Cell(10,4)) # 使用Cell(row,col)对象来定义起始单元格
print(range.Address) #可以Range对象的Address属性来打印出 搜索结果的 第一项的首行首列编号

此外，在遇到合并单元格时，返回的Range对象的Address地址范围只有左上角第一个的值，如果想要获得该合并单元格所有的行/列，可以使用offset
比如上述工作表，如果Find(‘10V’) 使用range.Address返回的只是$D$38，同时，使用range.Areas.count返回的只是1，也就是说 这个合并单元格的起始位置是$D$38且只有1个（并不是返回基本单元格的数量）。为了方便后续操作，参考Find方法如何找到合并单元格的位置，针对这种问题有两种常见解决方法

1. 使用offset
2. 使用Range对象的MergeArea属性 Range.MergeArea property (Excel)

由于我面临的场景不是单一合并（只是列方向或只是行方向），同时有许多合并单元格且合并的行数/列数并不一致，不能写死，所以采用第二种方法，使用Range对象的MergeArea属性（也是大多数情况下更方便推荐的一种）

range_freq = ws.Range(ws.Cells(1, 1), ws.Cells(10, 11)).Find('freq')
range_range=range_freq.Find('range')
print(range_range.Address,range_range.Areas.count)
for i in range_range.MergeArea:
    print(i.Address)
    print(i.row,i.column) # 以数字形式代替字母打印出列号，行号  和 Cell(row,col)形式类似   
// 此时打印出来就可以看到这个合并单元格里所有的基本单元格的（合并的行或者列都会打印出来）
//如果某个合并单元格既有行合并又有列合并，则先按照行打印，再打印列

上图的excel表格区域打印后(先行后列)：

$D$38
$E$38
$D$39
$E$39
$D$40
$E$40
$D$41
$E$41
$D$42
$E$42
$D$43
$E$43
$D$44
$E$44
$D$45
$E$45
$D$46
$E$46
$D$47
$E$47

1.2.2 获取Range对象范围

# 获取10行-20行 1-9列之间的一个区域
freq_range = ws.Range(ws.Cells(10, 1),ws.Cells(20, 9))
freq_start_line = freq_range.Row  # 区域起始行   等价于  freq_range.End(-4162).Row 有标题
freq_end_line = freq_range.End(-4121).Row # 区域的最后一行  但是注意 这里这个值一般需要-1 因为返回的是当前区域最后一行的下一行

参考：MSDN——关于Range的End()

在Python中使用时，似乎不认识 xlUp 这些关键字，所以直接用对应的数值代替。Down对应最下面一行，ToLeft对应最左侧一栏 同理，Up就是最上面一行 ToRight就是最右边一列，四个数值就是Range的范围了

---

1.3 小trick

1.3.1 两个list构成一个dict

主要参考：Convert two lists into a dictionary

方法一是性能最高的方法，方法二类似列表构造器，但是成为字典构造器会更合适一点

//方法一
a=['1','2','3']
b=[1,2,3]
c=dict(zip(a,b))
c
Out[5]: {'1': 1, '2': 2, '3': 3}
//方法二
new_dict = {k: v for k, v in zip(keys, values)}

1.3.2 不使用多余的库实现 字符串中 数字和字母分开

参考
Python 分割文本使得 字母和数字 分开？

但是最后发现还是re模块用起来最简单直接

---

1.4 win32com.client.gencache.EnsureDispatch和Dispatch以及DispatchEx的区别

在使用win32com调用excel的使用过程中，最常见的创建excel对象的方式有以下三种：

# 第一种
excel = win32.gencache.EnsureDispatch('Excel.Application')
# 第二种
excel = win32com.client.Dispatch('Excel.Application')
# 第三种
excel = win32com.client.DispatchEx('Excel.Application')

参考

1. Python笔记：使用pywin32处理excel文件： win32com.client.DispatchEx('Excel.Application') #也可以用Dispatch，前者开启新进程，后者会复用进程中的excel进程
2. 排坑：运行win32com.client.Dispatch(‘Word.Application’)和docx.Documents.Open()报错
   ：DispatchEx会使用独立进程
3. 调用win32com.client.gencache.EnsureDispatch后的坑 object has no attribute：说明使用win32com.client.gencache.EnsureDispatch(）这个方式对函数调用的大小写会把控更严格，而win32com.client.Dispatch()则不会
4. Stack overflow-win32.Dispatch vs win32.gencache in Python. What are the pros and cons?
   

如下程序所示，

import win32com.client as win32
xl_dis = win32.Dispatch("Excel.Application")
xl_dis
Out[3]: <COMObject Excel.Application>

import win32com.client as win32
xl_ens = win32.gencache.EnsureDispatch("Excel.Application")
xl_ens
Out[3]: <win32com.gen_py.Microsoft Excel 14.0 Object Library._Application instance at 0x35671240>

Dispatch调用属于late binding 调用时python不知道Excel object有哪些属性和方法可以使用

而EnsureDispatch调用属于earlybinding 调用时python知道Excel object有哪些属性和方法可以使用，这是因为EnsureDispatch会先运行makeup.py文件， 反正找找自己的目录，类似D:\anaconda\Lib\site-packages\win32com\client

此外，还可以在C:\Users\用户名\AppData\Local\Temp\gen_py\3.7\00020813-0000-0000-C000-000000000046x0x1x8类似的目录下找到这些内容，随便打开一个，里面就是这个object的属性方法，python也就是通过读取这些文件才知道这个com对象有什么属性方法的。

如果先使用了EnsureDispatch,那么就会产生这些文件，此时，不管调用Dispatch还是EnsureDispatch结果都一样。但是如果删除了这些文件，二者的不同之处就又会显现，所以最大的不同就是一个会产这些辅助文件，一个不会。这些东西是写在缓存文件夹位置的，不确定什么时候就会被清理

个人观点：EnsureDispatch用在写程序的时候，程序弄完之后，就用Dispatch比较好吧

使用EnsureDispatch的时候，有时候需要删除缓存下的内容，重新产一遍

 def open_excel():
     try:
         try:
             excel = win32.gencache.EnsureDispatch('Excel.Application')
             # print(excel.Name,excel.Version)
         except AttributeError:
             import shutil
             import sys
             # Remove cache and try again.
             MODULE_LIST = [m.__name__ for m in sys.modules.values()]
             for module in MODULE_LIST:
                 if re.match(r'win32com\.gen_py\..+', module):
                     del sys.modules[module]
             shutil.rmtree(os.path.join(os.environ.get('LOCALAPPDATA'), 'Temp', 'gen_py'))
             excel = win32.gencache.EnsureDispatch('Excel.Application')
         excel.Visible = False  # True 打开excel界面
         excel.DisplayAlerts = 0  # 不弹窗显示警告信息 覆盖同名文件时不弹出确认框（但是无法打开同名文件时会出错 这个不属于弹窗警告信息）
     except Exception as e:
         print('ERROR 02: excel打开错误：', e)
     else:
         return excel

1.5 win32关闭当前程序操作的excel时也关闭了其他的excel窗口

参考Advanced Python and COM，可能是我没有认真看，找不出相关的有效信息。

1. Python笔记：使用pywin32处理excel文件： win32com.client.DispatchEx('Excel.Application') #也可以用Dispatch，前者开启新进程，后者会复用进程中的excel进程

excel=win32.DispatchEx("Excel.Application")
# 很奇怪  一开始使用上述语句，报错 (-2147221005, '无效的类字符串', None, None)

# 然后把大写换小写,然后可以正常执行，正常执行后，再换成大写也可以正常执行了
# 我以前是使用 win32.gencache.EnsureDispatch('Excel.Application')这种方式的
excel=win32.DispatchEx("excel.Application")

使用DispatchEx确实是新开了一个excel进程，关闭时候只关闭当前excel进程，而不会影响以及打开的其他excel进程。good

2.wps和office冲突的问题

2.1 问题1

Rebuilding cache of generated files for COM support has no attribute 'CLSIDToClassMap'

搜索后，根据Issue in using win32com to access Excel file可知

// 如果想确保python启动一个新的excel实例（例如，访问xlsm文件中的宏），则使用
xlApp = win32com.client.DispatchEx("Excel.Application")
//这样，我可以关闭应用程序而不会损害已经打开的实例。否则，我可以简单地使用
xlApp = win32com.client.Dispatch("Excel.Application")

pywin32这里的坑挺多的

2.1.1 问题1.1

excel打开错误： module 'win32com.gen_py.45541000-5750-5300-4B49-4E47534F4655x0x3x0' has no attribute 'CLSIDToClassMap'

参考：rdapaz/win32com.client.py，感谢这位大佬，大致就是把缓存清除，重新加载模块中的一个文件

try:
    xl = client.gencache.EnsureDispatch('Excel.Application')
except AttributeError:
    # Corner case dependencies.
    import os
    import re
    import sys
    import shutil
    # Remove cache and try again.
    MODULE_LIST = [m.__name__ for m in sys.modules.values()]
    for module in MODULE_LIST:
        if re.match(r'win32com\.gen_py\..+', module):
            del sys.modules[module]
    shutil.rmtree(os.path.join(os.environ.get('LOCALAPPDATA'), 'Temp', 'gen_py'))
    from win32com import client
    xl = client.gencache.EnsureDispatch('Excel.Application')

2.1.1 问题1.2

应该才是真的核心问题，在问题1.1解决后报错：

AttributeError: '<win32com.gen_py.Upgrade WPS Spreadsheets 3.0 Object Library (Beta).Workbooks instance at 0x1553056241672>' object has no attribute 'open'

也就是说 我调用的明明是excel 但是却使用了wps（大概就是端口冲突了），核心要解决的问题。
搜索发现：1. 安装并卸载WPS后，导致office COM组件调用失败的解决方案，大哥你真是个天使，我这个小白对端口和注册表一看就头大，爱你。
部分文章截取：
造成问题的原因是WPS恶意修改了office的com组件注册信息，并且，不知道为什么，卸载和重新安装都没有修复 。WPS把office的接口全部指向自己，对应注册表里 计算机\HKEY_CLASSES_ROOT\Interface{000208**-0000-0000-C0000-000000000046}\TypeLib 默认值改为了{45541000-5750-5300-4B49-4E47534F4655}，原本应该为{00020813-0000-0000-C000-000000000046}，Version改为了3.0，原本应该是1.7（office2010）。共有100+项，将以上修改正常就好了。附上修改后的注册表文件。

• 先试试这个2. WPS Office 与 Microsoft Office 出现冲突的解决方法 操作后，重启了Pycharm，报错信息已经发生了变化

AttributeError: '<win32com.gen_py.Microsoft Excel 16.0 Object Library.Workbooks instance at 0x1898809804360>' object has no attribute 'open'

不用修改注册表？直接使用wps配置工具取消兼容？（反正我把wps配置工具里的所有默认关联打开文件全都取消了，哈哈哈），有兴趣的可以自己去根据
1. 安装并卸载WPS后，导致office COM组件调用失败的解决方案这里面给的注册表去看看，看是不是使用wps配置工具后 注册表文件发生了改变。

反正是可以用了，解决，yeah！

2.2 问题2 不用管

pywintypes.com_error: (-2147417851, '服务器出现意外情况。', None, None)

尝试过读写excel数据出现服务器出现意外：我电脑上没有福昕阅读器或者其他阅读器的插件，看过了，无效。

还有很多方法：

1. open的文件名/路径过长，反正服务器报错，涉及的问题其实很多，出现这个报错不要考虑了，造成这个错误的可能太宽泛

2.3查看当前调用对象的名字和版本

• Application.Version property (Excel)

excel=win32.client.Dispatch('Excel.Application')
print(excel.Name)
print(excel.Version)

Microsoft Excel 15.0(office2013对应的版本) 不是很区分大小写（上面改成小写的 excel.name 或者 excel.version都可以）

但是如果在office2016版本（Microsoft Excel 16.0）运行小写形式的属性name和version，则报错

'<win32com.gen_py.Microsoft Excel 16.0 Object Library._Application instance at 0x3142152794952>' object has no attribute 'name'

所以为了防止版本冲突（微软office官方给的都是大写），最好都改成大写

3. 部署时候的问题

福昕阅读器com口冲突（服务器没有响应错误）

部署在其他人电脑的时候，报错
服务器没有响应 这个错误。

然后想起之前搜索相关错误的时候，出现过 excel中如果有福昕阅读器插件的话，会占用excel的com口，所以试了一下，关闭了那人电脑上的福昕阅读器的com加载项。 就ok了

win32com相关的使用document

中文：

• python win32com.client
• win32com 处理word和excel文档说明
• python用win32com对Excel的一些操作
• win32com 处理word和excel文档说明
• python3,用win32com操作excel及其宏
• Python Win32com模块操作excel的几个应用（一）
• Python-Win32com-Excel

英文：

• excelapp.py
• Automating Windows Applications Using COM
• Python Excel Mini Cookbook
• Python 2.7: Read and Write Excel file with win32com
• 下面这两个网站其实属于一个ip
  • Python and com blowing the rest away
  • PyWin32 Documentation

相关函数参考

• Application.Range property (Excel)
• Range object (Excel)
• Range.MergeArea property (Excel)
• Workbooks object (Excel)
• Worksheet object (Excel)
• Application.Cells property (Excel)
• Worksheet.SaveAs method (Excel)
• Workbooks.Close method (Excel)
• Workbooks.Open method (Excel)
• Application.Quit method (Excel)
• Workbook.Save method (Excel)
• Workbook.SaveAs method (Excel)
• win32.Dispatch vs win32.gencache in Python. What are the pros and cons?
  
• Python Programming on Win32-ebook-online