用Excel展示SQL Server中的数据 (III): IronPython与自动化
2012-04-02 10:23 liangshi 阅读(2608) 评论(0) 编辑 收藏 举报在本系列文章中,“数据库连接”介绍了如何将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. 小结
本系列文章涉及了多项技术:
- SQL Server提供了灵活的T-SQL,允许程序员简练地表达数据计算逻辑。
- Excel利用SQLOLEDB控件,执行SQL查询获得SQL Server中的数据。
- Excel提供了数据表和多种图形,可以美观地呈现数据。
- 宏是Excel各组件间的胶水。它能够响应事件,调用组件函数,用简短的代码完成看似复杂的操作。
- COM和.NET的互操作,使得.NET平台上的语言能够方便地操控COM对象。
- IronPython具备Python的优雅语法和.NET的强大能力,是各种.NET和Windows应用的胶水。
“抱定一种统一语言不放的年代就快结束了”,我们需要用合适的工具做事(choose the right tool for the job)。本文的例子也许过于简单,但是它揭示了开放的思路会带来新颖的设计,组合或混搭的解决方法会节省大量的时间。