Python+Excel--工作簿中同名工作表对应批量复制

需求：两个工作簿，工作表名称、内容等一致，需将其中一个工作簿中的工作表批量数据复制到另一个底稿里，代码如下：

import xlwings as xw
import xlrd

def number_to_letter(number):
    if 1 <= number <= 26:
        return chr(number + 64)
    if 27 <= number <=52:
        return str('A')+str(chr(number + 38))
    else:
        return None

app = xw.App(visible=False, add_book=False)
workbook1 = app.books.open(r'E:\CZZ\6. 年报\5 WPS在线文档\测试\BTG附注.xlsx')
workbook2 = app.books.open(r'E:\CZZ\6. 年报\5 WPS在线文档\测试\副本BTG决算.xlsx')

worksheet1 = workbook1.sheets# 获取工作簿中的所有工作表
worksheet2 = workbook2.sheets
lists1 = []
lists2 = []
for i in worksheet1:
    sheet_name1 = i.name
    lists1.append(sheet_name1)
for j in worksheet2:
    sheet_name2 = j.name
    lists2.append(sheet_name2)
print(len(lists1),len(lists2))

for a in lists2:
    sht = workbook2.sheets[a]
    info = sht.used_range
    rows_count=info.last_cell.row#有数据的行数
    cols_count=info.last_cell.column#有数据的列数
    Col = number_to_letter(cols_count)
    source_JS=workbook2.sheets[a].range('A1:'+str(Col)+str(rows_count)).value
    workbook1.sheets[a].range('A2:'+str(Col)+str(rows_count+1)).value = source_JS
    print(a,rows_count,cols_count,"ok")

AA=workbook2.sheets['资产负债表'].range('A1:F4').value
BB=workbook1.sheets['资产负债表'].range('A2:F5').value
print(AA)
print(BB)

workbook1.save()
workbook2.save()
workbook1.close()
workbook2.close()
app.quit()