python批量读取多个Excel表格文件中指定单元格内容形成一个新的excel文件

源码如下：

# -*- coding:utf-8 -*-
import os
import openpyxl
import pandas as pd
from pandas.core.frame import DataFrame


cellnames = ["A6", "A12", "A19", "W2", "AD2", "W3", "AD3"]  # 指定文件单元格的位置
path = r"C:\Users\uig55925\Desktop\培训文件"  # 目录路径
wb = openpyxl.Workbook()
nsh = []
FileNames = os.listdir(path)
for fn in FileNames:
    fullfilename = os.path.join(path, fn)
    print(fullfilename)
    wb = openpyxl.load_workbook(fullfilename)
    sheet_names = wb.sheetnames
    print(sheet_names)
    sh = wb["Sample"]
    arr = [sh[n].value for n in cellnames]
    wb.close()

    print(arr)
    nsh.append(arr)
    dataframe = pd.DataFrame(nsh)
    dataframe.columns = ['Finding/Problem 发现/问题', 'Aftermatch 后果', 'Rootcause & Solution 原因和措施', 'Dept.', 'Author', 'No.', 'Date']  # 对应单元格读取的列名

dataframe.to_excel('output_file.xlsx')  # 输出路径

遇到问题：zipfile.BadZipFile: File is not a zip file

解决办法：由于运行时已打开文件导致出现了缓存，所以找不到要打开的excel是哪个，具体步骤打开任务管理器关掉进程，重启pycharm，以后每次运行时都要关闭全部excel在运行。