Qwen3-0.6B微调-Excel转标准JSON实战

{
  "title":        "string   职位名称",
  "city":         "string   工作城市，只留市名",
  "district":     "string   区县，可空",
  "salary_raw":   "string   原样字符串，一个字都不改",
  "salary_low_k": "number   月薪下限，单位 K",
  "salary_high_k":"number   月薪上限，单位 K",
  "salary_months":"number   一年发几薪，默认 12",
  "exp_raw":      "string   经验原文",
  "exp_years_min":"number   经验下限年数，不限填 0",
  "edu":          "string   枚举：大专/本科/硕士/博士/学历不限",
  "company":      "string",
  "source":       "string   枚举：zhaopin/liepin/boss/company_x",
  "url":          "string   原文里的链接，可空"
}

{"instruction":"你是Excel表头映射器。读取given列名与样例行，把每一列映射到标准字段。只能使用这些目标字段：title,city,district,salary_raw,salary_low_k,salary_high_k,salary_months,exp_raw,exp_years_min,edu,company,source,url。无法对应的列一律映射为IGNORE。只输出JSON对象，键为列名字符串，值为标准字段名。","input":"列名:[\"序号\",\"职位名称\",\"工作城市\",\"所在区县\",\"薪资(月薪) \",\"经验\",\"学历\",\"公司名称\",\"备注\",\"岗位链接\"]\n样例行1:[\"1\",\"风控策略运营\",\"北京\",\"海淀区·上地\",\"1.5万-2.5万·13薪\",\"3-5年\",\"本科\",\"某某科技\",\"需要出差\",\"https://x.cn/j/8812\"]\n样例行2:[\"2\",\"交易风控专家\",\"上海\",\"浦东新区\",\"2万-3.5万\",\"5-10年\",\"硕士\",\"某某基金\",\"\",\"https://x.cn/j/9021\"]","output":"{\"序号\":\"IGNORE\",\"职位名称\":\"title\",\"工作城市\":\"city\",\"所在区县\":\"district\",\"薪资(月薪) \":\"salary_raw\",\"经验\":\"exp_raw\",\"学历\":\"edu\",\"公司名称\":\"company\",\"备注\":\"IGNORE\",\"岗位链接\":\"url\"}"}

### qwen3_excel_mapping.yaml
model_name_or_path: Qwen/Qwen3-0.6B
trust_remote_code: true

stage: sft
do_train: true
finetuning_type: lora
lora_rank: 16
lora_alpha: 32
lora_dropout: 0.05
lora_target: all

dataset: excel_header_mapping_v3
template: qwen3            # 老版本挂 qwen 也能跑
cutoff_len: 1024           # 输入很短，没必要留长上下文
overwrite_cache: true
preprocessing_num_workers: 8

output_dir: saves/qwen3-0.6b/lora/excel_map_v3
logging_steps: 5
save_steps: 100
plot_loss: true
num_train_epochs: 3.0
per_device_train_batch_size: 8
gradient_accumulation_steps: 2
learning_rate: 1.0e-4
lr_scheduler_type: cosine
warmup_ratio: 0.03
bf16: true
flash_attn: auto

vllm serve /models/qwen3-0.6b-excel-map-awq \
  --served-model-name excel-mapper \
  --max-model-len 2048 \
  --enable-prefix-caching \
  --gpu-memory-utilization 0.55 \
  --dtype half

public final class HeaderLocator {

    private static final int SCAN_ROWS = 10;

    public static int find(Sheet sheet) {
        int best = 0;
        double bestScore = -1D;
        DataFormatter fmt = new DataFormatter();
        for (int r = 0; r <= Math.min(SCAN_ROWS, sheet.getLastRowNum()); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            int filled = 0;
            List<Integer> lens = new ArrayList<>();
            for (Cell c : row) {
                String v = fmt.formatCellValue(c).trim();
                if (!v.isEmpty()) {
                    filled++;
                    lens.add(v.length());
                }
            }
            if (filled < 2 || lens.isEmpty()) {
                continue;
            }
            double avg = lens.stream().mapToInt(Integer::intValue).average().orElse(0);
            // 表头特征：列都填了、名字都不长、名字长度还比较一致
            double spread = lens.stream().mapToDouble(l -> Math.abs(l - avg)).average().orElse(9);
            double score = filled * 2D - Math.max(0, avg - 8) - spread;
            if (score > bestScore) {
                bestScore = score;
                best = r;
            }
        }
        return best;
    }
}

@Service
@RequiredArgsConstructor
public class HeaderMappingClient {

    private static final Set<String> ALLOWED = Set.of(
        "title", "city", "district", "salary_raw", "salary_low_k", "salary_high_k",
        "salary_months", "exp_raw", "exp_years_min", "edu", "company", "source", "url", "IGNORE");

    private static final Pattern FENCE = Pattern.compile("\\{[\\s\\S]*}");

    private final RestClient llm;
    private final MappingCache cache;

    public Map<String, String> map(List<String> headers, List<List<String>> sampleRows) {
        String key = fingerprint(headers);
        var hit = cache.get(key);
        if (hit.isPresent()) {
            return hit.get();                       // 同一套表头只推理一次
        }

        String input = "列名:" + JSON.toJSONString(headers) + "\n"
            + IntStream.range(0, Math.min(3, sampleRows.size()))
              .mapToObj(i -> "样例行" + (i + 1) + ":" + JSON.toJSONString(sampleRows.get(i)))
              .collect(Collectors.joining("\n"));

        ChatBody body = ChatBody.builder()
            .model("excel-mapper")
            .messages(List.of(
                ChatMsg.system(SYSTEM),
                ChatMsg.user(input)))
            .temperature(0.0)                        // 关键，见下文
            .maxTokens(512)
            .addGenerationConfig("chat_template_kwargs", Map.of("enable_thinking", false))
            .build();

        String raw = llm.post().uri("/v1/chat/completions")
            .contentType(MediaType.APPLICATION_JSON).body(body)
            .retrieve().body(ChatResponse.class)
            .choices().get(0).message().content();

        Map<String, String> mapping = parseLoose(raw, headers);
        validate(mapping, headers);
        cache.put(key, mapping);
        return mapping;
    }

    /** 模型偶尔会包 ```json 围栏、或者结尾多一句"希望有帮助"，所以只截首尾大括号。 */
    private Map<String, String> parseLoose(String raw, List<String> headers) {
        Matcher m = FENCE.matcher(raw);
        if (!m.find()) {
            throw new MappingUnstableException("模型没返回 JSON: " + abbreviate(raw));
        }
        Map<String, String> parsed = JSON.parseObject(m.group(), new TypeReference<>() {});
        // 它可能把列名的空格吃掉，回贴成原列名
        Map<String, String> fixed = new LinkedHashMap<>();
        for (String h : headers) {
            String v = parsed.getOrDefault(h, parsed.get(h.trim()));
            fixed.put(h, v == null ? "IGNORE" : v.trim());
        }
        return fixed;
    }

    private void validate(Map<String, String> mapping, List<String> headers) {
        for (var e : mapping.entrySet()) {
            if (!ALLOWED.contains(e.getValue())) {
                throw new MappingUnstableException("非法目标字段 " + e.getValue());
            }
        }
        long named = mapping.values().stream().filter(v -> !"IGNORE".equals(v)).count();
        if (named < 2) {
            throw new MappingUnstableException("识别到的列太少，判定失败");
        }
        long distinct = mapping.values().stream().filter(v -> !"IGNORE".equals(v)).distinct().count();
        if (distinct != named) {
            throw new MappingUnstableException("两列映射到了同一个字段，交给规则兜底");
        }
    }
}

public List<JobRow> extract(List<String> headers, List<List<String>> rows,
                            Map<String, String> mapping) {
    List<JobRow> out = new ArrayList<>();
    for (List<String> row : rows) {
        Map<String, String> byField = new HashMap<>();
        for (int i = 0; i < headers.size(); i++) {
            String target = mapping.getOrDefault(headers.get(i), "IGNORE");
            if (!"IGNORE".equals(target) && !byField.containsKey(target)) {
                byField.put(target, cell(row, i));          // 先到的列优先
            }
        }
        if (isBlankRow(byField)) {
            continue;
        }
        JobRow j = new JobRow();
        j.setTitle(byField.get("title"));
        j.setCity(CityNorm.strip(byField.get("city")));        // "北京市/北京" -> 北京
        j.setDistrict(byField.get("district"));
        String salary = byField.get("salary_raw");
        j.setSalaryRaw(salary);
        salaryParser.parse(salary).ifPresent(s -> {
            j.setSalaryLowK(s.lowK());
            j.setSalaryHighK(s.highK());
            j.setSalaryMonths(s.months());
        });
        j.setExpRaw(byField.get("exp_raw"));
        j.setEdu(EduNorm.pick(byField.get("edu")));
        j.setCompany(byField.get("company"));
        j.setUrl(LinkNorm.first(byField.get("url")));
        j.setProblem(salary == null ? "no_salary" : null);
        out.add(j);
    }
    return out;
}

A           B              C        D(合并 D:F)      G          H
1  2026年春季校园招聘需求汇总表（本表由各部门填写后回传HR）
2  填表说明：薪资请写月薪，币种人民币；经验要求可写"不限"
3  序号   | 招聘部门·岗位 | 工作地 | 工作经历       | 薪资待遇   | 学历要求
4  1     | 风控-策略运营 | 北京市海淀区 | 3-5年    | 1.5万-2.5万·13薪 | 统招本科
5  2     | 交易风控     | 上海市  | 经验不限      | 20000-30000 | 硕士

{
  "序号": "IGNORE",
  "招聘部门·岗位": "title",
  "工作地": "city",
  "工作经历": "exp_raw",
  "": "IGNORE",
  "薪资待遇": "salary_raw",
  "学历要求": "edu"
}

{
  "ok": true,
  "fileKey": "2026春招需求表_final_v3.xlsx",
  "headerRow": 3,
  "total": 2,
  "mapping": { "薪资待遇": "salary_raw", "工作地": "city", "招聘部门·岗位": "title" },
  "columns": [
    { "field": "title",   "label": "岗位" },
    { "field": "city",    "label": "城市" },
    { "field": "salary",  "label": "月薪" },
    { "field": "edu",     "label": "学历" },
    { "field": "exp",     "label": "经验" }
  ],
  "rows": [
    {
      "title": "风控-策略运营",
      "city": "北京", "district": "海淀区",
      "salary_raw": "1.5万-2.5万·13薪",
      "salary_low_k": 15, "salary_high_k": 25, "salary_months": 13,
      "exp_raw": "3-5年", "exp_years_min": 3,
      "edu": "本科", "company": "风控", "problem": null
    },
    {
      "title": "交易风控",
      "city": "上海", "district": null,
      "salary_raw": "20000-30000",
      "salary_low_k": 20, "salary_high_k": 30, "salary_months": 12,
      "exp_raw": "经验不限", "exp_years_min": 0,
      "edu": "硕士", "company": null, "problem": "no_company"
    }
  ],
  "problems": [ { "row": 2, "field": "company", "code": "no_company" } ]
}