# Excel Filter

> |

- **Type:** Skill
- **Install:** `agentstack add skill-yuyy2004-excel-skills-excel-filter`
- **Verified:** Pending review
- **Seller:** [YuYY2004](https://agentstack.voostack.com/s/yuyy2004)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [YuYY2004](https://github.com/YuYY2004)
- **Source:** https://github.com/YuYY2004/excel-skills/tree/main/claude/skills/excel-filter

## Install

```sh
agentstack add skill-yuyy2004-excel-skills-excel-filter
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

> This skill follows [[excel-safe-workflow]] four-step method. Filtering logic uses pandas (fast), deletion uses XML direct ops (fast + format-preserving).
> 本技能遵循 [[excel-safe-workflow]] 四步法。筛选逻辑用 pandas（快），删除用 XML 直接操作（快+格式无损）。

# Excel Filter / Excel 筛选

## Two Modes / 两种模式

| 模式 | 含义 | 用户说 |
|------|------|--------|
| **keep**（保留） | 保留符合条件的行，删除其余 | "只要2020年后的""保留已授权的" |
| **remove**（删除） | 删除符合条件的行，保留其余 | "删掉空白的""去掉无效数据" |

默认是 **keep** 模式。

## 第零步：需求解析

### 条件类型识别

| 用户说 | 条件类型 | pandas 表达式 |
|--------|----------|---------------|
| "申请日大于2020年" | 大于 | `df[col] > '2020-01-01'` |
| "申请日=2020年" | 等于 | `df[col] == '2020'` |
| "标题包含石墨烯" | 包含 | `df[col].str.contains('石墨烯', na=False)` |
| "申请人包含 华为 或 腾讯" | 包含(或) | `df[col].str.contains('华为|腾讯', na=False)` |
| "申请日在2020到2023之间" | 范围 | `(df[col] >= '2020-01-01') & (df[col] 2022的" | keep模式, 当前法律状态=授权 AND 申请日>2022 |

## 第一步：勘察

```python
import os, sys
sys.stdout.reconfigure(encoding='utf-8')
from openpyxl import load_workbook

FILE = '目标文件.xlsx'
size_mb = os.path.getsize(FILE) / 1024 / 1024
print(f'文件大小: {size_mb:.1f} MB')

wb = load_workbook(FILE, read_only=True)
ws = wb.active
print(f'工作表: {ws.title}, 行: {ws.max_row}, 列: {ws.max_column}')

# 表头
print('\n=== 表头 ===')
for col_idx in range(1, min(ws.max_column + 1, 30)):
    h = ws.cell(row=1, column=col_idx).value
    if h:
        col_letter = chr(64 + col_idx) if col_idx  VALUE
elif OPERATOR == 'gte':
    mask = df[COL] >= VALUE
elif OPERATOR == 'lt':
    mask = df[COL] = VALUE[0]) & (df[COL]  "筛选完成，共删除 X 行。XML 删除后行号不连续，Excel 打开会看到空白行。是否压实行号让数据连续？"

用户确认后，执行 [[excel-delete]] 中的压实步骤（解压 → 行号重新连续编号 → 公式引用同步更新 → 打包）。

## 第四步：验证

```python
print(f'\n③ 验证...')

# 用 openpyxl 验证
from openpyxl import load_workbook
wb = load_workbook(FILE, read_only=True, data_only=True)
ws = wb.active
print(f'  当前: {ws.max_row}行 × {ws.max_column}列')

# 公式健康检查
print(f'  公式健康检查...')
ref_errors = 0
for row_idx in range(1, min(50, ws.max_row + 1)):
    for col_idx in range(1, min(10, ws.max_column + 1)):
        v = ws.cell(row=row_idx, column=col_idx).value
        if v and isinstance(v, str) and '#REF!' in v:
            print(f'  ❌ {ws.cell(row=row_idx, column=col_idx).coordinate}: {v}')
            ref_errors += 1
if ref_errors == 0:
    print(f'  ✅ 无 #REF!')

wb.close()

# 用 pandas 验证筛选结果
df2 = pd.read_excel(FILE)
print(f'  结果行数: {len(df2)}')
if MODE == 'keep':
    # 检查留下的都满足条件
    if OPERATOR == 'contains':
        not_match = df2[~df2[COL].astype(str).str.contains(VALUE, na=False)]
    elif OPERATOR == 'eq':
        not_match = df2[df2[COL] != VALUE]
    print(f'  不符合条件残留: {len(not_match)} {"✅" if len(not_match)==0 else "❌"}')
```

## 常用条件速查

```python
# 单条件
df['申请人'] == '华为技术有限公司'                            # 等于
df['申请人'].str.contains('华为', na=False)                   # 包含
df['申请日'] >= '2020-01-01'                                  # 大于等于
df['申请日'].between('2020-01-01', '2023-12-31')              # 范围
df['申请人'].isna()                                           # 为空

# 多条件
(df['申请人'].str.contains('华为', na=False)) & (df['法律状态'] == '授权')   # 与
(df['申请人'].str.contains('华为', na=False)) | (df['申请人'].str.contains('腾讯', na=False))  # 或
~(df['申请人'].str.contains('华为', na=False))                               # 非
```

## 注意事项

1. **条件用 pandas**：pandas 的字符串/日期比较比 openpyxl 逐格判断快几个数量级
2. **删除用 XML**：格式无损，比 openpyxl 快 10 倍
3. **操作前必备份**：遵循 [[excel-safe-workflow]] 第零步——操作前自动备份（时间戳命名），成功后保留最新3份，失误后立即删除损坏文件并从备份恢复
4. **日期列**：如果是 Excel 日期（datetime 类型），用 `pd.Timestamp('2020-01-01')` 比较
5. **空值**：`isna()` 匹配 None/NaN，不会匹配空字符串，如需匹配空字符串用 `df[col] == ''`

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [YuYY2004](https://github.com/YuYY2004)
- **Source:** [YuYY2004/excel-skills](https://github.com/YuYY2004/excel-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** yes
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: flagged — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-yuyy2004-excel-skills-excel-filter
- Seller: https://agentstack.voostack.com/s/yuyy2004
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.