跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
数据与分析

xlsx

Read, create, or edit Excel spreadsheets (.xlsx/.xlsm) — sheet data,

HKUDS · 已有 AI 审核记录

安装与使用

运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。

npx skills add HKUDS/DeepTutor --skill xlsx

技能说明


name: xlsx description: Read, create, or edit Excel spreadsheets (.xlsx/.xlsm) — sheet data, formulas, styles, charts, multi-sheet workbooks — and bulk .csv/.tsv tables; use whenever a spreadsheet is the input or the deliverable (extract/analyze data, add columns/formulas/formatting/charts, clean messy tables, build from scratch), but not for Google Sheets API or Word/PDF/script outputs. tags:

  • tool
  • office requires: sandbox: shell

Excel (.xlsx) workbooks

Work in the workspace dir (where uploads land) by running complete Python source via code_execution. Two libraries, both preinstalled — pick by task: Refer to the workbook exactly as the Generated artifacts list names it. Use exec only for a genuinely shell-only command; never put this source in python -c or a heredoc.

  • pandas — bulk tabular read/write/analysis. Use for "load this sheet, compute, dump a table". Drops all formatting and formulas.
  • openpyxl — cells, formulas, styles, charts, merged cells, multi-sheet, number formats. Use whenever formatting, formulas, or fidelity matter.

THE critical gotcha: openpyxl writes formulas but never computes them

ws["B10"] = "=SUM(B2:B9)" stores the formula string. openpyxl has no formula engine — the cached value stays empty (or stale, on an edited file). So:

  • A workbook you create/edit with openpyxl opens fine in Excel/LibreOffice (they recompute on open), but its cached values are wrong until then.
  • Anything reading cached values first — data_only=True, another pandas/openpyxl pass, or a downstream tool — sees blanks/stale data.

Pick by what the deliverable needs:

  1. Static numbers (most common). If the user just needs correct values and the sheet need not stay live, compute in Python and write the number, not a formula string: ws["B10"] = sum(c.value for c in ws["B2:B9"][0]). Correct immediately, no recalc needed.
  2. Live model (formulas that recompute on the user's later edits). Write real formulas, and reference cells not literals (=B5*(1+$B$6), not =B5*1.05). openpyxl can't set the cached value too, so either recalc with LibreOffice if present (gate it — often absent):
    command -v soffice >/dev/null && \
      soffice --headless --convert-to xlsx --outdir /tmp out.xlsx \
        >/dev/null 2>&1 && cp /tmp/out.xlsx out.xlsx
    
    --convert-to xlsx reopens and recalculates, repopulating cached values. If soffice is missing, say so and warn the user the formulas populate when they open the file in Excel — never assume soffice exists.

Reading

import pandas as pd

df = pd.read_excel("in.xlsx")  # first sheet
sheets = pd.read_excel("in.xlsx", sheet_name=None)  # dict of all sheets
df = pd.read_excel("in.xlsx", dtype={"id": str})  # stop id->float coercion

To read computed results of formulas (not the formula text), use openpyxl with data_only=True — returns the value Excel last cached:

from openpyxl import load_workbook

wb = load_workbook("in.xlsx", data_only=True)
val = wb["Sheet1"]["B10"].value  # None if Excel never opened/saved the file

Gotcha: never save() a workbook loaded with data_only=True — that discards every formula permanently (verified: the cell becomes None). Load twice if you need both formulas and values.

Large file: load_workbook(path, read_only=True) streams rows cheaply.

Creating

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
ws = wb.active
ws.title = "Summary"
ws.append(["Region", "Sales"])  # header row
for r in [("West", 120), ("East", 95)]:
    ws.append(r)
ws["B4"] = "=SUM(B2:B3)"  # see formula gotcha above

ws["A1"].font = Font(bold=True)
ws["A1"].fill = PatternFill("solid", fgColor="DDDDDD")
ws["A1"].alignment = Alignment(horizontal="center")
ws["B2"].number_format = "#,##0"  # thousands separator
ws.column_dimensions["A"].width = 18
ws.freeze_panes = "A2"  # freeze header
wb.create_sheet("Detail")  # second sheet
wb.save("out.xlsx")

Bulk data is faster via pandas, then style with openpyxl after:

df.to_excel("out.xlsx", index=False, sheet_name="Data")

Editing (preserve existing formatting)

load_workbook keeps styles, formulas, merged cells, charts intact — edit only what you touch. Do NOT round-trip through pandas to preserve formatting (pandas rewrites the whole sheet, losing styles).

from openpyxl import load_workbook

wb = load_workbook("in.xlsx")  # keep formulas (data_only=False)
ws = wb["Sheet1"]
ws["C2"] = "Updated"
wb.save("in.xlsx")

Match the file's existing conventions (font, number formats, colors) rather than imposing new ones — an established template wins over any default.

When inserting/deleting rows or columns (ws.insert_rows, ws.delete_cols), openpyxl does not rewrite formulas that reference shifted cells. Re-point affected formulas yourself, or avoid structural shifts in formula-heavy sheets.

Charts

from openpyxl.chart import BarChart, Reference

ch = BarChart()
ch.title = "Sales"
data = Reference(ws, min_col=2, min_row=1, max_row=3)  # include header for title
cats = Reference(ws, min_col=1, min_row=2, max_row=3)
ch.add_data(data, titles_from_data=True)
ch.set_categories(cats)
ws.add_chart(ch, "E2")

LineChart / PieChart / ScatterChart follow the same shape.

Verifying you produced clean output

After writing, reload and scan for error strings — these mean broken formulas that recalc surfaced (#REF! bad reference, #DIV/0! zero denominator, #VALUE! type mismatch, #NAME? unknown function, #N/A):

from openpyxl import load_workbook

wb = load_workbook("out.xlsx", data_only=True)
errs = [
    f"{s}!{c.coordinate}={c.value}"
    for s in wb.sheetnames
    for row in wb[s].iter_rows()
    for c in row
    if isinstance(c.value, str) and c.value.startswith("#")
]
print(errs or "clean")

This only catches errors in *c

阅读完整来源 ↗