fix excel file message parsing and dingtalk fileType reply

remote_sync/bot.py

@@ -12,7 +12,9 @@ import mimetypes
 import os
 import re
 import subprocess
+import sys
 import tempfile
+import threading
 import time
 import urllib.parse
 import uuid
@@ -151,6 +153,17 @@ DALONGXIA_ANALYTICS_HINT_KEYWORDS = (
     "analytics",
 )
 
+EXCEL_IMAGE_LINK_HINT_KEYWORDS = (
+    "图片链接",
+    "链接转图片",
+    "转成图片",
+    "回填",
+    "excel",
+    "xlsx",
+    "表格",
+    "文件",
+)
+
 
 URL_RE = re.compile(
     r"https?://[A-Za-z0-9\-._~:/?#\[\]@!$&'()*+,;=%]+",
@@ -296,6 +309,37 @@ class OpenClawDingTalkBot(ChatbotHandler):
         self.video_analysis_timeout = max(15, min(int(os.getenv("VIDEO_ANALYSIS_TIMEOUT", "360")), 900))
         self.video_analysis_max_tokens = max(256, min(int(os.getenv("VIDEO_ANALYSIS_MAX_TOKENS", "1800")), 8192))
         self.video_analysis_max_urls = max(1, min(int(os.getenv("VIDEO_ANALYSIS_MAX_URLS", "2")), 3))
+        self.excel_link_feature_enabled = bool_env("EXCEL_IMAGE_LINK_FEATURE_ENABLED", True)
+        self.excel_converter_script = (
+            os.getenv(
+                "EXCEL_IMAGE_CONVERTER_SCRIPT",
+                "/opt/openclaw-dingtalk-bridge/skills/dingtalk-excel-image-return/convert_excel_image_links.py",
+            ).strip()
+            or "/opt/openclaw-dingtalk-bridge/skills/dingtalk-excel-image-return/convert_excel_image_links.py"
+        )
+        self.excel_converter_timeout = max(60, min(int(os.getenv("EXCEL_IMAGE_CONVERTER_TIMEOUT", "480")), 3600))
+        self.excel_link_column = os.getenv("EXCEL_IMAGE_LINK_COLUMN", "图片链接").strip() or "图片链接"
+        self.excel_image_column_header = os.getenv("EXCEL_IMAGE_COLUMN_HEADER", "图片").strip() or "图片"
+        self.excel_image_max_width = max(80, min(int(os.getenv("EXCEL_IMAGE_MAX_WIDTH", "140")), 1200))
+        self.excel_image_max_height = max(80, min(int(os.getenv("EXCEL_IMAGE_MAX_HEIGHT", "140")), 1200))
+        self.excel_image_download_timeout = max(3, min(int(os.getenv("EXCEL_IMAGE_DOWNLOAD_TIMEOUT", "20")), 120))
+        self.excel_file_download_timeout = max(5, min(int(os.getenv("EXCEL_FILE_DOWNLOAD_TIMEOUT", "60")), 300))
+        self.excel_skip_tls_verify = bool_env("EXCEL_IMAGE_SKIP_TLS_VERIFY", False)
+        self.excel_text_prompt_cooldown_seconds = max(
+            10,
+            min(int(os.getenv("EXCEL_TEXT_PROMPT_COOLDOWN_SECONDS", "120")), 600),
+        )
+        self.excel_alert_log_enabled = bool_env("EXCEL_ALERT_LOG_ENABLED", True)
+        self.excel_alert_log_path = (
+            os.getenv(
+                "EXCEL_ALERT_LOG_PATH",
+                "/opt/openclaw-dingtalk-bridge/logs/excel_converter_alerts.log",
+            ).strip()
+            or "/opt/openclaw-dingtalk-bridge/logs/excel_converter_alerts.log"
+        )
+        self.excel_alert_detail_limit = max(200, min(int(os.getenv("EXCEL_ALERT_DETAIL_LIMIT", "1600")), 10000))
+        self._excel_alert_log_lock = threading.Lock()
+        self._recent_excel_success_ts = {}
 
     def _should_handle(self, msg: ChatbotMessage, text: str) -> bool:
         if text.startswith(self.prefix):
@@ -1401,6 +1445,154 @@ class OpenClawDingTalkBot(ChatbotHandler):
                 break
         return codes
 
+    def _normalize_file_name(self, raw_name: str) -> str:
+        raw = str(raw_name or "").strip().replace("\\", "_").replace("/", "_")
+        raw = re.sub(r"\s+", "_", raw)
+        stem, ext = os.path.splitext(raw)
+        safe_stem = re.sub(r"[^0-9A-Za-z._-]+", "_", stem).strip("._-")
+        safe_ext = re.sub(r"[^0-9A-Za-z.]+", "", ext).lower()
+        if safe_ext and not safe_ext.startswith("."):
+            safe_ext = f".{safe_ext}"
+        if not safe_stem:
+            safe_stem = f"upload_{int(time.time())}"
+        normalized = f"{safe_stem}{safe_ext}" if safe_ext else safe_stem
+        return normalized[:120]
+
+    def _is_supported_excel_file(self, file_name: str) -> bool:
+        return str(file_name or "").strip().lower().endswith(".xlsx")
+
+    def _is_supported_excel_payload(self, file_name: str, file_type: str) -> bool:
+        if self._is_supported_excel_file(file_name):
+            return True
+        normalized = str(file_type or "").strip().lower()
+        if not normalized:
+            return False
+        normalized = normalized.split(";")[0].strip()
+        if normalized in {
+            "xlsx",
+            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
+        }:
+            return True
+        if "spreadsheetml.sheet" in normalized:
+            return True
+        return False
+
+    def _is_excel_link_prompt(self, prompt: str) -> bool:
+        raw = str(prompt or "").strip().lower()
+        if not raw:
+            return False
+        return any(keyword in raw for keyword in EXCEL_IMAGE_LINK_HINT_KEYWORDS)
+
+    def _find_download_code_payload(self, value):
+        stack = [value]
+        while stack:
+            current = stack.pop()
+            if current is None:
+                continue
+            if isinstance(current, str):
+                content = current.strip()
+                if not content:
+                    continue
+                if content.startswith("{") or content.startswith("["):
+                    try:
+                        stack.append(json.loads(content))
+                        continue
+                    except Exception:
+                        continue
+            if isinstance(current, dict):
+                lowered = {str(k).lower(): k for k in current.keys()}
+                code = ""
+                if "downloadcode" in lowered:
+                    raw_code = current.get(lowered["downloadcode"])
+                    code = str(raw_code or "").strip()
+
+                download_url = ""
+                for key_name in (
+                    "downloadurl",
+                    "download_url",
+                    "privatedownloadurl",
+                    "private_download_url",
+                    "privateurl",
+                ):
+                    if key_name in lowered:
+                        raw_url = current.get(lowered[key_name])
+                        candidate_url = str(raw_url or "").strip()
+                        if candidate_url.lower().startswith(("http://", "https://")):
+                            download_url = candidate_url
+                            break
+
+                file_name = ""
+                for candidate in ("fileName", "filename", "file_name", "name", "title"):
+                    if candidate in current and str(current.get(candidate) or "").strip():
+                        file_name = str(current.get(candidate)).strip()
+                        break
+
+                file_type = ""
+                for candidate in ("fileType", "filetype", "mimeType", "mimetype", "suffix", "ext", "extension"):
+                    if candidate in current and str(current.get(candidate) or "").strip():
+                        file_type = str(current.get(candidate)).strip()
+                        break
+
+                if code or download_url:
+                    return {
+                        "download_code": code,
+                        "download_url": download_url,
+                        "file_name": file_name,
+                        "file_type": file_type,
+                    }
+                for child in current.values():
+                    stack.append(child)
+                continue
+            if isinstance(current, list):
+                for child in current:
+                    stack.append(child)
+        return None
+
+    def _extract_incoming_file_payload(self, callback_data: dict, msg: ChatbotMessage) -> dict:
+        payload = self._find_download_code_payload(callback_data)
+        if not payload:
+            payload = self._find_download_code_payload(getattr(msg, "extensions", {}))
+        if not payload:
+            try:
+                payload = self._find_download_code_payload(msg.to_dict())
+            except Exception:
+                payload = None
+        if not payload:
+            return {}
+
+        download_code = str(payload.get("download_code") or "").strip()
+        download_url = str(payload.get("download_url") or "").strip()
+        if download_url and not download_url.lower().startswith(("http://", "https://")):
+            download_url = ""
+        if not download_code and not download_url:
+            return {}
+
+        file_name = payload.get("file_name") or ""
+        file_type = payload.get("file_type") or ""
+        if not file_name and isinstance(callback_data, dict):
+            for key in ("fileName", "filename", "name", "title"):
+                raw = callback_data.get(key)
+                if raw:
+                    file_name = str(raw).strip()
+                    if file_name:
+                        break
+        if not file_type and isinstance(callback_data, dict):
+            for key in ("fileType", "filetype", "mimeType", "mimetype", "suffix", "ext", "extension"):
+                raw = callback_data.get(key)
+                if raw:
+                    file_type = str(raw).strip()
+                    if file_type:
+                        break
+        normalized_file_name = self._normalize_file_name(file_name or "upload.xlsx")
+        normalized_file_type = str(file_type or "").strip().lower()
+        if not self._is_supported_excel_payload(normalized_file_name, normalized_file_type):
+            return {}
+        payload["download_code"] = download_code
+        payload["download_url"] = download_url
+        payload["file_name"] = normalized_file_name
+        payload["file_type"] = normalized_file_type
+        return payload
+
     def _guess_image_ext(self, content_type: str, source_url: str) -> str:
         ctype = (content_type or "").split(";")[0].strip().lower()
         ext = ""
@@ -2079,6 +2271,259 @@ class OpenClawDingTalkBot(ChatbotHandler):
             raw = (proc.stdout or "").strip()
             return f"结果解析失败: {exc}.\n原始输出: {raw[:500]}"
 
+    def _reply_file_by_media_id(self, msg: ChatbotMessage, media_id: str, file_name: str = "") -> bool:
+        if not media_id:
+            return False
+        safe_name = self._normalize_file_name(file_name or f"excel_{int(time.time())}.xlsx")
+        file_ext = os.path.splitext(safe_name)[1].lower().lstrip(".") or "file"
+        request_headers = {
+            "Content-Type": "application/json",
+            "Accept": "*/*",
+        }
+        values = {
+            "msgtype": "file",
+            "file": {
+                "mediaId": media_id,
+                "fileName": safe_name,
+                "fileType": file_ext,
+            },
+            "at": {
+                "atUserIds": [msg.sender_staff_id] if msg.sender_staff_id else [],
+            },
+        }
+        try:
+            response_text = ""
+            response = requests.post(
+                msg.session_webhook,
+                headers=request_headers,
+                data=json.dumps(values),
+                timeout=20,
+            )
+            response_text = response.text
+            response.raise_for_status()
+            payload = {}
+            try:
+                payload = response.json()
+            except Exception:
+                payload = {}
+            if isinstance(payload, dict):
+                errcode = payload.get("errcode")
+                success = payload.get("success")
+                if (errcode is not None and str(errcode) not in {"0", ""}) or success is False:
+                    self.logger.error(
+                        "reply file business failed, errcode=%s payload=%s",
+                        errcode,
+                        str(payload)[:500],
+                    )
+                    return False
+        except Exception as exc:
+            self.logger.error("reply file failed, error=%s, response.text=%s", exc, response_text)
+            return False
+        return True
+
+    def _upload_excel_output_to_s3(self, file_path: str, filename: str) -> str:
+        if boto3 is None or BotoConfig is None:
+            return ""
+        if not (self.s3_bucket and self.s3_access_key and self.s3_secret_key):
+            return ""
+        try:
+            safe_name = self._normalize_file_name(filename or f"excel_{int(time.time())}.xlsx")
+            object_key = f"{self.s3_key_prefix}excel-output/{safe_name}"
+            self._upload_to_s3(
+                file_path,
+                object_key,
+                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
+            )
+            return self._build_public_s3_url(object_key)
+        except Exception as exc:
+            self.logger.exception("upload excel output to s3 failed: %s", exc)
+            return ""
+
+    def _excel_error_summary(self, detail: str) -> str:
+        raw = str(detail or "").strip()
+        if not raw:
+            return "unknown_error"
+        lines = [line.strip() for line in raw.splitlines() if line.strip()]
+        if not lines:
+            return raw[:300]
+        for line in reversed(lines):
+            lower_line = line.lower()
+            if lower_line.startswith("traceback"):
+                continue
+            if lower_line.startswith("file "):
+                continue
+            if lower_line in {"^^^^^^", "^^^^^"}:
+                continue
+            return line[:300]
+        return lines[-1][:300]
+
+    def _record_excel_failure_alert(
+        self,
+        msg: ChatbotMessage,
+        file_name: str,
+        stage: str,
+        detail: str,
+        return_code: int | None = None,
+        stdout_text: str = "",
+        stderr_text: str = "",
+    ) -> None:
+        normalized_detail = str(detail or "").strip() or "unknown_error"
+        summary = self._excel_error_summary(normalized_detail)
+        digest = hashlib.sha1(normalized_detail.encode("utf-8", errors="ignore")).hexdigest()[:12]
+        log_record = {
+            "ts": dt.datetime.now().isoformat(timespec="seconds"),
+            "event": "excel_convert_failed",
+            "stage": stage,
+            "digest": digest,
+            "summary": summary,
+            "return_code": return_code,
+            "file_name": str(file_name or ""),
+            "conversation_id": str(getattr(msg, "conversation_id", "") or ""),
+            "session_webhook": str(getattr(msg, "session_webhook", "") or ""),
+            "sender_staff_id": str(getattr(msg, "sender_staff_id", "") or ""),
+            "detail": normalized_detail[: self.excel_alert_detail_limit],
+            "stderr": str(stderr_text or "")[: self.excel_alert_detail_limit],
+            "stdout": str(stdout_text or "")[: self.excel_alert_detail_limit],
+        }
+        self.logger.warning(
+            "excel failure alert stage=%s digest=%s file=%s summary=%s",
+            stage,
+            digest,
+            file_name,
+            summary,
+        )
+        if not self.excel_alert_log_enabled:
+            return
+        try:
+            parent_dir = os.path.dirname(self.excel_alert_log_path) or "."
+            os.makedirs(parent_dir, exist_ok=True)
+            line = json.dumps(log_record, ensure_ascii=False)
+            with self._excel_alert_log_lock:
+                with open(self.excel_alert_log_path, "a", encoding="utf-8") as file_obj:
+                    file_obj.write(line + "\n")
+        except Exception:
+            self.logger.exception("write excel alert log failed")
+
+    def _run_excel_image_link_job(self, msg: ChatbotMessage, file_payload: dict) -> tuple[str, bool]:
+        if not self.excel_link_feature_enabled:
+            return "Excel 图片链接转图片功能未启用。", False
+        script_path = self.excel_converter_script
+        if not os.path.exists(script_path):
+            return f"转换脚本不存在：{script_path}", False
+
+        download_code = str(file_payload.get("download_code") or "").strip()
+        download_url = str(file_payload.get("download_url") or "").strip()
+        file_name = self._normalize_file_name(file_payload.get("file_name") or "upload.xlsx")
+        if not download_code and not download_url:
+            return "未获取到文件下载地址，请重试上传。", False
+        if not self._is_supported_excel_file(file_name):
+            return "当前仅支持 .xlsx 文件，请上传Excel后重试。", False
+
+        if not download_url:
+            download_url = self.get_image_download_url(download_code)
+            if not download_url:
+                return "文件下载地址获取失败，请重试。", False
+
+        with tempfile.TemporaryDirectory(prefix="dingtalk_excel_link_") as tmpdir:
+            input_path = os.path.join(tmpdir, file_name)
+            output_name = f"{os.path.splitext(file_name)[0]}_图片回填.xlsx"
+            output_path = os.path.join(tmpdir, output_name)
+            try:
+                response = requests.get(download_url, timeout=self.excel_file_download_timeout)
+                response.raise_for_status()
+                with open(input_path, "wb") as fh:
+                    fh.write(response.content)
+            except Exception as exc:
+                self.logger.exception("download incoming excel failed")
+                self._record_excel_failure_alert(
+                    msg,
+                    file_name,
+                    stage="download_source_excel",
+                    detail=f"{type(exc).__name__}: {exc}",
+                )
+                return f"下载源文件失败：{exc}", False
+
+            cmd = [
+                sys.executable,
+                script_path,
+                "--input",
+                input_path,
+                "--output",
+                output_path,
+                "--link-column",
+                self.excel_link_column,
+                "--image-column-header",
+                self.excel_image_column_header,
+                "--max-width",
+                str(self.excel_image_max_width),
+                "--max-height",
+                str(self.excel_image_max_height),
+                "--timeout",
+                str(self.excel_image_download_timeout),
+            ]
+            if self.excel_skip_tls_verify:
+                cmd.append("--skip-tls-verify")
+            try:
+                proc = subprocess.run(
+                    cmd,
+                    capture_output=True,
+                    text=True,
+                    timeout=self.excel_converter_timeout,
+                )
+            except subprocess.TimeoutExpired:
+                self._record_excel_failure_alert(
+                    msg,
+                    file_name,
+                    stage="excel_converter_timeout",
+                    detail=f"timeout>{self.excel_converter_timeout}s",
+                )
+                return f"Excel 转换超时（>{self.excel_converter_timeout}秒），请稍后重试。", False
+
+            stdout_text = (proc.stdout or "").strip()
+            stderr_text = (proc.stderr or "").strip()
+            if proc.returncode != 0 or not os.path.exists(output_path):
+                detail = stderr_text or stdout_text or "未知错误"
+                self.logger.error("excel converter failed code=%s detail=%s", proc.returncode, detail[:2500])
+                self._record_excel_failure_alert(
+                    msg,
+                    file_name,
+                    stage="excel_converter_failed",
+                    detail=detail,
+                    return_code=proc.returncode,
+                    stdout_text=stdout_text,
+                    stderr_text=stderr_text,
+                )
+                return f"Excel 转换失败：{detail[:1200]}", False
+
+            summary_line = ""
+            for line in stdout_text.splitlines():
+                if line.startswith("SUMMARY:"):
+                    summary_line = line.replace("SUMMARY:", "").strip()
+                    break
+
+            media_id = ""
+            try:
+                with open(output_path, "rb") as fh:
+                    media_content = fh.read()
+                media_id = self.dingtalk_client.upload_to_dingtalk(
+                    media_content,
+                    filetype="file",
+                    filename=output_name,
+                    mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
+                ) or ""
+            except Exception as exc:
+                self.logger.exception("upload converted excel failed")
+
+            sent = self._reply_file_by_media_id(msg, media_id, output_name) if media_id else False
+            if not sent:
+                s3_url = self._upload_excel_output_to_s3(output_path, output_name)
+                if s3_url:
+                    return f"钉钉文件回传受限，已生成下载链接：{s3_url}", True
+                return "处理完成但回传文件失败，请稍后重试。", False
+
+            summary_text = summary_line or "已完成图片回填。"
+            return f"文件已回传：{output_name}\n处理结果：{summary_text}", True
+
     def _is_image_url(self, raw_url: str) -> bool:
         try:
             parsed = urllib.parse.urlparse(raw_url)
@@ -2210,16 +2655,37 @@ class OpenClawDingTalkBot(ChatbotHandler):
             text = text_list[0].strip() if text_list else ""
 
         incoming_image_codes = self._extract_incoming_download_codes(msg)
+        incoming_file_payload = self._extract_incoming_file_payload(callback.data, msg)
+        has_excel_file = bool(incoming_file_payload)
+        conversation_key = re.sub(r"[^A-Za-z0-9_-]+", "-", str(getattr(msg, "conversation_id", "") or "default"))
+        self.logger.info(
+            "incoming message: type=%s has_excel_file=%s has_image=%s text_len=%s",
+            str(getattr(msg, "message_type", "")),
+            has_excel_file,
+            bool(incoming_image_codes),
+            len(text or ""),
+        )
+        if str(getattr(msg, "message_type", "")).lower() == "file" and not has_excel_file:
+            callback_keys = []
+            if isinstance(callback.data, dict):
+                callback_keys = list(callback.data.keys())[:15]
+            self.logger.warning(
+                "file message without excel payload: callback_keys=%s extensions_keys=%s",
+                callback_keys,
+                list((getattr(msg, "extensions", {}) or {}).keys())[:15],
+            )
 
-        if not text and not incoming_image_codes:
+        if not text and not incoming_image_codes and not has_excel_file:
             return AckMessage.STATUS_OK, "ignored"
 
-        if not self._should_handle(msg, text):
+        if not has_excel_file and not self._should_handle(msg, text):
             return AckMessage.STATUS_OK, "ignored"
 
         prompt = self._normalize_prompt(text)
         if not prompt:
-            if incoming_image_codes:
+            if has_excel_file:
+                prompt = "请处理我上传的Excel文件，把图片链接转成图片并回传。"
+            elif incoming_image_codes:
                 prompt = "请识别并分析我上传的图片内容，输出结论和建议。"
             else:
                 self._reply_message(
@@ -2228,6 +2694,14 @@ class OpenClawDingTalkBot(ChatbotHandler):
                     prefer_card=False,
                 )
                 return AckMessage.STATUS_OK, "ok"
+        elif not has_excel_file and self.excel_link_feature_enabled and self._is_excel_link_prompt(prompt):
+            now_ts = time.time()
+            last_ts = self._recent_excel_success_ts.get(conversation_key, 0)
+            if last_ts and now_ts - last_ts <= self.excel_text_prompt_cooldown_seconds:
+                self._reply_message(msg, "文件刚处理完成，请查看上一条回传文件。", prefer_card=False)
+            else:
+                self._reply_message(msg, "请直接上传 .xlsx 文件（包含“图片链接”列），我会自动回填并回传。", prefer_card=False)
+            return AckMessage.STATUS_OK, "excel_text_prompt_hint"
 
         event_key = self._event_key(callback, msg, prompt)
         if self._seen_or_mark_event(event_key):
@@ -2253,7 +2727,6 @@ class OpenClawDingTalkBot(ChatbotHandler):
             except Exception:
                 self.logger.exception("failed to send processing hint")
 
-        conversation_key = re.sub(r"[^A-Za-z0-9_-]+", "-", str(getattr(msg, "conversation_id", "") or "default"))
         conv_lock = self._conversation_locks.setdefault(conversation_key, asyncio.Lock())
 
         async with conv_lock:
@@ -2268,7 +2741,23 @@ class OpenClawDingTalkBot(ChatbotHandler):
                         conversation_key,
                         self.max_parallel_jobs,
                     )
-                    if incoming_image_codes and not self._is_install_media_prompt(prompt):
+                    if has_excel_file:
+                        file_answer, file_sent = await asyncio.to_thread(
+                            self._run_excel_image_link_job,
+                            msg,
+                            incoming_file_payload,
+                        )
+                        if file_sent:
+                            self._recent_excel_success_ts[conversation_key] = time.time()
+                            self._reply_message(msg, file_answer, prefer_card=False)
+                            self.logger.info(
+                                "excel file processed: session=%s file=%s",
+                                session_id,
+                                incoming_file_payload.get("file_name", ""),
+                            )
+                            return AckMessage.STATUS_OK, "excel_file_ok"
+                        answer = file_answer
+                    elif incoming_image_codes and not self._is_install_media_prompt(prompt):
                         image_context_result = await asyncio.to_thread(
                             self._build_image_context_from_dingtalk,
                             msg,
@@ -2278,17 +2767,18 @@ class OpenClawDingTalkBot(ChatbotHandler):
                         context_text = image_context_result.get("context_text", "")
                         if context_text:
                             prompt_for_exec = self._merge_prompt_with_image_context(prompt, context_text)
-                    if self._is_install_media_prompt(prompt):
-                        answer = await asyncio.to_thread(self._run_install_media_lookup, prompt)
-                    elif self._is_dalongxia_analytics_prompt(prompt):
-                        answer = await asyncio.to_thread(self._run_dalongxia_analytics, prompt)
-                    elif self._is_video_analysis_prompt(prompt):
-                        answer = await asyncio.to_thread(self._run_video_analysis, prompt)
-                    elif self._is_market_prompt(prompt):
-                        subject = self._extract_market_subject(prompt)
-                        answer = await asyncio.to_thread(self._run_market_brief, subject)
-                    else:
-                        answer = await asyncio.to_thread(self._run_openclaw, session_id, prompt_for_exec)
+                    if not has_excel_file:
+                        if self._is_install_media_prompt(prompt):
+                            answer = await asyncio.to_thread(self._run_install_media_lookup, prompt)
+                        elif self._is_dalongxia_analytics_prompt(prompt):
+                            answer = await asyncio.to_thread(self._run_dalongxia_analytics, prompt)
+                        elif self._is_video_analysis_prompt(prompt):
+                            answer = await asyncio.to_thread(self._run_video_analysis, prompt)
+                        elif self._is_market_prompt(prompt):
+                            subject = self._extract_market_subject(prompt)
+                            answer = await asyncio.to_thread(self._run_market_brief, subject)
+                        else:
+                            answer = await asyncio.to_thread(self._run_openclaw, session_id, prompt_for_exec)
             except Exception:
                 self.logger.exception("worker execution failed: session=%s", session_id)
                 answer = "系统执行异常，请稍后重试。"

remote_sync/skills/dingtalk-excel-image-return/SKILL.md

@@ -0,0 +1,68 @@
+---
+name: dingtalk-excel-image-return
+description: 在钉钉应用机器人（推荐 Stream 模式）场景处理用户上传的 Excel（.xlsx）文件：识别图片链接列，下载链接图片并插入新增图片列，再上传并回传结果文件。用户提到“钉钉机器人”“上传表格”“图片链接转图片”“新增图片列”“回传文件”“批量插图”“Excel 图片列”时使用此技能；涉及钉钉文件下载、Excel 图片写入、文件回传时优先使用此技能。
+---
+
+# dingtalk-excel-image-return
+
+## 触发关键词
+
+优先匹配以下关键词或等价表达：
+
+- `钉钉机器人`
+- `上传表格`
+- `Excel 图片链接`
+- `图片链接转图片`
+- `新增图片列`
+- `回传文件`
+- `批量插图`
+
+## 执行流程（最稳方案）
+
+1. 使用钉钉应用机器人接收消息，优先使用 Stream 模式。
+2. 在用户与机器人单聊中接收 `file` 消息，提取 `downloadCode`。
+3. 调用钉钉文件下载接口获取原始 `.xlsx`。
+4. 运行 `scripts/convert_excel_image_links.py` 处理表格。
+5. 上传结果文件到钉钉文件存储。
+6. 调用会话文件发送接口回传结果。
+
+如果用户要求“群里 @ 机器人并直接传文件”，先提示群聊文件接收限制，再引导改为单聊上传，或提供上传入口卡片。
+
+## Excel 处理命令
+
+执行：
+
+```bash
+python3 scripts/convert_excel_image_links.py \
+  --input /tmp/input.xlsx \
+  --output /tmp/output.xlsx \
+  --link-column 图片链接 \
+  --image-column-header 图片
+```
+
+常用参数：
+
+- `--link-column`：链接列（表头名或列字母，如 `C`）
+- `--header-row`：表头行号（默认 `1`）
+- `--sheet`：只处理指定工作表（可重复）
+- `--max-width` / `--max-height`：限制图片尺寸
+- `--skip-tls-verify`：图片链接为自签名证书时临时使用
+
+## 钉钉接口编排
+
+按顺序调用：
+
+1. 获取用户上传文件（通过 `downloadCode`）。
+2. 调用下载接口拉取原表格。
+3. 本地转换并生成新文件。
+4. 上传新文件。
+5. 发送文件消息回会话。
+
+接口详情、字段和最小实现建议见 `references/dingtalk-workflow.md`。
+
+## 失败兜底
+
+- 下载图片失败时不中断整表，记录失败行并继续。
+- 非 `http/https` 链接标记为失败。
+- 处理完成后始终输出结果文件；失败行通过日志返回。
+- 涉及认证或证书错误时先打印明确错误，再提示运维修复证书/权限。

remote_sync/skills/dingtalk-excel-image-return/agents/openai.yaml

@@ -0,0 +1,4 @@
+interface:
+  display_name: "钉钉表格图片回填"
+  short_description: "把钉钉Excel里的图片链接批量转成嵌入图片并自动回传"
+  default_prompt: "Use $dingtalk-excel-image-return to convert image URLs in an uploaded .xlsx file into embedded images and return the processed file."

remote_sync/skills/dingtalk-excel-image-return/references/dingtalk-workflow.md

@@ -0,0 +1,49 @@
+# DingTalk workflow for Excel image-link conversion
+
+## 1) Recommended architecture
+
+- Use an **app bot** (not a group custom bot).
+- Use **Stream mode** to receive messages.
+- Prefer **1:1 chat** for file uploads.
+
+## 2) End-to-end API sequence
+
+1. Receive message event (`msgtype=file`) and parse:
+   - `conversationId`
+   - `senderStaffId`
+   - `downloadCode`
+   - original `fileName`
+2. Download source file:
+   - `POST /v1.0/robot/messageFiles/download`
+   - Body includes `downloadCode`.
+3. Run local conversion script:
+   - `scripts/convert_excel_image_links.py`
+4. Upload output file to DingTalk file service (get file storage identifiers).
+5. Send output file back:
+   - `POST /v1.0/convFile/conversations/files/send`
+   - Include `conversationId` and uploaded file info.
+
+## 3) Message handling policy
+
+- Reject non-`.xlsx` files with clear error text.
+- If file size exceeds service limit, return size-limit guidance.
+- Keep idempotency key (`conversationId + messageId`) to avoid duplicate processing.
+- Return progress checkpoints in long jobs: received -> processing -> done/failed.
+
+## 4) Security and reliability
+
+- Keep app secrets in env vars; never hardcode credentials.
+- Restrict outbound image downloads with timeout and max size.
+- Allow partial success for broken image URLs.
+- Record each failed row with reason for troubleshooting.
+
+## 5) Suggested runtime contract
+
+Input contract:
+- `.xlsx` file
+- URL column header default: `图片链接`
+
+Output contract:
+- New `.xlsx` with inserted column header default: `图片`
+- Same sheet names and original data preserved
+- Failed URL rows logged

remote_sync/skills/dingtalk-excel-image-return/scripts/convert_excel_image_links.py

@@ -0,0 +1,505 @@
+#!/usr/bin/env python3
+from __future__ import annotations
+
+import argparse
+import io
+import mimetypes
+import re
+import ssl
+import tempfile
+import zipfile
+import urllib.error
+import urllib.parse
+import urllib.request
+from dataclasses import dataclass
+from pathlib import Path
+from typing import Iterable
+
+from openpyxl import load_workbook
+from openpyxl.drawing.image import Image as XLImage
+from openpyxl.packaging import manifest as openpyxl_manifest
+from openpyxl.utils import column_index_from_string, get_column_letter
+try:
+    from PIL import Image as PILImage
+except Exception:  # noqa: BLE001
+    PILImage = None
+
+
+CONTENT_TYPE_TO_EXT = {
+    "image/png": ".png",
+    "image/jpeg": ".jpg",
+    "image/jpg": ".jpg",
+    "image/webp": ".webp",
+    "image/bmp": ".bmp",
+    "image/gif": ".gif",
+}
+
+SUPPORTED_EXTENSIONS = {".png", ".jpg", ".jpeg", ".webp", ".bmp", ".gif"}
+EXCEL_SAFE_EXTENSIONS = {".png", ".jpg", ".jpeg", ".bmp", ".gif"}
+UNSAFE_OR_UNKNOWN_EXTENSIONS = {".webp", ".tif", ".tiff", ".heic", ".avif"}
+IMAGE_HINT_HEADERS = (
+    "图片链接",
+    "商品图片",
+    "主图链接",
+    "图片url",
+    "image_url",
+    "image url",
+    "图片",
+)
+
+
+def _iter_mimetype_modules():
+    yield mimetypes
+    compat = getattr(openpyxl_manifest, "mimetypes", None)
+    if compat is not None:
+        yield compat
+
+
+def _resolve_type_maps(module) -> tuple[dict, dict]:
+    type_maps = getattr(module, "types_map", {})
+    if isinstance(type_maps, tuple) and len(type_maps) >= 2:
+        loose_map, strict_map = type_maps[0], type_maps[1]
+        if isinstance(strict_map, dict) and isinstance(loose_map, dict):
+            return strict_map, loose_map
+    if (
+        isinstance(type_maps, dict)
+        and isinstance(type_maps.get(True), dict)
+        and isinstance(type_maps.get(False), dict)
+    ):
+        return type_maps[True], type_maps[False]
+    if isinstance(type_maps, dict):
+        loose_map = getattr(module, "common_types", {})
+        if not isinstance(loose_map, dict):
+            loose_map = type_maps
+        return type_maps, loose_map
+    return {}, {}
+
+
+def _register_type(module, extension: str, mime_type: str) -> None:
+    add_type = getattr(module, "add_type", None)
+    if callable(add_type):
+        try:
+            add_type(mime_type, extension, strict=True)
+        except TypeError:
+            try:
+                add_type(mime_type, extension)
+            except Exception:
+                pass
+        except Exception:
+            pass
+        try:
+            add_type(mime_type, extension, strict=False)
+        except Exception:
+            pass
+
+    strict_map, loose_map = _resolve_type_maps(module)
+    strict_map[extension] = mime_type
+    loose_map.setdefault(extension, mime_type)
+
+
+def ensure_mimetype_for_ext(ext: str) -> None:
+    normalized_raw = str(ext or "").strip()
+    if not normalized_raw:
+        return
+
+    if not normalized_raw.startswith("."):
+        normalized_raw = f".{normalized_raw}"
+    normalized = normalized_raw.lower()
+
+    guessed = mimetypes.types_map.get(normalized)
+    if not guessed:
+        guessed = mimetypes.common_types.get(normalized)
+    if not guessed:
+        guessed, _ = mimetypes.guess_type(f"x{normalized}", strict=False)
+    if not guessed and normalized in {".webp", ".WEBP"}:
+        guessed = "image/webp"
+    if not guessed:
+        guessed = "application/octet-stream"
+
+    variants = {normalized}
+    variants.add(normalized_raw)
+    if normalized_raw != normalized:
+        variants.add(normalized_raw.upper())
+
+    for module in _iter_mimetype_modules():
+        for variant in variants:
+            _register_type(module, variant, guessed)
+
+
+ensure_mimetype_for_ext(".webp")
+
+
+def register_mimetypes_from_archive(path: Path) -> None:
+    try:
+        with zipfile.ZipFile(path, "r") as archive:
+            for name in archive.namelist():
+                suffix = Path(name).suffix
+                if suffix:
+                    ensure_mimetype_for_ext(suffix)
+    except Exception:
+        return
+
+
+@dataclass
+class RowResult:
+    row: int
+    url: str
+    status: str
+    detail: str
+
+
+def parse_args() -> argparse.Namespace:
+    parser = argparse.ArgumentParser(
+        description="Download image URLs from an Excel column and insert images into a new column."
+    )
+    parser.add_argument("--input", required=True, help="Input .xlsx path")
+    parser.add_argument("--output", required=True, help="Output .xlsx path")
+    parser.add_argument(
+        "--link-column",
+        default="图片链接",
+        help="URL column by header text or column letter (default: 图片链接)",
+    )
+    parser.add_argument(
+        "--image-column-header",
+        default="图片",
+        help="Header value for inserted image column",
+    )
+    parser.add_argument(
+        "--header-row",
+        type=int,
+        default=1,
+        help="Header row number (default: 1)",
+    )
+    parser.add_argument(
+        "--start-row",
+        type=int,
+        default=0,
+        help="Start processing row (default: header_row + 1)",
+    )
+    parser.add_argument(
+        "--sheet",
+        action="append",
+        default=[],
+        help="Sheet name to process (repeatable). Default: process all sheets",
+    )
+    parser.add_argument(
+        "--max-width",
+        type=int,
+        default=140,
+        help="Max image width in pixels (default: 140)",
+    )
+    parser.add_argument(
+        "--max-height",
+        type=int,
+        default=140,
+        help="Max image height in pixels (default: 140)",
+    )
+    parser.add_argument(
+        "--timeout",
+        type=int,
+        default=15,
+        help="Image download timeout in seconds (default: 15)",
+    )
+    parser.add_argument(
+        "--skip-tls-verify",
+        action="store_true",
+        help="Disable TLS certificate verification for image URLs",
+    )
+    return parser.parse_args()
+
+
+def resolve_link_column(sheet, header_row: int, link_column: str) -> int:
+    column_spec = link_column.strip()
+    if re.fullmatch(r"[A-Za-z]{1,3}", column_spec):
+        return column_index_from_string(column_spec.upper())
+
+    for col_idx in range(1, sheet.max_column + 1):
+        value = sheet.cell(row=header_row, column=col_idx).value
+        if value is None:
+            continue
+        if str(value).strip() == column_spec:
+            return col_idx
+
+    guessed = guess_link_column(sheet, header_row)
+    if guessed is not None:
+        return guessed
+
+    raise ValueError(
+        f"Sheet '{sheet.title}' cannot find link column '{link_column}' in header row {header_row}."
+    )
+
+
+def looks_like_http_url(value: str) -> bool:
+    return str(value or "").lower().startswith(("http://", "https://"))
+
+
+def looks_like_image_url(url: str) -> bool:
+    raw = str(url or "").strip().lower()
+    if not looks_like_http_url(raw):
+        return False
+    parsed = urllib.parse.urlparse(raw)
+    path = parsed.path or ""
+    host = parsed.hostname or ""
+    if any(path.endswith(ext) for ext in SUPPORTED_EXTENSIONS):
+        return True
+    if host.startswith("img.") or "image" in host:
+        return True
+    if any(token in path for token in ("/img", "/image", "/upload")):
+        return True
+    return False
+
+
+def guess_link_column(sheet, header_row: int) -> int | None:
+    max_scan_rows = min(sheet.max_row, header_row + 30)
+    best_col = None
+    best_score = -1
+
+    for col_idx in range(1, sheet.max_column + 1):
+        header_val = str(sheet.cell(row=header_row, column=col_idx).value or "").strip().lower()
+        score = 0
+        if header_val:
+            if header_val in IMAGE_HINT_HEADERS:
+                score += 40
+            if "图片" in header_val:
+                score += 20
+            if "链接" in header_val or "url" in header_val:
+                score += 15
+
+        http_count = 0
+        image_like_count = 0
+        for row in range(header_row + 1, max_scan_rows + 1):
+            cell_value = sheet.cell(row=row, column=col_idx).value
+            if cell_value is None:
+                continue
+            text = str(cell_value).strip()
+            if not text:
+                continue
+            if looks_like_http_url(text):
+                http_count += 1
+            if looks_like_image_url(text):
+                image_like_count += 1
+
+        score += http_count * 3 + image_like_count * 6
+        if image_like_count == 0 and http_count == 0 and score < 20:
+            continue
+
+        if score > best_score:
+            best_score = score
+            best_col = col_idx
+
+    return best_col
+
+
+def choose_sheets(workbook, names: Iterable[str]):
+    if not names:
+        return list(workbook.worksheets)
+
+    chosen = []
+    existing = {ws.title: ws for ws in workbook.worksheets}
+    for name in names:
+        if name not in existing:
+            raise ValueError(f"Sheet '{name}' does not exist.")
+        chosen.append(existing[name])
+    return chosen
+
+
+def build_ssl_context(skip_verify: bool):
+    if not skip_verify:
+        return None
+    context = ssl.create_default_context()
+    context.check_hostname = False
+    context.verify_mode = ssl.CERT_NONE
+    return context
+
+
+def guess_extension(url: str, content_type: str | None) -> str:
+    parsed = urllib.parse.urlparse(url)
+    suffix = Path(parsed.path).suffix.lower()
+    if suffix in SUPPORTED_EXTENSIONS:
+        return suffix
+
+    if content_type:
+        clean_content_type = content_type.split(";")[0].strip().lower()
+        if clean_content_type in CONTENT_TYPE_TO_EXT:
+            return CONTENT_TYPE_TO_EXT[clean_content_type]
+
+    return ".png"
+
+
+def download_image(url: str, timeout: int, ssl_context):
+    request = urllib.request.Request(
+        url,
+        headers={"User-Agent": "Mozilla/5.0 CodexSkill/1.0"},
+    )
+    with urllib.request.urlopen(request, timeout=timeout, context=ssl_context) as response:
+        content_type = response.headers.get("Content-Type")
+        data = response.read()
+    return data, guess_extension(url, content_type)
+
+
+def to_excel_safe_image(payload: bytes, extension: str) -> tuple[bytes, str]:
+    ext = (extension or "").lower()
+    ensure_mimetype_for_ext(ext)
+    if ext in EXCEL_SAFE_EXTENSIONS and ext not in UNSAFE_OR_UNKNOWN_EXTENSIONS:
+        return payload, ext
+
+    if PILImage is None:
+        return payload, ".png"
+
+    with PILImage.open(io.BytesIO(payload)) as image:
+        if image.mode not in {"RGB", "RGBA"}:
+            image = image.convert("RGBA")
+        output = io.BytesIO()
+        image.save(output, format="PNG")
+        return output.getvalue(), ".png"
+
+
+def pixels_to_excel_width(px: int) -> float:
+    return max(12.0, round((px + 12) / 7, 2))
+
+
+def pixels_to_points(px: int) -> float:
+    return round(px * 0.75, 2)
+
+
+def resize_image(image: XLImage, max_width: int, max_height: int) -> None:
+    width = image.width or max_width
+    height = image.height or max_height
+    scale = min(max_width / width, max_height / height, 1.0)
+    image.width = int(width * scale)
+    image.height = int(height * scale)
+
+
+def process_sheet(sheet, args: argparse.Namespace, ssl_context, temp_files: list[Path]) -> list[RowResult]:
+    results: list[RowResult] = []
+
+    header_row = args.header_row
+    start_row = args.start_row if args.start_row > 0 else header_row + 1
+
+    link_column_idx = resolve_link_column(sheet, header_row, args.link_column)
+    image_column_idx = link_column_idx + 1
+    sheet.insert_cols(image_column_idx)
+    sheet.cell(row=header_row, column=image_column_idx, value=args.image_column_header)
+
+    image_column_letter = get_column_letter(image_column_idx)
+    column_width = pixels_to_excel_width(args.max_width)
+    existing_width = sheet.column_dimensions[image_column_letter].width
+    if existing_width is None or existing_width < column_width:
+        sheet.column_dimensions[image_column_letter].width = column_width
+
+    for row in range(start_row, sheet.max_row + 1):
+        source = sheet.cell(row=row, column=link_column_idx).value
+        if source is None:
+            continue
+
+        url = str(source).strip()
+        if not url:
+            continue
+        if not url.lower().startswith(("http://", "https://")):
+            results.append(RowResult(row=row, url=url, status="failed", detail="invalid_url"))
+            continue
+
+        try:
+            payload, extension = download_image(url, args.timeout, ssl_context)
+            payload, extension = to_excel_safe_image(payload, extension)
+            with tempfile.NamedTemporaryFile(delete=False, suffix=extension) as temp_file:
+                temp_file.write(payload)
+                temp_path = Path(temp_file.name)
+            temp_files.append(temp_path)
+
+            image = XLImage(str(temp_path))
+            resize_image(image, args.max_width, args.max_height)
+
+            anchor = f"{image_column_letter}{row}"
+            sheet.add_image(image, anchor)
+
+            needed_height = pixels_to_points(image.height + 8)
+            current_height = sheet.row_dimensions[row].height
+            if current_height is None or current_height < needed_height:
+                sheet.row_dimensions[row].height = needed_height
+
+            results.append(RowResult(row=row, url=url, status="ok", detail="inserted"))
+        except urllib.error.URLError as error:
+            results.append(RowResult(row=row, url=url, status="failed", detail=f"download_error:{error.reason}"))
+        except Exception as error:  # noqa: BLE001
+            results.append(RowResult(row=row, url=url, status="failed", detail=f"image_error:{error}"))
+
+    return results
+
+
+def drop_broken_images(workbook) -> int:
+    removed = 0
+    for sheet in workbook.worksheets:
+        existing = list(getattr(sheet, "_images", []))
+        if not existing:
+            continue
+        kept = []
+        removed_in_sheet = 0
+        for image in existing:
+            try:
+                image._data()
+                kept.append(image)
+            except Exception:
+                removed += 1
+                removed_in_sheet += 1
+        if removed_in_sheet:
+            sheet._images = kept
+    return removed
+
+
+def main() -> int:
+    args = parse_args()
+
+    input_path = Path(args.input)
+    output_path = Path(args.output)
+
+    if input_path.suffix.lower() != ".xlsx":
+        print("ERROR: input must be .xlsx")
+        return 1
+    if not input_path.exists():
+        print(f"ERROR: input does not exist: {input_path}")
+        return 1
+
+    register_mimetypes_from_archive(input_path)
+    ssl_context = build_ssl_context(args.skip_tls_verify)
+
+    workbook = load_workbook(input_path)
+    sheets = choose_sheets(workbook, args.sheet)
+    temp_files: list[Path] = []
+
+    all_results: list[tuple[str, RowResult]] = []
+    for sheet in sheets:
+        sheet_results = process_sheet(sheet, args, ssl_context, temp_files)
+        all_results.extend((sheet.title, item) for item in sheet_results)
+
+    output_path.parent.mkdir(parents=True, exist_ok=True)
+    try:
+        try:
+            workbook.save(output_path)
+        except Exception as save_error:
+            removed = drop_broken_images(workbook)
+            if removed <= 0:
+                raise
+            print(f"WARN: dropped_broken_images={removed} reason={save_error}")
+            workbook.save(output_path)
+    finally:
+        for file_path in temp_files:
+            file_path.unlink(missing_ok=True)
+
+    success_count = sum(1 for _, item in all_results if item.status == "ok")
+    fail_count = sum(1 for _, item in all_results if item.status == "failed")
+
+    print(f"DONE: output={output_path}")
+    print(f"SUMMARY: success={success_count} failed={fail_count}")
+
+    for sheet_name, result in all_results:
+        if result.status == "failed":
+            print(
+                f"FAILED: sheet={sheet_name} row={result.row} url={result.url} reason={result.detail}"
+            )
+
+    return 0
+
+
+if __name__ == "__main__":
+    raise SystemExit(main())