wehub-resource-sync
57 lines
1.6 KiB
Markdown
57 lines
1.6 KiB
Markdown
# Spreadsheet Manipulation Skill (xlsx)
|
|
|
|
## Overview
|
|
This skill guides agents in manipulating Excel (.xlsx) spreadsheets using Python.
|
|
|
|
**Primary libraries**: `openpyxl` (structure-preserving read/write), `pandas` (data transformation).
|
|
Never use any other third-party libraries.
|
|
|
|
---
|
|
|
|
## Common Workflow
|
|
|
|
1. **Explore** the input file: list sheets, inspect headers, check dimensions.
|
|
2. **Write `solution.py`** with `INPUT_PATH` and `OUTPUT_PATH` defined at the top.
|
|
3. **Execute** `python solution.py` and verify the output file was created.
|
|
4. **Confirm** the target cells/range contain the expected values.
|
|
|
|
---
|
|
|
|
## Library Selection
|
|
|
|
| Use case | Library |
|
|
|----------|---------|
|
|
| Preserve formulas, formatting, named ranges | `openpyxl` |
|
|
| Bulk data transformation, aggregation, sorting | `pandas` → write back with `openpyxl` |
|
|
| Simple cell read/write | `openpyxl` |
|
|
|
|
**Warning**: `pandas.to_excel()` silently destroys existing formulas and named ranges.
|
|
When writing back to a spreadsheet that contains formulas, always use `openpyxl.save()`.
|
|
|
|
---
|
|
|
|
## solution.py Template
|
|
|
|
```python
|
|
import openpyxl
|
|
import pandas as pd
|
|
|
|
INPUT_PATH = "..." # set to the actual input path
|
|
OUTPUT_PATH = "..." # set to the actual output path
|
|
|
|
wb = openpyxl.load_workbook(INPUT_PATH)
|
|
ws = wb.active # or wb["SheetName"]
|
|
|
|
# --- perform manipulation ---
|
|
|
|
wb.save(OUTPUT_PATH)
|
|
```
|
|
|
|
---
|
|
|
|
## Output Requirements
|
|
|
|
- Save the result to `OUTPUT_PATH`.
|
|
- Do not hardcode row counts or column letters — iterate over actual rows in the workbook.
|
|
- Preserve sheets and cells not mentioned in the instruction.
|