复制excel 图片复制失败
复制带有图片的excel失败，图片没复制过去，图片已经做成jpg格式了，许愿下，万一有大佬能帮帮呢import pandas as pd
import tkinter as tk
from tkinter import filedialog
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import os
import shutil
def select_input_file():
file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx;*.xls")], title="请选择输入数据excel，.xlsx格式")
return file_path
def select_template_file():
file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx;*.xls")], title="请选择工艺卡模版的excel，.xlsx格式")
return file_path
def select_target_folder():
folder_path = filedialog.askdirectory(title="请选择输出文件夹")
return folder_path
def process01_df(df_in):
for index, row in df_in.iterrows():
if row['焊接类型'] == '对焊' or row['焊接类型'] == '承插焊':
diam_thickness = f"Φ{row['主管外径']}*{row['壁厚']}"
df_in.at = diam_thickness
elif row['焊接类型'] == '开口焊':
diam_thickness = f"Φ{row['支管外径']}*{row['支管壁厚']}-Φ{row['主管外径']}*{row['壁厚']}"
df_in.at = diam_thickness
# 提取前段材质和前段材质编号
if "A105" in row["焊缝前材质"]:
df_in.at = "A105"
df_in.at = 20
elif "20-G" in row["焊缝前材质"]:
df_in.at = "20"
df_in.at = 21
elif "20G-" in row["焊缝前材质"]:
df_in.at = "20G"
df_in.at = 22
elif "20Ⅱ" in row["焊缝前材质"]:
df_in.at = "20Ⅱ"
df_in.at = 23
elif "20Ⅲ" in row["焊缝前材质"]:
df_in.at = "20Ⅲ"
df_in.at = 24
elif "12Cr1MoVG" in row["焊缝前材质"]:
df_in.at = "12Cr1MoVG"
df_in.at = 2
elif "12Cr1MoVⅡ" in row["焊缝前材质"]:
df_in.at = "12Cr1MoVⅡ"
df_in.at = 3
elif "12Cr1MoVⅢ" in row["焊缝前材质"]:
df_in.at = "12Cr1MoVⅢ"
df_in.at = 4
elif "15CrMoG" in row["焊缝前材质"]:
df_in.at = "15CrMoG"
df_in.at = 5
elif "15CrMoⅡ" in row["焊缝前材质"]:
df_in.at = "15CrMoⅡ"
df_in.at = 6
elif "15CrMoⅢ" in row["焊缝前材质"]:
df_in.at = "15CrMoⅢ"
df_in.at = 7
elif "S30408-" in row["焊缝前材质"]:
df_in.at = "S30408"
df_in.at = 11
elif "S30408Ⅱ" in row["焊缝前材质"]:
df_in.at = "S30408Ⅱ"
df_in.at = 12
elif "S30408Ⅲ" in row["焊缝前材质"]:
df_in.at = "S30408Ⅲ"
df_in.at = 13
elif "S31603-" in row["焊缝前材质"]:
df_in.at = "S31603"
df_in.at = 14
elif "S31603Ⅱ" in row["焊缝前材质"]:
df_in.at = "S31603Ⅱ"
df_in.at = 15
elif "S31603Ⅲ" in row["焊缝前材质"]:
df_in.at = "S31603Ⅲ"
df_in.at = 16
else:
df_in.at = 0
# 提取后段材质和前段材质编号
if "A105" in row["焊缝后材质"]:
df_in.at = "A105"
df_in.at = 20
elif "20-G" in row["焊缝后材质"]:
df_in.at = "20"
df_in.at = 21
elif "20G-" in row["焊缝后材质"]:
df_in.at = "20G"
df_in.at = 22
elif "20Ⅱ" in row["焊缝后材质"]:
df_in.at = "20Ⅱ"
df_in.at = 23
elif "20Ⅲ" in row["焊缝后材质"]:
df_in.at = "20Ⅲ"
df_in.at = 24
elif "12Cr1MoVG-" in row["焊缝后材质"]:
df_in.at = "12Cr1MoVG"
df_in.at = 2
elif "12Cr1MoVⅡ" in row["焊缝后材质"]:
df_in.at = "12Cr1MoVⅡ"
df_in.at = 3
elif "12Cr1MoVⅢ" in row["焊缝后材质"]:
df_in.at = "12Cr1MoVⅢ"
df_in.at = 4
elif "15CrMoG-" in row["焊缝后材质"]:
df_in.at = "15CrMoG"
df_in.at = 5
elif "15CrMoⅡ" in row["焊缝后材质"]:
df_in.at = "15CrMoⅡ"
df_in.at = 6
elif "15CrMoⅢ" in row["焊缝后材质"]:
df_in.at = "15CrMoⅢ"
df_in.at = 7
elif "S30408-" in row["焊缝后材质"]:
df_in.at = "S30408"
df_in.at = 11
elif "S30408Ⅱ" in row["焊缝后材质"]:
df_in.at = "S30408Ⅱ"
df_in.at = 12
elif "S30408Ⅲ" in row["焊缝后材质"]:
df_in.at = "S30408Ⅲ"
df_in.at = 13
elif "S31603-" in row["焊缝后材质"]:
df_in.at = "S31603"
df_in.at = 14
elif "S31603Ⅱ" in row["焊缝后材质"]:
df_in.at = "S31603Ⅱ"
df_in.at = 15
elif "S31603Ⅲ" in row["焊缝后材质"]:
df_in.at = "S31603Ⅲ"
df_in.at = 16
else:
df_in.at = 0
# 汇合成为材质列
df_in['材质'] = ''
df_in.loc[(df_in['前材质编码'] == 0) & (df_in['后材质编码'] == 0), '材质'] = ''
df_in.loc[(df_in['前材质编码'] == 0) & (df_in['后材质编码'] != 0), '材质'] = df_in['后材质']
df_in.loc[(df_in['前材质编码'] != 0) & (df_in['后材质编码'] == 0), '材质'] = df_in['前材质']
df_in.loc[(df_in['前材质编码'] != 0) & (df_in['后材质编码'] != 0) & (df_in['前材质编码'] == df_in['后材质编码']),\
'材质'] = df_in['前材质'].astype(str)
df_in.loc[(df_in['前材质编码'] != 0) & (df_in['后材质编码'] != 0) & (df_in['前材质编码'] < df_in['后材质编码']),\
'材质'] = df_in['前材质'].astype(str) + "&" + df_in['后材质'].astype(str)
df_in.loc[(df_in['前材质编码'] != 0) & (df_in['后材质编码'] != 0) & (df_in['前材质编码'] > df_in['后材质编码']),\
'材质'] = df_in['后材质'].astype(str) + "&" + df_in['前材质'].astype(str)
if row['管线寸口'] == '1/2"':
df_in.at = 1
elif row['管线寸口'] == '3/4"':
df_in.at = 1
elif row['管线寸口'] == '1"':
df_in.at = 1
elif row['管线寸口'] == '11/4"':
df_in.at = 1.25
elif row['管线寸口'] == '11/2"':
df_in.at = 1.5
elif row['管线寸口'] == '2"':
df_in.at = 2
elif row['管线寸口'] == '21/2"':
df_in.at = 2.5
elif row['管线寸口'] == '3"':
df_in.at = 3
elif row['管线寸口'] == '4"':
df_in.at = 4
elif row['管线寸口'] == '5"':
df_in.at = 5
elif row['管线寸口'] == '6"':
df_in.at = 6
elif row['管线寸口'] == '8"':
df_in.at = 8
elif row['管线寸口'] == '10"':
df_in.at = 10
elif row['管线寸口'] == '12"':
df_in.at = 12
elif row['管线寸口'] == '14"':
df_in.at = 14
elif row['管线寸口'] == '16"':
df_in.at = 16
elif row['管线寸口'] == '20"':
df_in.at = 20
elif row['管线寸口'] == '24"':
df_in.at = 24
df_in['道数'] = 1
return df_in
# 定义填充“工艺卡编号”的函数
def fill_process_card(row):
if row['焊接类型'] == '对焊' and '12Cr' in row['材质']:
if row['壁厚'] > 13:
return 'HKB12-06'
elif row['壁厚'] > 10:
return 'HKB12-05'
elif row['壁厚'] > 8:
return 'HKB12-04'
elif row['壁厚'] > 5.5:
return 'HKB12-03'
elif row['壁厚'] > 3:
return 'HKB12-02'
elif row['壁厚'] > 0:
return 'HKB12-01'
elif row['焊接类型'] == '承插焊' and '12Cr' in row['材质']:
if row['壁厚'] > 5.5:
return 'HKC12-03'
elif row['壁厚'] > 3:
return 'HKC12-02'
elif row['壁厚'] > 0:
return 'HKC12-01'
elif row['焊接类型'] == '开口焊' and '12Cr' in row['材质']:
if row['支管壁厚'] > 5:
return 'HKD12-03'
elif row['支管壁厚'] > 3:
return 'HKD12-02'
elif row['支管壁厚'] > 0:
return 'HKD12-01'
elif row['焊接类型'] == '对焊' and '15Cr' in row['材质']:
if row['壁厚'] > 13:
return 'HKB15-06'
elif row['壁厚'] > 10:
return 'HKB15-05'
elif row['壁厚'] > 8:
return 'HKB15-04'
elif row['壁厚'] > 5.5:
return 'HKB15-03'
elif row['壁厚'] > 3:
return 'HKB15-02'
elif row['壁厚'] > 0:
return 'HKB15-01'
elif row['焊接类型'] == '承插焊' and '15Cr' in row['材质']:
if row['壁厚'] > 5.5:
return 'HKC15-03'
elif row['壁厚'] > 3:
return 'HKC15-02'
elif row['壁厚'] > 0:
return 'HKC15-01'
elif row['焊接类型'] == '开口焊' and '15Cr' in row['材质']:
if row['支管壁厚'] > 5:
return 'HKD15-03'
elif row['支管壁厚'] > 3:
return 'HKD15-02'
elif row['支管壁厚'] > 0:
return 'HKD15-01'
elif row['焊接类型'] == '对焊' and ('304' in row['材质'] or '316' in row['材质']):
if row['壁厚'] > 7:
return 'HKB304-04'
elif row['壁厚'] > 5:
return 'HKB304-03'
elif row['壁厚'] > 3:
return 'HKB304-02'
elif row['壁厚'] > 0:
return 'HKB304-01'
elif row['焊接类型'] == '承插焊' and ('304' in row['材质'] or '316' in row['材质']):
if row['壁厚'] > 5.5:
return 'HKC304-03'
elif row['壁厚'] > 3:
return 'HKC304-02'
elif row['壁厚'] > 0:
return 'HKC304-01'
elif row['焊接类型'] == '开口焊' and ('304' in row['材质'] or '316' in row['材质']):
if row['支管壁厚'] > 6.5:
return 'HKD304-04'
elif row['支管壁厚'] > 5:
return 'HKD304-03'
elif row['支管壁厚'] > 3:
return 'HKD304-02'
elif row['支管壁厚'] > 0:
return 'HKD304-01'
elif row['焊接类型'] == '对焊' and '20' in row['材质']:
if row['壁厚'] > 13:
return 'HKB20-06'
elif row['壁厚'] > 10:
return 'HKB20-05'
elif row['壁厚'] > 8:
return 'HKB20-04'
elif row['壁厚'] > 5.5:
return 'HKB20-03'
elif row['壁厚'] > 3:
return 'HKB20-02'
elif row['壁厚'] > 0:
return 'HKB20-01'
elif row['焊接类型'] == '承插焊' and '20' in row['材质']:
if row['壁厚'] > 5.5:
return 'HKC20-03'
elif row['壁厚'] > 3:
return 'HKC20-02'
elif row['壁厚'] > 0:
return 'HKC20-01'
elif row['焊接类型'] == '开口焊' and '20' in row['材质']:
if row['壁厚'] > 8 and row['支管壁厚'] > 8:
return 'HKD20-09'
elif row['壁厚'] > 8 and row['支管壁厚'] > 6.5:
return 'HKD20-08'
elif row['壁厚'] > 8 and row['支管壁厚'] > 5:
return 'HKD20-07'
elif row['壁厚'] > 8 and row['支管壁厚'] > 3:
return 'HKD20-06'
elif row['壁厚'] > 8 and row['支管壁厚'] > 0:
return 'HKD20-05'
elif row['壁厚'] > 0 and row['支管壁厚'] > 6.5:
return 'HKD20-04'
elif row['壁厚'] > 0 and row['支管壁厚'] > 5:
return 'HKD20-03'
elif row['壁厚'] > 0 and row['支管壁厚'] > 3:
return 'HKD20-02'
elif row['壁厚'] > 0 and row['支管壁厚'] > 0:
return 'HKD20-01'
return ""
# 做一个将焊缝编号进行归总序列化的函数，用来函数内调用
def format_numbers(numbers):
if not numbers:
return ""
# 对数字列表进行排序
sorted_numbers = sorted(set(numbers))
# 初始化结果字符串和当前范围的起始值
result = []
start = sorted_numbers
end = start
# 遍历排序后的数字列表，合并连续的数字范围
for num in sorted_numbers:
if num == end + 1:
end = num
else:
if start == end:
result.append(str(start))
else:
result.append(f"{start}~{end}")
start = end = num
# 处理最后一个范围
if start == end:
result.append(str(start))
else:
result.append(f"{start}~{end}")
# 将结果列表转换为字符串，用逗号分隔
return ",".join(result)
def copy_template_to_new_file(pipeline_number_copy, template_path_copy, output_folder_copy):
# 构建新的文件名
new_file = f"{pipeline_number_copy}.xlsx"
# 构建新文件的完整路径
#new_file_path = os.path.join(output_folder_copy, new_file)
new_file_path = f"{output_folder_copy}/{new_file}"#实测这样写格式没有问题的，可以读到实际位置。
# 复制模板文件到新文件路径
shutil.copy(template_path_copy, new_file_path)
# 加载源文件和新文件
workbook_source = load_workbook(template_path_copy)
workbook_new = load_workbook(new_file_path)
# 复制每个 sheet 的图片
for sheet_name in workbook_source.sheetnames:
source_sheet = workbook_source
new_sheet = workbook_new
# 提取图片并添加到新 sheet 中
for img in source_sheet._images:
new_img = Image(img.ref)
new_img.anchor = img.anchor# 保持原图的位置
new_sheet.add_image(new_img) # 添加到新表中
# 筛选出管线号对应的行
filtered_rows = df_merge == pipeline_number_copy]
processcards = filtered_rows['工艺卡编号'].unique()
# 获取所有sheet名
sheet_names = workbook_new.sheetnames
# 遍历所有sheet名，检查并删除不需要的sheet
for sheet_name in sheet_names:
if 'H' in sheet_name and sheet_name not in processcards:
std = workbook_new# 获取具体的sheet
workbook_new.remove(std) # 删除sheet
# 保存修改后的Excel文件
workbook_new.save(new_file_path)
"""
这里加代码
"""
#尝试取得外径壁厚的值
# 筛选出管线号对应的行
filtered_rows_OD1 = df_merge == pipeline_number_copy]
processcards = filtered_rows['工艺卡编号'].unique()
workbook_new = load_workbook(new_file_path)
#sheet3.cell((7 + i), 1).value = unique_result_list
workbook_new["封面"].cell(17,9) .value = pipeline_number_copy.removeprefix(r"/")
count_sheet = 1
for sheet3 in workbook_new:
#sheet_name.cell(8,1,"好的")
#sheet_name.cell(9,1,"不好")
if 'H' in sheet3.title:
cover = workbook_new["封面"].cell(5, 13).value
sheet3.cell(2, 14).value = f"{cover}-{count_sheet}"
#sheet3['C8'] = "11"
#sheet3['C9'] = "12"
filtered_rows_OD = df_merge[(df_merge['管线号'] == pipeline_number_copy) & (df_merge['工艺卡编号'] == sheet3.title)]
# 提取“外径壁厚”和“焊接材质”列的值并存储到列表中
result_list = []
bianhao = []
for index, row in filtered_rows_OD.iterrows():
result_list.append((row['材质'], row['外径壁厚']))
bianhao.append(row['焊缝编号'])
unique_result_list = list(set(result_list))
txt_bianhao = format_numbers(bianhao)
txt_bianhao = txt_bianhao.replace(".0", "")
sheet3.cell(4, 13).value = f"{txt_bianhao}({len(bianhao)}处)"
#这里为了测试，先避开不同壁厚的
for i in range(0, min(len(unique_result_list ),6)):
sheet3.cell((7 + i), 1).value = unique_result_list
sheet3.cell((7 + i), 3).value = unique_result_list
# 这里开始填写目录
workbook_new["焊接工艺目录"].cell((7 + count_sheet), 1).value = sheet3.cell(4, 13).value
workbook_new["焊接工艺目录"].cell((7 + count_sheet), 2).value = sheet3.cell(4, 4).value
workbook_new["焊接工艺目录"].cell((7 + count_sheet), 3).value = sheet3.cell(2, 14).value
# 提取第1列的第8行到11行的数据
data_weld = []
for row in range(24,27):
cell_value = sheet3.cell(row=row, column=1).value
if cell_value is not None:
data_weld.append(cell_value)
# 去重并去除空值
unique_data = list(set(filter(None, data_weld)))
# 将数据转换为字符串并用+号连接
result = '+'.join(map(str, unique_data))
workbook_new["焊接工艺目录"].cell((7 + count_sheet), 4).value = result
workbook_new["焊接工艺目录"].cell((7 + count_sheet), 5).value = sheet3.cell(4, 8).value
workbook_new["焊接工艺目录"].cell((7 + count_sheet), 6).value = sheet3.cell(21, 8).value
count_sheet += 1
workbook_new.save(new_file_path)
print(f"已创建并保存文件：{new_file_path}")
"""
新增图形界面
"""
root = tk.Tk()
root.withdraw()# 隐藏主窗口
input_file = select_input_file()
if not input_file:
print("未选择输入文件")
exit()
template_file = select_template_file()
if not template_file:
print("未选择模板文件")
exit()
target_folder = select_target_folder()
if not target_folder:
print("未选择输出文件夹")
exit()
# 读取原始数据文件
df_merge = pd.read_excel(input_file)
# 将列“焊缝类型”数值为“对焊”，“承插焊”，“开口焊”的行里列“主管外径”，“壁厚”类型改为浮点型
selected_weld_types = ['对焊', '承插焊', '开口焊']
df_merge.loc.isin(selected_weld_types), ['主管外径', '壁厚']] = df_merge.loc.isin(selected_weld_types), ['主管外径', '壁厚']].astype(float)
# 将列“焊缝类型”数值为"开口焊"的行里列“支管外径”，“支管壁厚”类型改为浮点型
df_merge.loc == '开口焊', ['支管外径', '支管壁厚']] = df_merge.loc == '开口焊', ['支管外径', '支管壁厚']].astype(float)
df_merge = process01_df(df_merge)
df_merge['工艺卡编号'] = df_merge.apply(fill_process_card, axis=1)
df_merge = df_merge != ""]
# 对相同的“管线号”，“焊缝类型”，“焊缝材质”进行分组，并对“焊缝数量”求和
#df_merge = df_merge.groupby(["管线号", "焊接类型", "工艺卡编号", "焊接材质", "外径壁厚", "管线寸口"], as_index=False).agg({"焊接寸口": "sum"})
# df_merge = df_merge.groupby(["管线号", "焊接类型", "工艺卡编号", "材质", "外径壁厚", "管线寸口"], as_index=False).agg({"道数": "sum","焊接寸口": "sum"})
# 根据“管线号”，“焊缝类型”，“焊缝材质”进行排序
# df_merge.sort_values(by=["管线号", "材质", "管线寸口", "外径壁厚", "焊接类型"], inplace=True)
# 获取所有不同的管线号
pipeline_ids = df_merge["管线号"].unique() # 请确保替换为实际的列名
output_file = f"{target_folder}/new.xlsx"
df_merge.to_excel(output_file, index=False)
# 确保目标文件夹存在
if not os.path.exists(target_folder):
os.makedirs(target_folder)
# 遍历所有管线号，复制模板文件并重命名
for pipeline_number in pipeline_ids:
copy_template_to_new_file(pipeline_number, template_file, target_folder)
图片没啥好办法虽然图片能提取出来，但是和你的单元格的位置都是无法匹配的 之前成功过能提取的，后来隔了一个月，继续开始做这个事。程序继续往下做，又失败了 或者确实没办法了，就只能换种思路来，我指定一个图片文件夹，图片文件名为对应的sheet，对应sheet找到了，就贴过去图，这样估计反而行，但是不管怎么样，其实还是在许愿能直接复制阶段就能完成，别搞太难看 我来写个测试程序测试下这个思路，应该是可以 你好，鱼油！从你的代码和描述来看，图片未能复制到Excel文件。在使用`openpyxl`库处理Excel文件，并插入图片时，可以按照以下步骤：
1. **确保图片路径和格式正确**：
确保图片路径和格式都正确，确保图片文件确实存在并能被正常读取。
2. **用`openpyxl`插入图片**：
使用`openpyxl.drawing.image.Image`来插入图片，并将其添加到指定的单元格中。以下是完整的修改代码：
import pandas as pd
import tkinter as tk
from tkinter import filedialog
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import os
import shutil
def select_input_file():
file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")], title="请选择输入数据excel，.xlsx格式")
return file_path
def select_template_file():
file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")], title="请选择工艺卡模版的excel，.xlsx格式")
return file_path
def select_target_folder():
folder_path = filedialog.askdirectory(title="请选择输出文件夹")
return folder_path
def process_file(input_file, template_file, target_folder):
df_in = pd.read_excel(input_file)
process01_df(df_in)
# 读取模板文件
wb = load_workbook(template_file)
ws = wb.active
# 这里假设你需要把图片插入到特定的单元格
for index, row in df_in.iterrows():
# 根据你的逻辑进行处理
# 假设图片路径在某个列（比如第5列）
image_path = row
if os.path.exists(image_path):
img = Image(image_path)
ws.add_image(img, f"A{index+2}")# 假设从A2单元格开始插入图片
# 将处理后的文件保存到目标文件夹
output_file = os.path.join(target_folder, os.path.basename(input_file))
wb.save(output_file)
def main():
root = tk.Tk()
root.withdraw()# 隐藏主窗口
input_file = select_input_file()
template_file = select_template_file()
target_folder = select_target_folder()
if input_file and template_file and target_folder:
process_file(input_file, template_file, target_folder)
def process01_df(df_in):
for index, row in df_in.iterrows():
if row == '对焊' or row == '承插焊':
diam_thickness = f"Φ{row}*{row}"
df_in.at = diam_thickness
elif row == '开口焊':
diam_thickness = f"Φ{row}*{row}-Φ{row}*{row}"
df_in.at = diam_thickness
# 提取前段材质和前段材质编号
if "A105" in row:
df_in.at = "A105"
df_in.at = 20
elif "20-G" in row:
df_in.at = "20"
df_in.at = 21
elif "20G-" in row:
df_in.at = "20G"
df_in.at = 22
elif "20Ⅱ" in row:
df_in.at = "20Ⅱ"
df_in.at = 23
elif "20Ⅲ" in row:
df_in.at = "20Ⅲ"
df_in.at = 24
elif "12Cr1MoVG" in row:
df_in.at = "12Cr1MoVG"
df_in.at = 2
elif "12Cr1MoVⅡ" in row:
df_in.at = "12Cr1MoVⅡ"
df_in.at = 3
elif "12Cr1MoVⅢ" in row:
df_in.at = "12Cr1MoVⅢ"
df_in.at = 4
elif "15CrMoG" in row:
df_in.at = "15CrMoG"
df_in.at = 5
elif "15CrMoⅡ" in row:
df_in.at = "15CrMoⅡ"
df_in.at = 6
elif "15CrMoⅢ" in row:
df_in.at = "15CrMoⅢ"
df_in.at = 7
if __name__ == "__main__":
main()
这样，程序将插入图片到Excel中适当的单元格。注意修改`ws.add_image(img, f"A{index+2}")`这部分代码，确保图片插入到正确的单元格。根据实际情况调整代码即可。希望这个解答能帮到你！
以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。
页:
[1]