Zhonggong Costing House BOQ Item Cleaner

The Problem

Engineering cost data is often scattered across multiple .xlsx / .xls workbooks. BOQ or “分部分项” sheets may have inconsistent headers, merged-cell residue, mixed item numbers, missing values, or negative amounts. After manual copying and merging, it is hard to keep field definitions consistent or trace each row back to its source file.

How It Works

The skill takes an input folder and scans Excel files, excluding temporary files such as ~$*. It identifies target sheets by sheet name or the first 10 rows using a configurable keyword, defaulting to 分部分项. Within the first 20 rows, it locates 8 required fields: 序号, 项目编码, 项目名称, 项目特征, 计量单位, 工程量, 单价, and 合价. Sheets missing any required field are skipped.

For matched rows, it applies practical cleaning rules:
- Fill merged-cell gaps using forward fill;
- Validate 工程量, 单价, and 合价 as non-negative numbers;
- Accept integer, decimal, or string item numbers, and skip invalid rows;
- Add a 数据来源 column with the workbook name for audit tracing;
- Concatenate valid records and output a formatted .xlsx workbook with styled headers, thousands separators, borders, filters, and a frozen first row.

Boundaries

This is a focused cleaner for structured BOQ data, not a generic spreadsheet ETL tool. It supports .xlsx / .xls only, requires the eight target fields, and allows configuration for negative values, field matching, and logging. Back up source files before processing and watch memory usage for large workbooks; use a UTF-8 compatible environment for Chinese labels.