需求:
使用Python合并多个Excel文件,生成新表格,固定前9列为包括一级单位、二级公司名称、三级部门、四级业务系统、五级租户名称、联系号码、邮箱、业务负责人和工单号字段。
试错:
代码①(报错如下)
# This is a sample Python script.
import os
import pandas as pd
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
def print_hi():
# 设置工作目录
source_src = os.getcwd() + '/'
working_dir = source_src + 'directory'
print(source_src)
print(working_dir)
os.chdir(working_dir)
# 合并所有 xlsx 文件
all_data = pd.DataFrame()
for file in os.listdir(working_dir):
if file.endswith('.xlsx'):
df = pd.read_excel(file)
all_data = pd.concat([all_data, df], ignore_index=True)
# 选择需要的列并重新排列
required_cols = ['一级单位', '二级公司名称', '三级部门', '四级业务系统', '五级租户名称', '联系号码', '邮箱',
'业务负责人', '工单']
all_data = all_data[required_cols + [col for col in all_data.columns if col not in required_cols]]
# 保存到新的 Excel 文件
all_data.to_excel('merged_data.xlsx', index=False)
# Press the green button in the gutter to run the script.
if __name__ == '__main__':
print_hi()
# See PyCharm help at https://www.jetbrains.com/help/pycharm/
设置工作目录,确保所有需要合并的 xlsx 文件都在该目录下。
使用 pd.read_excel() 函数读取所有 xlsx 文件,并使用 pd.concat() 将它们合并成一个 DataFrame。
选择需要的列,并将它们放在前 9 列。其他列则放在后面。
使用 to_excel() 函数将合并后的 DataFrame 保存到一个新的 Excel 文件 merged_data.xlsx。
完善:
DataFrame 中没有包含所有需要的列,导致了 KeyError。可以先检查一下 DataFrame 的列名,确保它们与您需要的列名匹配。
运行代码,会打印出当前 DataFrame 的列名。可以对比一下这些列名与您需要的列名是否一致。如果不一致,可能需要修改 required_cols 列表,使其与实际的列名匹配。
如果列名确实与您需要的不一致,您也可以尝试使用 all_data.rename(columns={'old_name': 'new_name'}, inplace=True) 来重命名列名。
Excel 文件中可能没有包含所有需要的列。在这种情况下,我们可以添加一些判断逻辑,如果某些列不存在,就设置单元格值为空。
最终版本经历如下3个变化:
①主要的变化在于:
我们创建了一个新的 DataFrame all_data_with_required,用于存储需要的列。
在遍历 required_cols 时,我们先检查该列是否存在于 all_data 中。如果存在,就将该列的数据添加到 all_data_with_required。如果不存在,就设置该列的值为空字符串。
最后,我们将 all_data_with_required 保存到新的 Excel 文件中。
这样即使某些列在 Excel 文件中不存在,代码也能正常运行,并将该列的值设置为空。
②主要的变化在于:
我们仍然遍历 required_cols,如果该列存在于 all_data 中,就将数据添加到 all_data_with_required。如果不存在,就设置为空字符串。
在这之后,我们使用 pd.concat() 函数,将 all_data_with_required 和 all_data 中剩余的列(即不在 required_cols 中的列)合并起来,并将结果赋值给 all_data_with_required。这样可以确保所有的列都被包含在最终的 DataFrame 中。
③主要的变化在于:
在遍历 required_cols 时,如果当前列是 '联系号码',我们将其赋值为 all_data['电话']。
如果当前列是 '业务负责人',我们将其赋值为 all_data['责任人']。
这样就可以将 '电话' 列的值赋给 '联系号码' 列,将 '责任人' 列的值赋给 '业务负责人' 列。其他列的处理方式与之前的代码相同。
最终版本(多余注释行可删除)
# This is a sample Python script.
import os
import pandas as pd
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
def print_hi():
# # 设置工作目录
# source_src = os.getcwd() + '/'
# working_dir = source_src + 'directory'
#
# # print(source_src)
# # working_dir = r'C:\path\to\your\directory'
# # print(working_dir)
#
# os.chdir(working_dir)
#
# # 合并所有 xlsx 文件
# all_data = pd.DataFrame()
# for file in os.listdir(working_dir):
# if file.endswith('.xlsx'):
# df = pd.read_excel(file)
# all_data = pd.concat([all_data, df], ignore_index=True)
#
# # 选择需要的列并重新排列
# required_cols = ['一级单位', '二级公司名称', '三级部门', '四级业务系统', '五级租户名称', '联系号码', '邮箱',
# '业务负责人', '工单号']
# all_data = all_data[required_cols + [col for col in all_data.columns if col not in required_cols]]
#
# # 保存到新的 Excel 文件
# all_data.to_excel('merged_data.xlsx', index=False)
# # 设置工作目录
# source_src = os.getcwd()
# working_dir = os.path.join(source_src, 'directory')
#
# if not os.path.exists(working_dir):
# os.makedirs(working_dir)
#
# os.chdir(working_dir)
#
# # 合并所有 xlsx 文件
# all_data = pd.DataFrame()
# for file in os.listdir(working_dir):
# if file.endswith('.xlsx'):
# df = pd.read_excel(os.path.join(working_dir, file))
# all_data = pd.concat([all_data, df], ignore_index=True)
#
# # 选择需要的列并重新排列
# required_cols = ['一级单位', '二级公司名称', '三级部门', '四级业务系统', '五级租户名称', '联系号码', '邮箱',
# '业务负责人', '工单号']
# # required_cols = ['一级单位']
# all_data = all_data[required_cols + [col for col in all_data.columns if col not in required_cols]]
#
# # 保存到新的 Excel 文件
# all_data.to_excel(os.path.join(working_dir, 'merged_data.xlsx'), index=False)
# # 设置工作目录
# source_src = os.getcwd()
# working_dir = os.path.join(source_src, 'directory')
#
# if not os.path.exists(working_dir):
# os.makedirs(working_dir)
#
# os.chdir(working_dir)
#
# # 合并所有 xlsx 文件
# all_data = pd.DataFrame()
# for file in os.listdir(working_dir):
# if file.endswith('.xlsx'):
# print(f"Processing file: {file}")
# df = pd.read_excel(os.path.join(working_dir, file))
# all_data = pd.concat([all_data, df], ignore_index=True)
#
# # 选择需要的列并重新排列
# required_cols = ['一级单位', '二级公司名称', '三级部门', '四级业务系统', '五级租户名称', '联系号码', '邮箱',
# '业务负责人', '工单号']
# all_data_with_required = pd.DataFrame()
# for col in required_cols:
# if col in all_data.columns:
# all_data_with_required[col] = all_data[col]
# else:
# all_data_with_required[col] = ''
#
# # 将其他列添加到末尾
# all_data_with_required = pd.concat(
# [all_data_with_required, all_data[list(set(all_data.columns) - set(required_cols))]], axis=1)
#
# # 保存到新的 Excel 文件
# print("Saving merged data to 'merged_data.xlsx'...")
# all_data_with_required.to_excel(os.path.join(working_dir, 'merged_data.xlsx'), index=False)
# print("Done!")
# 设置工作目录
source_src = os.getcwd()
working_dir = os.path.join(source_src, 'directory')
if not os.path.exists(working_dir):
os.makedirs(working_dir)
os.chdir(working_dir)
# 合并所有 xlsx 文件
all_data = pd.DataFrame()
for file in os.listdir(working_dir):
if file.endswith('.xlsx'):
print(f"Processing file: {file}")
df = pd.read_excel(os.path.join(working_dir, file))
all_data = pd.concat([all_data, df], ignore_index=True)
# 选择需要的列并重新排列
required_cols = ['一级单位', '二级公司名称', '三级部门', '四级业务系统', '五级租户名称', '联系号码', '邮箱',
'业务负责人', '工单号']
all_data_with_required = pd.DataFrame()
for col in required_cols:
if col == '联系号码':
all_data_with_required[col] = all_data['电话']
elif col == '业务负责人':
all_data_with_required[col] = all_data['责任人']
elif col in all_data.columns:
all_data_with_required[col] = all_data[col]
else:
all_data_with_required[col] = ''
# 将其他列添加到末尾
all_data_with_required = pd.concat(
[all_data_with_required, all_data[list(set(all_data.columns) - set(required_cols))]], axis=1)
# 保存到新的 Excel 文件
print("Saving merged data to 'merged_data.xlsx'...")
all_data_with_required.to_excel(os.path.join(working_dir, 'merged_data.xlsx'), index=False)
print("Done!")
# Press the green button in the gutter to run the script.
if __name__ == '__main__':
print_hi()
# See PyCharm help at https://www.jetbrains.com/help/pycharm/
从输出结果来看,代码已经成功运行,没有报错信息。这些警告信息是由于 openpyxl 库在处理 Excel 文件是发出的,通常不会影响代码的正常执行。
可以检查一下 directory 文件夹,看看是否生成了 merged_data.xlsx 文件。如果文件存在,那说明您的代码已经成功合并了所有的 Excel 文件,并将结果保存到了新的 Excel 文件中。
如果想要在控制台上输出更多的调试信息,可以在代码中添加一些 print() 语句,比如在合并文件和保存文件的地方添加一些提示信息。