尧图网页设计 , ‘U‘ + str(index)).api.merge()，xlwings合并Excel上下相邻单元格，代码中断执行，也不报错。)
一、背景采用xlwings包根据template.xlsx模板导出excel成果内容包括多个sheet内容的填充可实现上下相邻单元格的合并左右单元格合并也是一个道理代码运行卡着也不报错。代码需合并U4和U5上下两个单元格二、解决方法修改api.merge()为api.Merge()虽然鼠标在Merge()方法上面无法跳转但仍然可用sheet.range(U str(index - 1), U str(index)).api.Merge()完整测试类代码如下# -*- coding: utf-8 -*- author: tjm software: PyCharm file: ExcelUtils.py time: 2022/4/14 9:54 import os import xlwings as xw import pandas as pd import time # Excel工具类 class ExcelUtils: def __init__(self, template_pathNone): self.template_path template_path self.app xw.App(visibleFalse, add_bookFalse) self.wb self.app.books.open(self.template_path) # 根据模板导出excel文件 def write2excel(self, excel_name, outcome_dataNone, defect_dataNone, maintain_dataNone, kind_dataNone): # 1.写入成果表sheet self.write_outcome_table(outcome_data) # 2.写入管段缺陷表sheet self.write_defect_table(defect_data) # 3.写入维护管段表sheet self.write_maintain_table(maintain_data) # 4.写入缺陷种类表sheet self.write_kind_table(kind_data) self.wb.save(Excels\\ excel_name) self.wb.close() # 成果表sheet填充数据 def write_outcome_table(self, dataNone): if data is None: return sheet self.wb.sheets[成果表] # 合并单元格时用 cur_a, cur_b, cur_c None, None, None for index, row in data.iterrows(): index 4 print(index:, index) sheet.range(A str(index)).value row[0] sheet.range(B str(index)).value row[1] sheet.range(C str(index)).value row[2] if index 4: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(D str(index)).value row[3] sheet.range(E str(index)).value row[4] sheet.range(F str(index)).value row[5] sheet.range(G str(index)).value row[6] sheet.range(H str(index)).value row[7] sheet.range(I str(index)).value row[8] sheet.range(J str(index)).value row[9] sheet.range(K str(index)).value row[10] sheet.range(L str(index)).value row[11] sheet.range(M str(index)).value row[12] sheet.range(N str(index)).value row[13] sheet.range(O str(index)).value row[14] # 插入图片 cell_img sheet.range(P str(index)) file_name os.path.join(os.getcwd(), row[15]) width, height 140, 80 # 指定图片大小 left cell_img.left (cell_img.width - width) / 2 # 居中 top cell_img.top (cell_img.height - height) / 2 sheet.pictures.add(file_name, leftleft, toptop, widthwidth, heightheight) sheet.range(Q str(index)).value row[16] sheet.range(R str(index)).value row[17] sheet.range(S str(index)).value row[18] sheet.range(T str(index)).value row[19] if index 4 and cur_a row[0] and cur_b row[1] and cur_c row[2]: sheet.range(U str(index - 1), U str(index)).api.Merge() else: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(U str(index)).value row[20] sheet.range(V str(index)).value row[21] sheet.range(W str(index)).value row[22] sheet.range(X str(index)).value row[23] sheet.range(Y str(index)).value row[24] if row[24] Ⅳ: sheet.range(Y str(index)).color (225, 0, 0) elif row[24] Ⅲ: sheet.range(Y str(index)).color (225, 165, 0) elif row[24] Ⅱ: sheet.range(Y str(index)).color (225, 255, 0) sheet.range(Z str(index)).value row[25] sheet.range(AA str(index)).value row[26] sheet.range(AB str(index)).value row[27] sheet.range(AC str(index)).value row[28] sheet.range(AD str(index)).value row[29] sheet.range(AE str(index)).value row[30] if row[30] Ⅳ: sheet.range(AE str(index)).color (225, 0, 0) elif row[30] Ⅲ: sheet.range(AE str(index)).color (225, 165, 0) elif row[30] Ⅱ: sheet.range(AE str(index)).color (225, 255, 0) sheet.range(AF str(index)).value row[31] # 管段缺陷表sheet填充数据 def write_defect_table(self, data): if data is None: return sheet self.wb.sheets[管段缺陷表] for index, row in data.iterrows(): index 4 sheet.range(D str(index)).value row[0] sheet.range(E str(index)).value row[1] # 维护管段表sheet填充数据 def write_maintain_table(self, data): if data is None: return sheet self.wb.sheets[维护管段表] for index, row in data.iterrows(): index 4 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] # 缺陷种类表sheet填充数据 def write_kind_table(self, data): if data is None: return sheet self.wb.sheets[缺陷种类表] for index, row in data.iterrows(): index 3 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] sheet.range(G str(index)).value row[4] # 测试入口方法 if __name__ __main__: excel ExcelUtils(template_pathExcels\\cctv_template.xlsx) tep_time time.strftime(%Y%m%d%H%M%S) print(tep_time) exl_name 陈村大道排水管道内窥检测与评估成果表_ tep_time .xlsx outcome_dat pd.DataFrame(data[[陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.50m-2.50m,1212h, 结构性缺陷, 破裂, PL, 4, rExcels\imgs\froge.jpg, 管道材料破碎处边缘环向覆盖范围弧长60度。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ], [陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.00m-2.00m,1212h, 结构性缺陷, 错口, CK, 2, rExcels\imgs\grass.jpg, 相接的两个管口偏差为管壁厚度的1/2-1之间。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ] ], columns[工程名称, 起始井号, 终止井号, 管段类型, 管段材质, 管段直径, 管段长度, 检测长度, 起点埋深, 终点埋深, 缺陷位置, 缺陷类型, 缺陷名称, 缺陷代码, 缺陷等级, 检测图片, 检测描述, 检测方法, 检测方向, 检测日期, 平均值S, JG最大值Smax, JG缺陷密度, 修复指数, 修复等级, 修复建议, 平均值Y, GN最大值Smax, GN缺陷密度, 养护指数, 养护等级, 养护建议]) defect_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096, 2], [ N0630YS1096~N0630YS1095, 1]], columns[缺陷管段, 管段数量]) maintain_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1, 0.2, 结构基本完好不修复], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096、N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 4, 0.8, 结构在短期内不会发生破坏但应做修复计划], [ N0630YS1096~N0630YS1095, 1, 0.2, 结构在短期内可能会发生破坏应尽快修复], [N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 2, 0.4, 结构已经发生或即将发生破坏应立即修复]], columns[维护管段, 管段数量段, 管段占比%, 维护建议]) kind_dat pd.DataFrame(data[[1, 3, 1, 2, 7], [-, -, -, -, -], [-, -, -, -, -], [-, -, -, -, -]], columns[1级, 2级, 3级, 4级, 合计]) excel.write2excel(excel_nameexl_name, outcome_dataoutcome_dat, defect_datadefect_dat, maintain_datamaintain_dat, kind_datakind_dat)代码可正常运行合并单元格成功。虚拟环境如下(py36_excel_env) C:\Users\DELLpip list Package Version ----------------- ------------------- absl-py 0.13.0 astunparse 1.6.3 certifi 2020.6.20 flatbuffers 1.12 google-pasta 0.2.0 keras-nightly 2.5.0.dev2021032900 numpy 1.19.5 object-detection 0.1 pandas 1.1.5 pip 21.2.2 python-dateutil 2.8.2 pytz 2021.3 pywin32 302 setuptools 58.0.4 six 1.16.0 typing-extensions 3.7.4.3 wheel 0.37.0 wincertstore 0.2 xlwings 0.25.2三、备注合并多个上下单元格代码如下# -*- coding: utf-8 -*- author: tjm software: PyCharm file: ExcelUtils.py time: 2022/4/14 9:54 import os import xlwings as xw import pandas as pd import time # Excel工具类 class ExcelUtils: def __init__(self, template_pathNone): self.template_path template_path self.app xw.App(visibleFalse, add_bookFalse) self.wb self.app.books.open(self.template_path) # 根据模板导出excel文件 def write2excel(self, excel_name, outcome_dataNone, defect_dataNone, maintain_dataNone, kind_dataNone): # 1.写入成果表sheet self.write_outcome_table(outcome_data) # 2.写入管段缺陷表sheet self.write_defect_table(defect_data) # 3.写入维护管段表sheet self.write_maintain_table(maintain_data) # 4.写入缺陷种类表sheet self.write_kind_table(kind_data) self.wb.save(Excels\\ excel_name) self.wb.close() # 成果表sheet填充数据 def write_outcome_table(self, dataNone): if data is None: return sheet self.wb.sheets[成果表] # 合并单元格时用 cur_a, cur_b, cur_c None, None, None for index, row in data.iterrows(): index 4 print(index:, index) sheet.range(A str(index)).value row[0] sheet.range(B str(index)).value row[1] sheet.range(C str(index)).value row[2] if index 4: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(D str(index)).value row[3] sheet.range(E str(index)).value row[4] sheet.range(F str(index)).value row[5] sheet.range(G str(index)).value row[6] sheet.range(H str(index)).value row[7] sheet.range(I str(index)).value row[8] sheet.range(J str(index)).value row[9] sheet.range(K str(index)).value row[10] sheet.range(L str(index)).value row[11] sheet.range(M str(index)).value row[12] sheet.range(N str(index)).value row[13] sheet.range(O str(index)).value row[14] # 插入图片 cell_img sheet.range(P str(index)) file_name os.path.join(os.getcwd(), row[15]) width, height 140, 80 # 指定图片大小 left cell_img.left (cell_img.width - width) / 2 # 居中 top cell_img.top (cell_img.height - height) / 2 sheet.pictures.add(file_name, leftleft, toptop, widthwidth, heightheight) sheet.range(Q str(index)).value row[16] sheet.range(R str(index)).value row[17] sheet.range(S str(index)).value row[18] sheet.range(T str(index)).value row[19] # 合并上下单元格结构性和功能性缺陷指标 if index 4 and cur_a row[0] and cur_b row[1] and cur_c row[2]: sheet.range(U str(index - 1), U str(index)).api.Merge() sheet.range(V str(index - 1), V str(index)).api.Merge() sheet.range(W str(index - 1), W str(index)).api.Merge() sheet.range(X str(index - 1), X str(index)).api.Merge() sheet.range(Y str(index - 1), Y str(index)).api.Merge() sheet.range(Z str(index - 1), Z str(index)).api.Merge() sheet.range(AA str(index - 1), AA str(index)).api.Merge() sheet.range(AB str(index - 1), AB str(index)).api.Merge() sheet.range(AC str(index - 1), AC str(index)).api.Merge() sheet.range(AD str(index - 1), AD str(index)).api.Merge() sheet.range(AE str(index - 1), AE str(index)).api.Merge() sheet.range(AF str(index - 1), AF str(index)).api.Merge() else: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(U str(index)).value row[20] sheet.range(V str(index)).value row[21] sheet.range(W str(index)).value row[22] sheet.range(X str(index)).value row[23] sheet.range(Y str(index)).value row[24] sheet.range(Z str(index)).value row[25] sheet.range(AA str(index)).value row[26] sheet.range(AB str(index)).value row[27] sheet.range(AC str(index)).value row[28] sheet.range(AD str(index)).value row[29] sheet.range(AE str(index)).value row[30] sheet.range(AF str(index)).value row[31] # 根据修复等级设置背景色 if row[24] Ⅳ: sheet.range(Y str(index)).color (225, 0, 0) elif row[24] Ⅲ: sheet.range(Y str(index)).color (225, 165, 0) elif row[24] Ⅱ: sheet.range(Y str(index)).color (225, 255, 0) if row[30] Ⅳ: sheet.range(AE str(index)).color (225, 0, 0) elif row[30] Ⅲ: sheet.range(AE str(index)).color (225, 165, 0) elif row[30] Ⅱ: sheet.range(AE str(index)).color (225, 255, 0) # 管段缺陷表sheet填充数据 def write_defect_table(self, data): if data is None: return sheet self.wb.sheets[管段缺陷表] for index, row in data.iterrows(): index 4 sheet.range(D str(index)).value row[0] sheet.range(E str(index)).value row[1] # 维护管段表sheet填充数据 def write_maintain_table(self, data): if data is None: return sheet self.wb.sheets[维护管段表] for index, row in data.iterrows(): index 4 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] # 缺陷种类表sheet填充数据 def write_kind_table(self, data): if data is None: return sheet self.wb.sheets[缺陷种类表] for index, row in data.iterrows(): index 3 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] sheet.range(G str(index)).value row[4] # 测试入口方法 if __name__ __main__: excel ExcelUtils(template_pathExcels\\cctv_template.xlsx) tep_time time.strftime(%Y%m%d%H%M%S) print(tep_time) exl_name 陈村大道排水管道内窥检测与评估成果表_ tep_time .xlsx outcome_dat pd.DataFrame(data[[陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.50m-2.50m,1212h, 结构性缺陷, 破裂, PL, 4, rExcels\imgs\froge.jpg, 管道材料破碎处边缘环向覆盖范围弧长60度。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ], [陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.00m-2.00m,1212h, 结构性缺陷, 错口, CK, 2, rExcels\imgs\grass.jpg, 相接的两个管口偏差为管壁厚度的1/2-1之间。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ] ], columns[工程名称, 起始井号, 终止井号, 管段类型, 管段材质, 管段直径, 管段长度, 检测长度, 起点埋深, 终点埋深, 缺陷位置, 缺陷类型, 缺陷名称, 缺陷代码, 缺陷等级, 检测图片, 检测描述, 检测方法, 检测方向, 检测日期, 平均值S, JG最大值Smax, JG缺陷密度, 修复指数, 修复等级, 修复建议, 平均值Y, GN最大值Smax, GN缺陷密度, 养护指数, 养护等级, 养护建议]) defect_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096, 2], [ N0630YS1096~N0630YS1095, 1]], columns[缺陷管段, 管段数量]) maintain_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1, 0.2, 结构基本完好不修复], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096、N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 4, 0.8, 结构在短期内不会发生破坏但应做修复计划], [ N0630YS1096~N0630YS1095, 1, 0.2, 结构在短期内可能会发生破坏应尽快修复], [N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 2, 0.4, 结构已经发生或即将发生破坏应立即修复]], columns[维护管段, 管段数量段, 管段占比%, 维护建议]) kind_dat pd.DataFrame(data[[1, 3, 1, 2, 7], [-, -, -, -, -], [-, -, -, -, -], [-, -, -, -, -]], columns[1级, 2级, 3级, 4级, 合计]) excel.write2excel(excel_nameexl_name, outcome_dataoutcome_dat, defect_datadefect_dat, maintain_datamaintain_dat, kind_datakind_dat) sheet.range(‘U‘ + str(index - 1), ‘U‘ + str(index)).api.merge()，xlwings合并Excel上下相邻单元格，代码中断执行，也不报错。
一、背景采用xlwings包根据template.xlsx模板导出excel成果内容包括多个sheet内容的填充可实现上下相邻单元格的合并左右单元格合并也是一个道理代码运行卡着也不报错。代码需合并U4和U5上下两个单元格二、解决方法修改api.merge()为api.Merge()虽然鼠标在Merge()方法上面无法跳转但仍然可用sheet.range(U str(index - 1), U str(index)).api.Merge()完整测试类代码如下# -*- coding: utf-8 -*- author: tjm software: PyCharm file: ExcelUtils.py time: 2022/4/14 9:54 import os import xlwings as xw import pandas as pd import time # Excel工具类 class ExcelUtils: def __init__(self, template_pathNone): self.template_path template_path self.app xw.App(visibleFalse, add_bookFalse) self.wb self.app.books.open(self.template_path) # 根据模板导出excel文件 def write2excel(self, excel_name, outcome_dataNone, defect_dataNone, maintain_dataNone, kind_dataNone): # 1.写入成果表sheet self.write_outcome_table(outcome_data) # 2.写入管段缺陷表sheet self.write_defect_table(defect_data) # 3.写入维护管段表sheet self.write_maintain_table(maintain_data) # 4.写入缺陷种类表sheet self.write_kind_table(kind_data) self.wb.save(Excels\\ excel_name) self.wb.close() # 成果表sheet填充数据 def write_outcome_table(self, dataNone): if data is None: return sheet self.wb.sheets[成果表] # 合并单元格时用 cur_a, cur_b, cur_c None, None, None for index, row in data.iterrows(): index 4 print(index:, index) sheet.range(A str(index)).value row[0] sheet.range(B str(index)).value row[1] sheet.range(C str(index)).value row[2] if index 4: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(D str(index)).value row[3] sheet.range(E str(index)).value row[4] sheet.range(F str(index)).value row[5] sheet.range(G str(index)).value row[6] sheet.range(H str(index)).value row[7] sheet.range(I str(index)).value row[8] sheet.range(J str(index)).value row[9] sheet.range(K str(index)).value row[10] sheet.range(L str(index)).value row[11] sheet.range(M str(index)).value row[12] sheet.range(N str(index)).value row[13] sheet.range(O str(index)).value row[14] # 插入图片 cell_img sheet.range(P str(index)) file_name os.path.join(os.getcwd(), row[15]) width, height 140, 80 # 指定图片大小 left cell_img.left (cell_img.width - width) / 2 # 居中 top cell_img.top (cell_img.height - height) / 2 sheet.pictures.add(file_name, leftleft, toptop, widthwidth, heightheight) sheet.range(Q str(index)).value row[16] sheet.range(R str(index)).value row[17] sheet.range(S str(index)).value row[18] sheet.range(T str(index)).value row[19] if index 4 and cur_a row[0] and cur_b row[1] and cur_c row[2]: sheet.range(U str(index - 1), U str(index)).api.Merge() else: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(U str(index)).value row[20] sheet.range(V str(index)).value row[21] sheet.range(W str(index)).value row[22] sheet.range(X str(index)).value row[23] sheet.range(Y str(index)).value row[24] if row[24] Ⅳ: sheet.range(Y str(index)).color (225, 0, 0) elif row[24] Ⅲ: sheet.range(Y str(index)).color (225, 165, 0) elif row[24] Ⅱ: sheet.range(Y str(index)).color (225, 255, 0) sheet.range(Z str(index)).value row[25] sheet.range(AA str(index)).value row[26] sheet.range(AB str(index)).value row[27] sheet.range(AC str(index)).value row[28] sheet.range(AD str(index)).value row[29] sheet.range(AE str(index)).value row[30] if row[30] Ⅳ: sheet.range(AE str(index)).color (225, 0, 0) elif row[30] Ⅲ: sheet.range(AE str(index)).color (225, 165, 0) elif row[30] Ⅱ: sheet.range(AE str(index)).color (225, 255, 0) sheet.range(AF str(index)).value row[31] # 管段缺陷表sheet填充数据 def write_defect_table(self, data): if data is None: return sheet self.wb.sheets[管段缺陷表] for index, row in data.iterrows(): index 4 sheet.range(D str(index)).value row[0] sheet.range(E str(index)).value row[1] # 维护管段表sheet填充数据 def write_maintain_table(self, data): if data is None: return sheet self.wb.sheets[维护管段表] for index, row in data.iterrows(): index 4 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] # 缺陷种类表sheet填充数据 def write_kind_table(self, data): if data is None: return sheet self.wb.sheets[缺陷种类表] for index, row in data.iterrows(): index 3 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] sheet.range(G str(index)).value row[4] # 测试入口方法 if __name__ __main__: excel ExcelUtils(template_pathExcels\\cctv_template.xlsx) tep_time time.strftime(%Y%m%d%H%M%S) print(tep_time) exl_name 陈村大道排水管道内窥检测与评估成果表_ tep_time .xlsx outcome_dat pd.DataFrame(data[[陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.50m-2.50m,1212h, 结构性缺陷, 破裂, PL, 4, rExcels\imgs\froge.jpg, 管道材料破碎处边缘环向覆盖范围弧长60度。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ], [陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.00m-2.00m,1212h, 结构性缺陷, 错口, CK, 2, rExcels\imgs\grass.jpg, 相接的两个管口偏差为管壁厚度的1/2-1之间。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ] ], columns[工程名称, 起始井号, 终止井号, 管段类型, 管段材质, 管段直径, 管段长度, 检测长度, 起点埋深, 终点埋深, 缺陷位置, 缺陷类型, 缺陷名称, 缺陷代码, 缺陷等级, 检测图片, 检测描述, 检测方法, 检测方向, 检测日期, 平均值S, JG最大值Smax, JG缺陷密度, 修复指数, 修复等级, 修复建议, 平均值Y, GN最大值Smax, GN缺陷密度, 养护指数, 养护等级, 养护建议]) defect_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096, 2], [ N0630YS1096~N0630YS1095, 1]], columns[缺陷管段, 管段数量]) maintain_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1, 0.2, 结构基本完好不修复], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096、N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 4, 0.8, 结构在短期内不会发生破坏但应做修复计划], [ N0630YS1096~N0630YS1095, 1, 0.2, 结构在短期内可能会发生破坏应尽快修复], [N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 2, 0.4, 结构已经发生或即将发生破坏应立即修复]], columns[维护管段, 管段数量段, 管段占比%, 维护建议]) kind_dat pd.DataFrame(data[[1, 3, 1, 2, 7], [-, -, -, -, -], [-, -, -, -, -], [-, -, -, -, -]], columns[1级, 2级, 3级, 4级, 合计]) excel.write2excel(excel_nameexl_name, outcome_dataoutcome_dat, defect_datadefect_dat, maintain_datamaintain_dat, kind_datakind_dat)代码可正常运行合并单元格成功。虚拟环境如下(py36_excel_env) C:\Users\DELLpip list Package Version ----------------- ------------------- absl-py 0.13.0 astunparse 1.6.3 certifi 2020.6.20 flatbuffers 1.12 google-pasta 0.2.0 keras-nightly 2.5.0.dev2021032900 numpy 1.19.5 object-detection 0.1 pandas 1.1.5 pip 21.2.2 python-dateutil 2.8.2 pytz 2021.3 pywin32 302 setuptools 58.0.4 six 1.16.0 typing-extensions 3.7.4.3 wheel 0.37.0 wincertstore 0.2 xlwings 0.25.2三、备注合并多个上下单元格代码如下# -*- coding: utf-8 -*- author: tjm software: PyCharm file: ExcelUtils.py time: 2022/4/14 9:54 import os import xlwings as xw import pandas as pd import time # Excel工具类 class ExcelUtils: def __init__(self, template_pathNone): self.template_path template_path self.app xw.App(visibleFalse, add_bookFalse) self.wb self.app.books.open(self.template_path) # 根据模板导出excel文件 def write2excel(self, excel_name, outcome_dataNone, defect_dataNone, maintain_dataNone, kind_dataNone): # 1.写入成果表sheet self.write_outcome_table(outcome_data) # 2.写入管段缺陷表sheet self.write_defect_table(defect_data) # 3.写入维护管段表sheet self.write_maintain_table(maintain_data) # 4.写入缺陷种类表sheet self.write_kind_table(kind_data) self.wb.save(Excels\\ excel_name) self.wb.close() # 成果表sheet填充数据 def write_outcome_table(self, dataNone): if data is None: return sheet self.wb.sheets[成果表] # 合并单元格时用 cur_a, cur_b, cur_c None, None, None for index, row in data.iterrows(): index 4 print(index:, index) sheet.range(A str(index)).value row[0] sheet.range(B str(index)).value row[1] sheet.range(C str(index)).value row[2] if index 4: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(D str(index)).value row[3] sheet.range(E str(index)).value row[4] sheet.range(F str(index)).value row[5] sheet.range(G str(index)).value row[6] sheet.range(H str(index)).value row[7] sheet.range(I str(index)).value row[8] sheet.range(J str(index)).value row[9] sheet.range(K str(index)).value row[10] sheet.range(L str(index)).value row[11] sheet.range(M str(index)).value row[12] sheet.range(N str(index)).value row[13] sheet.range(O str(index)).value row[14] # 插入图片 cell_img sheet.range(P str(index)) file_name os.path.join(os.getcwd(), row[15]) width, height 140, 80 # 指定图片大小 left cell_img.left (cell_img.width - width) / 2 # 居中 top cell_img.top (cell_img.height - height) / 2 sheet.pictures.add(file_name, leftleft, toptop, widthwidth, heightheight) sheet.range(Q str(index)).value row[16] sheet.range(R str(index)).value row[17] sheet.range(S str(index)).value row[18] sheet.range(T str(index)).value row[19] # 合并上下单元格结构性和功能性缺陷指标 if index 4 and cur_a row[0] and cur_b row[1] and cur_c row[2]: sheet.range(U str(index - 1), U str(index)).api.Merge() sheet.range(V str(index - 1), V str(index)).api.Merge() sheet.range(W str(index - 1), W str(index)).api.Merge() sheet.range(X str(index - 1), X str(index)).api.Merge() sheet.range(Y str(index - 1), Y str(index)).api.Merge() sheet.range(Z str(index - 1), Z str(index)).api.Merge() sheet.range(AA str(index - 1), AA str(index)).api.Merge() sheet.range(AB str(index - 1), AB str(index)).api.Merge() sheet.range(AC str(index - 1), AC str(index)).api.Merge() sheet.range(AD str(index - 1), AD str(index)).api.Merge() sheet.range(AE str(index - 1), AE str(index)).api.Merge() sheet.range(AF str(index - 1), AF str(index)).api.Merge() else: cur_a, cur_b, cur_c row[0], row[1], row[2] sheet.range(U str(index)).value row[20] sheet.range(V str(index)).value row[21] sheet.range(W str(index)).value row[22] sheet.range(X str(index)).value row[23] sheet.range(Y str(index)).value row[24] sheet.range(Z str(index)).value row[25] sheet.range(AA str(index)).value row[26] sheet.range(AB str(index)).value row[27] sheet.range(AC str(index)).value row[28] sheet.range(AD str(index)).value row[29] sheet.range(AE str(index)).value row[30] sheet.range(AF str(index)).value row[31] # 根据修复等级设置背景色 if row[24] Ⅳ: sheet.range(Y str(index)).color (225, 0, 0) elif row[24] Ⅲ: sheet.range(Y str(index)).color (225, 165, 0) elif row[24] Ⅱ: sheet.range(Y str(index)).color (225, 255, 0) if row[30] Ⅳ: sheet.range(AE str(index)).color (225, 0, 0) elif row[30] Ⅲ: sheet.range(AE str(index)).color (225, 165, 0) elif row[30] Ⅱ: sheet.range(AE str(index)).color (225, 255, 0) # 管段缺陷表sheet填充数据 def write_defect_table(self, data): if data is None: return sheet self.wb.sheets[管段缺陷表] for index, row in data.iterrows(): index 4 sheet.range(D str(index)).value row[0] sheet.range(E str(index)).value row[1] # 维护管段表sheet填充数据 def write_maintain_table(self, data): if data is None: return sheet self.wb.sheets[维护管段表] for index, row in data.iterrows(): index 4 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] # 缺陷种类表sheet填充数据 def write_kind_table(self, data): if data is None: return sheet self.wb.sheets[缺陷种类表] for index, row in data.iterrows(): index 3 sheet.range(C str(index)).value row[0] sheet.range(D str(index)).value row[1] sheet.range(E str(index)).value row[2] sheet.range(F str(index)).value row[3] sheet.range(G str(index)).value row[4] # 测试入口方法 if __name__ __main__: excel ExcelUtils(template_pathExcels\\cctv_template.xlsx) tep_time time.strftime(%Y%m%d%H%M%S) print(tep_time) exl_name 陈村大道排水管道内窥检测与评估成果表_ tep_time .xlsx outcome_dat pd.DataFrame(data[[陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.50m-2.50m,1212h, 结构性缺陷, 破裂, PL, 4, rExcels\imgs\froge.jpg, 管道材料破碎处边缘环向覆盖范围弧长60度。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ], [陈村大道, N0630YS1096, N0630WS1097, WS, 混凝土管, 300, 8.7, 8.7, 1.6, 1.54, 2.00m-2.00m,1212h, 结构性缺陷, 错口, CK, 2, rExcels\imgs\grass.jpg, 相接的两个管口偏差为管壁厚度的1/2-1之间。, QV, 逆流, 2022/03/12, 11, 10, 0.23, 8.3, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪;输水功能受到严重影响应立即进行处理, 4, 10, 0.06, 9.05, Ⅳ, 管道过流受阻很严重即将或已经导致运行瘫痪。 ] ], columns[工程名称, 起始井号, 终止井号, 管段类型, 管段材质, 管段直径, 管段长度, 检测长度, 起点埋深, 终点埋深, 缺陷位置, 缺陷类型, 缺陷名称, 缺陷代码, 缺陷等级, 检测图片, 检测描述, 检测方法, 检测方向, 检测日期, 平均值S, JG最大值Smax, JG缺陷密度, 修复指数, 修复等级, 修复建议, 平均值Y, GN最大值Smax, GN缺陷密度, 养护指数, 养护等级, 养护建议]) defect_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096, 2], [ N0630YS1096~N0630YS1095, 1]], columns[缺陷管段, 管段数量]) maintain_dat pd.DataFrame(data[[N0630YS1099~N0630YS1096, 1, 0.2, 结构基本完好不修复], [ N0630YS1096~N0630YS1095、N0630YS1099~N0630YS1096、N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 4, 0.8, 结构在短期内不会发生破坏但应做修复计划], [ N0630YS1096~N0630YS1095, 1, 0.2, 结构在短期内可能会发生破坏应尽快修复], [N0630WS1097~N0630YS1096、N0630YS1095~N0630YS1094, 2, 0.4, 结构已经发生或即将发生破坏应立即修复]], columns[维护管段, 管段数量段, 管段占比%, 维护建议]) kind_dat pd.DataFrame(data[[1, 3, 1, 2, 7], [-, -, -, -, -], [-, -, -, -, -], [-, -, -, -, -]], columns[1级, 2级, 3级, 4级, 合计]) excel.write2excel(excel_nameexl_name, outcome_dataoutcome_dat, defect_datadefect_dat, maintain_datamaintain_dat, kind_datakind_dat)