代码如下：




```python
import requests
import pandas as pd
from requests.exceptions import RequestException

def safe_json_to_excel(url, output_file, timeout=10):
    try:
        # 添加浏览器指纹头
        headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
            'Accept': 'application/json'
        }
        
        response = requests.get(url, headers=headers, timeout=timeout)
        response.raise_for_status()
        
        data = response.json()
        
        # 处理不同API返回结构
        if isinstance(data, dict):
            if 'data' in data:  # 常见结构1：{data: [...]}
                # 检查是否存在嵌套的字典列表
                nested_columns = []
                sample_data = data['data'][0] if data['data'] else {}
                for key, value in sample_data.items():
                    if isinstance(value, list) and value and isinstance(value[0], dict):
                        nested_columns.append(key)
                
                if nested_columns:
                    # 对每个包含嵌套字典的列进行展开
                    dfs = []
                    for col in nested_columns:
                        # 首先创建基础DataFrame
                        base_df = pd.json_normalize(data['data'])
                        
                        # 展开嵌套列
                        expanded_data = []
                        for idx, row in base_df.iterrows():
                            if isinstance(row[col], list):
                                for item in row[col]:
                                    if isinstance(item, dict):
                                        new_row = row.drop(col).to_dict()
                                        new_row.update(item)
                                        expanded_data.append(new_row)
                        
                        if expanded_data:
                            df_nested = pd.DataFrame(expanded_data)
                            dfs.append(df_nested)
                    
                    # 合并所有展开的数据框
                    if dfs:
                        df = pd.concat(dfs, axis=0, ignore_index=True)
                    else:
                        df = pd.json_normalize(data['data'])
                else:
                    df = pd.json_normalize(data['data'])
            elif 'items' in data:  # 常见结构2：{items: [...]}
                df = pd.json_normalize(data['items'])
            else:
                df = pd.json_normalize(data)
        else:
            df = pd.json_normalize(data)
            
        # 保存结果
        df.to_excel(output_file, index=False, engine='openpyxl')
        print(f"成功保存 {len(df)} 行数据")
        
    except RequestException as e:
        print(f"请求失败: {str(e)}")
    except ValueError:
        print("响应不是有效JSON")
    except KeyError as e:
        print(f"JSON结构异常，缺少关键字段: {str(e)}")

# 使用示例
safe_json_to_excel(
    url="https://srv1.te-germany.com/DataSourceContainerTracingPublic/Get?customerId=459&apiKey=",
    output_file="container_tracing.xlsx"
)
```

将网站的Tracing转换成Excel

按要求将`CAAU7083393 TE-WB1215 1-10843 0T 19.02.2025.pdf`这类文件名批量替换为`TE-WB1215 CAAU7083393 1-10843 0T 19.02.2025.pdf`




```python
import os
import re
from datetime import datetime

def validate_container_number(container):
    
    return re.match(r'^[A-Za-z]{4}\d{7}$', container) is not None

def validate_tewb_code(tewb):
    
    return re.match(r'^\w{1,}-WB\d{4}$', tewb, re.IGNORECASE) is not None

def validate_date(date_str):
    
    try:
        datetime.strptime(date_str, "%d.%m.%Y")
        return True
    except ValueError:
        return False

def rename_pdf_files(directory, dry_run=True):
    """
    批量重命名PDF文件
    :param directory: 目标目录路径
    :param dry_run: 模拟运行模式（True只显示预览，False实际执行）
    """
    
    pattern = re.compile(
        r'^'
        r'(?P<container>[A-Za-z]{4}\d{7}) '      
        r'(?P<tewb>\w{1,}-WB\d{4}) '                 
        r'(?P<content>.+?) '                     
        r'(?P<date>\d{2}\.\d{2}\.\d{4})'        
        r'\.pdf$',
        re.IGNORECASE
    )

    for filename in sorted(os.listdir(directory)):
        if not filename.lower().endswith('.pdf'):
            continue

        match = pattern.match(filename)
        if not match:
            print(f"格式不匹配，跳过文件: {filename}")
            continue

        groups = match.groupdict()
        
        
        validation = (
            (validate_container_number(groups['container']), "集装箱号格式错误"),
            (validate_tewb_code(groups['tewb']), "WB代码格式错误"),
            (validate_date(groups['date']), "日期格式错误")
        )

        if not all(v[0] for v in validation):
            errors = ", ".join([v[1] for v in validation if not v[0]])
            print(f"验证失败：{filename} -> {errors}")
            continue

        
        new_name = f"{groups['tewb']} {groups['container']} {groups['content']} {groups['date']}.pdf"
        
        old_path = os.path.join(directory, filename)
        new_path = os.path.join(directory, new_name)

        
        if dry_run:
            print(f"[模拟模式] 将重命名: {filename} -> {new_name}")
        else:
            try:
                os.rename(old_path, new_path)
                print(f"成功重命名: {filename} -> {new_name}")
            except Exception as e:
                print(f"重命名失败: {filename} | 错误: {str(e)}")

if __name__ == "__main__":
    target_dir = os.getcwd()  
    # 第一次先运行模拟模式（不实际修改文件）
    print("=== 模拟运行 ===")
    rename_pdf_files(target_dir, dry_run=True)
    
    # 确认无误后，注释下面两行并取消注释最后一行
    # confirm = input("\n确认要执行重命名吗？(y/n): ")
    # if confirm.lower() == 'y':
    #    rename_pdf_files(target_dir, dry_run=False)
```

按要求重命名PDF

代码如下：



```python

import pandas as pd
import os
from datetime import datetime

# 读取数据
for file in os.listdir():
    if file.endswith('xlsx'):
        df = pd.read_excel(file, sheet_name='Tracing report',header=0)  # 假设输入文件名为 input.xlsx

# 处理合并单元格：填充空白值
df['ATD'] = df['ATD'].ffill()

# 将时间列转换为 datetime 类型
df['ATD'] = pd.to_datetime(df['ATD'],errors='coerce')  # E列是 ATD
df['ATA_T'] = pd.to_datetime(df.iloc[:,19],errors='coerce')  # T列是 ATA

df['ATA  KZH border'] = pd.to_datetime(df['ATA  KZH border'],errors='coerce')  # M列是 口岸

df = df.dropna(subset=['ATD', 'ATA_T','ATA  KZH border'])
# 计算时间差
df['整段时效'] = (df['ATA_T'] - df['ATD']).dt.days -1 # 转换为天
df['欧洲段时效'] = (df['ATA_T'] - df['ATA  KZH border']).dt.days -1  # 转换为天

# 按车站、月份和口岸分组
df['Month'] = df['ATD'].dt.to_period('M')  # 提取月份
# df['Month'] = df['ATD'].dt.month 
grouped = df.groupby(['Part No.', 'Month', 'ATA  CN border'], as_index=False)  # I列是 车站, K列是 口岸
# 计算每组的平均值
result = grouped.agg({
    '整段时效': lambda x: round(x.mean(), 2),
    '欧洲段时效': lambda x: round(x.mean(), 2)
})

result = result.rename(columns={
    'Part No.': '车站',
    'Month': '月份',
    'ATA  CN border': '口岸'
})
# 将结果保存到 Excel 文件
result.to_excel('output.xlsx', index=False) 
```

记录Excel按条件计算平均值问题

如下：



```python
import pandas as pd

# 读取Excel文件
file_path = "source.xlsx"  # Excel文件路径
df = pd.read_excel(file_path)

email_column = "客户接收报价单的邮箱"  # 替换为实际的邮箱列名

# 创建一个新的DataFrame，用于存放拆分后的结果
new_data = []

# 遍历原始数据
for index, row in df.iterrows():
    emails = str(row[email_column]).replace('，',',').split(",")  # 按逗号分隔多个邮箱
    for email in emails:
        new_row = row.copy()
        new_row[email_column] = email.strip()  # 去除邮箱的空格
        new_data.append(new_row)

# 转换为新的DataFrame
new_df = pd.DataFrame(new_data)

# 保存为新的Excel文件
output_file = "processed_file.xlsx"
new_df.to_excel(output_file, index=False)

print(f"数据已成功处理并保存为 {output_file}")

```

记录下拆分Email程序

注：需要先安装Ghostscript并设置环境变量



```python
from PyPDF2 import PdfReader, PdfWriter
import os


#-----函数层开始-----
def compress_pdf_with_ghostscript(input_pdf, output_pdf):
    """
    使用 Ghostscript 压缩 PDF 文件
    :param input_pdf: 输入 PDF 文件路径
    :param output_pdf: 输出压缩后的 PDF 文件路径
    """
    command = f"gswin64 -sDEVICE=pdfwrite -dCompatibilityLevel=1.4 -dPDFSETTINGS=/screen -dNOPAUSE -dQUIET -dBATCH -sOutputFile={output_pdf} {input_pdf}"
    os.system(command)
    print(f"压缩后的 PDF 文件已保存为：{output_pdf}")

def extract_text_from_page(page):
    """
    提取 PDF 页面中的文本内容。
    """
    return page.extract_text()

def contains_container_number(text):
    """
    判断页面文本是否包含集装箱号。
    假设集装箱号是由字母和数字组成的特定格式（例如：ABC1234567）。
    """
    import re
    # 集装箱号的正则表达式（假设是4个字母+7个数字）
    pattern = r'[A-Z]{4}\d{7}'
    match = re.search(pattern, text)
    return match.group(0) if match else None

def split_pdf_by_container_number(input_pdf, output_folder):
    """
    按集装箱号分割 PDF，并将没有集装箱号的页面与前一个有集装箱号的页面合并。
    """
    # 读取 PDF 文件
    reader = PdfReader(input_pdf)
    total_pages = len(reader.pages)
    
    writer = None
    current_container_number = None
    output_files = []

    for page_number in range(total_pages):
        page = reader.pages[page_number]
        text = extract_text_from_page(page)

        # 检查当前页面是否包含集装箱号
        container_number = contains_container_number(text)

        if container_number:
            # 如果当前页面包含集装箱号
            if writer:
                # 保存之前的 PDF 文件
                output_file = f"{output_folder}/{current_container_number}货票.pdf"
                with open(output_file, "wb") as f:
                    writer.write(f)
                output_files.append(output_file)

            # 更新当前集装箱号并创建新的 PDF 写入器
            current_container_number = container_number
            writer = PdfWriter()
            writer.add_page(page)

        else:
            # 如果当前页面没有集装箱号，将其添加到当前的 PDF 中
            if writer:
                writer.add_page(page)
            else:
                print(f"警告：第 {page_number + 1} 页没有集装箱号，且没有前一个页面可合并！")

    # 保存最后一个 PDF
    if writer and current_container_number:
        output_file = f"{output_folder}/{current_container_number}货票.pdf"
        with open(output_file, "wb") as f:
            writer.write(f)
        output_files.append(output_file)

    print("分割完成！生成的文件：")
    for file in output_files:
        print(file)

#------函数层结束----------

input_pdf = [file for file in os.listdir() if file.lower().endswith('.pdf')][0]
output_pdf = "compressed.pdf"

#执行压缩
compress_pdf_with_ghostscript(input_pdf, output_pdf)

input_pdf= output_pdf
output_folder = "output"  # 输出文件夹路径


if not os.path.exists(output_folder):
    os.makedirs(output_folder)

#主函数
split_pdf_by_container_number(input_pdf, output_folder)

```