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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180602G1OILQ00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券