python excel access交互_Python操作Word、EXCEL,ACCESS

灰太狼 2022-10-22 11:49 230阅读 0赞

python Excel 编程

1)Excel hyperlink:

xlsApp = win32com.client.Dispatch('Excel.Application')

cell = xls.App.ActiveSheet.Cells(1,1)

cell.Hyperlink.Add(cell,'http://xxx')

2)Excel row/column count:

sht = xlsApp.ActiveSheet

sht.Columns.Areas.Count

sht.Rows.Areas.Count

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

［1］使用PyExcelerator读写EXCEL文件(Platform: Win,Unix-like)

优点：简单易用        缺点：不可改变已存在的EXCEL文件。

PyExcelerator是一个开源的MS Excel文件处理python包。它主要是用来写 Excel 文件.URL:    http://sourceforge.net/projects/pyexcelerator/

我没有找到关于PyExcelerator的文档。只是看到了limodou的一篇介绍。

http://blog.donews.com/limodou/archive/2005/07/09/460033.aspx

这个包使用起来还是比较简单的：)。带了很多小例子，可以参照。

例mini.py.

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

\#!/usr/bin/env python

\# -\*- coding: windows-1251 -\*-

\_\_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):

"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()

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

python Word 编程

http://doc.zoomquiet.org/data/20051227094903/

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() \# 增加一行

\# 转换为htmlwc = win32com.client.constants

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()

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*python ACCESS编程

因为使用的第三方组件用到python，所以需要了解python的数据库操作。

后来找到了方法，写了如下的sample：

import win32com.client

def db1():

print "Start db1."         try:             conn = win32com.client.Dispatch(r'ADODB.Connection')             conn.Open('Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sa;Initial Catalog=ECI-SERVICE;Data Source=10.240.4.135')             rs = win32com.client.Dispatch(r'ADODB.Recordset')             rs.Cursorlocation=3             rs.Open('select \* from EBP\_B\_AS\_ALERT',conn)             rs.MoveFirst()

for x in range(rs.RecordCount):                 if rs.EOF:                     print "End of records"                     break                 else:                     print rs.Fields.Item(1).Value                     rs.MoveNext()             rs.Close()             conn.Close()         except:             print "Except, now."

用的是win32com 的extension。Python2.2.3 自身不带win32 extension，需要安装win32all-162(win extensions).exe。

代码比较简单，可惜没有找到api，所以提供哪些方法只能在网上找。