excel-toolkit

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Excel Toolkit

Excel工具包

Setup (First Use)

设置（首次使用）

Run the dependency installer before any Excel operation:

bash

python3 scripts/setup_deps.py

Installs: openpyxl, pandas, xlsxwriter, matplotlib. Skip if already installed.

在进行任何Excel操作前运行依赖安装程序：

bash

python3 scripts/setup_deps.py

将安装：openpyxl、pandas、xlsxwriter、matplotlib。若已安装可跳过此步骤。

Workflow Selection

工作流选择

Inspect a file → Run
```
scripts/inspect_excel.py
```
Analyze data / get insights → Run
```
scripts/analyze_excel.py
```
Read data for processing → Use pandas in inline Python
Edit existing file → Use openpyxl (preserves formulas/formatting)
Create new file → Use openpyxl (formulas/formatting) or pandas (data export)
Recalculate formulas → Run
```
scripts/recalc.py
```

检查文件 → 运行
```
scripts/inspect_excel.py
```
分析数据 / 获取见解 → 运行
```
scripts/analyze_excel.py
```
读取数据用于处理 → 在嵌入式Python中使用pandas
编辑现有文件 → 使用openpyxl（保留公式/格式）
创建新文件 → 使用openpyxl（支持公式/格式）或pandas（数据导出）
重新计算公式 → 运行
```
scripts/recalc.py
```

Quick-Start Scripts

快速开始脚本

Inspect File Structure

检查文件结构

bash

python3 scripts/inspect_excel.py data.xlsx                    # Structure only
python3 scripts/inspect_excel.py data.xlsx --data              # With data preview
python3 scripts/inspect_excel.py data.xlsx --sheet "Sales"     # Specific sheet
python3 scripts/inspect_excel.py data.xlsx --data --rows 50    # More preview rows

Returns JSON: sheet names, dimensions, headers, column types, optional data preview.

bash

python3 scripts/inspect_excel.py data.xlsx                    # Structure only
python3 scripts/inspect_excel.py data.xlsx --data              # With data preview
python3 scripts/inspect_excel.py data.xlsx --sheet "Sales"     # Specific sheet
python3 scripts/inspect_excel.py data.xlsx --data --rows 50    # More preview rows

返回JSON格式内容：工作表名称、尺寸、表头、列类型、可选的数据预览。

Analyze Data

数据分析

bash

python3 scripts/analyze_excel.py data.xlsx                         # Basic stats
python3 scripts/analyze_excel.py data.xlsx --correlations          # With correlations
python3 scripts/analyze_excel.py data.xlsx --sheet "Revenue"       # Specific sheet

Returns JSON: shape, dtypes, missing values, numeric stats, categorical summaries, duplicates, date ranges.

bash

python3 scripts/analyze_excel.py data.xlsx                         # Basic stats
python3 scripts/analyze_excel.py data.xlsx --correlations          # With correlations
python3 scripts/analyze_excel.py data.xlsx --sheet "Revenue"       # Specific sheet

返回JSON格式内容：数据形状、数据类型、缺失值、数值统计、分类汇总、重复项、日期范围。

Recalculate Formulas

重新计算公式

bash

python3 scripts/recalc.py output.xlsx [timeout_seconds]

Requires LibreOffice. Returns JSON with formula errors and locations.

bash

python3 scripts/recalc.py output.xlsx [timeout_seconds]

需要LibreOffice。返回包含公式错误及位置的JSON内容。

Reading Data

读取数据

python

import pandas as pd

df = pd.read_excel('file.xlsx')                          # First sheet
df = pd.read_excel('file.xlsx', sheet_name='Sales')      # Named sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict
df = pd.read_excel('file.xlsx', dtype={'id': str})       # Force types

python

import pandas as pd

df = pd.read_excel('file.xlsx')                          # First sheet
df = pd.read_excel('file.xlsx', sheet_name='Sales')      # Named sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict
df = pd.read_excel('file.xlsx', dtype={'id': str})       # Force types

Creating / Editing

创建 / 编辑

Create New

创建新文件

python

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

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws['A1'] = 'Category'
ws['A1'].font = Font(bold=True)
ws.append(['Sales', 1500])
ws['B3'] = '=SUM(B2:B2)'
ws.column_dimensions['A'].width = 18
wb.save('output.xlsx')

python

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

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws['A1'] = 'Category'
ws['A1'].font = Font(bold=True)
ws.append(['Sales', 1500])
ws['B3'] = '=SUM(B2:B2)'
ws.column_dimensions['A'].width = 18
wb.save('output.xlsx')

Edit Existing

编辑现有文件

python

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')  # preserves formulas
ws = wb['Sheet1']
ws['A1'] = 'Updated'
ws.insert_rows(2)
wb.save('modified.xlsx')

python

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')  # preserves formulas
ws = wb['Sheet1']
ws['A1'] = 'Updated'
ws.insert_rows(2)
wb.save('modified.xlsx')

Critical Rules

重要规则

Use Excel formulas, not hardcoded calculations

❌
```
ws['B10'] = df['Sales'].sum()
```
✅
```
ws['B10'] = '=SUM(B2:B9)'
```

Recalculate after writing formulas — openpyxl writes formula strings but doesn't evaluate:
bash
```
python3 scripts/recalc.py output.xlsx
```
Never save workbooks opened with
data_only=True
— destroys all formulas permanently.
Preserve existing formatting — use
```
load_workbook()
```
and match existing conventions.

使用Excel公式，而非硬编码计算

❌
```
ws['B10'] = df['Sales'].sum()
```
✅
```
ws['B10'] = '=SUM(B2:B9)'
```

写入公式后重新计算 — openpyxl仅写入公式字符串但不进行计算：
bash
```
python3 scripts/recalc.py output.xlsx
```
切勿保存使用
data_only=True
打开的工作簿 — 会永久销毁所有公式。
保留现有格式 — 使用
```
load_workbook()
```
并遵循现有格式规范。

Providing Insights

提供见解

When analyzing data:

Run
```
scripts/inspect_excel.py
```
to understand structure
Run
```
scripts/analyze_excel.py --correlations
```
for numeric data
Present findings:
- Overview: rows, columns, data types
- Key Statistics: means, medians, ranges
- Data Quality: missing values, duplicates, anomalies
- Patterns: correlations, trends, distributions
- Actionable Insights: what stands out, recommendations

分析数据时：

运行
```
scripts/inspect_excel.py
```
了解文件结构
对数值数据运行
```
scripts/analyze_excel.py --correlations
```
呈现分析结果：
- 概述：行数、列数、数据类型
- 关键统计：均值、中位数、范围
- 数据质量：缺失值、重复项、异常值
- 模式：相关性、趋势、分布
- 可执行见解：突出的发现、建议

Building Dashboards & Insights Sheets

构建仪表板与见解工作表

When creating dashboard/insights sheets with tables and charts, you MUST follow the layout rules in

references/advanced-patterns.md

→ "Dashboard Layout & Spacing":

Use a running ROW counter — never hardcode row positions for sections/charts
Reserve 17-20 rows after each chart anchor for chart height
Leave 2 blank rows between tables and charts
Set chart dimensions explicitly — use the sizing guide for each chart type
Apply consistent styling — title/section/header fonts, zebra striping, thin borders
Use the standard color palette — BLUE for primary, ORANGE for secondary, RED for warnings
Set column widths — use the defaults table for readable layouts

当创建包含表格和图表的仪表板/见解工作表时，您必须遵循

references/advanced-patterns.md

中的「仪表板布局与间距」规则：

使用运行中的行计数器 — 切勿为板块/图表硬编码行位置
每个图表锚点后预留17-20行 用于图表高度
表格与图表之间保留2个空白行
明确设置图表尺寸 — 为每种图表类型使用尺寸指南
应用一致的样式 — 标题/板块/表头字体、斑马纹、细边框
使用标准调色板 — 蓝色为主色调、橙色为次要色调、红色为警告色
设置列宽 — 使用默认表格确保布局可读性

Advanced Features

高级功能

For charts, conditional formatting, pivot tables, data validation, CSV conversion, dashboard layout, and large file handling → see

references/advanced-patterns.md

有关图表、条件格式、数据透视表、数据验证、CSV转换、仪表板布局及大文件处理的内容 → 请查看

references/advanced-patterns.md

。