1、代码
特殊操作包括(隐藏列,解锁工作表保护,插入批注,创建文本框,追加修改单元格内容)
from openpyxl import load_workbook
import win32com.client
# 隐藏列
def hidden_column(path, column, sheet_name=0):
'''
:param path: 文件路径
:param column: 列名,如A,B,C,可以传入单个,可以是区间[B,E]
:return:
'''
try:
wb = load_workbook(path, data_only=True)
if isinstance(sheet_name, str):
ws = wb.get_sheet_by_name(sheet_name)
else:
ws = wb.worksheets[sheet_name]
if isinstance(column, list):
ws.column_dimensions.group(column[0], column[1], hidden=True)
else:
ws.column_dimensions[column].hidden = True
wb.save(path)
except Exception as e:
print("打开文件失败:%s" % e)
# 解锁工作表保护
def unlock_excel(path, password, sheet_name="Sheet1"):
'''
:param path: 文件路径
:param password:工作表保护密码
:param sheetname: sheet名
:return:
'''
xlApp = win32com.client.DispatchEx("Excel.Application")
try:
# 后台运行, 不显示, 不警告
xlApp.Visible = False
xlApp.DisplayAlerts = False
wb = xlApp.Workbooks.Open(path)
# 屏蔽弹窗
wb.Checkcompatibility = False
sht = wb.Worksheets(sheet_name)
sht.Unprotect(password)
wb.Save()
wb.Close(SaveChanges=True)
except Exception as e:
xlApp.Quit()
print("打开文件失败:%s" % e)
# 插入批注
def insert_notes(path, cell, content, sheet_name="Sheet1"):
'''
:param path: 文件路径
:param cell: 批注单元格:如B4
:param content: 批注内容
:param notes_name: 批注人名
:param sheet_name: sheet名
:return:
'''
xlApp = win32com.client.DispatchEx("Excel.Application")
try:
# 后台运行, 不显示, 不警告
xlApp.Visible = False
xlApp.DisplayAlerts = False
wb = xlApp.Workbooks.Open(path)
sht = wb.Worksheets(sheet_name)
if not sht.Range(cell).Comment:
sht.Range(cell).AddComment()
sht.Range(cell).Comment.Text(content)
wb.Save()
wb.Close()
except Exception as e:
xlApp.Quit()
print("打开文件失败:%s" % e)
# 创建文本框
def create_text_box(path, left,top,Width,Height,content, sheet_name="Sheet1"):
xlApp = win32com.client.DispatchEx("Excel.Application")
try:
# 后台运行, 不显示, 不警告
xlApp.Visible = False
xlApp.DisplayAlerts = False
wb = xlApp.Workbooks.Open(path)
sht = wb.Worksheets(sheet_name)
# 分别是文字方向,文本框的左上角相对于文档左上角的位置,
# 相对于文档顶部的文本框左上角的位置,文本框的宽度,文本框的高度(以磅为单位)
# 磅的大小为 1/72 英寸。 字号通常用磅衡量
sht.Shapes.AddTextbox(1, left,top,Width,Height).TextFrame.Characters().Text=content
wb.Save()
wb.Close()
except Exception as e:
xlApp.Quit()
print("打开文件失败:%s" % e)
# 读取单元格,并修改单元格
def add_content_cell(path,cell,add_content,sheet_name="Sheet1"):
xlApp = win32com.client.DispatchEx("Excel.Application")
try:
# 后台运行, 不显示, 不警告
xlApp.Visible = False
xlApp.DisplayAlerts = False
wb = xlApp.Workbooks.Open(path)
sht = wb.Worksheets(sheet_name)
value=sht.Range(cell).Value
sht.Range(cell).Value=value.strip("\n")+"\n"+add_content
wb.Save()
wb.Close()
except Exception as e:
xlApp.Quit()
print("打开文件失败:%s" % e)
2、对win32进行了open,close封装
class Win32_excel(object):
def __init__(self, path, sheet_name="Sheet1"):
self.xlApp = win32com.client.DispatchEx("Excel.Application")
self.path = path
self.sheet_name = sheet_name
def __enter__(self):
try:
# 后台运行, 不显示, 不警告
self.xlApp.Visible = False
self.xlApp.DisplayAlerts = False
self.wb = self.xlApp.Workbooks.Open(self.path)
# 屏蔽弹窗
self.wb.Checkcompatibility = False
self.sht = self.wb.Worksheets(self.sheet_name)
return self
except Exception as e: self.xlApp.Quit()
print("打开文件失败:%s" % e)
def __exit__(self, exc_type, exc_val, exc_tb):
self.wb.Save()
self.wb.Close(SaveChanges=True)
self.xlApp.Quit()
# 解锁工作表保护
def unlock_excel(self, password):
self.sht.Unprotect(password)
# 插入批注
def insert_notes(self, cell, content):
if not self.sht.Range(cell).Comment:
self.sht.Range(cell).AddComment()
self.sht.Range(cell).Comment.Text(content)
# 创建文本框
def create_text_box(self, left, top, Width, Height, content):
# 分别是文字方向,文本框的左上角相对于文档左上角的位置,
# 相对于文档顶部的文本框左上角的位置,文本框的宽度,文本框的高度(以磅为单位)
# 磅的大小为 1/72 英寸。 字号通常用磅衡量
self.sht.Shapes.AddTextbox(1, left, top, Width, Height).TextFrame.Characters().Text = content
# 读取单元格,并修改单元格
def add_content_cell(self, cell, add_content):
value = self.sht.Range(cell).Value
self.sht.Range(cell).Value = value.strip("\n") + "\n" + add_content
# 复制单元格
def copy_cells(self, copy_cells, to_cells):
# copy_cells,如:"A1:B1"
# to_cells,如:"A2:B2"
self.sht.Range(copy_cells).Copy()
self.sht.Range(to_cells).PasteSpecial()
# 插入单元格
def insert_cells(self,cells):
# cells,如:"A1:E1"
self.sht.Range(cells).Insert()
# 插入行
def insert_row(self,row):
# 在第几行之前插入新行
self.sht.Rows(row).Insert()
# 单元格写入
def write(self,cell,content):
self.sht.Range(cell).Value=content
if __name__ == '__main__':
path=r"日报.xlsx"
df=pd.read_excel(path,sheet_name="sheet名")
row=df.shape[0]
print(row)
with Win32_excel(path,sheet_name="sheet名") as w32:
w32.insert_row(row+1)
w32.copy_cells("A%s:G%s"%(row,row),"A%s:G%s"%(row+1,row+1))
3、改进更多方法
class Win32_excel(object):
def __init__(self, path, sheet_name="Sheet1"):
self.xlApp = win32com.client.DispatchEx("Excel.Application")
self.path = path
self.sheet_name = sheet_name
try:
# 后台运行, 不显示, 不警告
self.xlApp.Visible = False
self.xlApp.DisplayAlerts = False
self.wb = self.xlApp.Workbooks.Open(self.path)
# 屏蔽弹窗
self.wb.Checkcompatibility = False
self.sht = self.wb.Worksheets(self.sheet_name)
except Exception as e:
self.xlApp.Quit()
print("打开文件失败:%s" % e)
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.close()
def close(self):
self.wb.Save()
self.wb.Close(SaveChanges=True)
self.xlApp.Quit()
# 解锁工作表保护
def unlock_excel(self, password):
self.sht.Unprotect(password)
# 插入批注
def insert_notes(self, cell, content):
if not self.sht.Range(cell).Comment:
self.sht.Range(cell).AddComment()
self.sht.Range(cell).Comment.Text(content)
self.sht.Range(cell).Comment.Shape.TextFrame.Characters(6).Font.Bold = False # 取消字体加粗
# 判断是否有批注
def have_notes(self,cell):
if self.sht.Range(cell).Comment:
return True
# 追加批注
def add_notes(self, cell, content):
if not self.sht.Range(cell).Comment:
self.sht.Range(cell).AddComment()
text = self.sht.Range(cell).Comment.Text()
self.sht.Range(cell).Comment.Text(text + content)
self.sht.Range(cell).Comment.Shape.TextFrame.Characters(6).Font.Bold = False # 取消字体加粗
self.sht.Range(cell).Comment.Shape.Height = 70
# 设置批注高度宽度
def set_notes_height_width(self, cell, height, width):
self.sht.Range(cell).Comment.Shape.Height = height
self.sht.Range(cell).Comment.Shape.Width = width
# 创建文本框
def create_text_box(self, left, top, Width, Height, content):
# 分别是文字方向,文本框的左上角相对于文档左上角的位置,
# 相对于文档顶部的文本框左上角的位置,文本框的宽度,文本框的高度(以磅为单位)
# 磅的大小为 1/72 英寸。 字号通常用磅衡量
self.sht.Shapes.AddTextbox(1, left, top, Width, Height).TextFrame.Characters().Text = content
# 读取单元格,并修改单元格
def add_content_cell(self, cell, add_content):
value = self.sht.Range(cell).Value
self.sht.Range(cell).Value = value.strip("\n") + "\n" + add_content
# 复制单元格
def copy_cells(self, copy_cells, to_cells):
# copy_cells,如:"A1:B1"
# to_cells,如:"A2:B2"
self.sht.Range(copy_cells).Copy()
self.sht.Range(to_cells).PasteSpecial()
# 复制单元格只黏贴数值
def copy_cells_only_data(self, copy_cells, to_cells):
# copy_cells,如:"A1:B1"
# to_cells,如:"A2:B2"
self.sht.Range(copy_cells).Copy()
self.sht.Range(to_cells).PasteSpecial(Paste=-4163)
# 只清除单元格数据
def del_cells_only_data(self, cells):
self.sht.Range(cells).ClearContents()
# 删除列
def del_cols(self, col):
self.sht.Columns(col).Delete()
# 删除行
def del_rows(self, row):
self.sht.Rows(row).Delete()
# 向右复制一列
def copy_col(self, col):
self.sht.Columns(col).Copy()
self.sht.Columns(col + 1).PasteSpecial()
# 复制一行到指定位置
def copy_col_to_other(self, col1, col2):
self.sht.Rows(col1).Copy()
self.sht.Rows(col2).PasteSpecial()
# 向上复制一行
def copy_row_up(self, col):
self.sht.Rows(col + 1).Copy()
self.sht.Rows(col).PasteSpecial()
# 向下复制一行
def copy_row_down(self, col):
self.sht.Rows(col).Copy()
self.sht.Rows(col + 1).PasteSpecial()
# 插入单元格
def insert_cells(self, cells):
# cells,如:"A1:E1"
self.sht.Range(cells).Insert()
# 插入行
def insert_row(self, row):
# 在第几行之前插入新行
self.sht.Rows(row).Insert()
# 插入一列
def insert_col(self, col):
# 在第几行之前插入新行
self.sht.Columns(col).Insert()
# 单元格写入
def write(self, cell, content):
self.sht.Range(cell).Value = content
# 获取值
def get_content(self, cell):
content = self.sht.Range(cell).Value
return content
# 获取图表数据源
def get_ChartObject(self, num):
Formula = self.sht.ChartObjects(num).Chart.SeriesCollection(7).Formula
print(self.sht.ChartObjects(num).Chart.ChartTitle.Text) # 查看图表标题
return Formula
# 设置图表数据源
def set_ChartObject(self, num, Formula):
self.sht.ChartObjects(num).Chart.SeriesCollection(1).Formula = Formula
# self.sht.ChartObjects(num).SetSourceData(Formula)
# 获取excel英文列名
def get_col_name(self, col_num):
Address = self.sht.Columns(col_num).Address
return Address
# 查找内容
def find(self, what):
res = self.sht.UsedRange.Find(what)
return res
# 设置单元格颜色
def set_color(self, cell, color):
colors = {"无": 0, "黑": 1, "白": 2, "红": 3, "绿": 4, "蓝": 5, "黄": 6}
self.sht.Range(cell).Interior.ColorIndex = colors.get(color, 0)
# 获取单元格公式
def get_cell_Formula(self, cell):
res = self.sht.Range(cell).Formula
return res
# 设置单元格公式
def set_cell_Formula(self, cell, Formula):
self.sht.Range(cell).Formula = Formula
# 删除批注
def del_comment(self, cell):
if self.sht.Range(cell).Comment:
self.sht.Range(cell).Comment.Delete()
# 隐藏列
def hidden_col(self, col):
self.sht.Columns(col).Hidden = True
# 筛选
def cell_Filter(self, cell, Field, filter):
self.sht.Range(cell).AutoFilter(Field=Field, Criteria1=filter)
# 获取A最后一行的行号
def get_last_row(self):
row = self.sht.Range("A65536").End(-4162).Row
return row
# 获取指定最后一行的行号
def get_assign_row(self, row):
row = self.sht.Range("%s65536" % row).End(-4162).Row
return row
# 另存为pdf
def save_to_pdf(self, path):
self.sht.ExportAsFixedFormat(Type=0, Filename=path,From=1,To=1)
# 激活sheet
def activate_sheet(self):
self.sht.Activate()
# 删除行内容
def del_col_content(self, col):
self.sht.Rows(col).ClearContents()
# 取消合并单元格
def un_merger(self, cell):
self.sht.Range(cell).UnMerge()
# 刷新数透表
def refreshAll(self):
self.sht.PivotTables(1).PivotCache().Refresh()
# 数值化
def set_range_values(self, cell):
self.sht.Range(cell).value = self.sht.Range(cell).value
# 自动设置行高
def set_row_high(self, cell, high):
self.sht.Range(cell).RowHeight = high
def set_PrintArea(self, col_name, col_num):
self.sht.PageSetup.PrintArea = "$A$1:$%s$%s" % (col_name, col_num)
4、单文件多sheet,多文件多sheet
# -*- coding: utf-8 -*-
import win32com.client
class Win32_excel(object):
def __init__(self, path=None,sheet_name=None,more_paths=None):
'''
:param path: 单文件处理路径
:param sheet_name: 单sheet
:param more_paths: 多sheet或多文件,格式:{path1:[sheet1,sheet2],path2:[sheet1,sheet2]}
'''
self.xlApp = win32com.client.DispatchEx("Excel.Application")
# 后台运行, 不显示, 不警告
self.xlApp.Visible = False
self.xlApp.DisplayAlerts = False
self.wbs=[] # [wb1,wb2]
self.more=False
if path and sheet_name:
self.open(path,sheet_name)
else:
if not more_paths:
raise ("请正确输入文件路径和sheet名参数")
count=1
for path,sheet_names in more_paths.items():
self.more=True
self.open(path,sheet_names,count)
count+=1
def add_wb_sht(self,wb_name,sht_name,sht):
self.__setattr__(wb_name+"_"+sht_name,sht)
def open(self,path,sheet_name,count=1):
try:
if self.more:
wb=self.xlApp.Workbooks.Open(path)
# 屏蔽弹窗
wb.Checkcompatibility = False
self.wbs.append(wb)
count_num=1
for sheet in sheet_name:
sht = wb.Worksheets(sheet)
self.add_wb_sht("wb%s"%count,"sht%s"%count_num,sht)
count_num+=1
else:
self.wb = self.xlApp.Workbooks.Open(path)
# 屏蔽弹窗
self.wb.Checkcompatibility = False
self.sht = self.wb.Worksheets(sheet_name)
except Exception as e:
self.xlApp.Quit()
raise ("打开文件失败:%s" % e)
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.close()
def close(self):
if self.more:
for wb in self.wbs:
wb.Save()
wb.Close(SaveChanges=True)
else:
self.wb.Save()
self.wb.Close(SaveChanges=True)
self.xlApp.Quit()
# 解锁工作表保护
def unlock_excel(self, password):
self.sht.Unprotect(password)
# 插入批注
def insert_notes(self, cell, content):
if not self.sht.Range(cell).Comment:
self.sht.Range(cell).AddComment()
self.sht.Range(cell).Comment.Text(content)
self.sht.Range(cell).Comment.Shape.TextFrame.Characters(6).Font.Bold = False # 取消字体加粗
# 判断是否有批注
def have_notes(self, cell):
if self.sht.Range(cell).Comment:
return True
# 追加批注
def add_notes(self, cell, content):
if not self.sht.Range(cell).Comment:
self.sht.Range(cell).AddComment()
text = self.sht.Range(cell).Comment.Text()
self.sht.Range(cell).Comment.Text(text + content)
self.sht.Range(cell).Comment.Shape.TextFrame.Characters(6).Font.Bold = False # 取消字体加粗
self.sht.Range(cell).Comment.Shape.Height = 70
# 设置批注高度宽度
def set_notes_height_width(self, cell, height, width):
self.sht.Range(cell).Comment.Shape.Height = height
self.sht.Range(cell).Comment.Shape.Width = width
# 创建文本框
def create_text_box(self, left, top, Width, Height, content):
# 分别是文字方向,文本框的左上角相对于文档左上角的位置,
# 相对于文档顶部的文本框左上角的位置,文本框的宽度,文本框的高度(以磅为单位)
# 磅的大小为 1/72 英寸。 字号通常用磅衡量
self.sht.Shapes.AddTextbox(1, left, top, Width, Height).TextFrame.Characters().Text = content
# 读取单元格,并修改单元格
def add_content_cell(self, cell, add_content):
value = self.sht.Range(cell).Value
self.sht.Range(cell).Value = value.strip("\n") + "\n" + add_content
# 复制单元格
def copy_cells(self, copy_cells, to_cells):
# copy_cells,如:"A1:B1"
# to_cells,如:"A2:B2"
self.sht.Range(copy_cells).Copy()
self.sht.Range(to_cells).PasteSpecial()
# 复制单元格只黏贴数值
def copy_cells_only_data(self, copy_cells, to_cells):
# copy_cells,如:"A1:B1"
# to_cells,如:"A2:B2"
self.sht.Range(copy_cells).Copy()
self.sht.Range(to_cells).PasteSpecial(Paste=-4163)
# 只清除单元格数据
def del_cells_only_data(self, cells):
self.sht.Range(cells).ClearContents()
# 删除列
def del_cols(self, col):
self.sht.Columns(col).Delete()
# 删除行
def del_rows(self, row):
self.sht.Rows(row).Delete()
# 向右复制一列
def copy_col(self, col):
self.sht.Columns(col).Copy()
self.sht.Columns(col + 1).PasteSpecial()
# 复制一行到指定位置
def copy_col_to_other(self, col1, col2):
self.sht.Rows(col1).Copy()
self.sht.Rows(col2).PasteSpecial()
# 向上复制一行
def copy_row_up(self, col):
self.sht.Rows(col + 1).Copy()
self.sht.Rows(col).PasteSpecial()
# 向下复制一行
def copy_row_down(self, col):
self.sht.Rows(col).Copy()
self.sht.Rows(col + 1).PasteSpecial()
# 插入单元格
def insert_cells(self, cells):
# cells,如:"A1:E1"
self.sht.Range(cells).Insert()
# 插入行
def insert_row(self, row):
# 在第几行之前插入新行
self.sht.Rows(row).Insert()
# 插入一列
def insert_col(self, col):
# 在第几行之前插入新行
self.sht.Columns(col).Insert()
# 单元格写入
def write(self, cell, content):
self.sht.Range(cell).Value = content
# 获取值
def get_content(self, cell):
content = self.sht.Range(cell).Value
return content
# 获取图表数据源
def get_ChartObject(self, num):
Formula = self.sht.ChartObjects(num).Chart.SeriesCollection(1).Formula
print(self.sht.ChartObjects(num).Chart.ChartTitle.Text) # 查看图表标题
return Formula
# 设置图表数据源
def set_ChartObject(self, num, Formula):
self.sht.ChartObjects(num).Chart.SeriesCollection(1).Formula = Formula
# self.sht.ChartObjects(num).SetSourceData(Formula)
# 获取excel英文列名
def get_col_name(self, col_num):
Address = self.sht.Columns(col_num).Address
return Address
# 查找内容
def find(self, what):
res = self.sht.UsedRange.Find(what)
return res
# 设置单元格颜色
def set_color(self, cell, color):
colors = {"无": 0, "黑": 1, "白": 2, "红": 3, "绿": 4, "蓝": 5, "黄": 6}
self.sht.Range(cell).Interior.ColorIndex = colors.get(color, 0)
# 获取单元格公式
def get_cell_Formula(self, cell):
res = self.sht.Range(cell).Formula
return res
# 设置单元格公式
def set_cell_Formula(self, cell, Formula):
self.sht.Range(cell).Formula = Formula
# 删除批注
def del_comment(self, cell):
if self.sht.Range(cell).Comment:
self.sht.Range(cell).Comment.Delete()
# 隐藏列
def hidden_col(self, col):
self.sht.Columns(col).Hidden = True
# 筛选
def cell_Filter(self, cell, Field, filter):
self.sht.Range(cell).AutoFilter(Field=Field, Criteria1=filter)
# 获取A最后一行的行号
def get_last_row(self):
row = self.sht.Range("A65536").End(-4162).Row
return row
# 获取指定最后一行的行号
def get_assign_row(self, row):
row = self.sht.Range("%s65536" % row).End(-4162).Row
return row
# 另存为pdf
def save_to_pdf(self, path):
self.sht.ExportAsFixedFormat(Type=0, Filename=path, From=1, To=1)
# 激活sheet
def activate_sheet(self):
self.sht.Activate()
# 删除行内容
def del_col_content(self, col):
self.sht.Rows(col).ClearContents()
# 取消合并单元格
def un_merger(self, cell):
self.sht.Range(cell).UnMerge()
# 刷新数透表
def refreshAll(self):
self.sht.PivotTables(1).PivotCache().Refresh()
# 数值化
def set_range_values(self, cell):
self.sht.Range(cell).value = self.sht.Range(cell).value
# 自动设置行高
def set_row_high(self, cell, high):
self.sht.Range(cell).RowHeight = high
# 设置打印区域
def set_PrintArea(self, col_name, col_num):
self.sht.PageSetup.PrintArea = "$A$1:$%s$%s" % (col_name, col_num)
# range向下复制一行
def copy_range_down(self, up_cell, down_cell):
self.sht.Range(up_cell).Copy()
self.sht.Range(down_cell).PasteSpecial()
#单文件单sheet
with Win32_excel(path=r"********",sheet_name=1) as w32:
print(w32.wb) #获取的是wb对象
print(w32.sht) #获取的是sheet对象
# 单文件多sheet,多文件多sheet
with Win32_excel(more_paths={r"********":[1,2,3]}) as w32:
print(w32.wbs) #获取的是wb列表
print(w32.wb1_sht1) #获取的是sheet对象
print(w32.wb1_sht2)
# 使用方法前,重新赋值w32.sht
w32.sht=w32.wb1_sht1
w32.write("A1","内容")