python合并两个Excel文件的脚本
合并两个Excel文档：第一个文档如下：https://pic.imgdb.cn/item/6715be5fd29ded1a8c567034.png第二个文档如下：https://pic.imgdb.cn/item/6715be5fd29ded1a8c56707e.png想要合并成下面图片的样子：https://pic.imgdb.cn/item/6715be5fd29ded1a8c5670b6.png学习一段时间了，写了个python合并两个Excel文件的脚本，希望能帮助到像我一样的小白，代码简单，大佬勿喷。不足之处请您不吝赐教：下面代码实现："""
Date：2024/10/21 15:46
File : MergeExcel.py
"""
from copy import copy
from openpyxl import load_workbook
class MergeExcel:
def __init__(self):
e1 = "fileDirectory/excel1.xlsx"
e2 = "fileDirectory/excel2.xlsx"
self.wb1 = load_workbook(e1)
self.wb2 = load_workbook(e2)
self.sheet1 = self.wb1.active
self.sheet2 = self.wb2.active
self.insert_row = 99 # 这里设置从哪行插入第一张表的数据 或者 self.sheet2.max_row + 1
@staticmethod
def copy_style(source_cell, target_cell):
if source_cell.has_style:
target_cell.font = copy(source_cell.font)
target_cell.fill = copy(source_cell.fill)
target_cell.border = copy(source_cell.border)
target_cell.alignment = copy(source_cell.alignment)
target_cell.number_format = source_cell.number_format
target_cell.protection = copy(source_cell.protection)
# 使用openpyxl库的cell.comment属性获取单元格的注释。
if source_cell.comment:
target_cell.comment = source_cell.comment# 有批注的复制批注
def apply_merge_cell(self, merged_ranges):
for min_row, min_col, max_row, max_col in merged_ranges:
self.sheet2.merge_cells(start_row=min_row, start_column=min_col,
end_row=max_row, end_column=max_col)
def copy_value_style(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,
target_start_col):
for row in range(source_start_row, source_end_row + 1):
for col in range(source_start_col, source_end_col + 1):
source_cell = self.sheet1.cell(row=row, column=col)
target_cell = self.sheet2.cell(row=row + target_start_row - source_start_row,
column=col + target_start_col - source_start_col)
self.sheet2.cell(row=row + target_start_row - source_start_row,
column=col + target_start_col - source_start_col).value = source_cell.value
self.copy_style(source_cell, target_cell)
def copy_stats(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,
target_start_col):
merged_ranges = []
for merge_range in self.sheet1.merged_cells.ranges:
min_col, min_row, max_col, max_row = merge_range.bounds
print(min_row, min_col, max_row, max_col)
print(source_start_row, source_start_col,source_end_row, source_end_col)
if (min_row >= source_start_row and max_row <= source_end_row and
min_col >= source_start_col and max_col <= source_end_col):
merged_ranges.append((min_row + target_start_row - source_end_row - 1,
min_col + target_start_col - source_start_col,
max_row + target_start_row - source_end_row - 1,
max_col + target_start_col - source_start_col))
self.apply_merge_cell(merged_ranges)
print(merged_ranges)
def main(self):
self.copy_value_style(1, self.sheet1.max_row, 1, self.sheet1.max_column, self.insert_row, 1)
self.copy_stats(1, self.sheet1.max_row, 1, self.sheet1.max_column, self.sheet2.max_row + 1, 1)
# 保存工作簿
self.wb2.save('fileDirectory/MergeExcel.xlsx')
print("Done...")
if __name__ == '__main__':
merge_excel = MergeExcel()
merge_excel.main()
这个代码量、、、、、、、、、、、、、、 强烈支持楼主ing…… 强烈支持楼主ing…… 强烈支持楼主ing…… 本帖最后由 glh 于 2024-11-1 14:14 编辑
laoda1229 发表于 2024-10-23 07:16
这个代码量、、、、、、、、、、、、、、
import pandas as pd
# 读取第一个 Excel 文件
df1 = pd.read_excel('file1.xlsx')
# 读取第二个 Excel 文件
df2 = pd.read_excel('file2.xlsx')
# 拼接两个 DataFrame
combined_df = pd.concat(, ignore_index=True)
# 保存为新的 Excel 文件
combined_df.to_excel('combined_file.xlsx', index=False)
print("两个 Excel 文件已成功拼接并保存为 combined_file.xlsx")
chatgpt回答的，够简洁吧。看着没什么问题。格式可能乱掉了，可以根句具体情况一直问。
页:
[1]