Python 代码：从视频文件夹中提取信息并生成 Excel 文件

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


# 需要处理的视频文件夹
video_folder = "/home/115/up"

# 输出Excel文件夹路径
excel_folder = "/home/115/up/"

# 输出Excel文件名
excel_file = "video_info.xlsx"

# 创建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:
        if file.endswith(".mp4"):
            video_files.append(os.path.join(root, file))

# 遍历所有视频文件，获取视频信息、音频信息和音频语言，并写入Excel文件
for row, video_file in enumerate(video_files, start=2):
    # 获取文件名
    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))}"
    
    # 写入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) # 写入音频语言

# 自动调整列宽
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column].width = adjusted_width

# 保存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"视频信息已保存到 {excel_file_path}")