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)

二、对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))

三、改进更多方法

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)

四、单文件多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","内容")