xlwings.copy两种用法和匹配超链接

第一种复制整个sheet页,不能覆盖同名sheet,需先删除.
name新sheet也名称 after复制后的位置
wb.sheets('增量机会-体外刷新导入').copy(name='快照版本(勿动)',after=wb.sheets('手工录入-服务收入等预测'))
第二种类似vba复制选中区域从A到B
wb.sheets("快照版本(勿动)").range(f'2:{source_last_row}').copy(wb.sheets("增量机会-体外刷新导入").range('A5'))

 

#从一个已有的xls导入子项目名称的超链接到现在的xls
def add_hyperlink(path):
    '''
    匹配超链接
    :param path:
    :return:
    '''
    #一键匹配超链接
    try:
        wb = xw.Book.caller()
        hyperlink_wb = load_workbook(path) #type: Workbook
        ws = hyperlink_wb['Sheet1']
        #获取输入超链接所在列
        columns = [ i.value for i in ws[1]]
        column = columns.index('子项目名称') if  '子项目名称' in columns else None
        #获取超链接map
        if column != None:
            column +=1
            link_map = {}
            rows = ws.max_row
            for index in  range(2,rows+1):
                cell = ws.cell(row=index,column=column)
                if cell.hyperlink:
                    key = cell.value
                    value = cell.hyperlink.target
                    link_map[key] = value
            #获取当前wb 子项目名称 所在列
            wb_columns = wb.sheets('增量机会-体外刷新导入').range('4:4').value
            wb_column =  wb_columns.index('子项目名称') if  '子项目名称' in wb_columns else None
            last_row = wb.sheets("增量机会-体外刷新导入").used_range.last_cell.row
            for index in range(5,last_row+1):
                cell  = wb.sheets('增量机会-体外刷新导入').range(index,wb_column+1)

                map_res = link_map.get(cell.value)
                if map_res:
                    cell.add_hyperlink(address=map_res,text_to_display=cell.value)
                else:
                    cell.font.color = '#00000'
                    cell.characters.api.Font.Underline = False
        wb.save()
    except BaseException as e:
        try:
            print(e)
            wb.sheets('config(勿动)').range('message').value = ','.join(e.args)
        except:
            pass
if __name__ == '__main__':
    xw.serve()

#vba代码
Sub add_hyperlink()
    On Error GoTo exit_
    ThisWorkbook.Sheets("config(勿动)").Range("message") = ""
    ThisWorkbook.Sheets("增量机会-体外刷新导入").Select
    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
    fpath = pickFile()
    strFileName = Replace(fpath, "\", "\\")
    RunPython "import MainProgram; MainProgram.add_hyperlink('" & strFileName & "')"
exit_:
     msg = ThisWorkbook.Sheets("config(勿动)").Range("message")
     If msg <> "" Then
            MsgBox msg
        Else
            MsgBox ("执行完成!")
        End If
End Sub

 

wb.sheets('增量机会-体外刷新导入').copy(name='快照版本(勿动)',after=wb.sheets('手工录入-服务收入等预测'))