用Excel展示SQL Server中的数据 (III): IronPython与自动化

在本系列文章中，“数据库连接”介绍了如何将SQL Server中的数据导入Excel工作簿，“宏与自动化”讨论了如何使用宏来自动化数据导入。本文将讨论如何利用IronPython来自动化数据发布。相关代码可以从这里下载。

利用数据库连接和宏，我可以自动地获取Bug的分布并生成柱状图。如果这些信息需要发布，我可以将包含数据连接和宏的Excel数据薄发布给关系人，让他们自行获取数据。这是数据获取的拉模式：用户主动获取数据。另一个选择是推模式：我定期地将当前的结果用邮件发送给他们。若直接发送Excel文件，收件人必须安装Excel才能查看，于是我打算是将图表嵌入在邮件中发送。如何才能获得Excel中的图形呢？一个简单的方法是利用COM互操作（COM interop）调用Excel数据薄的SaveAs函数，将Excel文件另存为HTML文件，其对应文件夹中包含png格式的图形。

1. 用IronPython操控Excel

Excel提供了COM接口以支持自动化。.NET Framework提供了.NET COM interop使得.NET平台上的所有语言都可以调用COM接口。因此，我可以利用IronPython来操纵Excel。关于这一主题，IronPython Cookbook上有一篇很好的教程，我就不再赘述。本文将通过代码来说明相关技术。

如下图所示，在同一文件夹下放置Book2.xlsm和excel.py。Book2.xlsm包含数据库连接、数据表、柱状图和宏RefreshData。excel.py调用宏RefreshData刷新Book2.xlsm中的数据表和柱状图，然后将其另存为Book2.htm。这样，Book2.xlsm中的图形就会保存在文件夹Book2_files中。

excel.py的源代码如下：

1:   import System
2:   import clr
3:   clr.AddReference('Microsoft.Office.Interop.Excel')
4:   import Microsoft.Office.Interop.Excel as Excel
5:  
6:   file_path = System.Environment.CurrentDirectory
7:   file_name = 'Book2'
8:   excel_file = r'%s%s.xlsm' % (file_path, file_name)
9:  
10:  html_file = r'%s%s.htm' % (file_path, file_name)
11:  html_folder = r'%s%s_files' % (file_path, file_name)
12:  System.IO.File.Delete(html_file)
13:  System.IO.Directory.Delete(html_folder, True)
14:  
15:  ex = Excel.ApplicationClass()
16:  ex.Visible = False
17:  
18:  wb = ex.Workbooks.Open(excel_file)
19:  wb.Application.Run('RefreshData')
20:  wb.SaveAs(Filename = html_file, FileFormat = Excel.XlFileFormat.xlHtml)
21:  wb.Close(False)

• 第1行：导入名空间System。
• 第2行：导入IronPython模块clr。
• 第3行：加载Excel的COM代理的.NET程序集。
• 第4行：导入名空间Microsoft.Office.Interop.Excel，并将其命名为Excel。
• 第6~8上：获得Book2.xlsm的绝对路径，并将其保存于变量excel_file。之所以保存绝对路径，是因为第16行的Open函数只支持绝对路径。
• 第10~13行：删除文件Book2.htm和文件夹Book2_files，以避免另存操作失败。
• 第15行：实例化Excel的ApplicationClass，启动Excel程序。
• 第16行：将Excel程序的可见性设为False，使得Excel窗口不可见。
• 第18行：打开工作簿Book2.xlsm。
• 第19行：调用宏RefreshData。
• 第20行：将工作簿另存为HTML文件。
• 第21行：关闭工作簿。

感谢Excel、.NET和IronPython的无缝集成，代码简短且直观。ipy_excel.py运行完毕，Book2.xlsm的Sheet1的数据表会保存在Book2_filessheet001.htm中，图形会保存在Book2_filesimage001.png中。

2. 用IronPython发送邮件

利用如下代码即可用邮件将图片发出。

1:  import System.Net.Mail as Mail
2:  
3:  netmail = Mail.MailMessage('liang.shi@live.com', 'liang.shi@live.com')
4:  netmail.IsBodyHtml = True
5:  netmail.Subject = 'Test'
6:  
7:  image = Mail.LinkedResource(r'.Book2_filesimage001.png', 'image/gif')
8:  image.ContentId = 'image001'
9:  
10:  content = 'result <img src="cid:image001">'
11:  html_body = Mail.AlternateView.CreateAlternateViewFromString(content, None, 'text/html')
12:  html_body.LinkedResources.Add(image)
13:  netmail.AlternateViews.Add(html_body)
14:  
15:  netsmtp = Mail.SmtpClient('some_smtp_server')
16:  netsmtp.UseDefaultCredentials = True
17:  netsmtp.Send(netmail)

代码很直观，无需再做解释。唯一值得关注的是，第8行的ContentId的值（字符串image001）需要与第10行的cid的值（也是字符串image001）相同，否则邮件不能显示图片。下图是在Outlook中收到的邮件。

3. 小结

本系列文章涉及了多项技术：

1. SQL Server提供了灵活的T-SQL，允许程序员简练地表达数据计算逻辑。
2. Excel利用SQLOLEDB控件，执行SQL查询获得SQL Server中的数据。
3. Excel提供了数据表和多种图形，可以美观地呈现数据。
4. 宏是Excel各组件间的胶水。它能够响应事件，调用组件函数，用简短的代码完成看似复杂的操作。
5. COM和.NET的互操作，使得.NET平台上的语言能够方便地操控COM对象。
6. IronPython具备Python的优雅语法和.NET的强大能力，是各种.NET和Windows应用的胶水。

“抱定一种统一语言不放的年代就快结束了”，我们需要用合适的工具做事（choose the right tool for the job）。本文的例子也许过于简单，但是它揭示了开放的思路会带来新颖的设计，组合或混搭的解决方法会节省大量的时间。