如何使用Pandas读取多个xlsx文件，并在多个工作表的单个文件中输出为一个文件? [英] How do I use Pandas for reading multiple xlsx files and outputting into one in individual file in multiple sheets?

问题描述

.xlsx文件全部位于一个目录中.此目录中只有.xlsx个文件.我需要获取每个单独的.xlsx文件，并将其插入到一张纸中.

The .xlsx files are all found in one directory. There is only .xlsx file in this directory. I need to take each of the individual .xlsx files and insert it into a single sheet.

示例如下:

1. 目录中有4个.xlsx文件
2. 读取全部4个.xlsx文件
3. 将所有4个.xlsx文件放入一个文件中
4. 每个文件应代表一张纸.

1. Directory has 4 .xlsx files
2. Read all 4 .xlsx files
3. Put all 4 .xlsx files into one single file
4. Each file should represent one sheet.

最终结果应该是一个带有4张纸的 Excel 文件.

The final result should be one Excel file with 4 sheets.

推荐答案

执行此操作的过程是:

0 .设置

安装必需的软件包:

pip install pandas
pip install xlsxwriter

然后将熊猫导入您正在使用的Python文件中:

Then import pandas into the Python file you're working in:

import pandas as pd

1 .读入.xlsx文件

a .每个按名称:

df1 = pd.read_excel('./excelfile1.xlsx')

等

b .阅读当前目录中的所有内容:

b. Read all in current directory in:

import os, re
dfs = []
for fname in os.listdir():
    if re.search(r'\.xlsx$', fname):
        dfs.append(pd.read_excel(fname))

2 .创建一个新文件并将现有文件添加为工作表

2. Create a new file and add existing files as sheets

writer = pd.ExcelWriter('./newfilename.xlsx', engine='xlsxwriter')
sheet_names = ['sheet1', ...]
for df, sheet_name in zip(dfs, sheet_names):
    df.to_excel(writer, sheet_name=sheet_name)
writer.save()

这将在当前目录中创建一个名为newfilename.xlsx的新Excel文件，其中每个现有Excel文件都作为工作表.

This will create a new Excel file in the current directory called newfilename.xlsx with each of your existing Excel files as sheets.

这篇关于如何使用Pandas读取多个xlsx文件，并在多个工作表的单个文件中输出为一个文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！