Microsoft Office编程(二）

3.中级实例

上一篇博客，主要用于让你使用Python控制Microsoft Office产品进行入门。接下来的实例，将真正运用于现实世界中。

• Excel

应用：从网上下载股票数据，然后直接插入Excel中。

摘自:python核心编程（第三版）

#!/usr/bin/python3
# -*- coding: utf-8 -*-
from tkinter import Tk
from time import sleep,ctime
from tkinter.messagebox import showwarning
import requests
import win32com.client as win32
import re

warn=lambda app:showwarning(app,'Exit?')
RANGE=range(3,8)
COLS=('TICKER','PRICE','CHG','%AGE')
URL='http://hq.sinajs.cn/list=s_sh000001'

def excel():
    app = 'Excel'
    '''
    静态调动,PythonWin运行Makepy工具，创建应用所需的对象
    动态调动代码：x1=win32.Dispatch('{0}.Application'.format(app))
    '''
    x1 = win32.gencache.EnsureDispatch('{0}.Application'.format(app))
    # 新建一个sheet
    ss = x1.Workbooks.Add()
    # 激活sheet
    sh = ss.ActiveSheet
    # 设置桌面上可见
    x1.Visible = True
    sleep(1)
    #设置单元格数值
    sh.Cells(1,1).Value='Python-to-{0} Stock Quote Demo'.format(app)
    sleep(1)
    sh.Cells(3,1).Value='Prices quoted as of: {0}'.format(ctime())
    sleep(1)
    sh.Cells(5, 1).Value ='上证指数'
    sleep(1)
    for i in range(4):
        sh.Cells(7,i+1).Value=COLS[i]
    sleep(1)
    #设置字体变粗
    sh.Range(sh.Cells(5,1),sh.Cells(5,4)).Font.Bold=True
    sh.Range(sh.Cells(7, 1), sh.Cells(7, 4)).Font.Bold = True
    sleep(1)
    row=8
    #获取上证指数的信息，并用re提取数据
    date_str=re.findall(r'上证指数,(.+)"',requests.get(URL).text)
    u=date_str[0].split(',')
    sh.Cells(row,1).Value=u[0]
    sh.Cells(row, 2).Value =u[1]
    sh.Cells(row, 3).Value =u[2]
    sh.Cells(row, 4).Value =u[3]

    warn(app)
    ss.Close(False)
    x1.Application.Quit()

if __name__ == '__main__':
    Tk().withdraw()
    excel()

• PowerPoint

应用：将纯文本文件生成PowerPoint演示文稿

#!/usr/bin/python3
# -*- coding: utf-8 -*-

from tkinter import Tk,Label,Entry,Button
from time import sleep
import win32com.client as win32

INDENT='    '
DEMO='''
PRESENTATION TITLE
    optional subtitle
slide 1 title
    slide 1 bullet 1
    slide 1 bullet 2
slide 2 title
    slide 2 bullet 1
    slide 2 bullet 2
        slide 2 bullet 2a
        slide 3 bullet 2b
'''

def txt2ppt(lines):
    ppoint=win32.gencache.EnsureDispatch('PowerPoint.Application')
    pres=ppoint.Presentations.Add()
    ppoint.Visible=True
    sleep(2)
    nslide=1
    for line in lines:
        if not line:
            continue
        linedata=line.split(INDENT)
        if len(linedata)==1:
            title=(line==line.upper())
            if title:
                stype=win32.constants.ppLayoutTitle
            else:
                stype=win32.constants.ppLayoutText
            s=pres.Slides.Add(nslide,stype)
            ppoint.ActiveWindow.View.GotoSlide(nslide)
            list(s.Shapes)[0].TextFrame.TextRange.Text=line.title()
            body=list(s.Shapes)[1].TextFrame.TextRange
            nline=1
            nslide+=1
            sleep((nslide<4) and 0.5 or 0.01)

        else:
            line='{0}\r\n'.format(line.lstrip())
            body.InsertAfter(line)
            para=body.Paragraphs(nline)
            para.IndentLevel=len(linedata)-1
            nline+=1
            sleep((nslide < 4) and 0.5 or 0.01)

    s=pres.Slides.Add(nslide,win32.constants.ppLayoutTitle)
    ppoint.ActiveWindow.View.GotoSlide(nslide)
    list(s.Shapes)[0].TextFrame.TextRange.Text="It's time for a slide-show!".upper()
    sleep(1.)
    for i in range(3,0,-1):
        list(s.Shapes)[1].TextFrame.TextRange.Text=str(i)
        sleep(1.)
    pres.SlideShowSettings.ShowType=win32.constants.ppShowTypeSpeaker
    ss=pres.SlideShowSettings.Run()
    #pres.ApplyTemplate
    list(s.Shapes)[0].TextFrame.TextRange.Text='FINIS'
    list(s.Shapes)[1].TextFrame.TextRange.Text=''

def _start(ev=None):
    fn=en.get().strip()
    try:
        f=open(fn,'U')
    except IOError as e:
        from io import StringIO
        f=StringIO(DEMO)
        en.delete(0,'end')
        if fn.lower()=='demo':
            en.insert(0,fn)
        else:
            import os
            en.insert(0,r"DEMO (can't open {0}:{1}".format(os.path.join(os.getcwd(),fn),str(e)))
        en.update_idletasks()
    txt2ppt(line.rstrip() for line in f)
    f.close()
if __name__ == '__main__':
    tk=Tk()
    lb=Label(tk,text='Enter file [or "DEMO"]:')
    lb.pack()
    en=Entry(tk)
    en.bind('<Return>',_start)
    en.pack()
    en.focus_set()
    quit=Button(tk,text='QUIT',command=tk.quit,fg='white',bg='red')
    quit.pack(fill='x',expand=True)
    tk.mainloop()