Python执行选择性粘贴

import os
import win32com
from win32com.client import Dispatch, constants


# 横坐标转换为数字
def colname_to_num(colname):
    if type(colname) is not str:
        return colname
    col = 0
    power = 1
    for i in range(len(colname) - 1, -1, -1):
        ch = colname[i]
        col += (ord(ch) - ord(‘A‘) + 1) * power
        power *= 26
    return col


# 数字转换为横坐标
def column_to_name(colnum):
    # 返回数字对应的excel列的位置， 比如2返回B, 27返回AA
    if type(colnum) is not int or colnum <= 0:
        return ‘请输入正整数‘
    str = ‘‘
    while (colnum > 0) :
        temp = 25
        if (colnum % 26 == 0):
            str += chr(temp + 65)
            colnum -= 1
        else:
            str += chr(colnum % 26 - 1 + 65)
        colnum //= 26
        # print(str)
    return str[::-1]


def wkb_copy(path, path_wkb, sheet_dict, newwkb_name):
    os.system(‘taskkill /IM EXCEL.exe /F‘)
    xlapp = win32com.client.gencache.EnsureDispatch(‘Excel.Application‘)
    xlapp.Visible = True
    xlapp.DisplayAlerts = False  # 关闭警告
    wkb = xlapp.Workbooks.Open(path_wkb)
    print(‘文件【{}】已打开！‘.format(path_wkb))
    wkb_new = xlapp.Workbooks.Add()
    new_path = path + ‘\\{}‘.format(newwkb_name)
    print(newwkb_name)
    wkb_new.SaveAs(new_path)
    wkb_new.Close(1)
    wkb_new = xlapp.Workbooks.Open(new_path)
    for key, value in sheet_dict.items():
        sheet_name = value[‘sheetname‘]
        first_range = value[‘数据区域首行首列‘]
        first_cell = value[‘开始扩展单元格‘]
        old_wkb_sheet = wkb.Worksheets(sheet_name)
        if sheet_name == ‘Sheet1‘:                          # 判读"Sheet1"的情况
            new_wkb_sheet = wkb_new.Worksheets(sheet_name)
        else:
            wkb_new.Worksheets.Add().Name = sheet_name
            new_wkb_sheet = wkb_new.Worksheets(sheet_name)
        first_row = old_wkb_sheet.Range(first_range).Row
        first_col = old_wkb_sheet.Range(first_range).Column
        last_row = old_wkb_sheet.Range(first_cell).End(-4121).Row              # 想右扩展
        last_col = old_wkb_sheet.Range(first_cell).End(-4161).Column           # 想下扩展
        last_range = column_to_name(last_col) + str(last_row)
        old_range = first_range + ‘:‘ + last_range                             #获得复制区域
        print(‘当前复制单元格区域为：{}‘.format(old_range))
        new_range = ‘A1‘ + ‘:‘ + column_to_name(last_col - first_col +1) + str((last_row - first_row + 1))
        # 在新表中从A1开始复制,如果是B1开始复制；当粘贴为数值时，没有影响；但粘贴为格式等其他形式会造成影响。
        # 可在excel中自己体会各种粘贴的形式
        print(‘当前粘贴单元格区域为：{}‘.format(new_range))
        old_wkb_sheet.Range(old_range).Copy()                                 # 复制旧区域
        # new_wkb_sheet = wkb_new.Worksheets(sheet_name)     # 再次选中目标sheet;如果中间没有选择其他sheet,可不执行此操作。
        new_wkb_sheet.Range(new_range).PasteSpecial(Paste=constants.xlPasteValues, Operation=constants.xlNone)
        new_wkb_sheet.Range(new_range).PasteSpecial(Paste=constants.xlPasteFormats, Operation=constants.xlNone)
        new_wkb_sheet.Range(new_range).PasteSpecial(Paste=constants.xlPasteColumnWidths,Operation=constants.xlNone)
        print(‘复制完成：{}‘.format(sheet_name))
    wkb.Worksheets(sheet_dict[‘sheet0‘][‘sheetname‘]).Cells.Copy()
    wkb_new.Worksheets(sheet_dict[‘sheet0‘][‘sheetname‘]).Range(‘A1‘).Select()  # 必须放在A1未知
    wkb_new.Worksheets(sheet_dict[‘sheet0‘][‘sheetname‘]).Paste()
    sheet_name_list = [ value[‘sheetname‘] for value in sheet_dict.values()]
    if ‘Sheet1‘ not in sheet_name_list:
        wkb_new.Worksheets(‘Sheet1‘).Delete()
    wkb.Save()
    wkb_new.Save()
    wkb.Close(1)
    wkb_new.Close(1)
    xlapp.Quit()
    print(‘#更新 成功:%s‘ % path_wkb)


sheet_dict = {‘sheet3‘: {‘sheetname‘: ‘Sheet1‘,
                               ‘数据区域首行首列‘: ‘A1‘,
                                ‘开始扩展单元格‘: ‘A1‘},
                    ‘sheet1‘: {‘sheetname‘: ‘测试1‘,
                               ‘数据区域首行首列‘: ‘A5‘,
                               ‘开始扩展单元格‘: ‘A7‘},
                    ‘sheet2‘: {‘sheetname‘: ‘测试2‘,
                               ‘数据区域首行首列‘: ‘B16‘,
                               ‘开始扩展单元格‘: ‘B16‘},
                    ‘sheet0‘: {‘sheetname‘: ‘全复制‘,
                               ‘数据区域首行首列‘: ‘C9‘,
                               ‘开始扩展单元格‘: ‘C9‘}
                    }



path = r"D:\测试\nicai"
path_a = r"D:\测试\nicai\复制excel.xlsx"
wkb_copy(path, path_a, sheet_dict, ‘ceshi.xlsx‘)