本来想直接写入，但是我们的excel报表太麻烦了，里面表头有多处要求合并的，用python去写太要命了，想了下，设置一堆空的excel模板，这样只需要把原文件的数据读出来就可以了，简单多了
#读取文件夹下所有的excel，并遍历所有的sheet页,然后把读取的数据增加跳转列后保存到新的文件夹对应文件中，# /qq_38140292/article/details/121134465import pandas as pdimport osfrom openpyxl import Workbook,load_workbookfrom openpyxl.styles import *dfs=pd.DataFrame()oldPath = r'E:\aaa\aaa\excelOLD'newPath = r'E:\aaa\aaa\excelNEW'# 先遍历旧的文件夹，读取数据for root_dir,sub_dir,files in os.walk(oldPath):for fileName in files:if fileName.endswith(".xlsx"):#构造绝对路径filePath = os.path.join(root_dir, fileName)print('filePath：' + filePath)#读取sheet页for sheetName in pd.read_excel(filePath,sheet_name=None).keys():print('sheet_name：' + sheetName)df = pd.read_excel(filePath,sheet_name=sheetName)# 获取所有的数据，返回的是一个listvalue = df.values# print(value)cols = ['=HYPERLINK("/qa/76641", ">>>")' for i in range(df.index.values.size)]df2 = pd.DataFrame({"跳转": cols})result = pd.concat([df, df2], axis=1)print(result)newFilePath = os.path.join(newPath, fileName)book = load_workbook(newFilePath)# sheet = book.get_sheet_by_name(sheetName) #会有红色错误提示，不影响使用sheet = book[sheetName]row_num = sheet.max_rowprint('row_num:' + str(row_num))# address = "A3" # 数据插入的起始行和列，比如源数据插入是第四行，那就是"A4“，但是我们迁移去掉了表头，所以需要减1，设置为"A3"address = 'A' + str(row_num)start_row, start_col = sheet[address].row - 1, sheet[address].column - 1for i, row in enumerate(result.values, 2):for j, v in enumerate(row, 1):sheet.cell(start_row + i, start_col + j).value = v# thin 细框线，thick粗线sheet.cell(start_row + i, start_col + j).border = Border(left=Side(style='thin'), bottom=Side(style='thin'),right=Side(style='thin'), top=Side(style='thin'))book.save(newFilePath)
