import os import re import subprocess import openpyxl import math from openpyxl.styles import Font, PatternFill, Alignment

视频格式

supported_formats = ['.mp4', '.avi', '.mkv', '.wmv', '.mov', '.flv', '.m2ts', '.ts', '.rm', '.rmvb', '.vob', '.3gp', '.webm', '.hdmov', '.mp4v', '.mpv4', '.divx', '.xvid', '.f4v', '.mpeg', '.asf', '.asx', '.m2t']

视频文件夹

video_folder = input('请输入文件夹路径：')

输出Excel路径

excel_folder = '.'

输出Excel文件名

excel_file = 'video_info.xlsx'

正则表达式匹配码率数据中的空格

pattern = re.compile(r'(\d+)\s+(\d+)')

创建Excel文件

wb = openpyxl.Workbook() ws = wb.active ws.title = 'Video Info'

设置表头样式

header_font = Font(bold=True, color='800080') # 紫色 header_fill = PatternFill('solid', fgColor='C5E0B4') header_alignment = Alignment(horizontal='center', vertical='center') headers = ['文件名', '文件路径', '格式', '大小', '时长', '分辨率', '码率', '帧率', '音频码率', '音频采样率', '音频语言', '压缩比率'] for col, header in enumerate(headers, start=1): cell = ws.cell(row=1, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.alignment = header_alignment

锁定表头

ws.freeze_panes = 'A2'

获取视频文件列表（包括子目录）

video_files = [] for root, dirs, files in os.walk(video_folder): for file in files: for format in supported_formats: if file.endswith(format): video_files.append(os.path.join(root, file)) break

处理失败文件个数

failed_count = 0

遍历所有视频文件

success_count = 0 for row, video_file in enumerate(video_files, start=2): try: # 获取文件名 file_name = os.path.basename(video_file)

    # 获取文件大小并进行单位换算
    size = os.path.getsize(video_file)
    if size < 1024:
        size_str = f'{size} B'
    elif size < 1024 * 1024:
        size_str = f'{size / 1024:.2f} KiB'
    elif size < 1024 * 1024 * 1024:
        size_str = f'{size / 1024 / 1024:.2f} MiB'
    else:
        size_str = f'{size / 1024 / 1024 / 1024:.2f} GiB'

    # 使用mediainfo获取视频信息 / 音频信息
    video_result = subprocess.run(['mediainfo', '--Inform=Video;%Format%|%Width%x%Height%|%BitRate/String%|%FrameRate%|%Duration/String3%', video_file], stdout=subprocess.PIPE)
    video_output = video_result.stdout.decode().strip()
    audio_result = subprocess.run(['mediainfo', '--Inform=Audio;%BitRate/String%|%SamplingRate/String%|%Language/String%', video_file], stdout=subprocess.PIPE)
    audio_output = audio_result.stdout.decode().strip()

    # 解析输出结果
    format, resolution, bitrate, framerate, duration = video_output.split('|')
    audiobitrate, audiosamplingrate, audiolang = audio_output.split('|')

    # 时长取整
    duration = duration.split('.')[0]
    h, m, s = duration.split(':')
    duration = f'{h}:{m}:{math.ceil(float(s))}'

    # 以分钟计算的时长 2
    duration_minutes = int(h) * 60 + int(m) + math.ceil(float(s)) / 60

    # 计算压缩比率
    ratio = round(duration_minutes / size * 1000000000, 2)

    # 使用正则表达式替换码率数据中第一个数字和第二个数字之间的空格
    bitrate = re.sub(pattern, r'\1\2', bitrate)

    # 写入Excel文件
    ws.cell(row=row, column=1, value=file_name)
    ws.cell(row=row, column=2, value=os.path.dirname(video_file)) # 写入文件夹路径
    ws.cell(row=row, column=3, value=format)
    ws.cell(row=row, column=4, value=size_str) # 写入文件大小
    ws.cell(row=row, column=5, value=duration)
    ws.cell(row=row, column=6, value=resolution)
    ws.cell(row=row, column=7, value=bitrate)
    ws.cell(row=row, column=8, value=framerate)
    ws.cell(row=row, column=9, value=audiobitrate)
    ws.cell(row=row, column=10, value=audiosamplingrate)
    ws.cell(row=row, column=11, value=audiolang)
    ws.cell(row=row, column=12, value=ratio)
    # 打印进度
    print(f'已处理 {row-1}/{len(video_files)} 共计', end='\r')
    success_count += 1

except Exception as e:
    # 处理失败，跳过并打印文件名
    failed_count += 1
    print(f'处理文件 {file_name} 时失败：{e}')
    with open('error.txt', 'a') as f:
        f.write(file_name + '\n')
    continue

总计处理文件数

total_count = len(video_files)

调整L列左对齐

l_col = ws['L'] for cell in l_col: cell.alignment = Alignment(horizontal='left') # 左对齐

固定列宽

for col in ws.columns: col_letter = col[0].column_letter if col_letter not in ['']: # 不固定文件夹路径、格式、音频语言列的宽度 ws.column_dimensions[col_letter].width = 11

保存Excel文件

if not os.path.exists(excel_folder): os.makedirs(excel_folder) excel_file_path = os.path.join(excel_folder, excel_file) wb.save(excel_file_path)

打印处理失败文件个数和总计处理文件数

print(f'已完成，处理成功 {success_count} 个文件，处理失败 {failed_count} 个文件，视频信息已保存到 {excel_file_path}')