---
name: xlsx
description: "处理电子表格文件，支持检查、清洗、转换、编辑、公式、格式和简单图表。"
author: Xiake / OpenClaw
license: MIT
metadata:
  openclaw:
    tags: [spreadsheet, xlsx, csv, data-cleaning]
---

# Xiake XLSX Skill

This is a clean OpenClaw-native spreadsheet workflow for local workbook files. Prefer deterministic Python scripts and inspect the actual workbook before editing.

## Core Workflow

1. Identify the input format and user goal: inspect, clean, edit, create, convert, validate, or summarize.
2. Make a quick workbook inventory before changing files: sheet names, dimensions, headers, data types, formulas, merged ranges, and obvious blank or duplicate rows.
3. Choose the narrowest tool:
   - Use `pandas` for tabular cleaning, joins, filtering, aggregation, CSV/TSV conversion, and simple workbook creation.
   - Use `openpyxl` for preserving or editing workbook structure, formulas, sheets, widths, freezes, styles, filters, merged cells, and comments.
   - Use LibreOffice headless, when available, to recalculate formulas or convert legacy formats.
4. Write output to a new file unless the user explicitly asks to overwrite.
5. Reopen the result and validate row counts, sheet names, formulas, and representative cell values.

## Script Helper

Use `scripts/xlsx_tool.py` for common inspection and conversion tasks:

```bash
python "${OPENCLAW_BUNDLED_SKILLS_DIR:-$HOME/.openclaw/skills}/xlsx/scripts/xlsx_tool.py" inspect input.xlsx
python "${OPENCLAW_BUNDLED_SKILLS_DIR:-$HOME/.openclaw/skills}/xlsx/scripts/xlsx_tool.py" csv-to-xlsx input.csv output.xlsx
python "${OPENCLAW_BUNDLED_SKILLS_DIR:-$HOME/.openclaw/skills}/xlsx/scripts/xlsx_tool.py" xlsx-to-csv input.xlsx out_dir
```

If the bundled path is unknown, locate this skill directory first, then run the script from its `scripts/` folder.

## Common Tasks

### Inspect a workbook

Inventory first. Check:

- Sheet names and dimensions
- Header row candidates
- Empty rows or columns
- Duplicate headers
- Formula cells
- Merged ranges
- Freeze panes and filters

Use the helper or a short `openpyxl` script. Do not assume the first sheet is the only important sheet.

### Clean messy tabular data

Use `pandas` when the workbook is essentially tables:

```python
import pandas as pd

df = pd.read_excel("input.xlsx", sheet_name="Sheet1")
df.columns = [str(c).strip() for c in df.columns]
df = df.dropna(how="all").drop_duplicates()
df.to_excel("cleaned.xlsx", index=False)
```

After writing, inspect the output file and compare row/column counts.

### Preserve workbook layout

Use `openpyxl` when the file has formulas, formatting, multiple sheets, merged cells, charts, or workbook-level structure:

```python
from openpyxl import load_workbook

wb = load_workbook("input.xlsx")
ws = wb["Sheet1"]
ws["A1"] = "Updated"
ws.freeze_panes = "A2"
ws.auto_filter.ref = ws.dimensions
wb.save("updated.xlsx")
```

Prefer editing specific cells or ranges over rebuilding the workbook.

### Create a workbook from data

For simple output, use `pandas.ExcelWriter`; for polished output, use `openpyxl` after writing:

```python
import pandas as pd

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Data", index=False)
    summary.to_excel(writer, sheet_name="Summary", index=False)
```

Then reopen with `openpyxl` to set widths, freeze panes, filters, number formats, and tab colors if requested.

### Handle formulas

`openpyxl` preserves formulas but does not calculate them. If calculated values are required:

- Use LibreOffice headless if installed.
- Otherwise, explain that formula results will update when the workbook is opened in Excel or another spreadsheet app.
- For formulas you generate yourself, validate the formula strings and the referenced ranges.

### Convert CSV or TSV

Detect delimiters and encodings carefully. For CSV/TSV to XLSX, preserve strings that look like IDs, ZIP codes, or account numbers:

```python
import pandas as pd

df = pd.read_csv("input.csv", dtype=str)
df.to_excel("output.xlsx", index=False)
```

For XLSX to CSV, export each sheet separately unless the user names one sheet.

## Validation Checklist

- Output file opens with `openpyxl.load_workbook`.
- Expected sheets exist.
- Header row is correct.
- Row and column counts match the intended transformation.
- Critical formulas are present and reference the expected ranges.
- Dates, currency, percentages, IDs, and leading zeros are preserved.
- The output path is clearly reported to the user.

## Safety Notes

- Do not silently overwrite user files.
- Do not flatten a multi-sheet workbook unless requested.
- Do not convert identifiers to numbers when leading zeros matter.
- Do not drop formulas or formatting when the user asked for a workbook edit rather than a data-table rewrite.
