#coding=utf-8
from win32com.client importDispatchimportpywintypes'''查看excel最大行数和列数
打开一个空白新建EXCEL表格，按CTRL+下箭头，可以查看到最大行数；按CTRL+右箭头,
可以查看到最大列标(若想显示列数，可在最右一列的某单元格中输入=column()回车，出现数字就是最大列数)。'''
classExcel:def __init__(self, filename=None):
self.excel= Dispatch('Excel.Application') #打开excel应用程序
self.excel.DisplayAlerts =0
self.excel.Visible=0if filename is notNone:
self.filename=filename
self.workBook=self.excel.Workbooks.Open(filename)else:
self.filename= ''self.workBook= self.excel.Workbooks.Add() #新建excel
#保存excel文件
def save(self, save_path=None):if notsave_path:
self.filename=save_path
self.workBook.SaveAs(save_path)else:
self.workBook.Save()#关闭excel文件
defclose(self):
self.workBook.Close(SaveChanges=0)#添加工作表
def addSheet(self, sheetName=None):
sheet=self.workBook.Sheets.Add()if sheetName is notNone:
sheet.Name=sheetNamedef copySheet(self, srcSheetName, destSheetName=None, before=None):
sheet=self.workBook.WorkSheets(srcSheetName)#在指定工作表的后面插入新的工作表
if before isNone:
sheet.Copy(None, sheet)
newSheet=sheet.Nextif destSheetName is notNone:
newSheet.Name=destSheetName#在指定工作表的前面插入新的工作表
else:
sheet.Copy(before, None)if destSheetName is notNone:
index= before.Index - 1 #所获新插入的工作表所在的位置(工作表的index从1开始)
self.workBook.Sheets(index).Name =destSheetName#删除工作表
defdeleteSheet(self, sheetName):try:
sheet=self.workBook.WorkSheets(sheetName)if sheet is notNone:
sheet.Delete()_error:pass
#获取指定单元格的内容
defgetCell(self, sheet, row, col):
workSheet=self.workBook.WorkSheets(sheet)returnworkSheet.Cells(row, col).Value#设置单元格内容
defsetCell(self, sheet, row, col, val):
workSheet=self.workBook.WorkSheets(sheet)
workSheet.Cells(row, col).Value=val#获取一块区域的内容
defgetRange(self, sheet, row1, col1, row2, col2):
workSheet=self.workBook.WorkSheets(sheet)returnworkSheet.Range(workSheet.Cells(
row1, col1), workSheet.Cells(row2, col2)).Value#拷贝一块区域
defcopyRange(self, sheet, row1, col1, row2, col2, targetRow, targetCol):
workSheet=self.workBook.WorkSheets(sheet)
srcRange=workSheet.Range(
workSheet.Cells(
row1, col1), workSheet.Cells(
row2, col2))
targetRange=workSheet.Range(
workSheet.Cells(
targetRow, targetCol), workSheet.Cells(
targetRow+ row2 - row1, targetCol + col2 -col1))#targetRange.Value = srcRange.Value
srcRange.Copy(targetRange) #与targetRange.Value = srcRange.Value效果相同
#移动一块区域
defcutRange(self, sheet, row1, col1, row2, col2, targetRow, targetCol):
workSheet=self.workBook.WorkSheets(sheet)
srcRange=workSheet.Range(
workSheet.Cells(
row1, col1), workSheet.Cells(
row2, col2))#方案1
#targetRange = workSheet.Range(workSheet.Cells(targetRow, targetCol), workSheet.Cells(targetRow + row2 - row1, targetCol + col2 - col1))
#targetRange.Value = srcRange.Value
#srcRange.Clear() #清空原区域的值
#方案2
#targetRange = workSheet.Range(workSheet.Cells(targetRow, targetCol), workSheet.Cells(targetRow + row2 - row1, targetCol + col2 - col1))
#srcRange.Cut(targetRange) #直接调用系统函数
#方案3与方案2类似
srcRange.Cut(workSheet.Cells(targetRow, targetCol))#清空指定区域内容
defclearRange(self, sheet, row1, col1, row2, col2):
workSheet=self.workBook.WorkSheets(sheet)
srcRange=workSheet.Range(
workSheet.Cells(
row1, col1), workSheet.Cells(
row2, col2))
srcRange.Clear()#添加图片
defaddPicture(self, sheet, pictureName, left, top, width, height):
workSheet=self.workBook.WorkSheets(sheet)'''，
AddPicture(self, Filename=, LinkToFile=,
>, Left=, Top=, Width=
hod of win32com.client.CDispatch instance'''workSheet.Shapes.AddPicture(
pictureName,1, 1, left, top, width, height)#删除指定行
defdeletRow(self, sheet, row):if row < 1 or row > 1048576:returndeleteRow=self.workBook.WorkSheets(sheet).Rows(row)
deleteRow.Delete()#删除指定列
defdeleteCol(self, sheet, col):if col < 1 or col > 16384:returndeleteCol=self.workBook.WorkSheets(sheet).Columns(col)
deleteCol.Delete()
