openpyxl照片處理筆記

前言

目前在處理輸出報表的實務上會遇到要貼上照片的問題,有關python在處理Excel照片部分會需要透過openpyxl裡面相關的套件進行,希望能夠達成下列情況:

給定照片之後可以依照儲存格的範圍貼上照片,儲存格可能是單一儲存格或者是合併儲存格,找出儲存格範圍寬度、儲存格範圍高度

最適合的方式計算過程

  • 照片寬的,比例設定為儲存格範圍寬度/照片寬度,照片長寬同乘上這個比例放大縮小
  • 照片長的,比例設定為儲存格範圍高度/照片高度,照片長寬同乘上這個比例放大縮小

照片貼上方式

貼到一定範圍間

https://openpyxl.readthedocs.io/en/latest/_modules/openpyxl/drawing/spreadsheet_drawing.html#TwoCellAnchor

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
import streamlit as st
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as OpenpyxlImage
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker,TwoCellAnchor
import io

def is_merged_cell(ws, col, row):
for merged_range in ws.merged_cells.ranges:
if ws.cell(row=row+1, column=col+1).coordinate in merged_range:
return merged_range
return None

def insert_image(worksheet,img,target_row,target_col):

col1,row1,col2,row2=(is_merged_cell(worksheet, target_col,target_row).bounds)

marker1=AnchorMarker(col=col1-1, row=row1-1)
marker2=AnchorMarker(col=col2, row=row2)
img.anchor = TwoCellAnchor(_from=marker1, to=marker2)
worksheet.add_image(img)

# Streamlit application title
st.title("Excel Image Insertion Tool")

# File upload
uploaded_excel = st.file_uploader("Upload Excel file", type=["xlsx"])
uploaded_image = st.file_uploader("Upload image file", type=["png", "jpg", "jpeg"])
uploaded_image2 = st.file_uploader("Upload image file2", type=["png", "jpg", "jpeg"])

# Input for sheet name and cell location
sheet_name = st.text_input("Enter sheet name", "概要表")
cell_location = st.text_input("Enter cell location for image insertion", "E3")

if uploaded_excel and uploaded_image and uploaded_image2:
# Read Excel file
workbook = load_workbook(filename=uploaded_excel)

# Check if sheet exists
if sheet_name not in workbook.sheetnames:
st.error(f"Sheet '{sheet_name}' does not exist!")
else:
worksheet = workbook[sheet_name]

# Load image
img = OpenpyxlImage(uploaded_image)
img2=OpenpyxlImage(uploaded_image2)

insert_image(worksheet,img,3,5) #E3
insert_image(worksheet,img2,14,5) #E14

# Save modified Excel file
output = io.BytesIO()
workbook.save(output)
output.seek(0)

# Provide download link
st.download_button(label="Download modified Excel file",
data=output,
file_name="modified_excel.xlsx")

st.success("Image successfully inserted into Excel file!")

直接貼到左上角點

目前openpyxl的套件只能透過OneCellAnchor處理,但是照片大小無從適應(也許是我還沒找到方法)。

貼到左上角點之後進行偏移(目前施工照片是這樣處理)

VBA 內容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
dblGap = 2#
Count = 0
For Each fleTmp In fldMain.Files
strTmp = UCase(fsoMain.GetExtensionName(fleTmp.path))
If strTmp = "JPG" Or strTmp = "JPEG" Then
Count = Count + 1
With shtAct
Set ranOri = .Range(.Cells(2 + (Count - 1) * 14, 4), .Cells(2 + (Count - 1) * 14, 4))
Set ranNum = ranOri.Offset(2, -2)
ranNum = Count
End With
Set picAct = shtAct.Pictures.Insert(fleTmp.path)
With picAct
.ShapeRange.LockAspectRatio = msoTrue '鎖定照片長寬比
If .Height > .Width Then '直式
Set ranOri = ranOri.Resize(13, 1) '.Merge
ranOri.Merge
Else '橫式
Set ranOri = ranOri.Offset(4, -2).Resize(9, 3) '.Merge
ranOri.Merge
End If
dblRatioPic = .Width / .Height
dblRatioOri = ranOri.Width / ranOri.Height
If dblRatioPic > dblRatioOri Then '寬度控制
.Width = ranOri.Width - 2 * dblGap
.Top = ranOri.Top + 0.5 * ranOri.Height - 0.5 * .Height
.Left = ranOri.Left + dblGap
Else '高度控制
.Height = ranOri.Height - 2 * dblGap
.Top = ranOri.Top + dblGap
.Left = ranOri.Left + 0.5 * ranOri.Width - 0.5 * .Width
End If
End With
End If
Next fleTmp

開發小問題

I/O operation on closed file

Sol:

如果uploaded_file沒有先經過img_file進行儲存的話,會在OpenpyxlImage處理完畢之後就close

1
2
3
4
5
6
7
8
9
10
11
12
13
14

img1_file = io.BytesIO(st.session_state.uploaded_file1.getvalue())
img1 = OpenpyxlImage(img1_file)
img2_file = io.BytesIO(st.session_state.uploaded_file2.getvalue())
img2 = OpenpyxlImage(img2_file)
img3_file = io.BytesIO(st.session_state.uploaded_file3.getvalue())
img3 = OpenpyxlImage(img3_file)

# img1=OpenpyxlImage(st.session_state.uploaded_file1)
insert_image(sheet,img1,3,5)
# img2=OpenpyxlImage(st.session_state.uploaded_file2)
insert_image(sheet,img2,14,5)
# img3=OpenpyxlImage(st.session_state.uploaded_file3)
insert_image(sheet,img3,14,8)

ref

https://blog.csdn.net/chenliang1038/article/details/119410308