1 import os 2 import win32com 3 from win32com.client import Dispatch,constants 4 5 path = r"\\10.250.50.23\共享\qsl\数值" 6 path_a = r"\\10.250.50.23\共享\qsl\数值\大表_EM_V3.xlsx" 7 8 #横坐标转换为数字 9 def colname_to_num(colname): 10 if type(colname) is not str: 11 return colname 12 col = 0 13 power = 1 14 for i in range(len(colname)-1,-1,-1): 15 ch = colname[i] 16 col += (ord(ch)-ord(\'A\')+1)*power 17 power *= 26 18 return col 19 20 #数字转换为横坐标 21 def column_to_name(colnum): 22 if type(colnum) is not int: 23 return colnum 24 str = \'\' 25 while(not(colnum//26 == 0 and colnum % 26 == 0)): 26 temp = 25 27 if(colnum % 26 == 0): 28 str += chr(temp+65) 29 else: 30 str += chr(colnum % 26 - 1 + 65) 31 colnum //= 26 32 return str[::-1] 33 34 def wkb_client(path,wkb_path,class_Collection,newwkb_name): 35 os.system(\'taskkill /IM EXCEL.exe /F\') 36 xlapp = win32com.client.gencache.EnsureDispatch(\'Excel.Application\') 37 #xlapp = Dispatch(\'Excel.Application\') 38 xlapp.Visible = True 39 xlapp.DisplayAlerts = False # 关闭警告 40 wkb = xlapp.Workbooks.Open(wkb_path) 41 print(\'文件【{}】已打开！\'.format(wkb_path)) 42 wkb_new = xlapp.Workbooks.Add() 43 new_path = path + \'\\{}\'.format(newwkb_name) 44 print(newwkb_name) 45 wkb_new.SaveAs(new_path) 46 wkb_new.Close(1) 47 wkb_new = xlapp.Workbooks.Open(new_path) 48 49 for key,vlaue in class_Collection.items(): 50 51 sheet_name = class_Collection[key][\'sheetname\'] 52 first_range = class_Collection[key][\'数据区域首行首列\'] 53 54 old_wkb_sheet = wkb.Worksheets(sheet_name) 55 wkb_new.Worksheets.Add().Name = sheet_name 56 new_wkb_sheet = wkb_new.Worksheets(sheet_name) 57 first_row = old_wkb_sheet.Range(first_range).Row 58 first_col = old_wkb_sheet.Range(first_range).Column 59 last_row = old_wkb_sheet.Range(first_range).End(-4121).Row 60 last_col = old_wkb_sheet.Range(first_range).End(-4161).Column 61 last_rane = column_to_name(last_col)+str(last_row) 62 current_range = first_range + \':\'+last_rane 63 print (\'当前复制单元格区域为：{}\'.format(current_range)) 64 new_current_range = \'A1\'+\':\'+column_to_name(last_col-first_col+1)+str((last_row-first_row+1)) 65 print(new_current_range) 66 old_wkb_sheet.Range(current_range).Copy() 67 new_wkb_sheet.Range(\'A1\').Select() 68 new_wkb_sheet.Range(new_current_range).PasteSpecial(Paste = constants.xlPasteValues,Operation = constants.xlNone) 69 new_wkb_sheet.Range(new_current_range).PasteSpecial(Paste = constants.xlPasteFormats,Operation = constants.xlNone) 70 new_wkb_sheet.Range(new_current_range).PasteSpecial(Paste = constants.xlPasteColumnWidths,Operation = constants.xlNone) 71 wkb.Worksheets(class_Collection[\'sheet0\'][\'sheetname\']).Cells.Copy() 72 wkb_new.Worksheets(class_Collection[\'sheet0\'][\'sheetname\']).Select() 73 wkb_new.Worksheets(class_Collection[\'sheet0\'][\'sheetname\']).Paste() 74 wkb.Save() 75 wkb_new.Save() 76 wkb.Close(1) 77 wkb_new.Close(1) 78 xlapp.Quit() 79 print(\'#更新 成功:%s\' % wkb_path) 80 pass 81 82 83 class_Collection = {\'sheet1\':{\'sheetname\':\'业务经营大表_姓名 (日)\', 84 \'数据区域首行首列\':\'A17\',}, 85 86 \'sheet2\':{\'sheetname\':\'组织管理大表_姓名 (日)\', 87 \'数据区域首行首列\':\'A17\',}, 88 89 \'sheet3\':{\'sheetname\':\'用户运营大表_姓名 (日)\', 90 \'数据区域首行首列\':\'A17\',}, 91 92 \'sheet4\':{\'sheetname\':\'业务经营大表_姓名\', 93 \'数据区域首行首列\':\'A17\',}, 94 95 \'sheet5\':{\'sheetname\':\'用户运营大表_姓名\', 96 \'数据区域首行首列\':\'A17\',}, 97 98 \'sheet6\':{\'sheetname\':\'组织管理大表_姓名\', 99 \'数据区域首行首列\':\'A17\',}, 100 101 \'sheet0\':{\'sheetname\':\'定义说明\', 102 \'数据区域首行首列\':\'A1\',} 103 } 104 105 wkb_client(path,path_a,class_Collection,\'大表_EM_数值.xlsx\')