代码改变世界

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

2012-04-02 10:23  liangshi  阅读(2404)  评论(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中。

image_thumb

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中。

image_thumb[2]%2060B3056A

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中收到的邮件。

image_thumb[5]%200664CC35

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)。本文的例子也许过于简单,但是它揭示了开放的思路会带来新颖的设计,组合或混搭的解决方法会节省大量的时间。