python-win32操作excel的一些特殊功能

一、代码

特殊操作包括（隐藏列，解锁工作表保护，插入批注，创建文本框，追加修改单元格内容）

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)

二、对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","内容")