办公自动化：增强版excel图片下载并插入工具

支持按列或按行下载图片并插入到excel中，同时可设置单元格高和宽，图片大小会同时调整。

log:

7月25日更正了pyinstaller的版本，使用最新版即可；删除了报错的参数keep_vba

实现效果如下：

依赖包及版本：

py代码实现：

import wx
import pandas as pd
import requests
import openpyxl
from openpyxl.drawing.image import Image as xlImage
from io import BytesIO
from PIL import Image
import threading
import re
import pandas._libs.tslibs.base
import pandas._libs.tslibs.nattype
import openpyxl.styles.stylesheet
import pkg_resources

app_title = u'增强版Excel图片处理工具'

class EnhancedExcelImageFrame(wx.Frame):
    def __init__(self, parent):
        wx.Frame.__init__(self, parent, -1, app_title, size=(650, 450))
        self.SetBackgroundColour(wx.Colour(240, 240, 240))
        self.Center()
        
        self._create_controls()
        self._layout_components()
        self.stop_event = threading.Event()

    def _create_controls(self):
        """创建增强版界面组件"""
        # 文件选择控件
        self.input_entry = wx.TextCtrl(self)
        self.btn_input = wx.Button(self, label=u"选择Excel文件")
        self.output_entry = wx.TextCtrl(self)
        self.btn_output = wx.Button(self, label=u"保存路径")
        
        # 处理模式选择
        self.mode_choice = wx.Choice(self, choices=[u"按列处理", u"按行处理"])
        self.mode_choice.SetSelection(0)
        
        # 目标输入（复用为列号/行号）
        self.target_input = wx.TextCtrl(self, style=wx.TE_PROCESS_ENTER, size=(260, -1))  # 加宽输入框        
        self.target_input.SetHint(u"输入列号（数字）")
        
        # 尺寸设置
        self.width_entry = wx.TextCtrl(self, value="50")
        self.height_entry = wx.TextCtrl(self, value="65")
        
        # 操作按钮
        self.btn_process = wx.Button(self, label=u"开始处理")
        self.progress = wx.Gauge(self, range=100)
        
        # 事件绑定
        self.btn_input.Bind(wx.EVT_BUTTON, self._on_select_input)
        self.btn_output.Bind(wx.EVT_BUTTON, self._on_select_output)
        self.btn_process.Bind(wx.EVT_BUTTON, self._on_process)
        self.mode_choice.Bind(wx.EVT_CHOICE, self._on_mode_change)

    def _on_process(self, event):
        """启动处理线程（从基础版补充）"""
        if not self._validate_inputs():
            return
            
        self.btn_process.Disable()
        self.progress.SetValue(0)
        self.stop_event.clear()  # 重置停止事件
        
        worker = threading.Thread(target=self._process_excel)
        worker.start()

    def _create_file_picker(self, label, entry, button):
        """创建文件选择行（从基础版复制）"""
        sizer = wx.BoxSizer(wx.HORIZONTAL)
        sizer.Add(wx.StaticText(self, label=label), 0, wx.RIGHT|wx.ALIGN_CENTER_VERTICAL, 5)
        sizer.Add(entry, 1, wx.EXPAND|wx.RIGHT, 5)
        sizer.Add(button, 0)
        return sizer

    def _on_select_input(self, event):
        """选择输入文件（从基础版复制）"""
        dlg = wx.FileDialog(self, u"选择Excel文件", wildcard="Excel文件 (*.xlsx)|*.xlsx")
        if dlg.ShowModal() == wx.ID_OK:
            self.input_entry.SetValue(dlg.GetPath())
        dlg.Destroy()

    def _on_select_output(self, event):
        """选择输出文件（从基础版复制）"""
        dlg = wx.FileDialog(self, u"保存Excel文件", style=wx.FD_SAVE|wx.FD_OVERWRITE_PROMPT, 
                          wildcard="Excel文件 (*.xlsx)|*.xlsx")
        if dlg.ShowModal() == wx.ID_OK:
            self.output_entry.SetValue(dlg.GetPath())
        dlg.Destroy()   

    def _layout_components(self):
        """布局增强版组件"""
        main_sizer = wx.BoxSizer(wx.VERTICAL)
        
        # 文件选择部分
        input_sizer = self._create_file_picker(u"输入文件:", self.input_entry, self.btn_input)
        output_sizer = self._create_file_picker(u"输出文件:", self.output_entry, self.btn_output)
        
        # 参数设置网格
        param_sizer = wx.FlexGridSizer(rows=4, cols=2, hgap=10, vgap=10)
        param_sizer.AddMany([
            (wx.StaticText(self, label=u"处理模式:"), 0, wx.ALIGN_CENTER_VERTICAL),
            self.mode_choice,
            (wx.StaticText(self, label=u"目标列/行:"), 0, wx.ALIGN_CENTER_VERTICAL),
            self.target_input,
            (wx.StaticText(self, label=u"图片宽度:"), 0, wx.ALIGN_CENTER_VERTICAL),
            self.width_entry,
            (wx.StaticText(self, label=u"图片高度:"), 0, wx.ALIGN_CENTER_VERTICAL),
            self.height_entry
        ])
        
        # 主布局
        main_sizer.Add(input_sizer, 0, wx.EXPAND|wx.ALL, 10)
        main_sizer.Add(output_sizer, 0, wx.EXPAND|wx.ALL, 10)
        main_sizer.Add(param_sizer, 0, wx.EXPAND|wx.ALL, 10)
        main_sizer.Add(self.progress, 0, wx.EXPAND|wx.ALL, 10)
        main_sizer.Add(self.btn_process, 0, wx.ALIGN_CENTER|wx.ALL, 10)
        
        self.SetSizer(main_sizer)

    def _on_mode_change(self, event):
        """处理模式切换事件"""
        if self.mode_choice.GetSelection() == 0:  # 列模式
            self.target_input.SetHint(u"输入列号（数字）")
        else:  # 行模式
            self.target_input.SetHint(u"输入行号（如2,5-8）")
        self.Layout()

    def _validate_inputs(self):
        """增强版输入验证"""
        if not self.input_entry.GetValue():
            self._show_error(u"请选择输入文件")
            return False
        if not self.output_entry.GetValue():
            self._show_error(u"请指定输出路径")
            return False
        
        mode = self.mode_choice.GetSelection()
        
        try:
            df = pd.read_excel(self.input_entry.GetValue(), header=None)
            
            if mode == 0:  # 列模式
                col = int(self.target_input.GetValue())
                if col > len(df.columns):
                    self._show_error(u"列号超过实际列数")
                    return False
            else:  # 行模式
                rows = self._parse_row_input(self.target_input.GetValue())
                if not rows:
                    self._show_error(u"请输入有效的行号")
                    return False
                if max(rows) > len(df):
                    self._show_error(u"存在行号超过实际行数")
                    return False
                if min(rows) < 1:  # 新增最小值校验
                    self._show_error(u"行号不能小于1")
                    return False
                
        except ValueError as e:
            self._show_error(str(e))
            return False
        except Exception as e:
            self._show_error(str(e))
            return False
            
        return True

    def _process_excel(self):
        """增强版处理逻辑"""
        try:
            input_path = self.input_entry.GetValue()
            output_path = self.output_entry.GetValue()
            img_width = int(self.width_entry.GetValue())
            img_height = int(self.height_entry.GetValue())
            mode = self.mode_choice.GetSelection()

            df = pd.read_excel(input_path, header=None)
            writer = pd.ExcelWriter(output_path, engine='openpyxl')
            df.to_excel(writer, index=False, sheet_name='Processed')
            worksheet = writer.sheets['Processed']

            if mode == 0:  # 列模式
                self._process_column_mode(df, worksheet, img_width, img_height)
            else:  # 行模式
                self._process_row_mode(df, worksheet, img_width, img_height)

            writer.close()
            wx.CallAfter(self._show_success, u"处理完成！")
            
        except Exception as e:
            wx.CallAfter(self._show_error, str(e))
        finally:
            wx.CallAfter(self.btn_process.Enable)

    def _process_column_mode(self, df, worksheet, width, height):
        """列处理模式"""
        col = int(self.target_input.GetValue()) - 1  # 转换为0-based索引
        total = len(df)
        
        for idx, row in df.iterrows():
            if self.stop_event.is_set():
                break
                
            cell_value = df.iloc[idx, col]
            if self._is_valid_url(cell_value):
                self._insert_image(worksheet, cell_value, col+1, idx+2, width, height)
                
            wx.CallAfter(self.progress.SetValue, int((idx+1)/total*100))

    def _process_row_mode(self, df, worksheet, width, height):
        """行处理模式"""
        rows = self._parse_row_input(self.target_input.GetValue())
        total = len(rows) * df.shape[1]
        processed = 0
        
        for row in rows:
            for col_idx in range(df.shape[1]):
                if self.stop_event.is_set():
                    break
                
                cell_value = df.iloc[row-1, col_idx]
                if self._is_valid_url(cell_value):
                    self._insert_image(worksheet, cell_value, col_idx+1, row + 1, width, height)
                
                processed += 1
                wx.CallAfter(self.progress.SetValue, int(processed/total*100))


    def _insert_image(self, worksheet, url, col, row, width, height):
        """插入图片到单元格（从基础版补充）"""
        try:
            response = requests.get(url, timeout=10)
            img = Image.open(BytesIO(response.content))
            img = img.resize((width, height))
            
            img_bytes = BytesIO()
            img.save(img_bytes, format='PNG')
            xl_img = xlImage(img_bytes)
            worksheet.add_image(xl_img, f"{openpyxl.utils.get_column_letter(col)}{row}")
            
            # 调整单元格尺寸
            worksheet.row_dimensions[row].height = height * 0.75
            worksheet.column_dimensions[openpyxl.utils.get_column_letter(col)].width = width * 0.14
            
        except Exception as e:
            print(f"图片处理失败: {str(e)}")                

    def _parse_row_input(self, input_text):
        """解析多行输入"""
        rows = set()
        for part in re.split(r'[,]', input_text.strip()):
            if '-' in part:
                start_end = part.split('-')
                if len(start_end) != 2:
                    continue
                start = int(start_end[0])
                end = int(start_end[1])
                # 添加行号有效性校验
                if start < 1 or end < 1:
                    continue
                rows.update(range(max(1, start), end+1))
            elif part.isdigit():
                if int(part) < 1:  # 过滤0和负数
                    continue
                rows.add(int(part))
        return sorted([x for x in rows if x >= 1])  # 二次过滤

    def _is_valid_url(self, text):
        """简单URL验证"""
        return isinstance(text, str) and text.startswith(('http://', 'https://'))

    def _show_error(self, message):
        """显示错误提示"""
        dlg = wx.MessageDialog(self, message, u"错误", wx.OK|wx.ICON_ERROR)
        dlg.ShowModal()
        dlg.Destroy()

    def _show_success(self, message):
        """显示成功提示"""
        dlg = wx.MessageDialog(self, message, u"完成", wx.OK|wx.ICON_INFORMATION)
        dlg.ShowModal()
        dlg.Destroy()    

if __name__ == "__main__":
    app = wx.App()
    frame = EnhancedExcelImageFrame(None)
    frame.Show()
    app.MainLoop()