|
合并两个Excel文档:第一个文档如下:第二个文档如下:想要合并成下面图片的样子:学习一段时间了,写了个python合并两个Excel文件的脚本,希望能帮助到像我一样的小白,代码简单,大佬勿喷。不足之处请您不吝赐教:下面代码实现: ' G1 P$ f" N4 g4 p& B( q) n* R
<ol>"""$ L/ W1 Q& x0 g
Date:2024/10/21 15:46
/ @1 j+ R7 H% {7 @File : MergeExcel.py ]1 m7 F. c5 b% A+ G' x( ^& ^+ U2 A
"""
* C) v% X2 z7 m' M( U' J9 H3 yfrom copy import copy
3 i2 h0 C8 M9 O. c c( Sfrom openpyxl import load_workbook
+ {- N# n$ F8 a3 r9 l2 X" r6 V2 Y9 N! _3 X
' J6 r9 w' B3 i& k! \0 rclass MergeExcel:- W/ _' d+ B6 k p0 \: @. n5 H
def __init__(self):6 [1 x6 W+ I0 [! m" W4 w/ Y
e1 = "fileDirectory/excel1.xlsx"
5 }' [: d; V3 A0 e e2 = "fileDirectory/excel2.xlsx"# u; a0 ]* \$ z0 r9 _+ V. m2 ?
self.wb1 = load_workbook(e1) }- Z/ Y. I0 G* Z7 W
self.wb2 = load_workbook(e2)
6 r7 ^2 r2 y9 d$ f self.sheet1 = self.wb1.active3 ?$ z3 F3 x6 y
self.sheet2 = self.wb2.active
: Y5 `: x2 v8 N& Q self.insert_row = 99 # 这里设置从哪行插入第一张表的数据 或者 self.sheet2.max_row + 1
- P2 m# F2 k- z2 S- A8 [" M4 [
/ Q" f! ?* m. H9 t, Z) ?6 `. ]3 Q2 E @staticmethod; x; J6 Q5 G4 p8 Z
def copy_style(source_cell, target_cell):: |. d1 i* e6 l, ]" y) J) m' q1 O/ [
if source_cell.has_style:3 K' u( d% n7 }3 s: ?
target_cell.font = copy(source_cell.font)2 F K W- N; \% T
target_cell.fill = copy(source_cell.fill)
1 ~8 H9 L- j1 ?8 ^& d5 ^ target_cell.border = copy(source_cell.border)$ [* K. j0 m; [) }3 j7 _5 C
target_cell.alignment = copy(source_cell.alignment)
- [) u' ?* Q: @; r5 d target_cell.number_format = source_cell.number_format! }( _+ [: I* [3 c3 Z
target_cell.protection = copy(source_cell.protection)/ z0 V3 ?" J" U8 l3 r* `
# 使用openpyxl库的cell.comment属性获取单元格的注释。5 o$ o: ?6 E1 B: e
if source_cell.comment:. q9 d/ m3 }3 I6 D; R% I5 C
target_cell.comment = source_cell.comment # 有批注的复制批注
$ x* s9 V1 L+ |& s& z+ o; k& R& {' U5 J( b2 ?
def apply_merge_cell(self, merged_ranges):
4 |0 _& U; x6 c3 H for min_row, min_col, max_row, max_col in merged_ranges:5 b; _+ n. `! L: m% u k3 u! N% n
self.sheet2.merge_cells(start_row=min_row, start_column=min_col,
t3 q& A2 m: L: A: w/ s9 l" [ end_row=max_row, end_column=max_col)
4 [' f( E# T( P* t. W! t3 B' ^2 o& Y( ~0 h( g* Z& L* n4 p" y
def copy_value_style(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,2 P7 T6 r' D* ?
target_start_col):. w" K, G) w- |% K2 Z2 ]" J- R& _
for row in range(source_start_row, source_end_row + 1):& P2 S! Y) M$ s/ @1 I7 ]- `& C
for col in range(source_start_col, source_end_col + 1):
: i1 b) V6 s) T. n+ H source_cell = self.sheet1.cell(row=row, column=col), ~1 Q1 A0 F; ?; H( \+ d0 g
target_cell = self.sheet2.cell(row=row + target_start_row - source_start_row,
2 _; i# p& k% N1 p' D column=col + target_start_col - source_start_col)
! q6 a, W: u# { C self.sheet2.cell(row=row + target_start_row - source_start_row,
1 @+ w3 H. q, p7 E' C1 f$ a column=col + target_start_col - source_start_col).value = source_cell.value
9 T G( y5 ?6 C1 r7 l. z- n2 s: t) w# e
self.copy_style(source_cell, target_cell)
2 K$ O) J' F7 A2 Q
! b' N! J. @; i def copy_stats(self, source_start_row, source_end_row, source_start_col, source_end_col, target_start_row,+ L2 O1 T P* j! R" K" {3 A
target_start_col):
5 O% s6 N* s. y& j2 J Q, D merged_ranges = []
) x* f2 d/ j/ l5 H& z3 m. B& x for merge_range in self.sheet1.merged_cells.ranges:% i& b ]- `% }6 b N2 ]% ]1 }4 c
min_col, min_row, max_col, max_row = merge_range.bounds
$ |; K i" |) Q$ D print(min_row, min_col, max_row, max_col)3 k- \( o5 D: f2 t5 V5 v; D5 D2 O6 n
print(source_start_row, source_start_col,source_end_row, source_end_col)9 M1 J% _! b' V$ {; j( R; M# |
if (min_row >= source_start_row and max_row = source_start_col and max_col |
|