yipng05-max/-skills/foreign-literature-search

pip install openpyxl

组1（研究对象，主检索）：gig economy + platform labor + ride-hailing + Uber driver + DiDi
组2（核心议题，后置过滤）：meaning of work + work meaning + occupational identity + livelihood

【主体联合检索】
组1（主检索）：gig economy + platform labor + ride-hailing + Uber driver
组2（后置过滤）：meaning of work + work meaning + occupational identity + livelihood
 
【独立补充检索】（主检索完成后必做）
补充A（研究对象背景）：precarious work + informal economy × labor market + working conditions
补充B（核心理论）：identity work + sensemaking + meaning-making × labor + work + occupation
 
WoS 布尔检索式（同步生成，供有机构权限用户使用）

TS = ("gig economy" OR "platform labor" OR "ride-hailing" OR "Uber driver")
AND TS = ("meaning of work" OR "occupational identity" OR "livelihood" OR "worker identity")
AND WC = (Sociology OR "Industrial Relations" OR "Labor Relations")

TITLE-ABS-KEY("gig economy" OR "platform labor" OR "ride-hailing")
AND TITLE-ABS-KEY("meaning of work" OR "occupational identity" OR "livelihood")
AND SUBJAREA(SOCI OR PSYC OR ECON OR BUSI)

python3 /Users/songyiping/.claude/skills/foreign-literature-search/scripts/openalex_search.py \
  --keywords "gig economy + platform labor + ride-hailing + Uber driver" \
  --keywords "meaning of work + work meaning + occupational identity + livelihood" \
  --max-results 100 \
  --topic "快车司机生计变迁与意义建构" \
  --category "直接相关（平台劳动×意义建构）" \
  --color "D9E1F2" \
  --output-dir ~/Downloads

python3 /Users/songyiping/.claude/skills/foreign-literature-search/scripts/openalex_search.py \
  --keywords "precarious work + informal work + gig worker + platform worker" \
  --keywords "labor market + working conditions + employment + job quality" \
  --max-results 60 \
  --topic "快车司机生计变迁与意义建构" \
  --category "间接相关-劳动条件背景" \
  --color "E2EFDA" \
  --output-file ~/Downloads/外文补充A_劳动背景.xlsx

python3 /Users/songyiping/.claude/skills/foreign-literature-search/scripts/openalex_search.py \
  --keywords "identity work + sensemaking + meaning-making + self-concept" \
  --keywords "labor + work + occupation + employment" \
  --max-results 60 \
  --topic "快车司机生计变迁与意义建构" \
  --category "间接相关-意义/身份认同理论" \
  --color "FFF2CC" \
  --output-file ~/Downloads/外文补充B_意义理论.xlsx

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from pathlib import Path
files = {
    "直接相关（平台劳动×意义建构）":     ("D9E1F2", "~/Downloads/外文文献检索_快车司机生计变迁_YYYYMMDD.xlsx"),
    "间接相关-劳动条件背景":              ("E2EFDA", "~/Downloads/外文补充A_劳动背景.xlsx"),
    "间接相关-意义/身份认同理论":         ("FFF2CC", "~/Downloads/外文补充B_意义理论.xlsx"),
}
all_rows = []
seen_titles = set()
orig_headers = None  # 从第一个文件读取真实表头，不硬编码
for category, (color, filepath) in files.items():
    wb = openpyxl.load_workbook(Path(filepath).expanduser())
    ws = wb.active
    rows = list(ws.iter_rows(values_only=True))
    if orig_headers is None:
        orig_headers = list(rows[0])  # 实际表头：序号(0), 文献类别(1), 标题(2), ...
    for row in rows[1:]:
        title = str(row[2] or "").strip()  # 标题固定在索引2
        if title and title not in seen_titles:
            seen_titles.add(title)
            # 用正确的 category 覆盖原行的文献类别（索引1），保留其余列原样
            all_rows.append((color, category) + tuple(row[2:]))
    wb.close()
wb_out = openpyxl.Workbook()
ws_out = wb_out.active
ws_out.title = "外文文献汇总"
# 直接使用从文件读取的真实表头，避免硬编码导致列名错位或重复
ws_out.append(orig_headers)
for cell in ws_out[1]:
    cell.font = Font(bold=True, color="FFFFFF", size=11)
    cell.fill = PatternFill(fill_type="solid", fgColor="1F4E79")
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
for seq, row_data in enumerate(all_rows, 1):
    color, category = row_data[0], row_data[1]
    ws_out.append((seq, category) + row_data[2:])  # 用新序号替换原序号
    fill = PatternFill(fill_type="solid", fgColor=color)
    for cell in ws_out[ws_out.max_row]:
        cell.fill = fill
        cell.alignment = Alignment(vertical="top", wrap_text=True)
# 列宽按实际列数动态设置（原始文件有14列）
col_widths = [5, 28, 45, 22, 22, 8, 8, 10, 6, 8, 36, 36, 30, 60]
for i, w in enumerate(col_widths[:len(orig_headers)], 1):
    ws_out.column_dimensions[openpyxl.utils.get_column_letter(i)].width = w
for row in ws_out.iter_rows(min_row=2):
    ws_out.row_dimensions[row[0].row].height = 65
ws_out.freeze_panes = "A2"
ws_out.auto_filter.ref = ws_out.dimensions
ws2 = wb_out.create_sheet("分类统计")
ws2.append(["文献类别", "颜色", "篇数"])
color_desc = {"D9E1F2": "蓝-直接相关", "E2EFDA": "绿-背景文献", "FFF2CC": "黄-理论文献", "FCE4D6": "橙-扩展视角"}
for cat, (color, _) in files.items():
    cnt = sum(1 for r in all_rows if r[1] == cat)
    ws2.append([cat, color_desc.get(color, color), cnt])
ws2.append(["合计（去重后）", "", len(all_rows)])
for cell in ws2[1]: cell.font = Font(bold=True)
output_path = "~/Downloads/外文文献汇总_快车司机生计变迁_YYYYMMDD.xlsx"
wb_out.save(Path(output_path).expanduser())
print(f"[✓] 已保存: {output_path}（共 {len(all_rows)} 篇）")

open ~/Downloads/外文文献汇总_*.xlsx