一：表格過度配置，表格資源是否在工程中存在，并輸出不存在的資源

import pandas as pd
import glob
import osassets = []
count = 0# 遍歷configs文件夾下所有xlsx文件
for file_path in glob.glob('configs/*.xlsx'):count += 1try:sheets = pd.read_excel(file_path, sheet_name=None)except Exception as e:print(f"讀取文件 {file_path} 時出錯：{e}")continue# 遍歷每個工作表for sheet_name, df in sheets.items():print('正在讀取文件:', file_path, 'sheet:', sheet_name)all_values = df.values.ravel()for value in all_values:if isinstance(value, str) and value.startswith('Assets/'):assets.append(value)# 寫入output.xlsx
output_path = "output.xlsx"
if os.path.exists(output_path):os.remove(output_path)if assets:df_output = pd.DataFrame(assets, columns=["Asset Path"])df_output.to_excel(output_path, index=False)print(f"\n結果已寫入 {output_path}")
else:print("\n未找到以'Assets/'開頭的內容")# 檢查文件是否存在并處理不存在的文件
not_exist_assets = []
for asset_path in assets:# 添加前綴../MainProject/并檢查路徑是否存在full_path = os.path.join('../MainProject', asset_path)if not os.path.exists(full_path):not_exist_assets.append(asset_path)not_exist_output = "FileNotExist.xlsx"
if not_exist_assets:if os.path.exists(not_exist_output):os.remove(not_exist_output)df_not_exist = pd.DataFrame(not_exist_assets, columns=["Asset Path"])df_not_exist.to_excel(not_exist_output, index=False)print(f"\n以下資源文件不存在，已寫入 {not_exist_output}：")for path in not_exist_assets:print(path)
else:print("\n所有資源文件均存在。")# 打印統計信息
print("\n匹配的內容列表：")
for item in assets:print(item)print("\n一共讀取了", count, "個文件，匹配到", len(assets), "條有效數據")

二：收集打包目錄

import os# 需要處理的文件列表
file_paths = [r'..\MainProject\Assets\Build\AssetBundle\AssetBundleConfig.asset',r'..\MainProject\Assets\Build\AssetBundle\SegmentedUpdateConfig.asset'
]
keys = []  # 存儲所有處理后的鍵值for file_path in file_paths:try:with open(file_path, 'r', encoding='utf-8') as file:print(f"\n{'='*50}")print(f"正在讀取文件：{os.path.abspath(file_path)}")print("-" * 50)file_keys = []  # 當前文件的臨時存儲for line_number, line in enumerate(file, 1):formatted_line = line.strip()# 關鍵字段提取邏輯if "Assets/" in formatted_line:# 清洗數據：移除 "- " 字符cleaned_key = formatted_line.replace("- ", "")file_keys.append(cleaned_key)print(f"[Line {line_number:03d}] 發現有效鍵 | 原始內容：{formatted_line} | 處理后：{cleaned_key}")else:print(f"[Line {line_number:03d}] {formatted_line}")keys.extend(file_keys)print(f"\n當前文件提取到 {len(file_keys)} 個有效鍵")except FileNotFoundError:print(f"錯誤：文件不存在 {os.path.abspath(file_path)}")except PermissionError:print(f"錯誤：沒有權限讀取文件 {file_path}")except UnicodeDecodeError:print(f"錯誤：文件編碼格式不支持，嘗試使用其他編碼（如utf-16）")except Exception as e:print(f"發生未知錯誤：{str(e)}")else:print("-" * 50)print(f"文件 {os.path.basename(file_path)} 處理完成")print("\n" + "="*50)
print(f"共處理 {len(file_paths)} 個文件，總計提取到 {len(keys)} 個有效鍵：")
for i, key in enumerate(keys, 1):print(f"{i:03d}. {key}")

三：是否進包，資源是否存在打包目錄

import pandas as pd
import glob
import os
from datetime import datetimedef clean_old_files():"""清理歷史文件"""target_files = ['keys.txt', '異常資源報告.xlsx']for file in target_files:try:os.remove(file)print(f" ? 已刪除舊文件：{file}")except FileNotFoundError:pass  # 靜默處理文件不存在的情況except Exception as e:print(f"?? 文件刪除異常：{file}")print(f"錯誤詳情：{str(e)}")def collect_assets():"""從Excel文件收集資源路徑"""assets = []file_count = 0print(f"\n{'='*50}")print("開始掃描Excel配置文件")print(f"掃描時間：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")for file_path in glob.glob('configs/*.xlsx'):file_count += 1try:sheets = pd.read_excel(file_path, sheet_name=None)filename = os.path.basename(file_path)print(f"\n? 正在處理：{filename}")for sheet_name, df in sheets.items():print(f" ├─ 工作表：{sheet_name}")all_values = df.values.ravel()for value in all_values:if isinstance(value, str) and value.startswith('Assets/'):assets.append({'file': filename,'sheet': sheet_name,'path': value})except Exception as e:print(f"\n?? 文件處理異常：{file_path}")print(f"錯誤詳情：{str(e)}")print(f"\n{'='*50}")print(f"掃描完成 | 處理文件：{file_count} 個 | 發現路徑：{len(assets)} 條")return assetsdef extract_keys(file_paths):"""提取并保存去重鍵值"""keys = []print(f"\n{'='*50}")print("開始解析關鍵鍵值")for path in file_paths:# 處理Excel文件if path.endswith('.xlsx'):print(f"\n? 解析Excel文件：{os.path.basename(path)}")try:df = pd.read_excel(path)excel_keys = df.iloc[:, 0].astype(str).dropna().tolist()keys.extend(excel_keys)print(f" ├─ 提取到 {len(excel_keys)} 條鍵值")for key in excel_keys[:3]:  # 顯示前3條示例print(f" │ 示例：{key[:60]}...")except Exception as e:print(f"\n?? Excel文件讀取失敗：{path}")print(f"錯誤詳情：{str(e)}")# 處理.asset文件elif path.endswith('.asset'):print(f"\n? 解析配置文件：{os.path.basename(path)}")try:with open(path, 'r', encoding='utf-8') as f:line_count = 0for line in f:line_count += 1clean_line = line.strip().replace("- ", "")if "Assets/" in clean_line:keys.append(clean_line)print(f" ├─ [L{line_count:03d}] 發現鍵值：{clean_line[:40]}...")except Exception as e:print(f"\n?? 文件讀取失敗：{path}")print(f"錯誤詳情：{str(e)}")# 去重并保存keys = list(set(keys))with open('keys.txt', 'w', encoding='utf-8') as f:f.write('\n'.join(keys))print(f"\n{'='*50}")print(f"解析完成 | 去重鍵值：{len(keys)} 條")print(f" ? 鍵值文件已保存：{os.path.abspath('keys.txt')}")return keysdef validate_assets(assets, keys):"""執行路徑校驗"""errors = []print(f"\n{'='*50}")print("開始校驗路徑完整性")print("\n校驗進度：")for idx, asset in enumerate(assets, 1):if not any(key in asset['path'] for key in keys):errors.append({'file': asset['file'],'sheet': asset['sheet'],'path': asset['path']})print(f" ? 已校驗 {idx}/{len(assets)} 條路徑", end='\r')print(f"\n\n{'='*50}")if errors:print(f"? 發現 {len(errors)} 條異常路徑：")for i, err in enumerate(errors[:5], 1):print(f" {i:02d}. 文件：{err['file']} | 工作表：{err['sheet']}\n 路徑：{err['path']}")if len(errors) > 5:print(f" ...（僅顯示前5條，共{len(errors)}條）")else:print("? 所有路徑均包含有效鍵值")return errorsdef main():# 初始化清理print(f"\n{'='*50}")print("開始清理歷史文件")clean_old_files()# 配置輸入路徑asset_files = [r'..\MainProject\Assets\Build\AssetBundle\AssetBundleConfig.asset',r'..\MainProject\Assets\Build\AssetBundle\SegmentedUpdateConfig.asset','代碼路徑.xlsx']# 執行數據收集assets = collect_assets()keys = extract_keys(asset_files)# 執行校驗errors = validate_assets(assets, keys)# 生成報告if errors:report_data = []for err in errors:report_data.append({'來源文件': err['file'],'工作表': err['sheet'],'資源路徑': err['path'],'校驗結果': 'Invalid'})report_df = pd.DataFrame(report_data)report_path = "異常資源報告.xlsx"report_df.to_excel(report_path, index=False)print(f"\n📊 異常報告已生成：{os.path.abspath(report_path)}")if __name__ == "__main__":main()