def CopySheet(target_sheet, source_sheet):
#由于设置合并单元格后 使得某些cell变得只读，所以先处理单元格的内容，再处理合并单元格的内容
DebugHelper.Log("复制单元格")
progress = 0
total = source_sheet.max_row
for col_items in source_sheet.iter_rows():
progress += 1
PrintPercent(progress, total)
for cell in col_items:
#copy行列间距(行距设置有问题)
# if cell.column == 1:
# target_sheet.row_dimensions[cell.row].height = source_sheet.row_dimensions[cell.row].height
# if cell.row == 1:
# col_letter = get_column_letter(cell.column)
# target_sheet.column_dimensions[col_letter].width = source_sheet.column_dimensions[col_letter].width
#copy单元格数据
source_cell = source_sheet.cell(cell.row, cell.column)
target_cell = target_sheet.cell(cell.row, cell.column)
CopyCell(target_cell, source_cell)
#copy合并单元格
ranges = source_sheet.merged_cells.ranges
if len(ranges) > 0:
DebugHelper.Log("复制合并单元格")
progress = 0
total = len(ranges)
for mergecell in ranges:
progress += 1
PrintPercent(progress, total)
target_sheet.merge_cells(mergecell.coord)
def _collectKeysDiffType(self, target_sheet_reader, base_sheet_reader):
result = {}
#方便合并时取cells和合并单元格得信息
result["target_sheet_reader"] = target_sheet_reader
result["base_sheet_reader"] = base_sheet_reader
target_body_list = target_sheet_reader.body_value_list
base_body_list = base_sheet_reader.body_value_list
#讲list转换为集合
target_set = set(target_body_list)
base_set = set(base_body_list)
#target对baset的差集 当作新增
result["add_key"] = target_set.difference(base_set)
#base对target的差集 当作删除
result["del_key"] = base_set.difference(target_set)
#base对target的交集有差异部分当作修改
interse_set = target_set.intersection(base_set)
result["mod_key"] = set()
for key in interse_set:
diff_state, tar_vlu_dic, bs_vlu_dic = self.HasBoundsDiff(
key, target_sheet_reader, base_sheet_reader)
if diff_state:
result["mod_key"].add(key)
if "mod_cache_dic" not in result.keys():
result["mod_cache_dic"] = dict()
if key not in result["mod_cache_dic"].keys():
result["mod_cache_dic"][key] = list()
result["mod_cache_dic"][key].append(tar_vlu_dic)
result["mod_cache_dic"][key].append(bs_vlu_dic)
result['has_diff'] = False
if len(result["add_key"]) > 0:
result['has_diff'] = True
cache = result["add_key"]
for item_key in cache:
DebugHelper.LogColor(
DebugHelper.FontColor.green,
"【新增】{} {} {}".format(target_sheet_reader.excel_title,
target_sheet_reader.sheet.title,
item_key))
if len(result["del_key"]) > 0:
result['has_diff'] = True
cache = result["del_key"]
for item_key in cache:
DebugHelper.LogColor(
DebugHelper.FontColor.yellow,
"【删除】{} {} {}".format(target_sheet_reader.excel_title,
target_sheet_reader.sheet.title,
item_key))
if len(result["mod_key"]) > 0:
result['has_diff'] = True
cache = result["mod_key"]
for item_key in cache:
DebugHelper.LogColor(
DebugHelper.FontColor.blue,
"【修改】{} {} {}".format(target_sheet_reader.excel_title,
target_sheet_reader.sheet.title,
item_key))
return result
def StartWork(self):
# mine=> base的比较
DebugHelper.Log("【收集差异】Mine 与 Base")
self.CheckAndUpdateExcelDiff(self.mine_reader,self.base_reader)
# their=> base的比较
DebugHelper.Log("【收集差异】Their 与 Base")
self.CheckAndUpdateExcelDiff(self.their_reader,self.base_reader)
state = self.IsSupportCurrentMerge()
if state:
self.ApplyDiff2MergeExcel()
self.CheckAndApplySheetOp()
else:
DebugHelper.Log("【合并失败】不支持的类型")
return state
def ApplyDiff2MergeSheet(self, mine_excel_reader, their_excel_reader,
sheet_names):
DebugHelper.Log("【开始合并】 ", self.excel_title)
for name in sheet_names:
sheet_reader = self.sheet_reader_dic[name]
if sheet_reader == None:
continue
self.has_diff = True
DebugHelper.Log("【执行合并】 ", name)
#将两个字典有差异的内容合并
mine_key_diffs = mine_excel_reader.sheet_reader_dic[name].key_diffs
their_key_diffs = their_excel_reader.sheet_reader_dic[
name].key_diffs
if mine_key_diffs and their_key_diffs:
sheet_reader.ApplyDiff2BodyBounds(mine_key_diffs,
their_key_diffs)
DebugHelper.Log("【完成合并】")
def CheckPath():
global path_modify
for path in path_modify.values():
if not os.path.exists(path):
DebugHelper.LogColor(DebugHelper.FontColor.red,
"对象文件不存在，请检查是否已经处理完毕 ", path)
return False
return True
def main(path_modify):
merger = ExcelMergeMain(path_modify["merge"], path_modify["their"],
path_modify["mine"], path_modify["base"])
result = merger.StartWork()
merger.OnRelease()
if result:
#如果检查通过 并合并 那就把base和their删除掉
DebugHelper.Log("【合并成功】")
def IsSupportCurrentMerge(self):
mine_name_set = set(self.mine_reader.sheet_names)
their_name_set = set(self.their_reader.sheet_names)
interse_set = mine_name_set.intersection(their_name_set)
#不支持表头有变更
for name in interse_set:
if name in self.mine_reader.sheet_reader_dic.keys() and name in self.base_reader.sheet_reader_dic.keys():
sheet_reader1 = self.mine_reader.sheet_reader_dic[name]
sheet_reader2 = self.base_reader.sheet_reader_dic[name]
if self.comparer.IsHasTitleDiff(sheet_reader1,sheet_reader2):
DebugHelper.LogColor(DebugHelper.FontColor.red,"【表头变更】终止合并 Mine和Base {}比较 存在表头差异(批注，合并单元格状态，占用行列，数据) ".format(name))
sheet_reader1.PrintTitle()
sheet_reader2.PrintTitle()
return False
if name in self.their_reader.sheet_reader_dic.keys() and name in self.base_reader.sheet_reader_dic.keys():
sheet_reader1 = self.their_reader.sheet_reader_dic[name]
sheet_reader2 = self.base_reader.sheet_reader_dic[name]
if self.comparer.IsHasTitleDiff(sheet_reader1,sheet_reader2):
DebugHelper.LogColor(DebugHelper.FontColor.red,"【表头变更】终止合并 Their和Base {}比较 存在表头差异（(批注，合并单元格状态，占用行列，数据）".format(name))
sheet_reader1.PrintTitle()
sheet_reader2.PrintTitle()
return False
if name in self.their_reader.sheet_reader_dic.keys() and name in self.mine_reader.sheet_reader_dic.keys():
sheet_reader1 = self.their_reader.sheet_reader_dic[name]
sheet_reader2 = self.mine_reader.sheet_reader_dic[name]
if self.comparer.IsHasTitleDiff(sheet_reader1,sheet_reader2):
DebugHelper.LogColor(DebugHelper.FontColor.red,"【表头变更】终止合并 Their和Mine {}比较 存在表头差异（(批注，合并单元格状态，占用行列，数据）".format(name))
sheet_reader1.PrintTitle()
sheet_reader2.PrintTitle()
return False
#合并操作需要只处理两个目标版本的差异数据，暂不判断同一修改区域的逻辑
#不支持 修改mine和thier修改了同一张sheet的同一key的bounds下的数据
interse_set = self._getSameSheetNames(self.mine_reader,self.their_reader)
has_same,sheet_name,diff_keys,mine_diff_dic,their_diff_dic = self.comparer.HasSameCboundDiff(self.mine_reader,self.their_reader,interse_set)
if has_same:
for same_key in diff_keys:
DebugHelper.LogColor(DebugHelper.FontColor.red,"【重复变更】Thier和Mine出现了同一区域的修改 {} {}".format(sheet_name,same_key))
self.PrintDiffDic(mine_diff_dic,their_diff_dic,same_key)
return False
return True
def __init__(self, path_xlsm, excel_title):
self.excel_title = excel_title
self.path_xlsm = path_xlsm
if path_xlsm is None:
raise Exception("Invalid ExcelReader path_xlsm!", path_xlsm)
DebugHelper.Log("【读取资源】 " + path_xlsm + " " + excel_title)
self.workBook = load_workbook(filename=path_xlsm,
read_only=False,
keep_vba=True,
data_only=True)
sheet_names = self.workBook.get_sheet_names()
sheet_reader_dic = dict()
for name in sheet_names:
sheet = self.workBook.get_sheet_by_name(name)
reader = SheetReader(sheet, excel_title)
sheet_reader_dic[name] = reader
self.sheet_reader_dic = sheet_reader_dic
self.sheet_names = set(sheet_names)
self.has_diff = False
DebugHelper.Log("【读取完成】 " + path_xlsm)
def _tryWriteDel(self, del_key_set, target_reader):
if len(del_key_set) > 0:
for key in del_key_set:
#先拆分所有的单元格
self_cbounds = self.body_bounds_dic[key]
DebugHelper.Log("【删除】", key, self_cbounds.tostring())
self._cleanMergeCellsByCbounds(self_cbounds,
has_key_merge_cell=True)
self.body_value_list.remove(key)
bounds_value = {"rect": [0]}
self._adapterCBounds(self.sheet, key, bounds_value)
def _tryWriteAdd(self, add_key_set, target_reader):
if len(add_key_set) > 0:
for key in add_key_set:
cbounds = target_reader.body_bounds_dic[key]
DebugHelper.Log("【新增】", cbounds.tostring())
seat_key = self._seachCommonKeyUp(key, target_reader)
new_Cb = self._insertCBounds(seat_key, cbounds)
bounds_value = self._getCellInfosByCbounds(
target_reader.sheet, cbounds)
self._drawMergeCellsByCbounds(bounds_value["merge_cell"],
new_Cb,
has_key_merge_cell=True)
self._writeSheetBoundsValue(key, bounds_value)
def GetNewOrDelSheet(self, target_sheet_names, base_sheet_names):
target_set = set(target_sheet_names)
base_set = set(base_sheet_names)
add_set = target_set.difference(base_set)
del_set = base_set.difference(target_set)
add_list = list()
del_list = list()
if len(add_set) > 0:
for item in add_set:
if not IsHasChinese(item):
add_list.append(item)
else:
DebugHelper.LogColor(DebugHelper.FontColor.pink,
"【新增备注sheet的操作不处理】{} ".format(item))
if len(del_set) > 0:
for item in del_list:
if not IsHasChinese(item):
del_list.append(item)
else:
DebugHelper.LogColor(DebugHelper.FontColor.pink,
"【删除备注sheet的操作不处理】{} ".format(item))
return len(add_list) > 0 or len(del_list) > 0, add_list, del_list
def main(path_modify):
mine_copy_helper = FileCopyHelper(path_modify["base"])
their_copy_helper = FileCopyHelper(path_modify["their"])
path_modify["base"] = mine_copy_helper.copypath
path_modify["their"] = their_copy_helper.copypath
merger = ExcelMergeMain(path_modify["merge"], path_modify["their"],
path_modify["mine"], path_modify["base"])
result = merger.StartWork()
merger.OnRelease()
if result:
#如果检查通过 并合并 那就把base和their删除掉
DebugHelper.Log("【合并成功】")
mine_copy_helper.DelSourceFile()
their_copy_helper.DelSourceFile()
mine_copy_helper.OnRelease()
their_copy_helper.OnRelease()
def _tryWriteMod(self, mod_key_set, target_reader):
if len(mod_key_set) > 0:
for key in mod_key_set:
#TODO 当修改的单位检查取得行由多行变更为一行时，可能会有问题
#从目标sheet取信息
cbounds = target_reader.body_bounds_dic[key]
bounds_value = self._getCellInfosByCbounds(
target_reader.sheet, cbounds)
#先拆分所有的单元格
self_cbounds = self.body_bounds_dic[key]
self._cleanMergeCellsByCbounds(self_cbounds)
self._adapterCBounds(self.sheet, key, bounds_value)
DebugHelper.Log("【修改】", self_cbounds.tostring())
self._drawMergeCellsByCbounds(bounds_value["merge_cell"],
self_cbounds)
self._writeSheetBoundsValue(key, bounds_value)
def PrintDiffDic(self,mine_diff_dic,their_diff_dic,same_key):
if same_key in mine_diff_dic.keys():
array1 = mine_diff_dic[same_key]
mine_diffs = array1[0]
base_diffs = array1[1]
for diff in mine_diffs.values():
DebugHelper.Log(" 【mine】",same_key,":",end = "")
self._printStrValue(diff)
DebugHelper.LogNoTime("")
for diff in base_diffs.values():
DebugHelper.Log(" 【Base】",same_key,":",end = "")
self._printStrValue(diff)
DebugHelper.LogNoTime("")
if same_key in their_diff_dic.keys():
array1 = their_diff_dic[same_key]
their_diffs = array1[0]
for diff in their_diffs.values():
DebugHelper.Log("【Their】",same_key,":",end = "")
self._printStrValue(diff)
DebugHelper.LogNoTime("")
def CheckAndApplySheetOp(self):
merge_reader = self.merge_reader
their_reader = self.their_reader
#获取并处理新增或删除的sheet
has_diff,add_list,del_list = self.comparer.GetNewOrDelSheet(their_reader.sheet_names,self.merge_reader.sheet_names)
if has_diff:
if len(add_list) > 0:
DebugHelper.Log("【开始新增sheet】")
for sheet_name in add_list:
merge_reader.has_diff = True
if sheet_name not in merge_reader.sheet_names:
DebugHelper.Log("【新增sheet】Sheet:{}".format(sheet_name))
target_sheet = merge_reader.workBook.create_sheet(sheet_name)
source_sheet = their_reader.sheet_reader_dic[sheet_name]
CopySheet(target_sheet,source_sheet.sheet)
DebugHelper.Log("【新增sheet】Sheet:{} Done".format(sheet_name))
else:
DebugHelper.LogColor(DebugHelper.FontColor.red,"【新增sheet失败】Their和Mine都新增了{} Sheet ".format(sheet_name))
if len(del_list) > 0:
DebugHelper.Log("【开始删除sheet】")
for sheet_name in del_list:
merge_reader.has_diff = True
if sheet_name in merge_reader.sheet_names:
DebugHelper.LogColor(DebugHelper.FontColor.yellow,"【不支持删除sheet】Sheet: {}".format(sheet_name))
def pause():
DebugHelper.Log("输入任意键继续")
message = input()
their_copy_helper.OnRelease()
def CheckPath():
global path_modify
for path in path_modify.values():
if not os.path.exists(path):
DebugHelper.LogColor(DebugHelper.FontColor.red,
"对象文件不存在，请检查是否已经处理完毕 ", path)
return False
return True
#只允许存在一个窗口
if LogFileHelper.IsExist(path_modify["mine"]):
DebugHelper.LogColor(DebugHelper.FontColor.red, "已经有一个程序窗口在运行！！！")
pause()
sys.exit()
LogFileHelper.InitLogFile(path_modify["mine"])
if __name__ == '__main__':
try:
if CheckPath():
main(path_modify)
except Exception as e:
print(e.args)
exc_type, exc_value, exc_obj = sys.exc_info()
traceback.print_tb(exc_obj)
LogFileHelper.ReleaseLogFile()
pause()
LogFileHelper.DelLogFile()
def _printStrValue(self,str_vlu):
str_array = str_vlu.split("___")
for vlu in str_array:
DebugHelper.LogNoTime(vlu,"\t",end = "")