一、背景
采用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_path=None):
self.template_path = template_path
self.app = xw.App(visible=False, add_book=False)
self.wb = self.app.books.open(self.template_path)
# 根据模板导出excel文件
def write2excel(self, excel_name, outcome_data=None, defect_data=None, maintain_data=None, kind_data=None):
# 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, data=None):
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, left=left, top=top, width=width, height=height)
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_path='Excels\\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', r'Excels\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', r'Excels\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_name=exl_name, outcome_data=outcome_dat, defect_data=defect_dat, maintain_data=maintain_dat,
kind_data=kind_dat)
代码可正常运行，合并单元格成功。
虚拟环境如下：
(py36_excel_env) C:\Users\DELL>pip 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_path=None):
self.template_path = template_path
self.app = xw.App(visible=False, add_book=False)
self.wb = self.app.books.open(self.template_path)
# 根据模板导出excel文件
def write2excel(self, excel_name, outcome_data=None, defect_data=None, maintain_data=None, kind_data=None):
# 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, data=None):
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, left=left, top=top, width=width, height=height)
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_path='Excels\\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', r'Excels\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', r'Excels\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_name=exl_name, outcome_data=outcome_dat, defect_data=defect_dat, maintain_data=maintain_dat,
kind_data=kind_dat)
1269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
