前言(最新版:Excel.)
- 使用 win32 库操作 Excel
- 注意传参变化,不同的参数对应不同的方式(单元格 or 区域)
- 注意:这是个类哦
导包 and 初始化
import win32com.client as win32
class Excel:
def __init__(self, visible=True):
"""
创建 Excel 对象
:param visible: 是否可视化, 默认 True
"""
self.excel = win32.gencache.EnsureDispatch('Excel.Application')
self.excel.Visible = visible
self.wb = None
打开(新建) Excel
def open_excel(self, path=None):
"""
打开 Excel 文档
:param path: Excel 路径, 不传则打开新的 Excel 表
"""
if path:
self.wb = self.excel.Workbooks.Open(path)
else:
self.wb = self.excel.Workbooks.Add()
新建 sheet 页
def creat_sheet(self, sheet=None):
"""
创建 sheet 页, 若名字已存在, 则取消操作
:param sheet: sheet 页名
"""
ws = self.wb.Worksheets.Add()
try:
ws.Name = sheet
except:
ws.Delete()
删除 sheet 页
def del_sheet(self, sheet):
"""
删除 sheet 页
:param sheet: sheet 页名
"""
try:
self.wb.Worksheets(sheet).Delete()
except:
pass
重命名 sheet 页
def rename_sheet(self, old_sheet, new_sheet):
"""
重命名 sheet 页
:param old_sheet: 原 sheet 页名
:param new_sheet: 新 sheet 页名
"""
self.wb.Worksheets(old_sheet).Name = new_sheet
读取内容(区域方式注意返回的是元组格式)
def read(self, sheet='Sheet1', start_row=None, start_col=None, end_row=None, end_col=None):
"""
读取内容, 若不传后面两个参数, 则读取单元格内容, 否则读取区域内容
:param sheet: sheet 页名
:param start_row: 开始行
:param start_col: 开始列
:param end_row: 结束行
:param end_col: 结束列
"""
ws = self.wb.Worksheets(sheet)
if None in [end_row, end_col]:
return ws.Cells(start_row, start_col).Value
else:
return ws.Range(f'{start_col}{start_row}:{end_col}{end_row}').Value
按单元格写入
def write_cell(self, sheet='Sheet1', row=None, col=None, value=None):
"""
写入内容, 单元格
:param sheet: sheet 页名
:param row: 行, 2
:param col: 列, B
:param value: 要写入的值
"""
self.wb.Worksheets(sheet).Cells(row, col).Value = value
按行写入
def write_row(self, sheet='Sheet1', row=None, start_col=None, end_col=None, value=None):
"""
写入内容, 行
:param sheet: sheet 页名
:param row: 行
:param start_col: 开始 列
:param end_col: 结束 列
:param value: 要写入的值, [1, 2, 3]
"""
self.wb.Worksheets(sheet).Range(f'{start_col}{row}:{end_col}{row}').Value = value
设置字体格式(单元格 or 区域)
def set_font_style(self, sheet, row=None, col=None, area=None, style=None):
"""
设置 字体 格式, 单元格 or 区域
:param sheet: sheet 页名
:param row: 行, 2
:param col: 列, B
:param area: 区域, 'A1:D4'
:param style: 格式, 字典 {'size': 16, 'bold': True}
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Cells(row, col)
name = sheet.pop('name', False) # 字体名称
if name:
ws.Font.Name = name
size = style.pop('size', False) # 字体大小
if size:
ws.Font.Size = int(size)
bold = style.pop('bold', False) # 是否加粗
if bold:
ws.Font.Bold = bold
设置数值格式(单元格 or 区域)
def set_style_number(self, sheet, row=None, col=None, area=None, style=None):
"""
设置数字格式
:param sheet: sheet页名
:param row: 行, 2
:param col: 列, B
:param area: 区域, 'A1:D4'
:param style: 格式, {'number': '$###,##0.00'}
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Cells(row, col)
number = style.pop('number', False) # 数字格式
if number:
ws.NumberFormat = number
设置背景色(单元格 or 区域)
- 注意,颜色需要找对应编号
def set_style_bg_color(self, sheet, row=None, col=None, area=None, style=None):
"""
设置 字体 格式, 单元格 or 区域
:param sheet: sheet 页名
:param row: 行, 2
:param col: 列, B
:param area: 区域, 'A1:D4'
:param style: 格式, 字典 {'color': 50}
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Cells(row, col)
color = style.pop("bg_color", None) # 背景色
if color:
ws.Interior.ColorIndex = color
设置列宽,对齐方式(单元格 or 区域)
def set_style_col(self, sheet, col=None, area=None, length=None, align=None, fit=True):
"""
设置 列宽, 对齐方式
:param sheet: sheet 页名
:param col: 列, 2, 'B'
:param area: 区域, 'B:B', 'B:D'
:param length: 长度, 20
:param align: 对齐方式, left, center, right
:param fit: 是否自适应
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Columns(col)
if length:
ws.ColumnWidth = int(length)
if align in ['left', 'center', 'right']:
ws.HorizontalAlignment = {'left': win32.constants.xlLeft,
'center': win32.constants.xlCenter,
'right': win32.constants.xlRight}[align]
if fit:
ws.Columns.AutoFit()
设置行高,是否上下居中(单元格 or 区域)
def set_style_row(self, sheet, row=None, area=None, height=None, align=False, fit=True):
"""
设置 行高, 是否上下居中
:param sheet: sheet 页名
:param row: 行, 2
:param area: 区域, 'B:B', 'B:D'
:param height: 高度, 20
:param align: 是否居中, 默认 否
:param fit: 是否自适应
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Rows(row)
ws.RowHeight = height
if align:
ws.VerticalAlignment = win32.constants.xlCenter
if fit:
ws.Rows.AutoFit()
保存
-路径要包含文件名和后缀哦
def save(self, path):
"""
保存 Excel
:param path: 路径, 'C:\\Users\\Aiden\\Desktop\\test.xlsx'
"""
self.wb.SaveAs(path)
退出 Excel
- 退出前记得先保存,否则会报错哦
def close(self):
"""退出 Excel"""
self.excel.Application.Quit()
完整代码
- 记得点赞哦
import win32com.client as win32
class Excel:
def __init__(self, visible=True):
"""
创建 Excel 对象
:param visible: 是否可视化, 默认 True
"""
self.excel = win32.gencache.EnsureDispatch('Excel.Application')
self.excel.Visible = visible
self.wb = None
def open_excel(self, path=None):
"""
打开 Excel 文档
:param path: Excel 路径, 不传则打开新的 Excel 表
"""
if path:
self.wb = self.excel.Workbooks.Open(path)
else:
self.wb = self.excel.Workbooks.Add()
def creat_sheet(self, sheet=None):
"""
创建 sheet 页, 若名字已存在, 则取消操作
:param sheet: sheet 页名
"""
ws = self.wb.Worksheets.Add()
try:
ws.Name = sheet
except:
ws.Delete()
def del_sheet(self, sheet):
"""
删除 sheet 页
:param sheet: sheet 页名
"""
try:
self.wb.Worksheets(sheet).Delete()
except:
pass
def rename_sheet(self, old_sheet, new_sheet):
"""
重命名 sheet 页
:param old_sheet: 原 sheet 页名
:param new_sheet: 新 sheet 页名
"""
self.wb.Worksheets(old_sheet).Name = new_sheet
def read(self, sheet='Sheet1', start_row=None, start_col=None, end_row=None, end_col=None):
"""
读取内容, 若不传后面两个参数, 则读取单元格内容, 否则读取区域内容
:param sheet: sheet 页名
:param start_row: 开始行
:param start_col: 开始列
:param end_row: 结束行
:param end_col: 结束列
"""
ws = self.wb.Worksheets(sheet)
if None in [end_row, end_col]:
return ws.Cells(start_row, start_col).Value
else:
return ws.Range(f'{start_col}{start_row}:{end_col}{end_row}').Value
def write_cell(self, sheet='Sheet1', row=None, col=None, value=None):
"""
写入内容, 单元格
:param sheet: sheet 页名
:param row: 行, 2
:param col: 列, B
:param value: 要写入的值
"""
self.wb.Worksheets(sheet).Cells(row, col).Value = value
def write_row(self, sheet='Sheet1', row=None, start_col=None, end_col=None, value=None):
"""
写入内容, 行
:param sheet: sheet 页名
:param row: 行
:param start_col: 开始 列
:param end_col: 结束 列
:param value: 要写入的值, [1, 2, 3]
"""
self.wb.Worksheets(sheet).Range(f'{start_col}{row}:{end_col}{row}').Value = value
def set_font_style(self, sheet, row=None, col=None, area=None, style=None):
"""
设置 字体 格式, 单元格 or 区域
:param sheet: sheet 页名
:param row: 行, 2
:param col: 列, B
:param area: 区域, 'A1:D4'
:param style: 格式, 字典 {'size': 16, 'bold': True}
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Cells(row, col)
name = sheet.pop('name', False) # 字体名称
if name:
ws.Font.Name = name
size = style.pop('size', False) # 字体大小
if size:
ws.Font.Size = int(size)
bold = style.pop('bold', False) # 是否加粗
if bold:
ws.Font.Bold = bold
def set_style_number(self, sheet, row=None, col=None, area=None, style=None):
"""
设置数字格式
:param sheet: sheet页名
:param row: 行, 2
:param col: 列, B
:param area: 区域, 'A1:D4'
:param style: 格式, {'number': '$###,##0.00'}
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Cells(row, col)
number = style.pop('number', False) # 数字格式
if number:
ws.NumberFormat = number
def set_style_bg_color(self, sheet, row=None, col=None, area=None, style=None):
"""
设置 字体 格式, 单元格 or 区域
:param sheet: sheet 页名
:param row: 行, 2
:param col: 列, B
:param area: 区域, 'A1:D4'
:param style: 格式, 字典 {'color': 50}
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Cells(row, col)
color = style.pop("bg_color", None) # 背景色
if color:
ws.Interior.ColorIndex = color
def set_style_col(self, sheet, col=None, area=None, length=None, align=None, fit=True):
"""
设置 列宽, 对齐方式
:param sheet: sheet 页名
:param col: 列, 2, 'B'
:param area: 区域, 'B:B', 'B:D'
:param length: 长度, 20
:param align: 对齐方式, left, center, right
:param fit: 是否自适应
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Columns(col)
if length:
ws.ColumnWidth = int(length)
if align in ['left', 'center', 'right']:
ws.HorizontalAlignment = {'left': win32.constants.xlLeft,
'center': win32.constants.xlCenter,
'right': win32.constants.xlRight}[align]
if fit:
ws.Columns.AutoFit()
def set_style_row(self, sheet, row=None, area=None, height=None, align=False, fit=True):
"""
设置 行高, 是否居中
:param sheet: sheet 页名
:param row: 行, 2
:param area: 区域, 'B:B', 'B:D'
:param height: 高度, 20
:param align: 是否居中, 默认 否
:param fit: 是否自适应
"""
if area:
ws = self.wb.Worksheets(sheet).Range(area)
else:
ws = self.wb.Worksheets(sheet).Rows(row)
ws.RowHeight = height
if align:
ws.VerticalAlignment = win32.constants.xlCenter
if fit:
ws.Rows.AutoFit()
def save(self, path):
"""
保存 Excel
:param path: 路径, 'C:\\Users\\Aiden\\Desktop\\test.xlsx'
"""
self.wb.SaveAs(path)
def close(self):
"""退出 Excel"""
self.excel.Application.Quit()