python按列索引提取文件夹内所有excel指定列汇总(示例代码)

下面我会详细讲解“python按列索引提取文件夹内所有excel指定列汇总”的完整实例教程。在教程中，我会使用Python语言和一些第三方库来实现这个功能。

一、需求分析

首先，我们需要明确我们的需求：从一个文件夹内的所有Excel文件中，提取出指定列的数据，并将其汇总到一个新的文件中。具体来说，我们可以定义如下需求：

• 文件夹路径：我们需要指定需要处理的Excel文件所在的文件夹路径，可以是相对路径或者绝对路径；
• 列索引：我们需要指定需要提取的指定列的列索引，可以是列的数字索引或者A、B、C这样的字母索引；
• 表头：我们需要指定Excel文件中用作表头的行数，以便于在数据汇总时排除表头中的内容；
• 汇总数据到新Excel文件：我们需要将所有提取到的数据汇总到一个新的Excel文件中，这个文件需要放在指定的位置上。

二、技术实现

接下来，我们可以考虑如何使用Python语言来实现这个功能。

1. 获取文件夹内的所有Excel文件

我们可以使用Python的os和glob模块来获取指定文件夹内的所有Excel文件。具体实现代码如下：

import os
import glob

def get_excel_files(folder_path):
    """
    获取指定文件夹内的所有Excel文件
    """
    os.chdir(folder_path)
    excel_files = glob.glob("*.xlsx") + glob.glob("*.xls")
    return excel_files

在上面的代码中，我们使用了os.chdir()函数来切换文件夹路径，可以确保后续操作是在指定的文件夹内进行的。然后，我们使用glob.glob()函数来获取所有的Excel文件，其中*.xlsx和*.xls表示通配符，匹配所有后缀名为.xlsx和.xls的文件。最后，我们将所有的Excel文件名称存储到一个列表中，作为后续操作的输入。

2. 读取Excel文件并提取指定列的数据

我们可以使用Python的pandas和openpyxl这两个库来读取Excel文件，并提取指定列的数据。具体实现代码如下：

import pandas as pd
from openpyxl import load_workbook

def get_column_data(file_name, sheet_name, column_index, header_row):
    """
    从Excel文件中提取指定列的数据
    """
    # 读取Excel文件
    wb = load_workbook(filename=file_name)
    ws = wb[sheet_name]

    # 读取数据
    df = pd.DataFrame(ws.values)
    header = df.iloc[header_row-1]
    data = df.iloc[header_row:]

    # 提取指定列的数据
    column_data = data.iloc[:, column_index-1]

    return column_data

在上面的代码中，我们使用了openpyxl.load_workbook()函数来加载Excel文件，并使用pandas.DataFrame函数将其转换为一个数据框。然后，我们使用iloc函数来提取数据框中的指定列数据，并将其返回。

3. 将所有Excel文件的指定列数据汇总到一个数据框中

接下来，我们需要将所有Excel文件的指定列数据汇总到一个数据框中，以便于后续的数据处理。具体实现代码如下：

def merge_data_columns(folder_path, sheet_name, column_index, header_row):
    """
    将所有Excel文件的指定列数据汇总到一个数据框中
    """
    # 获取所有的Excel文件
    excel_files = get_excel_files(folder_path)

    # 读取指定列的数据
    column_data_list = []
    for file_name in excel_files:
        column_data = get_column_data(file_name, sheet_name, column_index, header_row)
        column_data_list.append(column_data)
    all_data = pd.concat(column_data_list, ignore_index=True)

    return all_data

在上面的代码中，我们首先使用get_excel_files()函数获取指定文件夹内的所有Excel文件，然后循环遍历所有的Excel文件，使用get_column_data()函数提取指定列的数据，并将所有的数据拼接到一个数据框中，最后将数据框返回。

4. 将汇总的数据保存到新的Excel文件中

最后，我们可以使用pandas库将汇总的数据保存到一个新的Excel文件中。具体实现代码如下：

def save_to_file(data_frame, file_name, sheet_name):
    """
    将数据保存到Excel文件中
    """
    writer = pd.ExcelWriter(file_name, engine="openpyxl")

    sheet_exists = False
    try:
        # 尝试打开已经存在的工作表
        writer.book = load_workbook(file_name)
        writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
        sheet_exists = True
    except:
        # 如果不存在，则创建一个新工作表
        pass

    if sheet_exists:
        # 如果工作表已经存在，则在现有工作表上追加数据
        data_frame.to_excel(writer, sheet_name, index=False, header=False, startrow=writer.sheets[sheet_name].max_row)
    else:
        # 如果工作表不存在，则新建一个工作表
        data_frame.to_excel(writer, sheet_name, index=False)

    writer.save()

在上面的代码中，我们使用pandas.ExcelWriter()函数创建一个Excel写入器，然后使用openpyxl.load_workbook()函数打开已存在的工作表，或使用pandas.DataFrame.to_excel()函数在新的工作表上写入数据。

三、示例说明

最后，我可以给出两个示例来介绍如何使用上面的实现代码。假设我们有如下的Excel文件夹：

D:\
└─Excel Files
    └─SampleData
        ├─Jan.xlsx
        ├─Feb.xlsx
        └─Mar.xlsx

每个Excel文件包含如下数据：

	A	B	C	D	E
1	ID	Name	Age	City	Dept
2	001	Bob	31	LDN	HR
3	002	John	28	NYC	IT
4	003	Kate	30	LDN	IT
5	004	Tom	35	NYC	Sales
6	005	Lucy	40	NYC	HR
7	006	Gary	42	LDN	HR

示例一：提取每个Excel文件的第三列数据，汇总到一个新的Excel文件中

# 读取所有的Excel文件，并将第三列数据汇总到一个数据框中
folder_path = r"D:\Excel Files\SampleData"
header_row = 1
sheet_name = "Sheet1"
column_index = 3
all_data = merge_data_columns(folder_path, sheet_name, column_index, header_row)

# 将汇总的数据保存到一个新的Excel文件中
file_name = r"D:\Excel Files\Summary.xlsx"
save_to_file(all_data, file_name, sheet_name)

在上面的代码中，我们首先设置文件夹路径、表头、工作表名称和需要提取的列索引等参数，然后使用merge_data_columns()函数读取Excel文件并提取指定列数据，最后使用save_to_file()函数将汇总的数据保存到一个新的Excel文件中。

示例二：提取每个Excel文件的第二列、第四列数据，汇总到一个新的Excel文件中

# 读取所有的Excel文件，并将第二列和第四列数据汇总到一个数据框中
folder_path = r"D:\Excel Files\SampleData"
header_row = 1
sheet_name = "Sheet1"
column_index_list = [2, 4]
column_data_list = []
for i in column_index_list:
    column_data = merge_data_columns(folder_path, sheet_name, i, header_row)
    column_data_list.append(column_data)
all_data = pd.concat(column_data_list, axis=1)

# 将汇总的数据保存到一个新的Excel文件中
file_name = r"D:\Excel Files\Summary.xlsx"
save_to_file(all_data, file_name, sheet_name)

在上面的代码中，我们首先设置文件夹路径、表头、工作表名称和需要提取的列索引列表等参数。然后，我们循环遍历每个需要提取的列，使用merge_data_columns()函数读取Excel文件并提取指定列数据，并将所有的列数据合并到一个数据框中。最后，我们使用save_to_file()函数将汇总的数据保存到一个新的Excel文件中。

四、总结

通过上面的实例教程，我们学习了如何使用Python语言和一些第三方库来实现按列索引提取文件夹内所有Excel指定列汇总的功能。此外，我们还学习了如何使用函数和循环等技巧，能够提高代码的可重用性和可读性。