75 lines
2.2 KiB
Python
75 lines
2.2 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
把 数据/ 下所有 jsonl 的【成功】记录去重汇总成一个 xlsx。
|
|
列: 视频地址 | 字幕地址 | 时长(秒) | 时长(分:秒)
|
|
|
|
用法: python3 tools/导出成功xlsx.py [--dir 数据] [--out 成功字幕汇总.xlsx]
|
|
"""
|
|
import argparse
|
|
import glob
|
|
import json
|
|
import os
|
|
from openpyxl import Workbook
|
|
|
|
|
|
def fmt_dur(sec):
|
|
try:
|
|
s = int(round(float(sec)))
|
|
except (TypeError, ValueError):
|
|
return ""
|
|
if s <= 0:
|
|
return ""
|
|
h, rem = divmod(s, 3600)
|
|
m, ss = divmod(rem, 60)
|
|
return f"{h}:{m:02d}:{ss:02d}" if h else f"{m}:{ss:02d}"
|
|
|
|
|
|
def main():
|
|
ap = argparse.ArgumentParser()
|
|
ap.add_argument("--dir", default="数据")
|
|
ap.add_argument("--out", default="成功字幕汇总.xlsx")
|
|
args = ap.parse_args()
|
|
|
|
files = glob.glob(os.path.join(args.dir, "*.jsonl"))
|
|
print(f"读取 {len(files)} 个 jsonl ...")
|
|
latest = {}
|
|
for fp in files:
|
|
for line in open(fp, encoding="utf-8"):
|
|
line = line.strip()
|
|
if not line:
|
|
continue
|
|
try:
|
|
r = json.loads(line)
|
|
except Exception:
|
|
continue
|
|
v = r.get("video_cos_path")
|
|
if v:
|
|
latest[v] = r
|
|
|
|
rows = [r for r in latest.values() if r.get("status") == "success"]
|
|
# 按视频地址排序, 输出稳定
|
|
rows.sort(key=lambda r: r.get("video_cos_path", ""))
|
|
print(f"去重视频 {len(latest)} | 成功 {len(rows)} → 写 xlsx")
|
|
|
|
wb = Workbook(write_only=True) # 流式写, 省内存(几十万行也扛得住)
|
|
ws = wb.create_sheet("成功字幕")
|
|
ws.append(["视频地址", "字幕地址", "时长(秒)", "时长(分:秒)"])
|
|
n = 0
|
|
for r in rows:
|
|
dur = r.get("duration")
|
|
try:
|
|
dsec = round(float(dur), 1) if dur is not None else ""
|
|
except (TypeError, ValueError):
|
|
dsec = ""
|
|
ws.append([r.get("video_cos_path", ""),
|
|
r.get("srt_cos_path", ""),
|
|
dsec,
|
|
fmt_dur(dur)])
|
|
n += 1
|
|
wb.save(args.out)
|
|
print(f"完成: {n} 行 → {args.out} ({os.path.getsize(args.out)//1024//1024} MB)")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|