|
|
合并两个Excel文档：第一个文档如下： 第二个文档如下： 想要合并成下面图片的样子： 学习一段时间了，写了个python合并两个Excel文件的脚本，希望能帮助到像我一样的小白，代码简单，大佬勿喷。不足之处请您不吝赐教：下面代码实现：
# X$ p9 V& y- Y, \<ol>""") _+ m) o( i# z% Z m8 a
Date：2024/10/21 15:462 c. c B' n+ l& Y: ~$ k
File : MergeExcel.py
9 c1 z8 s# f3 U: ~( g& o1 `+ L"""5 t) j6 [ t+ d$ c
from copy import copy1 S- D. U% w$ K6 |0 A5 O% f% H9 E; @# d
from openpyxl import load_workbook
) ~' Z+ p2 u* o: z- q0 c0 z" h; G4 p q6 w6 _/ n' |9 d
% d( a+ L# W& a; }" n
class MergeExcel:+ a3 U7 e; W' Q% w. W* U3 j
def __init__(self):" P% r* ~3 T" h% L# w
e1 = "fileDirectory/excel1.xlsx"
( E, n, |$ c7 x8 ~( n+ I e2 = "fileDirectory/excel2.xlsx"
, x2 c( Q3 h/ V1 C self.wb1 = load_workbook(e1). L, K3 S% m* [3 @6 `- b
self.wb2 = load_workbook(e2)
9 x; Z0 }4 d0 t; f self.sheet1 = self.wb1.active
- ^1 h. l7 k0 I; y; Q/ j+ s6 i# O self.sheet2 = self.wb2.active1 f' w1 x6 @$ f+ r: L+ J6 v( x
self.insert_row = 99 # 这里设置从哪行插入第一张表的数据 或者 self.sheet2.max_row + 1: s$ G7 |/ z# z$ e8 G# i6 T
7 e' J; `; o8 p% U# W3 c7 t @staticmethod
1 O$ M* i) c8 i def copy_style(source_cell, target_cell):
0 C5 D6 k; w$ ^" |( i" i0 a if source_cell.has_style:0 ^! Z1 _3 k- p: B9 Z
target_cell.font = copy(source_cell.font)
; d+ F3 E: ~8 W+ I6 y target_cell.fill = copy(source_cell.fill)
! X" P+ W. E$ i7 P target_cell.border = copy(source_cell.border)
; H$ {& K+ T# x. e5 L- _ target_cell.alignment = copy(source_cell.alignment)
6 Q- K. h. {7 t/ D9 u) |2 Z target_cell.number_format = source_cell.number_format
( @7 @5 t3 P0 ]8 w, V target_cell.protection = copy(source_cell.protection)
5 c6 Z3 D3 |1 E! s# x # 使用openpyxl库的cell.comment属性获取单元格的注释。
/ W( e! q- ^" A if source_cell.comment:8 d6 m- K- p! L% H
target_cell.comment = source_cell.comment # 有批注的复制批注
" C* `% I+ u: B, ?% q3 Y
6 Q0 k! X0 H0 w- y. @7 p def apply_merge_cell(self, merged_ranges):
7 ?1 x6 @2 _7 @1 N+ B for min_row, min_col, max_row, max_col in merged_ranges:
" d' X+ x% V" p) ]) N& l self.sheet2.merge_cells(start_row=min_row, start_column=min_col,# {5 k7 e* G: q' B
end_row=max_row, end_column=max_col)
9 I! z' F/ f1 l& N3 W. d$ Z# t' |* K( n
def copy_value_style(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,% D% z6 c6 V3 ?
target_start_col):! l. q2 V) A, h' }: n
for row in range(source_start_row, source_end_row + 1):$ [+ n/ |3 y5 s0 u* L) N
for col in range(source_start_col, source_end_col + 1):2 k; C, B. u2 Z1 B) ?
source_cell = self.sheet1.cell(row=row, column=col)
! Y1 G9 M7 Z3 p; l- }% V target_cell = self.sheet2.cell(row=row + target_start_row - source_start_row,: F) }9 C, P& }3 s, r
column=col + target_start_col - source_start_col)
7 v% ^$ z2 t- u; U' _2 g self.sheet2.cell(row=row + target_start_row - source_start_row,( @2 [* l8 E$ r7 m$ n
column=col + target_start_col - source_start_col).value = source_cell.value
9 Q! z0 k9 ^ p
+ N! a( Q; ?, i2 m: F) u$ y& X, Z# |/ m self.copy_style(source_cell, target_cell)1 E% g, R$ C) R
% v. I* D- K# k+ b2 c/ D) j def copy_stats(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,
! g$ E" N0 k- K4 S9 t7 { L( n target_start_col):! Z' ^4 }/ K) `; E, \7 V) I8 ~
merged_ranges = []
1 d( H D0 D7 x$ h1 _2 y) ? for merge_range in self.sheet1.merged_cells.ranges:
) Q# s6 v v8 g! B min_col, min_row, max_col, max_row = merge_range.bounds/ m* D2 Y0 ~+ V, E8 y
print(min_row, min_col, max_row, max_col)% j# _) S7 Q. t' E8 P$ \
print(source_start_row, source_start_col,source_end_row, source_end_col)
; j J, ] `8 m. M if (min_row >= source_start_row and max_row = source_start_col and max_col |
|
发表于 2024-10-23 07:26:01
