使用“文件名到名称”选项卡将多个.xls文件添加到单个.xls文件

Question

我有多个目录，每个目录包含任意数量的.xls文件。 我想将任何给定目录中的文件合并成一个.xls文件，使用文件名作为选项卡名。 例如，如果有文件名称.xls, 年龄.xls, 位置.xls，我想将它们与来自的数据合并到一个新文件中名称.xls在名为NAME的选项卡上，来自年龄.xls在一个叫做年龄等的标签上。 每个source.xls文件只有一列没有标题的数据。 这就是我目前所拥有的，但它不起作用。 任何帮助都将不胜感激（我对Python相当陌生，以前从未做过类似的事情）。你知道吗

wkbk = xlwt.Workbook()

xlsfiles =  glob.glob(os.path.join(path, "*.xls"))
onlyfiles = [f for f in listdir(path) if isfile(join(path, f))]
tabNames = []
for OF in onlyfiles:
    if str(OF)[-4:] == ".xls":
        sheetName = str(OF)[:-4]
        tabNames.append(sheetName)
    else:
        pass

for TN in tabNames:
    outsheet = wkbk.add_sheet(str(TN))
    data = pd.read_excel(path + "\\" + TN + ".xls", sheet_name="data")
    data.to_excel(path + "\\" + "Combined" + ".xls", sheet_name = str(TN))

Answer 1

你能试试吗

import pandas as pd
import glob

path = 'YourPath\ToYour\Files\\' # Note the \\ at the end

# Create a list with only .xls files
list_xls = glob.glob1(path,"*.xls") 

# Create a writer for pandas
writer = pd.ExcelWriter(path + "Combined.xls", engine = 'xlwt')

# Loop on all the files
for xls_file in list_xls:
    # Read the xls file and the sheet named data
    df_data = pd.read_excel(io = path + xls_file, sheet_name="data") 
    # Are the sheet containing data in all your xls file named "data" ?
    # Write the data into a sheet named after the file
    df_data.to_excel(writer, sheet_name = xls_file[:-4])
# Save and close your Combined.xls
writer.save()
writer.close()

让我知道如果它适用于你，我从来没有尝试引擎='xlwt'因为我不与.xls文件，但.xlsx工作

Answer 2

这是一个小助手函数-它同时支持.xls和.xlsx文件：

import pandas as pd
try:
    from pathlib import Path
except ImportError:              # Python 2
    from pathlib2 import Path


def merge_excel_files(dir_name, out_filename='result.xlsx', **kwargs):
    p = Path(dir_name)
    with pd.ExcelWriter(out_filename) as xls:
        _ = [pd.read_excel(f, header=None, **kwargs)
               .to_excel(xls, sheet_name=f.stem, index=False, header=None)
             for f in p.glob('*.xls*')]

用法：

merge_excel_files(r'D:\temp\xls_directory', 'd:/temp/out.xls')
merge_excel_files(r'D:\temp\xlsx_directory', 'd:/temp/out.xlsx')