|
|
合并两个Excel文档：第一个文档如下： 第二个文档如下： 想要合并成下面图片的样子： 学习一段时间了，写了个python合并两个Excel文件的脚本，希望能帮助到像我一样的小白，代码简单，大佬勿喷。不足之处请您不吝赐教：下面代码实现： 7 U) `: _; _0 x) \( A
<ol>"""
/ j, _+ x9 D# f# f3 q9 iDate：2024/10/21 15:46
3 t5 w0 L. i; b$ w1 x: B3 @" mFile : MergeExcel.py
8 z7 A* N+ l+ O"""
2 A! M7 Q: H" S5 c/ Gfrom copy import copy
0 g( ]0 H$ x4 M' m5 [ q5 t+ l& ufrom openpyxl import load_workbook
( r1 O* g" l7 t. ?3 ~
# m& p7 `4 L: G6 }# t% f5 @8 Y
& x R* |( Q+ H n2 C! Hclass MergeExcel:
( \& k* E% p. d3 s8 t; h, E def __init__(self):
' H8 q- H; W+ S! V e1 = "fileDirectory/excel1.xlsx"3 q$ O1 }& H9 A1 C- C7 l6 m
e2 = "fileDirectory/excel2.xlsx"1 M; X, r1 c: q3 N; u- D
self.wb1 = load_workbook(e1)( R0 C& U/ t1 q; Z, h5 p
self.wb2 = load_workbook(e2)
! o& ]8 \6 N1 L7 Y/ N, {. X self.sheet1 = self.wb1.active7 ]+ {; w9 V" Y4 g/ W0 N* h8 u/ Y0 K
self.sheet2 = self.wb2.active
3 l2 C# q; ]+ A/ T# s/ ~- i self.insert_row = 99 # 这里设置从哪行插入第一张表的数据 或者 self.sheet2.max_row + 1
' [3 A8 Z& m' F) X/ {, Z2 Z" B- L {1 b7 I
@staticmethod; `+ c. b s% S$ C- ~# k
def copy_style(source_cell, target_cell):
z, {1 j$ {. \* H& M& B$ e% q if source_cell.has_style:. M9 t3 s, ?4 G( Z: W) v* l/ I5 f
target_cell.font = copy(source_cell.font)* |& p& e) W: X- `5 ]( v* q& p" X
target_cell.fill = copy(source_cell.fill)
. H; K, q7 C* K target_cell.border = copy(source_cell.border)4 g/ Q/ x0 r2 M" f9 g! T! j# J- q
target_cell.alignment = copy(source_cell.alignment)7 u1 F- f5 H; F6 v0 `
target_cell.number_format = source_cell.number_format
+ A, T( P% `- \( W) g target_cell.protection = copy(source_cell.protection)
% E& {- Y+ c, W7 g # 使用openpyxl库的cell.comment属性获取单元格的注释。
- F9 ^ J8 }8 E8 O. T* | if source_cell.comment:+ e |3 ]$ S; v. S: b6 o- E7 Y
target_cell.comment = source_cell.comment # 有批注的复制批注) U) V: V! d2 X" R$ ~+ u7 A, I
' B, L, v( d8 V3 V& A
def apply_merge_cell(self, merged_ranges):
1 M9 B9 {' A5 } for min_row, min_col, max_row, max_col in merged_ranges:
/ P# l6 ?/ l0 N/ E/ D. ~! I$ S self.sheet2.merge_cells(start_row=min_row, start_column=min_col,+ i, U! j9 a* c
end_row=max_row, end_column=max_col)" x& u& k. ^( |& y7 `
) @2 b3 E$ y$ y4 x" l def copy_value_style(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,
- h7 ?/ c9 D5 M, Q- c target_start_col):% f6 c: r; O/ B; O
for row in range(source_start_row, source_end_row + 1):$ a, z) m) `& ^1 [6 F
for col in range(source_start_col, source_end_col + 1):5 _; p1 N8 }% S" ]- A; m9 P
source_cell = self.sheet1.cell(row=row, column=col)
; @: B- o/ U- Z. e a. a6 Y1 y3 O target_cell = self.sheet2.cell(row=row + target_start_row - source_start_row,
/ v4 @4 x$ l. a/ t column=col + target_start_col - source_start_col)6 J- v; X& R( v* i
self.sheet2.cell(row=row + target_start_row - source_start_row,! {# \' p8 H8 H; ^/ H+ `& [$ S! i
column=col + target_start_col - source_start_col).value = source_cell.value
2 v8 x h) ]. A+ [& q$ m/ T. X& w8 t
self.copy_style(source_cell, target_cell), H( W1 p+ t4 v3 Y( R7 x& n6 H
4 V) A8 D1 a( E- g7 ?/ {! q- p
def copy_stats(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,
" h9 C) U+ V3 e/ e target_start_col):
+ d. L2 e2 h0 ], c& x( I/ l( X& j merged_ranges = []) i0 o) N. y1 d& _& Y
for merge_range in self.sheet1.merged_cells.ranges:, j* S" O% h2 A" i4 ^$ G9 L: N9 }; e
min_col, min_row, max_col, max_row = merge_range.bounds
: p; _, j s4 j- l3 l2 q8 N print(min_row, min_col, max_row, max_col)1 c8 M6 m4 _) y: l7 H( e
print(source_start_row, source_start_col,source_end_row, source_end_col)
& J: g- d- M z1 ~ N if (min_row >= source_start_row and max_row = source_start_col and max_col |
|
发表于 2024-10-23 07:26:01
