Xlsx Multisource Merge

Overview

Consolidate multiple supplier Excel files into one template, with composite-key merging and formatting rules. Handles three pain points:

External-link-unsafe templates — when the target xlsx contains [N]汇总!... external references, openpyxl.load_workbook + writeback wipes 4-5 sheets. Use direct ZIP-edit of xl/worksheets/sheetN.xml instead.
Composite-key merge with batch-shared cells — supplier rows that share values per shipment (pallets, total volume) are stored as vertical mergeCell, not summed per row.
Mixed integer/decimal display — a single column can hold both integers (17, 2800) and decimals (2906.03, 4.91). Single static 0.00 format breaks both; per-cell assignment ('0' for integer, '0.000' for decimal) is the fix.

Mode Selection (ASK ONCE — never re-ask)

Two output modes. Decide with the user once at the start, in a single question batch together with the source/target confirmation; then proceed without re-asking.

| Mode | 中文 | Behaviour | Row count | |---|---|---|---| | A. Collect-only | 仅汇总 / 只汇总不合并 | Copy every source row into the summary sheet. No dedup, no composite key, no aggregation. | out_rows == in_rows (N 条进 = N 条出) | | B. Merge | 汇总 + 合并 | Group rows by composite key (e.g. Device No. + brand + COO + Model) and sum the numeric columns. | out_rows == number of distinct keys |

User's own definition of Mode A (verbatim intent):「只把数据统计到汇总表，相同物料号的数据不合并，要统计的数据表有多少条就汇总多少条」. Mode A still does: header normalization, source-row ordering (per supplier / per file), numeric formatting, and BILL post-processing — it just never merges duplicates.

How to implement Mode A:

Reuse aggregate.py but set key_cols=[] / pass merge=False so each source row stays its own output row (mode='collect' in aggregate_sources). Keep the source_file / source_row origin so the summary stays auditable.
Then write through the same zipedit_write.py / apply_mixed_format.py pipeline.
Batch-shared columns (pallets / volume) are copied per row in Mode A (no vertical mergeCell grouping), because rows are not grouped by shipment.
Mode A is the safe default when the user is unsure: nothing is lost, dedup can be applied afterwards.

Input Auto-Detection (avoid repeated questions)

Do NOT ask the same thing twice. Detect first, ask at most once (batch all remaining unknowns into one AskUserQuestion call):

Target / 汇总表 — the file the data goes INTO:
- the only xlsx in the folder that contains sheets named CT合同 / INVOICE发票 / PL清单 (trailing spaces allowed), or whose filename contains CT INV PK / 报关;
- exclude files whose name starts with BD- (those are supplier data files).
Data / 数据表 — supplier files to be counted:
- BD-*.xlsx, one per supplier sub-folder (e.g. BD-YY…-胜宏packing list.xlsx, BD-LJ…-绿鲸 Packing List.xlsx, BD-CY…-铖宇Packing List.xlsx);
- the matching *送货单*.pdf is the shipment document, not a data source.
Sheets to write — default: the sheets that actually contain the merge-key columns (品牌/原产国/型号). Ask only if the folder holds several candidates.
Mode — ask once (see Mode Selection).
BILL post-processing — default ON: 装运日期 = today, TRUCK NO. cleared (see step 5). Ask only if the user said the BILL must stay untouched.
Old-data clearing — default ON: before writing, stale rows in the target data range are wiped via clear_old_data.py (see step 0). State it in the one-line defaults announcement; ask only if the user wants rows preserved.

Only fall back to a question when detection is genuinely ambiguous (two candidate target files, unknown supplier set). State the detected defaults in one line and proceed.

Workflow Decision Tree

START
  │
  ├─ Q0: Which mode?
  │     A. 仅汇总/collect-only → aggregate.py(mode='collect', key_cols=[]) — keep every row
  │     B. 汇总+合并/merge      → aggregate.py(mode='merge', key_cols=[...])  — dedup + sum
  │
  ├─ Q1: Does the target xlsx contain external `[N]xxx!` refs?
  │     YES → use ZIP-edit path (scripts/zipedit_write.py)
  │     NO  → openpyxl save is safe, use scripts/apply_mixed_format.py
  │
  ├─ Q2: Are some source columns "batch-shared" (same value per shipment)?
  │     YES → see references/batch-shared-cells.md; preserve vertical mergeCell
  │
  ├─ Q3: Source files have inconsistent header names (物料编码 / Device No. / Part No.)?
  │     YES → use scripts/aggregate.py (fuzzy header matching)
  │
  └─ Q4: Output columns mix integers and decimals?
        YES → per-cell format via scripts/apply_mixed_format.py

  └─ Q5: BILL sheet needs 装运日期/车牌 handling? (default YES)
        YES → scripts/postprocess_bill.py (date=today, TRUCK NO. cleared)

  └─ Q6: Target sheet already contains old data rows? (default YES → clear first)
        YES → scripts/clear_old_data.py BEFORE writing new rows
        (auto-detects old-data extent; ZIP-edit when external links present)

Quick Start

0. Clear stale data in the target sheet (DEFAULT ON — always run before writing)

The summary sheet usually still holds rows from the previous run. New rows must NOT be appended next to/below stale data — clear first, then write.

from scripts.clear_old_data import clear_old_data
report = clear_old_data(
    'target.xlsx', 'target_cleared.xlsx',
    sheet_name='PL清单',
    data_start_row=15,            # first data row (below the header)
    key_columns=('A', 'B', 'C'),  # columns that identify a data row
)
# report = {'cleared_rows': 16, 'start': 15, 'end': 30, 'method': 'zipedit'}

Auto-detect: scans key_columns (read-only openpyxl, safe on external-link files) to find the last row still holding data — no need to guess end_row.
Method auto-pick: files with external [N]xxx! links → ZIP-edit (values stripped, cell styles/borders KEPT so the template look survives); safe files → plain openpyxl.
Then point write_rows_via_zipedit (step 2) at the CLEARED output file.
Detection only touches the data range — header rows and the Total/Subtotal row (which usually has no value in key_columns) are never cleared.

1. Aggregate multiple sources (mode A collect-only / mode B merge)

from scripts.aggregate import aggregate_sources

# Mode B — 汇总+合并: dedup by composite key, sum the numeric columns
groups = aggregate_sources(
    sources=['shenghong_pl.xlsx', 'lvjing_pl.xlsx', 'chengyu_pl.xlsx'],
    mode='merge',
    key_cols=['Device No.', 'Brand', 'COO', 'Model'],
    sum_cols=['Quantity', 'Cartons'],
    header_row=13,  # supplier file's header row (varies)
)

# Mode A — 仅汇总/只汇总不合并: every source row becomes one output row
groups = aggregate_sources(
    sources=['shenghong_pl.xlsx', 'lvjing_pl.xlsx', 'chengyu_pl.xlsx'],
    mode='collect',            # no dedup, no summing
    header_row=13,
)
# len(groups) == total source data rows (N in = N out)

aggregate.py does fuzzy header matching (物料编码 ≈ Device No. ≈ Part No. ≈ part), field normalization (无→无型号, COO→CHINA), and per-source column index discovery.

2. Write merged rows into target sheet (ZIP-edit path)

When target xlsx has external links ([N]汇总!... in any cell formula):

from scripts.zipedit_write import write_rows_via_zipedit
write_rows_via_zipedit(
    target_path='contract_template.xlsx',
    output_path='contract_filled.xlsx',
    sheet_name='CT合同 ',           # trailing space preserved
    data_start_row=21,             # first row to replace
    data_end_row=35,               # last row to replace
    rows=groups,                   # list of dicts from step 1
    column_map={'A': 'item_no', 'B': 'device_no', 'C': 'commodity', ...},
    shared_strings_mode='reuse',   # 'reuse' or 'append'
)

This rewrites the row range directly in xl/worksheets/sheetN.xml and appends/reuses entries in xl/sharedStrings.xml. Other sheets (INVOICE/PL/BILL/AN) are byte-identical to source — no openpyxl re-serialization.

3. Remap mergeCells after row shift

When inserting/removing rows changes row numbers of mergeCell ranges:

from scripts.zipedit_write import remap_merge_cells
remap_merge_cells(
    target_path='output.xlsx',
    sheet_name='INVOICE发票 ',
    row_map={27: 141, 28: 142, ...},  # old_row -> new_row
)

Common patterns: Subtotal row shift, batch merge ranges moving with their data block.

4. Apply mixed integer/decimal number format

from scripts.apply_mixed_format import apply_mixed_format
apply_mixed_format(
    target_path='output.xlsx',
    sheet_name='PL清单',
    cell_range='K15:N30',     # K=Pallets, L=NW, M=GW, N=Volume
    integer_format='0',       # 17 → 17 (no decimal point)
    decimal_format='0.000',   # 4.91 → 4.910 (padded)
)

For openpyxl-safe files (no external links), openpyxl is used; for unsafe files, ZIP-edit the <xf> numFmtId mapping in xl/styles.xml directly.

5. Post-process the BILL sheet (装运日期 = today, 车牌 cleared) — default ON

Customs BILL sheets carry two fields that must be refreshed on every shipment:

| Field | Label as found in the sheet | Value cell | Action | |---|---|---|---| | Date of Loading (装运日期) | BILL!A9 = "Date of Loading(装运日期）" | BILL!A10 (merge A10:D10) | set to today | | TRUCK NO. (车牌/车头/柜号) | BILL!F20 = "TRUCK NO." | BILL!F21 (merge F21:H21) | clear（整格清空） |

from scripts.postprocess_bill import postprocess_bill
report = postprocess_bill(
    'merged_output.xlsx',
    'merged_output_final.xlsx',
    sheet_name='BILL',
    set_date=True,       # Date of Loading -> date.today()  (or pass a datetime)
    clear_truck=True,    # TRUCK NO. -> emptied, style/border kept
)

Location is label-driven (find_label_ref → value cell is the one directly BELOW the label), so it works on template variants with different row numbers.
Uses pure ZIP-edit → safe for templates with external [N]xxx! links; only xl/worksheets/sheetN.xml changes, every other zip part stays byte-identical.
Clearing keeps the cell's style (border of the mergeCell block survives).
The 车牌/TRUCK NO. value cell is a shared-string cell. Concrete ZIP-edit of xl/worksheets/sheet4.xml (GB 桂FB5878 template — BILL = sheet4.xml):
```
<c r="F21" s="80" t="s"><v>109</v></c>  →  <c r="F21" s="80"/>
```
Keep s= so the F21:H21 mergeCell border/look survives; drop only t="s" and <v>. The now-orphan sharedStrings.xml entry is harmless — do NOT re-index sharedStrings.xml. Verify with load_workbook(out)['BILL']['F21'].value is None and a byte-diff showing only sheet4.xml changed.
整格清空: the field is the whole 车牌/挂号/柜号 block (the cell holds e.g. 车牌: 桂FB5878 \n挂号：桂FA055挂\n柜号: 无) → clear the ENTIRE cell, not just the plate number.
AN sheet pulls these through formulas (AN!B14 = =BILL!G23+1, AN!A17 = =BILL!G1), so updating BILL is enough — do NOT edit AN separately.

6. 生成《汇总报表 · 数据统计》文档（用户常要求）

用户原话：「在生成一个汇总报表的统计文档，告诉我汇总了哪些文档的数据，各个 sheet 的数据统计。」

from scripts.make_summary_report import make_summary_report
make_summary_report(
    output_path='汇总报表统计_9月14日.xlsx',
    target_file=target_name, output_file=out_name, run_date='2026-09-14',
    sources=[dict(name=..., type='供应商箱单 ★主数据源', supplier='乐健',
                  sheet='PL清单', row_range='18-24', rows=7, doc_no=..., date=..., usage=...), ...],
    sheet_stats=[dict(sheet='PL清单', rows=5, qty=30940, cartons=649, pallets=22,
                      nw=9853.67, gw=10871.27, vol=23.32),
                 dict(sheet='CT合同 ', rows=5, qty=30940, amount=288021), ...],
    merge_detail=[dict(no=1, supplier='乐健', device_no='C00HSB5D1A2100',
                       src_file=..., src_range='PL清单!18', merged_rows=1,
                       qty=3240, cartons=72, pallets=3, nw=1008, gw=1080, vol=2.2), ...],
    rules=['PL清单 Total GW (kg) 取每笔单汇总值（读源表公式缓存），不是单独取。',
           '批次共享列以竖合并呈现，清除时不得取消合并，否则横线消失。'],
)

Produces 4 sheets: 汇总说明（目标/输出文件、模式、主键、口径规则）、 数据来源清单（每份文档的类型/工作表/行范围/有效行数/单据号/日期/用途）、 各Sheet数据统计（每张表的行数与数量/箱数/板数/净重/毛重/体积/金额合计）、 合并明细（源行范围 → 合并记录，含合并行数）。

Conventions that make the report trustworthy:

Mark the true value source with ★主数据源; list 到仓追踪清单 / 送货单 / 司机信息表 / 备案资料 as 参考 or 核对 only — and say so, because the 到仓追踪清单's 重量/体积 columns use a different caliber and must never be taken as the packing-list figures.
Every total in the report must be re-derivable: FG/GW/NW/体积 sums must equal the target sheet's own Total row, and the merge detail's sums must equal the 各Sheet statistics.

Key Techniques

Why ZIP-edit instead of openpyxl

The original template had [3]汇总!... external references (created by WPS/Excel). openpyxl.load_workbook(..., keep_links=True/False) + wb.save() strips all cell content from sheets that referenced those external cells. Direct XML editing bypasses this entirely.

See references/openpyxl-pitfalls.md for detection and recovery patterns.

sharedStrings reuse vs append

When inserting rows into a sheet that already has strings:

Reuse mode: build a Python dict mapping value → index from existing xl/sharedStrings.xml; for new values, append and update count/uniqueCount.
Inline mode: for ad-hoc strings (e.g., text dimension like 1.1*1.1*1), use <c t="inlineStr"><is><t>1.1*1.1*1</t></is></c> — no sharedStrings modification needed.

Per-cell number format

Single static format fails for mixed columns:

| Format | Integer (17) | Decimal (4.91) | 3-decimal (14.592) | |--------|--------------|----------------|---------------------| | 0.00 | 17.00 ❌ | 4.91 ⚠ | 14.59 ❌ (rounded) | | 0.### | 17. ❌ | 4.91 ✓ | 14.592 ✓ | | 0.000 | 17.000 ❌ | 4.910 ✓ | 14.592 ✓ | | per-cell '0' / '0.000' | 17 ✓ | 4.910 ✓ | 14.592 ✓ |

Solution: detect float(v)==int(v) per cell and assign '0' or '0.000' accordingly.

批次共享列：清除时绝不解除竖合并（否则横线消失）

Batch-shared columns (板数 Pallets / 毛重 GW / 体积 Volume) are stored in the target as vertical mergeCell spanning the supplier's rows — e.g. PL清单 K16:K17 (板数 13), M15:M17 (乐健整单毛重 7807.1), M18:M19 (鑫百汇整单毛重 3064.17), N16:N17 (体积 11.58). Those merge ranges are what removes the horizontal grid line between the batch rows, so they ARE the template's look. The single most common defect: clearing old data with ws.unmerge_cells(...) then writing 0, which leaves the freed cells styleless → 横线（单元格上边框）消失, and 0 also prints a stray zero.

Rules:

Never unmerge_cells on the target. Rewrite the cell XML in place instead: <c r="K17" s="164">…</c> → <c r="K17" s="164"/> — keep the s= style index, drop only <v>. The style (and therefore the border) survives; the merge is untouched.
Blank out cells whose source value is None (do not write 0): K17(板数)/N17(体积) for the 乐健 PCB 3rd item, M19(毛重) for the 鑫百汇 2nd item.
Verify after writing: the merge list must still contain K16:K17, M15:M17, M18:M19, N16:N17, and the style ids of the merged children must equal the template's (K17→164, N17→165, M19→166, M16/M17→157 in the GB template).
板数列(Pallets)横线补齐（用户原话：「这里的横线也要加上」）: the Pallets column is the one that visibly loses its horizontal lines. Its batch rows carry only side borders — s=161 = border15 (L+R, no top) — whereas Quantity/Cartons/NW columns use four-sided border1, so only Pallets shows gaps at 15/16、17/18、18/19. Fix by ZIP-editing sheet3.xml and repointing those cells at the template's top-lined style s=164 (numFmt184 + fill2 + border13 = L+R+T):
```
<c r="K16" s="161"><v>13</v></c>  →  <c r="K16" s="164"><v>13</v></c>
<c r="K18" s="161"><v>1</v></c>   →  <c r="K18" s="164"><v>1</v></c>
<c r="K19" s="161"><v>5</v></c>   →  <c r="K19" s="164"><v>5</v></c>
```
(K15/K17 already use border13; K20's border1 supplies the line under row 19.) Excel draws a horizontal grid line when either the upper row's bottom or the lower row's top is set, so giving every data row a top border makes the whole column continuous. Borders inside a vertical merge (K16:K17) are hidden by the merge — that is correct, never try to "fix" those.
Byte-diff the finished file against the template: all 39 parts must survive (xl/drawings/**, xl/media/image1.png, image2.GIF, xl/worksheets/_rels/**, docProps/custom.xml).

每笔单汇总：Total GW (kg) 必须取源表汇总值，不要单独取

用户原话：「PL清单的 Total GW (kg) 列取每笔单的汇总，不要单独取。」

Supplier packing lists often hold GW/NW as a formula: 乐健 Q19 = 2921.6+12.3 (净重) and R19 = 3087.6+13.3 (毛重). If you read the source with data_only=False, those cells come back as the string "=2921.6+12.3"; a naive re.findall(r'[\d.]+', v)[0] returns only the first summand → 2921.6 / 3087.6（单独取）instead of 2933.9 / 3100.9（每笔单汇总）.

Always openpyxl.load_workbook(src, data_only=True) for the SOURCE files → the cached formula result is the per-order aggregate.
Cross-check: the per-item GW of one supplier must sum to that packing list's own Total row (乐健 1080 + 3100.9 + 3336.3 = 7517.2 = R25; 鑫百汇整单 3064.17 = Q16).
The PL清单 M20 Total GW is NOT =SUM(M15:M19); it includes pallet weight and is written as a value: 乐健「包含16个卡板总毛重」7807.1 + 鑫百汇 3064.17 = 10871.27 kg. BILL pulls it via BILL!G17 = PL清单!M20, so the customs gross weight depends on this exact number.

同一单多行物料：Total GW (kg) 竖向合并为一个整单汇总值

用户原话：「这里这3项目属于同一单，应该合并一个，值取 7807.1。」

When several material rows in PL清单 belong to the same order (same supplier, one shipment), their GW must be displayed as one vertically-merged cell holding the whole-order gross weight — NOT three separate per-material values. 乐健's 3 items (rows 15/16/17 = materials C00HSB5D1A2100 / C00HSB4D5L2133 / C00HSB4D3A2140) are one order → merge M15:M17 with value 7807.1（源表乐健 PL清单 R27 = 7807.1，「包含16个卡板总毛重」），并把 M16/M17 清空（保留 style，去掉 <v>）。

# ZIP-edit xl/worksheets/sheet3.xml — merge the same-order GW rows (PL清单)
xml = xml.replace('<c r="M15" s="156"><v>1080</v></c>',   '<c r="M15" s="157"><v>7807.1</v></c>')
xml = xml.replace('<c r="M16" s="163"><v>3100.9</v></c>', '<c r="M16" s="157"/>')
xml = xml.replace('<c r="M17" s="163"><v>3336.3</v></c>', '<c r="M17" s="157"/>')
xml = xml.replace('<mergeCells count="12">',              '<mergeCells count="13">')
xml = xml.replace('<mergeCell ref="J7:N8"/>',             '<mergeCell ref="M15:M17"/><mergeCell ref="J7:N8"/>')
# s=157 = numFmt185 "0.000" + fill4 (row-15 light-green highlight) + border13 (top line)

Merge range covers only that order's material rows; sibling batch merges (K16:K17, N16:N17) stay untouched.
Host cell (top-left, M15) takes the whole-order value; the other cells are blanked with their style kept (<c r="M16" s="157"/>) — never write 0, never unmerge.
Result: PL清单 keeps 4 batch merges K16:K17 / M15:M17 / M18:M19 / N16:N17; Total M20 remains 10871.27.
After the merge, openpyxl.load_workbook(out).merged_cells.ranges must include M15:M17, and a byte-diff vs the pre-merge file must show only sheet3.xml changed (still 39 parts).

Critical Don'ts

❌ Don't openpyxl.save() on a file with [N]汇总! external refs — wipes content and silently drops xl/sharedStrings.xml, xl/drawings/** + xl/media/** (embedded logos) and xl/worksheets/_rels/**. Verified in the wild: a 41-part template came back as 19 parts with the whole BILL header block (SHIPPER / Date of Loading / TRUCK NO.) blanked out.
❌ Don't %.3f-format prices with 4-decimal precision (0.0095 → 0.010). Use repr().
❌ Don't "sum" vertically mergeCell'd columns (Pallets/Volume) — they're batch-shared, not per-row.
❌ Don't unmerge_cells() in the target to clear old data — that is what deletes the batch rows' horizontal border (横线消失). Rewrite the cell XML and keep the s= style, then blank the cell (<c r="K17" s="164"/>); never write 0 for a source None.
❌ Don't read supplier GW/NW with data_only=False — a formula cell =3087.6+13.3 returns the literal string, and grabbing the first number yields 3087.6 instead of the per-order aggregate 3100.9. 「Total GW (kg) 取每笔单的汇总，不要单独取」→ load the SOURCE with data_only=True.
❌ Don't recompute PL清单 M20 as =SUM(M15:M19) — the template's Total GW is a value that adds pallet weight (乐健 7807.1 + 鑫百汇 3064.17 = 10871.27) and feeds BILL!G17.
❌ Don't give each material row its own GW when those rows belong to the same order — merge them into one cell (乐健 M15:M17 = 7807.1) and blank the rest. Per-material GW (1080 / 3100.9 / 3336.3) is wrong for the customs PL清单; the whole order shares one gross weight.
❌ Don't leave the Pallets column with only side borders (s=161/border15) while the other numeric columns are four-sided — that is exactly the 「板数列横线没了」defect. Repoint those cells to the top-lined s=164 (border13). 用户原话：「这里的横线也要加上」。
❌ Don't blank only the plate number (e.g. 桂FB5878) and keep 挂号/柜号 — the 车牌 cell is cleared as a whole (<c r="F21" s="80"/>), style/merge kept.
❌ Don't use re.search(r't="s"', cell_body) — t="s" is on the <c> tag attribute, not the cell body. Match the full <c> tag.
❌ Don't merge rows in collect-only mode (仅汇总). N source rows in → N output rows out.
❌ Don't re-ask which file is the target/source or which mode — detect once, ask at most once.
❌ Don't write new rows into a summary sheet that still holds old data — always run clear_old_data.py first (default ON), otherwise stale rows mix with fresh output.
❌ Don't clear the header row or the Total/Subtotal row — restrict detection to key_columns within the data range.
❌ Don't lock the workbook yourself — if a Tencent Docs preview is open, write to a new file (_v2.xlsx) instead of overwriting.

Resources

scripts/aggregate.py — multi-source aggregation, mode='merge' | 'collect', weighted fuzzy header matching
scripts/zipedit_write.py — ZIP-edit row range replacement + sharedStrings handling + mergeCell remap
scripts/apply_mixed_format.py — per-cell number_format assignment
scripts/clear_old_data.py — auto-detect + clear stale data rows in target sheet (ZIP-edit for external-link files, styles kept)
scripts/postprocess_bill.py — BILL sheet 装运日期=today / TRUCK NO.=cleared (label-driven, ZIP-edit)
scripts/make_summary_report.py — 生成《汇总报表 · 数据统计》4-sheet 文档（来源清单/各Sheet统计/合并明细/口径说明）
references/openpyxl-pitfalls.md — detection, recovery, when-to-use-ZIP-edit guide
references/batch-shared-cells.md — vertical mergeCell for batch totals
references/sharedstrings-guide.md — xl/sharedStrings.xml reuse/append patterns

Known Template Layouts (this project)

| Sheet | Key rows | Notes | |---|---|---| | CT合同 | header r20, data r21-r35, total r36 | J3-adjacent Date: field; trailing space in sheet name | | INVOICE发票 | header r11, data r12-r140, Subtotal r141 | =SUM(J12:J140) / =SUM(L12:L140) | | PL清单 | header r14, data r15-r143, Total r144 | K=Pallets, L=NW, M=GW, N=Volume; batch vertical mergeCell | | PL清单 (GB 桂FB5878 template, verified) | header r14, data r15-19, Total r20 | A-N = Item/Device No./Commodity/Specifications/Model/brand/COO/Unit/Quantity/Cartons/Pallets/NW/GW/Volume; A-I are formulas pulling 'INVOICE发票 '!…; batch merges K16:K17(板数13) M15:M17(乐健整单毛重7807.1，含16卡板) M18:M19(鑫百汇整单毛重3064.17) N16:N17(体积11.58); M20 Total GW = value 10871.27 (= 乐健含卡板 7807.1 + 鑫百汇 3064.17), other totals are SUM formulas | | BILL | header block r1-r15, item table r16+, TOTAL r18/r29 | A9 Date of Loading → A10; F20 TRUCK NO. → F21 | | AN | r16 header + data rows | pulls BILL values by formula; no separate edit needed | | supplier PL | header row detected (r13/r14), data below | BD-*.xlsx; 胜宏/铖宇 qty=col16 cartons=15, 绿鲸 qty=15 cartons=14 |