Skip to content
TheChatArchiveThe Chat Archive
Claudeclaude-opus-4-20250514· AnthropicHugging Face LMArena Human Preference 140K

帮我编写一份python程序可以用来调用AI的API接口读取excel表格并分析题目的风险程度并进行评分,导出为excel表格。

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Geminigemini-2.5-flash-lite-preview-06-17-thinking’s answer.
  1. User

    帮我编写一份python程序可以用来调用AI的API接口读取excel表格并分析题目的风险程度并进行评分,导出为excel表格。

  2. Claude Assistant

    我来帮你编写一个Python程序,用于读取Excel表格中的题目,调用AI API进行风险分析,并将结果导出到新的Excel表格。

    import requests
    import pandas as pd
    import json
    import time
    from datetime import datetime
    import os
    
    # --- 配置区 ---
    
    # AI API配置
    AI_API_URL = "YOUR_AI_API_ENDPOINT"  # 例如: "https://api.openai.com/v1/chat/completions"
    AI_API_KEY = "YOUR_API_KEY"  # 你的API密钥
    AI_MODEL = "gpt-3.5-turbo"  # 或其他模型名称
    
    # 输入输出文件配置
    INPUT_EXCEL_FILE = "questions.xlsx"  # 输入的Excel文件名
    INPUT_SHEET_NAME = "Sheet1"  # Excel中的工作表名称
    QUESTION_COLUMN = "题目"  # 包含题目的列名
    OUTPUT_EXCEL_FILE = f"risk_analysis_results_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    
    # API请求配置
    REQUEST_TIMEOUT = 30  # 请求超时时间(秒)
    RETRY_TIMES = 3  # 重试次数
    DELAY_BETWEEN_REQUESTS = 1  # 请求之间的延迟(秒),避免触发速率限制
    
    # 风险评估提示词模板
    RISK_ANALYSIS_PROMPT = """
    请分析以下题目的风险程度,并从以下几个维度进行评估:
    
    1. 内容敏感度(0-10分):是否涉及敏感话题、争议性内容
    2. 法律合规性(0-10分):是否可能涉及法律风险
    3. 道德伦理(0-10分):是否涉及道德伦理问题
    4. 信息安全(0-10分):是否可能泄露敏感信息
    5. 社会影响(0-10分):可能产生的社会影响
    
    题目:{question}
    
    请以JSON格式返回分析结果,包含以下字段:
    {{
        "content_sensitivity": 分数,
        "legal_compliance": 分数,
        "ethical_concerns": 分数,
        "information_security": 分数,
        "social_impact": 分数,
        "total_risk_score": 总风险分数(0-100),
        "risk_level": "低风险/中风险/高风险",
        "risk_factors": ["具体风险因素1", "具体风险因素2"],
        "recommendations": "建议措施"
    }}
    """
    
    # --- 功能函数 ---
    
    def read_excel_questions(file_path, sheet_name, column_name):
        """从Excel文件读取题目列表"""
        try:
            print(f"正在读取Excel文件: {file_path}")
            df = pd.read_excel(file_path, sheet_name=sheet_name)
            
            if column_name not in df.columns:
                print(f"错误:找不到列 '{column_name}'")
                print(f"可用的列: {', '.join(df.columns)}")
                return None
                
            questions = df[column_name].dropna().tolist()
            print(f"成功读取 {len(questions)} 个题目")
            return questions, df
            
        except FileNotFoundError:
            print(f"错误:找不到文件 '{file_path}'")
            return None
        except Exception as e:
            print(f"错误:读取Excel文件时出错 - {e}")
            return None
    
    def call_ai_api(question, retry_count=0):
        """调用AI API进行风险分析"""
        headers = {
            "Authorization": f"Bearer {AI_API_KEY}",
            "Content-Type": "application/json"
        }
        
        # 构建请求数据(以OpenAI格式为例)
        data = {
            "model": AI_MODEL,
            "messages": [
                {
                    "role": "system",
                    "content": "你是一个专业的风险评估专家,擅长分析各类题目和内容的潜在风险。"
                },
                {
                    "role": "user",
                    "content": RISK_ANALYSIS_PROMPT.format(question=question)
                }
            ],
            "temperature": 0.3,  # 降低温度以获得更一致的结果
            "max_tokens": 500
        }
        
        try:
            print(f"正在分析题目: {question[:50]}...")
            response = requests.post(
                AI_API_URL,
                headers=headers,
                json=data,
                timeout=REQUEST_TIMEOUT
            )
            
            if response.status_code == 200:
                result = response.json()
                # 提取AI的回复内容(根据不同API调整)
                ai_response = result['choices'][0]['message']['content']
                
                # 尝试解析JSON响应
                try:
                    # 清理可能的markdown代码块标记
                    ai_response = ai_response.strip()
                    if ai_response.startswith("```json"):
                        ai_response = ai_response[7:]
                    if ai_response.endswith("```"):
                        ai_response = ai_response[:-3]
                    
                    analysis_result = json.loads(ai_response.strip())
                    return analysis_result
                    
                except json.JSONDecodeError:
                    print(f"警告:AI响应不是有效的JSON格式,尝试提取信息")
                    # 如果不是JSON,返回默认结构
                    return {
                        "content_sensitivity": 0,
                        "legal_compliance": 0,
                        "ethical_concerns": 0,
                        "information_security": 0,
                        "social_impact": 0,
                        "total_risk_score": 0,
                        "risk_level": "解析失败",
                        "risk_factors": ["无法解析AI响应"],
                        "recommendations": ai_response[:200]
                    }
            
            elif response.status_code == 429:  # 速率限制
                if retry_count < RETRY_TIMES:
                    wait_time = (retry_count + 1) * 5
                    print(f"遇到速率限制,等待 {wait_time} 秒后重试...")
                    time.sleep(wait_time)
                    return call_ai_api(question, retry_count + 1)
                else:
                    print(f"错误:超过最大重试次数")
                    return None
                    
            else:
                print(f"错误:API请求失败,状态码: {response.status_code}")
                print(f"响应内容: {response.text}")
                return None
                
        except requests.exceptions.Timeout:
            print(f"错误:请求超时")
            if retry_count < RETRY_TIMES:
                return call_ai_api(question, retry_count + 1)
            return None
            
        except Exception as e:
            print(f"错误:调用API时出错 - {e}")
            return None
    
    def analyze_questions(questions):
        """批量分析题目"""
        results = []
        total = len(questions)
        
        for i, question in enumerate(questions, 1):
            print(f"\n进度: {i}/{total}")
            
            # 调用AI API
            analysis = call_ai_api(question)
            
            if analysis:
                # 整理结果
                result = {
                    "题目": question,
                    "内容敏感度": analysis.get("content_sensitivity", 0),
                    "法律合规性": analysis.get("legal_compliance", 0),
                    "道德伦理": analysis.get("ethical_concerns", 0),
                    "信息安全": analysis.get("information_security", 0),
                    "社会影响": analysis.get("social_impact", 0),
                    "总风险分数": analysis.get("total_risk_score", 0),
                    "风险等级": analysis.get("risk_level", "未知"),
                    "风险因素": ", ".join(analysis.get("risk_factors", [])),
                    "建议措施": analysis.get("recommendations", ""),
                    "分析时间": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                }
            else:
                # API调用失败时的默认值
                result = {
                    "题目": question,
                    "内容敏感度": -1,
                    "法律合规性": -1,
                    "道德伦理": -1,
                    "信息安全": -1,
                    "社会影响": -1,
                    "总风险分数": -1,
                    "风险等级": "分析失败",
                    "风险因素": "API调用失败",
                    "建议措施": "",
                    "分析时间": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                }
            
            results.append(result)
            
            # 延迟以避免触发API速率限制
            if i < total:
                time.sleep(DELAY_BETWEEN_REQUESTS)
        
        return results
    
    def export_results(results, original_df=None):
        """将结果导出到Excel文件"""
        try:
            # 创建结果DataFrame
            results_df = pd.DataFrame(results)
            
            # 如果有原始数据,可以合并
            if original_df is not None and "题目" in original_df.columns:
                # 合并原始数据和分析结果
                merged_df = pd.merge(
                    original_df,
                    results_df,
                    left_on=QUESTION_COLUMN,
                    right_on="题目",
                    how="left"
                )
                final_df = merged_df
            else:
                final_df = results_df
            
            # 创建Excel写入器,支持多个工作表
            with pd.ExcelWriter(OUTPUT_EXCEL_FILE, engine='openpyxl') as writer:
                # 写入详细结果
                final_df.to_excel(writer, sheet_name='详细分析结果', index=False)
                
                # 创建统计摘要
                summary_data = {
                    "统计项": ["总题目数", "高风险题目数", "中风险题目数", "低风险题目数", "平均风险分数"],
                    "数值": [
                        len(results),
                        len([r for r in results if r["风险等级"] == "高风险"]),
                        len([r for r in results if r["风险等级"] == "中风险"]),
                        len([r for r in results if r["风险等级"] == "低风险"]),
                        sum([r["总风险分数"] for r in results if r["总风险分数"] >= 0]) / 
                        max(1, len([r for r in results if r["总风险分数"] >= 0]))
                    ]
                }
                summary_df = pd.DataFrame(summary_data)
                summary_df.to_excel(writer, sheet_name='统计摘要', index=False)
                
                # 高风险题目单独列出
                high_risk_df = final_df[final_df["风险等级"] == "高风险"]
                if not high_risk_df.empty:
                    high_risk_df.to_excel(writer, sheet_name='高风险题目', index=False)
            
            print(f"\n结果已成功导出到: {OUTPUT_EXCEL_FILE}")
            
            # 打印统计信息
            print("\n=== 分析统计 ===")
            for item, value in zip(summary_data["统计项"], summary_data["数值"]):
                print(f"{item}: {value:.2f}" if isinstance(value, float) else f"{item}: {value}")
            
        except Exception as e:
            print(f"错误:导出结果时出错 - {e}")
    
    def validate_config():
        """验证配置是否正确"""
        if AI_API_URL == "YOUR_AI_API_ENDPOINT":
            print("错误:请设置 AI_API_URL 为实际的API端点")
            return False
        
        if AI_API_KEY == "YOUR_API_KEY":
            print("错误:请设置 AI_API_KEY 为你的API密钥")
            return False
        
        if not os.path.exists(INPUT_EXCEL_FILE):
            print(f"错误:找不到输入文件 '{INPUT_EXCEL_FILE}'")
            print("请确保文件存在并且路径正确")
            return False
        
        return True
    
    # --- 主程序 ---
    
    def main():
        """主执行函数"""
        print("=== AI风险分析程序 ===")
        print(f"开始时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        
        # 验证配置
        if not validate_config():
            return
        
        # 读取Excel文件中的题目
        result = read_excel_questions(INPUT_EXCEL_FILE, INPUT_SHEET_NAME, QUESTION_COLUMN)
        if result is None:
            return
        
        questions, original_df = result
        
        if not questions:
            print("错误:没有找到任何题目")
            return
        
        print(f"\n准备分析 {len(questions)} 个题目...")
        
        # 确认是否继续
        user_input = input("\n是否继续?(y/n): ")
        if user_input.lower() != 'y':
            print("已取消操作")
            return
        
        # 分析题目
        results = analyze_questions(questions)
        
        # 导出结果
        export_results(results, original_df)
        
        print(f"\n完成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        print("程序执行完毕!")
    
    if __name__ == "__main__":
        main()
    

    使用说明:

    1. 安装必要的库:
    pip install requests pandas openpyxl
    
    1. 准备输入文件:

      • 创建一个名为 questions.xlsx 的Excel文件
      • 在第一个工作表中创建一列,列名为"题目"
      • 在该列中填入需要分析的题目
    2. 配置API信息:

      • 将 AI_API_URL 替换为你使用的AI服务的API端点
      • 将 AI_API_KEY 替换为你的API密钥
      • 根据你使用的AI服务调整请求格式
    3. 常见AI服务配置示例:

      OpenAI GPT:

      AI_API_URL = "https://api.openai.com/v1/chat/completions"
      AI_API_KEY = "sk-your-api-key"
      AI_MODEL = "gpt-3.5-turbo"
      

      Claude API:

      AI_API_URL = "https://api.anthropic.com/v1/messages"
      AI_API_KEY
      

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.