byted-sol-excel-bill-discount

Excel Bill Processing Tool

Function Description

Automatically processes the uploaded bill Excel detail sheet, generates a "Bill Discount Overview" pivot table that meets the format requirements, strictly follows the specified format, data retention, and verification rules. The generated file has no Excel repair pop-up and is compatible with Excel 2016+.

Usage

When the user uploads a bill Excel file and requests processing, directly call the processing script of this skill and pass in the input file path:

python ~/.openclaw/skills/byted-sol-excel-bill-discount/process_bill.py <输入文件路径>

Processing Rules (Strictly Follow)

1. Worksheet Requirements

• Create a new "Bill Discount Overview" pivot table and place it in the leftmost position
• Fully retain all content and formatting of the original worksheet
• Date field formatting in the original worksheet:
• Accounting Period, Business Period → YYYY-MM format
• Accounting Date → YYYY/MM/DD format
• Consumption Start Time (UTC+8), Consumption End Time (UTC+8), Transaction Time (UTC+8) → YYYY/MM/DD hh:mm:ss format
• Original worksheet font: DengXian 11, zoom 90%

2. Pivot Table Field Requirements

Filter the fields actually present in the original table from the following field list, arrange them in the original order, and fields missing from the original table do not need to be displayed in the pivot table: 账务账期、Payer账号ID、Payer ID、Owner账号ID、账号 ID、产品、商品、计费模式、账单类型、配置名称、计费单元、单价、单价单位、优惠类型、优惠内容、用量、原价、折后价、代金券抵扣、应付金额

• ForUsage, Original Price, Discounted Price, Voucher Deduction, Amount Payableaggregate and sum the fields present in the original table
• All configuration names and billing unit data must be fully retained and must not be missing

3. Format Requirements

• All cells: Microsoft YaHei 10 font, zoom 90%, all cells bordered
• First row (header): fill #BDD7EE, bold and centered
• Last row (total row): fill #D9D9D9, bold and centered
• Amount fields (Original Price, Discounted Price, Voucher Deduction, Amount Payable): 2 decimal places, thousands separator, right-aligned
• Usage field: no thousands separator, retain the natural length of the value, no fixed number of decimal places
• Centered columns: Accounting Period, Billing Mode, Bill Type, Unit Price, Unit Price Unit, Discount Type, Discount Content
• Automatic column width adaptation: each column width = (longest cell character width, Chinese counts as 2 units/English and digits count as 1 unit) × 1.1
• Accounting Period format: YYYY-MM

4. Output File Naming Rules

Generated by parsing the "Accounting Period" field:A年B月产品折扣账单.xlsx

• Example: Feb-26 → A=26, B=2
• Example: 2026-02-01 → A=26, B=2

5. File Specification Requirements

Must use xlsxwriter to generate the file, ensuring:

• No repair prompt pop-up when opening
• Complies with Office Open XML specifications, no XML structure errors
• Compatible with Microsoft Excel 2016+

6. Data Processing Principles

• Retain all original data, do not delete any rows, do not filter any content
• Automatic data cleaning is prohibited, determining dirty data is prohibited, and deleting specially formatted names is prohibited
• Mandatory use during grouped aggregationdropna=False, do not ignore any data

7. Verification Rules

Must verify before generation:

• Pivot table original price total = original table original price total
• Pivot table total discounted price = Original table total discounted price
• Pivot table total amount payable = Original table total amount payable
• If validation fails, prompt "Totals are inconsistent, cannot generate"