Windows PowerShell进阶Excel ComObject

这篇接着讲csv的相关操作，不过是要用powershell调用excel程序，然后执行宏macro来实现。需要小伙伴们有一点VBA语言基础，没有的话可以先去简单学下VBA，了解下基本的语法即可。或者这篇你完全当做兴趣了解看下也行， 心里有个数，以后有会遇到这块的需求时可以再回来看。这个网上资源很多，我自己百度盘现有一个资源，大家感兴趣的也可以看下这个，留作自己以后学习用。链接：https://pan.baidu.com/s/12414M3IGcetBYm5U0IKZGg 密码：ex48

28-1 Excel VBA基础教程

我们可以用录制宏来实现VBA主框架，剩下的需要细微修改下，比如下面的A1,A2之类的在实际开发过程中肯定得用变量来表示。

（PS:找到了一个不错的免费的录屏软件，(^▽^)）

28-2 录制宏并执行

我们可以看到录制宏的源代码是VBA语言写的，所以大家要多少有点VBA基础。

下面演示的是我们希望powershell脚本最终实现的效果（录的时候最后合并完的时候第6行多余的表头忘了删了），这个例子里面我们有2个csv file

harware.csv，系统的信息：类似分辨率，核心数

startup.csv，开始文件夹里面的文件，包含快捷方式和其源文件路径

28-3 合并CSV演示

我们可以看到原始录制的宏源码还是很粗糙的，需要额外修改下

再次强调下，这是powershell的相关文章，所以我不会过多地解释别的语言的使用，还请大家见谅。

接下来我们一步一步用powershell来实现。

拆解步骤

0. 初始化参数，函数等

1. 创建Excel COM 对象示例

powershell虽然强大，但并不是万能的，对Windows的有些操作还需要其他的接口来实现，其中一种就叫做COM(Component Object Model)，简单来讲就是其他应用对外开放的一个接口，可以让别的语言来对其交互使用，英语好的可以看下面微软官方介绍：

https://msdn.microsoft.com/en-us/library/windows/desktop/ms694363(v=vs.85).aspx

https://docs.microsoft.com/en-us/powershell/scripting/getting-started/cookbooks/creating-.net-and-com-objects--new-object-?view=powershell-6

那么对于excel我们就可以用下面命令来获取它的一个对象示例。

new-object: 新建对象

-comobject： 对象类型

excel.application： 对象名称，C#中叫做命名空间（namespace）

28-4 COM Object

2. 启用允许使用宏

28-5 宏被禁用

有的电脑比如公司的可能默认情况下是关闭可使用宏的功能的，所以为了保险起见我们都强制的把它enable，这个是通过修改注册表来完成的，以后会专门有篇幅讲针对注册表的操作。

2. 准备宏

目前我知道有3个方法来准备宏（依次代码量由少到多）：

打开提前保存宏的excel template文件，一般后缀是.xlsm

新建工作表，导入单独保存好的Module文件，一般后缀是.bas

28-6 导出宏

新建工作表，新建module，写入macro code, 从File或者从String

第3个我跑的时候出了点小问题,在试图添加module的时候报错了。还有就是确保module name不要和宏的入口函数名字一致，不然报错无法运行“CopyCsvFile”宏。可能是因为该宏在此工作簿中不可用，或者所有的宏都被禁用。

我查了下[Microsoft.Vbe.Interop.vbext_ComponentType]这个类，其定义在Microsoft.Vbe.Interop.dll这个DLL里面，一般在C:\Windows\System32或者C:\Windows\SysWOW64下面，而我是科学安装的excel（你懂的），所以组件可能有点阉割，我看了下我这2个文件夹下是没有这个DLL文件的。

但是比较巧的是我安装了印象笔记，它因为要支持excel，word在笔记里面的编辑，其安装目录下恰好是有这个文件的。

28-7 VBE DLL

我会把这个DLL文件一并上传到码云上，如果你也出现这个问题的话到时候下下来，然后在上面出错那一行代码前面运行下面的代码：

3. 运行宏并保存

XlFileFormat请参考 https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

3. 退出清理

回收excel com对象方法使用细节请访问官网 https://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject(v=vs.110).aspx

4. 完整运行验证4.1 $MacroSource="Template"

不显示excel, 1s

28-8 Test1

显示excel, 2s

28-9 Test2

因为我们只用了2个csv而且数据就几行，所以看不出来明显差别，实际中推荐纯后台跑。

4.2 $MacroSource="ModuleFile"

28-10 Test3

4.3 $MacroSource="VBFile"

28-11 Test4

结语

使用powershell调用excel运行macro就说到这了， 细心地你一定发现这个方法原则上来说可以实现对excel的任意操作，各种改格式，画图等等，只要你能录制好正确的宏并在此基础上编辑好VBA code。这里简单讲了下CSV的合并，就当是抛砖引玉了。下一篇打算讲下在没有装excel的电脑上怎么操作excel数据。提前透露下，也是.NET底层的方法。

相关代码，文件晚些会传到 https://gitee.com/chaoyuew/powershell/tree/feature/wechat/SPPS28

参考链接：

https://msdn.microsoft.com/en-us/library/windows/desktop/ms694363(v=vs.85).aspx

https://docs.microsoft.com/en-us/powershell/scripting/getting-started/cookbooks/creating-.net-and-com-objects--new-object-?view=powershell-6

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-displayalerts-property-excel

https://aritrasaha.wordpress.com/2010/03/20/add-vba-code-and-references-to-excel-document/

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

https://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject(v=vs.110).aspx

https://stackoverflow.com/questions/660319/where-can-i-find-all-of-the-com-objects-that-can-be-created-in-powershell