PythonWin32控制Excel
目录
- 首先需要导入一下需要使用的包
- 在传入的表格中定位单元格有两种方法,一种是字母加数字另外一种是坐标的方式,而选取目标单元格时有时会不方便用某一种,需要进行一下转化
- 在表格当中的操作都是基于类上的操作,我们需要先创建一个对象,在这个对象来操作
- 关闭Excel对象
- 写入表格
- 插入列或插入行
- 保存表格
- 另存为
- 读取表格的内容
- 对单元格设置样式
- ExcelSheet页相关操作
- 总结:
首先需要导入一下需要使用的包
import win32com.client as wc
import traceback
import os
import re
在传入的表格中定位单元格有两种方法,一种是字母加数字另外一种是坐标的方式,而选取目标单元格时有时会不方便用某一种,需要进行一下转化
def col2int(col_str):
"""
字母转数字
:param col_str: 列号
"""
abc = list(map(lambda x: chr(x), [i for i in range(65, 91)]))
cols = re.findall(r".", col_str)
start = 1
if len(col_str) == 2:
col_int = start + (abc.index(cols[0]) + 1) * 26 + abc.index(cols[-1])
elif len(col_str) == 3:
col_int = start + (abc.index(cols[0]) + 1) * 26 + abc.index(cols[1]) * 26 + abc.index(cols[-1])
else:
col_int = start + abc.index(col_str)
return col_int
def int2col(col_int):
"""
数字转字母
:param col_int: 列数
"""
abc = list(map(lambda x: chr(x), [i for i in range(65, 91)]))
if col_int <= 26:
col_str = abc[col_int - 1]
elif col_int <= 702:
col_int -= 27
col_str = abc[int(col_int / 26)] + abc[int(col_int % 26)]
else:
col_int -= 703
col_str = abc[int(col_int / 676)] + abc[int(int(col_int / 26)) % 26] + abc[int(col_int % 26)]
return col_str
在表格当中的操作都是基于类上的操作,我们需要先创建一个对象,在这个对象来操作
def creatExcelObj(file, excelType='open', visible=False, app='office'):
excelWorkBook = None
try:
if excelType == 'open':
if app == 'office':
excel = wc.Dispatch('Excel.Application')
excel.DisplayAlerts = False # 关闭警告
excel.Visible = visible
excelWorkBook = excel.Workbooks.Open(file, ReadOnly=False)
elif app == 'wps':
excel = wc.Dispatch('Ket.Application')
excel.DisplayAlerts = False # 关闭警告
excel.Visible = visible
excelWorkBook = excel.Workbooks.Open(file, ReadOnly=False)
elif excelType == 'new':
if app == 'office':
excel = wc.Dispatch('Excel.Application')
excel.DisplayAlerts = False # 关闭警告
excel.Visible = visible
excelWorkBook = excel.Workbooks.Add()
elif app == 'wps':
excel = wc.Dispatch('Ket.Application')
excel.DisplayAlerts = False # 关闭警告
excel.Visible = visible
excelWorkBook = excel.Workbooks.Add()
return excelWorkBook
except Exception as e:
print(traceback.format_exc())
raise '打开或新建表格失败'
关闭Excel对象
def closeExcelObj(excelWorkBook):
try:
# 保存关闭Excel
excelWorkBook.Save()
excelWorkBook.Close()
except Exception as e:
print(traceback.format_exc())
raise '关闭表格失败'
写入表格
def writeToExcel(excelWorkBook, sheetName, writeType, startCol: str or int, startRow: int, endCol: str or int = None,
endRow: int = None, writeData=None):
# 写入Excel数据
if writeType == 'cell':
if isinstance(startCol, str):
col = col2int(startCol)
excelWorkBook.Worksheets(sheetName).Cells(col, startRow).Value = writeData
elif isinstance(startCol, int):
excelWorkBook.Worksheets(sheetName).Cells(startCol, startRow).Value = writeData
elif writeType == 'col':
ws = excelWorkBook.Worksheets(sheetName)
assert isinstance(writeData, list)
startCol = startCol if isinstance(startCol, str) else int2col(startCol)
if endCol and endRow:
endCol = endCol if isinstance(endCol, str) else int2col(endCol)
ws.Range(ws.Cells(startCol, startRow), ws.Cells(endCol, endRow)).Value = writeData
else:
endRow = startRow + len(writeData) - 1
ws.Range(ws.Cells(startCol, startRow), ws.Cells(startCol, endRow)).Value = writeData
# if startCol != endCol:
# raise '请输入同一列'
# if isinstance(startCol, str):
# col = col2int(startCol)
# excelWorkBook.Worksheets(sheetName).Cells(col, startRow).Value = writeData
# elif isinstance(startCol, int):
# excelWorkBook.Worksheets(sheetName).Cells(startCol, startRow).Value = writeData
elif writeType == 'row':
assert isinstance(writeData, list)
startCol = startCol if isinstance(startCol, int) else col2int(startCol)
endRow = startRow + len(writeData)
listIndex = 0
while startRow < endRow:
excelWorkBook.Worksheets(sheetName).Cells(startCol, startRow).Value = writeData[listIndex]
startRow += 1
listIndex += 1
elif writeType == 'area':
# 需要传入一个二位列表,与选取区域匹配
assert isinstance(writeData, list)
assert endCol is not None and endRow is not None
startCol = startCol if isinstance(startCol, str) else int2col(startCol)
endCol = endCol if isinstance(endCol, str) else int2col(endCol)
excelWorkBook.Worksheets(sheetName).Range(f'{startCol}{startRow}:{endCol}{endRow}').Value = writeData
插入列或插入行
def addCell(excelWorkBook, sheetName, type='row', col: str or int = None, row: int = None):
ws = excelWorkBook.Worksheets(sheetName)
if type == 'row':
assert row is not None
assert row > 0
ws.Rows(row).Insert()
elif type == 'col':
assert col is not None
ws.Columns(col).Insert()
保存表格
def saveExcelFile(excelWorkBook):
# 保存Excel
excelWorkBook.Save()
另存为
def saveAsOtherExcelFile(excelWorkBook, path, fileName):
# 另存为Excel
excelWorkBook.SaveAs(os.path.join(path, fileName))
读取表格的内容
def readExcelCell(excelWorkBook, sheetName, col: str or int, row: int):
# 读取单元格
if isinstance(col, str):
col = col2int(col)
return excelWorkBook.Worksheets(sheetName).Cells(col, row).Value
elif isinstance(col, int):
return excelWorkBook.Worksheets(sheetName).Cells(col, row).Value
def readExcelRow(excelWorkBook, sheetName, row: int):
# 读取行
return [col for row in excelWorkBook.Worksheets(sheetName).Rows(row).Value for col in row]
def readExcelCol(excelWorkBook, sheetName, col: int):
# 读取列
return [row for col in excelWorkBook.Worksheets(sheetName).Columns(col).Value for row in col]
def readExcelArea(excelWorkBook, sheetName, startCol: str, startRow: int, endCol: str, endRow: int):
# 读取区域
return [cell for row in excelWorkBook.Worksheets(sheetName).Range(f'{startCol}{startRow}:{endCol}{endRow}').Value
for cell in row]
def readExcelMaxRow(excelWorkBook, sheetName):
# 读取表格已编辑的最大行数
return excelWorkBook.Worksheets(sheetName).UsedRange.Rows.Count
def readExcelMaxCol(excelWorkBook, sheetName):
# 读取表格已编辑的最大列数
return excelWorkBook.Worksheets(sheetName).UsedRange.Columns.Count
def getExcelEditedArea(excelWorkBook, sheetName):
# 获取已编辑的区域
return [excelWorkBook.Worksheets(sheetName).UsedRange.Rows.Count, excelWorkBook.Worksheets(
sheetName).UsedRange.Columns.Count]
对单元格设置样式
需先选取区域,对区域进行设置
def getExcelArea(excelWorkBook, sheetName, startCol: str or int, startRow: int, endCol: str or int = None,
endRow: int = None, setType='cell'):
# 选取设置单元格格式区域
if setType == 'cell':
col = startCol if isinstance(startCol, int) else col2int(startCol)
return excelWorkBook.Worksheets(sheetName).Cells(col, startRow)
elif setType == 'row':
startCol = startCol if isinstance(startCol, str) else int2col(startCol)
if endRow:
return excelWorkBook.Worksheets(sheetName).Range(f'{startCol}{startRow}:{startCol}{endRow}')
else:
return excelWorkBook.Worksheets(sheetName).Rows(startRow)
elif setType == 'col':
startCol = startCol if isinstance(startCol, str) else int2col(startCol)
if endCol:
endCol = endCol if isinstance(endCol, str) else int2col(endCol)
return excelWorkBook.Worksheets(sheetName).Range(f'{startCol}{startRow}:{endCol}{endRow}')
else:
return excelWorkBook.Worksheets(sheetName).Columns(col2int(startCol))
elif setType == 'area':
assert endCol is not None and endRow is not None
startCol = startCol if isinstance(startCol, str) else int2col(startCol)
endCol = endCol if isinstance(endCol, str) else int2col(endCol)
return excelWorkBook.Worksheets(sheetName).Range(f'{startCol}{startRow}:{endCol}{endRow}')
# def searchExcelData():
# # 数据赛选
# pass
def setCellNumber(area, style):
"""
设置 数字 格式
style: 格式, "$###,##0.00"
"""
area.NumberFormat = style
def setCellBgColor(area, color):
# 设置单元格背景色
area.Interior.ColorIndex = color
def setCellFont(area, size=None, bold=False, name=None, color=None):
"""
设置 字体 样式
:param size: 字体大小
:param bold: 是否加粗
:param name: 字体名称
:param color: 字体颜色
"""
if size:
area.Font.Size = size
if bold:
area.Font.Bold = bold
if name:
area.Font.Name = name
if color:
area.Font.Color = color
def setCellColStyle(area, align='left', width=None, fit=False):
"""
:param align: 对齐方式, left | center | right
:param width: 列宽
:param fit: 是否自适应
"""
area.HorizontalAlignment = {
"left": wc.constants.xlLeft,
"right": wc.constants.xlRight,
"center": wc.constants.xlCenter
}[align]
if width:
area.ColumnWidth = width
fit = False
if fit:
area.Columns.AutoFit()
def setCellRowStyle(area, align=False, height=None, fit=False):
# 设置单元格行格式
"""
:param align: 是否上下居中
:param height: 行高
:param fit: 是否自适应
"""
if align:
area.VerticalAlignment = wc.constants.xlCenter
if height:
area.RowHeight = height
fit = False
if fit:
area.Rows.AutoFit()
def setCellBorder(area, kind, width=1):
# 设置单元格边框
"""
:param kind: 样式
:param width: 宽度
"""
assert 0 <= kind < 14
assert 0 < width <= 4
area.BorderAround(kind, width)
def addExcelSheet(excelWorkBook, newSheetName=None):
# 新增Sheet
NewSheet = excelWorkBook.Worksheets.Add()
sheetNameList = getExcelAllSheetName(excelWorkBook)
if newSheetName and excelWorkBook not in sheetNameList:
NewSheet.Name = newSheetName
elif not newSheetName:
NewSheet.Name = NewSheet.Name
ExcelSheet页相关操作
def addExcelSheet(excelWorkBook, newSheetName=None):
# 新增Sheet
NewSheet = excelWorkBook.Worksheets.Add()
sheetNameList = getExcelAllSheetName(excelWorkBook)
if newSheetName and excelWorkBook not in sheetNameList:
NewSheet.Name = newSheetName
elif not newSheetName:
NewSheet.Name = NewSheet.Name
def getExcelAllSheetName(excelWorkBook):
return list(map(lambda x: x.Name, excelWorkBook.Worksheets))
def renameExcelSheetName(excelWorkBook, sheetName, newName):
# 重命名Sheet
excelWorkBook.Worksheets(sheetName).Name = newName
def copyExcelSheet(excelWorkBook, sheetName, newName=None, relative='after'):
# 复制Sheet
tarSheet = excelWorkBook.Worksheets(sheetName)
tarSheetName = excelWorkBook.Worksheets(sheetName).Name
if relative == 'after':
tarSheet.Copy(None, excelWorkBook.Worksheets(sheetName))
elif relative == 'before':
tarSheet.Copy(excelWorkBook.Worksheets(sheetName), None)
if newName:
renameExcelSheetName(excelWorkBook, f'{tarSheetName} (2)', newName)
return newName
else:
renameExcelSheetName(excelWorkBook, f'{tarSheetName} (2)', f'{tarSheetName}Copied')
return f'{tarSheetName}Copied'
def deleteExcelSheet(excelWorkBook, sheetName):
# 删除Sheet
try:
excelWorkBook.Worksheets(sheetName).Delete()
except Exception as e:
print(e)
raise '删除Sheet页失败'
总结:
本文是一篇Python代码,导入了所需包和模块,定义了一些函数。首先介绍了选取单元格的方法,可以使用字母加数字或坐标的方式。然后介绍了将字母转为数字和将数字转为字母的函数。接着是操作Excel表格的函数,包括创建Excel对象、关闭Excel对象、写入表格、插入列或插入行、保存表格和另存为、读取表格内容等。最后是设置单元格样式的函数,包括设置数字格式、背景色、字体样式、列宽和行高、边框等。
因为需求所需,没有采用面向对象的方式来写。当然,还有很多其他的函数和属性可以用来操作Excel文件,这里只是简单地介绍了一些常用的函数。