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