python编辑excel_python 如何能对一个excel文件进行编辑

展开全部

你需要win32api模块# -*- coding: gbk -*-

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 setRange(self, sheet, leftCol, topRow, data):

"""insert a 2d array starting at given location.

Works out the size needed for itself"""

bottomRow = topRow + len(data) - 1

rightCol = leftCol + len(data[0]) - 1

sht = self.xlBook.Worksheets(sheet)

sht.Range(

sht.Cells(topRow, leftCol),

sht.Cells(bottomRow, rightCol)

).Value = data

def getContiguousRange(self, sheet, row, col):

"""Tracks down and across from top left cell until it

encounters blank cells; returns the non-blank range.

Looks at first row and column; blanks at bottom or right

are OK and return None witin the array"""

sht = self.xlBook.Worksheets(sheet)

# find the bottom row

bottom = row

while sht.Cells(bottom + 1, col).Value not in [None, '']:

bottom = bottom + 1

# right column

right = col

while sht.Cells(row, right + 1).Value not in [None, '']:

right = right + 1

return sht.Range(sht.Cells(row, col), sht.Cells(bottom, right)).Value

def fixStringsAndDates(self, aMatrix):

# converts all unicode strings and s

newmatrix = []

for row in aMatrix:

newrow = []

for cell in row:

if type(cell) is UnicodeType:

newrow.append(str(cell))

elif type(cell) is TimeType:

newrow.append(int(cell))

else:

newrow.append(cell)

newmatrix.append(tuple(newrow))

return newmatrix

#下面将桌面文件62616964757a686964616fe78988e69d8331333332636434test.xlsx的Worksheets(2)写上一些数据,再把这些数据转置粘贴到Worksheets(1)

if __name__ == "__main__":

try:

xls = easyExcel(r'C:\Users\...\Desktop\test.xlsx')

st=xls.xlBook.Worksheets(1)

st2=xls.xlBook.Worksheets(2)

st2.Range("A1:G2").Value=900

st2.Range("A1:G2").Copy()

st.Range("A1:B7").Value=0

st.Range("A1").PasteSpecial(Transpose=True,SkipBlanks=True,)

finally:

xls.save()

xls.close()