pythonexcel代替vba_excel中想实现使⽤Python代替VBA,请
问应该怎么做?
python模块:win32com⽤法详解
使⽤技巧
import win32com
from win32com.client import Dispatch, constants
w = win32com.client.Dispatch('Word.Application')
# 或者使⽤下⾯的⽅法,使⽤启动独⽴的进程:
# w = win32com.client.DispatchEx('Word.Application')
# 后台运⾏,不显⽰,不警告
w.Visible = 0
w.DisplayAlerts = 0
# 打开新的⽂件
doc = w.Documents.Open( FileName = filenamein )
# worddoc = w.Documents.Add() # 创建新的⽂档
# 插⼊⽂字
myRange = doc.Range(0,0)
myRange.InsertBefore('Hello from Python!')
# 使⽤样式
wordSel = myRange.Select()
wordSel.Style = constants.wdStyleHeading1
# 正⽂⽂字替换
w.Selection.Find.ClearFormatting()
w.Selection.Find.Replacement.ClearFormatting()
w.Selection.Find.Execute(OldStr, False, False, False, False, False, True, 1, True, NewStr, 2)
# 页眉⽂字替换
w.ActiveDocument.Sections[0].Headers[0].Range.Find.ClearFormatting()
w.ActiveDocument.Sections[0].Headers[0].Range.Find.Replacement.ClearFormatting()
w.ActiveDocument.Sections[0].Headers[0].Range.Find.Execute(OldStr, False, False, False, False, False, True, 1, False, NewStr, 2)
# 表格操作
doc.Tables[0].Rows[0].Cells[0].Range.Text ='123123'
worddoc.Tables[0].Rows.Add() # 增加⼀⾏
# 转换为html
wc = stants
w.ActiveDocument.WebOptions.RelyOnCSS = 1
w.ActiveDocument.WebOptions.OptimizeForBrowser = 1
w.ActiveDocument.WebOptions.BrowserLevel = 0 # constants.wdBrowserLevelV4
w.ActiveDocument.WebOptions.OrganizeInFolder = 0
w.ActiveDocument.WebOptions.UseLongFileNames = 1
w.ActiveDocument.WebOptions.RelyOnVML = 0
w.ActiveDocument.WebOptions.AllowPNG = 1
w.ActiveDocument.SaveAs( FileName = filenameout, FileFormat = wc.wdFormatHTML )
# 打印
doc.PrintOut()
# 关闭
# doc.Close()
w.Documents.Close(wc.wdDoNotSaveChanges)
w.Quit()
(3)处理excel
[1]使⽤PyExcelerator读写EXCEL⽂件(Platform: Win,Unix-like)
优点:简单易⽤ 缺点:不可改变已存在的EXCEL⽂件。
PyExcelerator是⼀个开源的MS Excel⽂件处理python包。它主要是⽤来写 Excel ⽂件.URL: pyExcelerator download 我没有到关于PyExcelerator的⽂档。只是看到了limodou的⼀篇介绍。
这个包使⽤起来还是⽐较简单的:)。带了很多⼩例⼦,可以参照。
例mini.py.
=================================
#!/usr/bin/env python
# -*- coding: windows-1251 -*-
# Copyright (C) 2005 Kiseliov Roman
__rev_id__ = """$Id: mini.py,v 1.3 2005/03/27 12:47:06 rvk Exp $"""
"导⼊模块
from pyExcelerator import *
"⽣成⼀个⼯作薄
w = Workbook()
"加⼊⼀个Sheet
ws = w.add_sheet('Hey, Dude')
"保存
w.save('mini.xls')
=================================
[2]使⽤COM接⼝,直接操作EXCEL(只能在Win上)
优点:可以满⾜绝⼤数要求。缺点:有些⿇烦。:-)
这⽅⾯的例⼦很多,GOOGLE 看吧:-). ⽂档也可以参看OFFICE⾃带的VBA EXCEL 帮助⽂件(VBAXL.CHM)。这⾥⾯讲述了EXCEL VBA 的编程概念,
不错的教程!另外,《Python Programming on Win32》书中也有很详细的介绍。这本书中给出了⼀个类来操作EXCEL ⽂件,可以很容易的加以扩展。
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from win32com.client import Dispatch
import win32com.client
class easyExcel:
"""A utility to make it easier to get at Excel. Remembering
to save the data is your problem, as is error handling.
Operates on one workbook at a time."""
def __init__(self, filename=None):
self.xlApp = win32com.client.Dispatch('Excel.Application')
if filename:
self.filename = filename
self.xlBook = self.xlApp.Workbooks.Open(filename)
else:
self.xlBook = self.xlApp.Workbooks.Add()
self.filename = ''
def save(self, newfilename=None):
if newfilename:
self.filename = newfilename
self.xlBook.SaveAs(newfilename)
else:
self.xlBook.Save()
def close(self):
self.xlBook.Close(SaveChanges=0)
del self.xlApp
def getCell(self, sheet, row, col):
"Get value of one cell"
sht = self.xlBook.Worksheets(sheet)
return sht.Cells(row, col).Value
def setCell(self, sheet, row, col, value):
"set value of one cell"
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).Value = value
def getRange(self, sheet, row1, col1, row2, col2):
关于python的书"return a 2d array (i.e. tuple of tuples)"
sht = self.xlBook.Worksheets(sheet)
return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value def addPicture(self, sheet, pictureName, Left, Top, Width, Height): "Insert a picture in sheet"
sht = self.xlBook.Worksheets(sheet)
sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height) def cpSheet(self, before):
"copy sheet"
shts = self.xlBook.Worksheets
shts(1).Copy(None,shts(1))
"下⾯是⼀些测试代码。
if __name__ == "__main__":
PNFILE = r'c:\screenshot.bmp'
xls = easyExcel(r'D:\test.xls')
xls.addPicture('Sheet1', PNFILE, 20,20,1000,1000)
xls.cpSheet('Sheet1')
xls.save()
xls.close()
(4)python调⽤短信猫控件,发短信
#! /usr/bin/env python
#coding=gbk
import sys
import win32com.client
ocxname='ShouYan_SmsGate61.Smsgate'
axocx=win32com.client.Dispatch(ocxname)
axocx.CommPort=8#设置COM端⼝号
axocx.SmsService='+86138********'#设置短信服务号码axocx.Settings='9600,n,8,1'#设置com端⼝速度
axocx.sn='loyin'
c=axocx.Connect(1)#连接短信猫或⼿机
print '连接情况',axocx.Link()
axocx.SendSms('python确实是很好的','151********',0)#
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论