# JXLS MCP 功能扩展示例代码
本文档提供各个扩展功能的详细示例代码，帮助理解和实现这些功能。
## 1. 样式和格式化示例
### 1.1 基础样式设置
```python
from jxls_mcp.models import DataField, DataStruct, CellStyle
# 定义单元格样式
header_style = CellStyle(
font_name="Arial",
font_size=12,
bold=True,
italic=False,
text_color="#FFFFFF",
bg_color="#4472C4",
border="thin",
border_color="#000000",
alignment="center",
vertical_alignment="middle"
)
data_style = CellStyle(
font_name="Arial",
font_size=10,
number_format="#,##0.00",
alignment="right"
)
# 应用样式到字段
data_struct = DataStruct(
collectName="sales",
itemVariable="sale",
headerStyle=header_style, # 标题行样式
dataFields=[
DataField(
name="产品名称",
field="product",
style=CellStyle(alignment="left")
),
DataField(
name="销售额",
field="amount",
style=data_style # 数据行样式
),
DataField(
name="日期",
field="date",
style=CellStyle(
number_format="YYYY-MM-DD",
alignment="center"
)
)
]
)
```
### 1.2 条件样式示例
```python
# 根据数值范围设置不同颜色
DataField(
name="绩效得分",
field="score",
conditionalStyles=[
{
"type": "cell_value",
"operator": "greater_than",
"value": 90,
"style": {"text_color": "green", "bold": True}
},
{
"type": "cell_value",
"operator": "between",
"min_value": 60,
"max_value": 90,
"style": {"text_color": "orange"}
},
{
"type": "cell_value",
"operator": "less_than",
"value": 60,
"style": {"text_color": "red", "bold": True}
}
]
)
```
### 1.3 数据条和色阶
```python
# 添加数据条
DataField(
name="完成率",
field="completion",
dataBar={
"type": "gradient",
"min_value": 0,
"max_value": 100,
"bar_color": "#63C384",
"show_value": True
}
)
# 添加色阶
DataField(
name="温度",
field="temperature",
colorScale={
"type": "3_color",
"min_color": "#63BE7B", # 绿色
"mid_color": "#FFEB84", # 黄色
"max_color": "#F8696B", # 红色
"min_value": 0,
"mid_value": 20,
"max_value": 40
}
)
```
## 2. 数据验证示例
### 2.1 下拉列表验证
```python
# 简单下拉列表
DataField(
name="部门",
field="department",
validation={
"type": "list",
"source": ["技术部", "市场部", "财务部", "人事部"],
"allow_blank": False,
"show_dropdown": True,
"error_title": "输入错误",
"error_message": "请从下拉列表中选择部门"
}
)
# 级联下拉列表
DataField(
name="省份",
field="province",
validation={
"type": "list",
"source": ["北京", "上海", "广东", "江苏"],
"cascade_to": "city" # 级联到城市字段
}
)
DataField(
name="城市",
field="city",
validation={
"type": "cascade_list",
"cascade_from": "province",
"source_map": {
"北京": ["朝阳区", "海淀区", "东城区"],
"上海": ["浦东新区", "黄浦区", "静安区"],
"广东": ["广州", "深圳", "珠海"],
"江苏": ["南京", "苏州", "无锡"]
}
}
)
```
### 2.2 数值范围验证
```python
# 整数范围
DataField(
name="年龄",
field="age",
validation={
"type": "whole",
"operator": "between",
"min": 18,
"max": 65,
"error_title": "年龄错误",
"error_message": "年龄必须在18到65岁之间"
}
)
# 小数范围
DataField(
name="折扣率",
field="discount",
validation={
"type": "decimal",
"operator": "between",
"min": 0,
"max": 1,
"error_message": "折扣率必须在0到1之间"
}
)
```
### 2.3 日期验证
```python
DataField(
name="入职日期",
field="hire_date",
validation={
"type": "date",
"operator": "greater_than",
"value": "2020-01-01",
"error_message": "入职日期必须在2020年之后"
}
)
# 动态日期验证
DataField(
name="截止日期",
field="deadline",
validation={
"type": "date",
"operator": "between",
"min": "=TODAY()", # Excel函数
"max": "=TODAY()+30",
"error_message": "截止日期必须在未来30天内"
}
)
```
### 2.4 自定义验证
```python
DataField(
name="邮箱",
field="email",
validation={
"type": "custom",
"formula": '=ISNUMBER(FIND("@",A2))', # Excel公式
"error_message": "请输入有效的邮箱地址"
}
)
DataField(
name="手机号",
field="phone",
validation={
"type": "regex",
"pattern": r"^1[3-9]\d{9}$",
"error_message": "请输入有效的手机号码"
}
)
```
## 3. 公式和计算示例
### 3.1 基础公式
```python
# 简单计算
DataField(
name="小计",
formula={
"type": "excel",
"expression": "=D{row}*E{row}", # 数量*单价
"number_format": "#,##0.00"
}
)
# 跨列计算
DataField(
name="合计",
formula={
"type": "excel",
"expression": "=SUM(F{row}:H{row})", # 求和
"number_format": "#,##0.00"
}
)
```
### 3.2 条件公式
```python
# IF条件判断
DataField(
name="等级",
formula={
"type": "excel",
"expression": '=IF(B{row}>=90,"优秀",IF(B{row}>=60,"合格","不合格"))'
}
)
# VLOOKUP查找
DataField(
name="产品单价",
formula={
"type": "excel",
"expression": "=VLOOKUP(A{row},产品表!A:C,3,FALSE)"
}
)
```
### 3.3 汇总公式
```python
# 在模板底部添加汇总行
summary_row = SummaryRow(
position="bottom", # 或 "top"
fields=[
SummaryField(
column="D",
label="合计：",
formula="=SUM(D2:D{last_row})"
),
SummaryField(
column="E",
label="平均：",
formula="=AVERAGE(E2:E{last_row})"
),
SummaryField(
column="F",
label="最大值：",
formula="=MAX(F2:F{last_row})"
)
]
)
```
### 3.4 JXLS表达式
```python
# 字符串拼接
DataField(
name="完整地址",
expression="${item.province} + ${item.city} + ${item.address}"
)
# 条件表达式
DataField(
name="显示状态",
expression="${item.status == 1 ? '启用' : '禁用'}"
)
# 格式化
DataField(
name="格式化日期",
expression="${item.date?string('yyyy年MM月dd日')}"
)
```
## 4. 多Sheet示例
### 4.1 基础多Sheet
```python
from jxls_mcp.models import WorkbookRequest, SheetConfig
# 创建多个工作表
workbook_request = WorkbookRequest(
templateName="comprehensive_report",
sheets=[
SheetConfig(
name="员工信息",
dataStruct=DataStruct(
collectName="employees",
itemVariable="emp",
dataFields=[
DataField(name="工号", field="id"),
DataField(name="姓名", field="name"),
DataField(name="部门", field="dept")
]
)
),
SheetConfig(
name="部门汇总",
dataStruct=DataStruct(
collectName="departments",
itemVariable="dept",
dataFields=[
DataField(name="部门名称", field="name"),
DataField(name="人数", field="count"),
DataField(name="平均工资", field="avgSalary")
]
)
),
SheetConfig(
name="统计图表",
chartConfig={
"type": "column",
"data_range": "部门汇总!A1:C10",
"title": "部门人数统计"
}
)
]
)
```
### 4.2 Sheet间引用
```python
# Sheet间的数据引用
SheetConfig(
name="汇总表",
dataStruct=DataStruct(
collectName="summary",
itemVariable="item",
dataFields=[
DataField(
name="员工总数",
formula={
"type": "excel",
"expression": "=COUNTA(员工信息!A:A)-1"
}
),
DataField(
name="部门数量",
formula={
"type": "excel",
"expression": "=COUNTA(部门汇总!A:A)-1"
}
)
]
)
)
```
## 5. 数据分组示例
### 5.1 简单分组
```python
# 按部门分组
DataStruct(
collectName="employees",
itemVariable="emp",
groupBy={
"field": "department",
"sort": "asc",
"showSubtotal": True,
"subtotalFields": ["salary", "bonus"]
},
dataFields=[
DataField(name="部门", field="department"),
DataField(name="姓名", field="name"),
DataField(name="工资", field="salary"),
DataField(name="奖金", field="bonus")
]
)
```
### 5.2 多级分组
```python
# 多级分组（部门->职位）
DataStruct(
collectName="employees",
itemVariable="emp",
groupBy=[
{
"field": "department",
"level": 1,
"showSubtotal": True
},
{
"field": "position",
"level": 2,
"showSubtotal": True
}
],
dataFields=[
DataField(name="部门", field="department"),
DataField(name="职位", field="position"),
DataField(name="姓名", field="name"),
DataField(name="工资", field="salary")
]
)
```
## 6. 模板继承示例
### 6.1 基础模板
```python
# 定义基础模板
base_template = TemplateConfig(
name="base_report",
commonStyles={
"header": header_style,
"data": data_style,
"footer": footer_style
},
commonValidations={
"date": date_validation,
"number": number_validation
},
pageSetup={
"orientation": "landscape",
"paper_size": "A4",
"margins": {"top": 1, "bottom": 1, "left": 1, "right": 1}
}
)
```
### 6.2 继承模板
```python
# 继承基础模板
specific_template = TemplateConfig(
name="sales_report",
extends="base_report", # 继承基础模板
dataStruct=DataStruct(
collectName="sales",
itemVariable="sale",
dataFields=[
DataField(name="日期", field="date"),
DataField(name="产品", field="product"),
DataField(name="金额", field="amount")
]
),
# 覆盖或添加特定配置
additionalStyles={
"highlight": CellStyle(bg_color="#FFFF00")
}
)
```
## 7. 批量操作示例
### 7.1 批量生成
```python
# 批量生成多个部门的报表
batch_generator = BatchGenerator()
departments = ["技术部", "市场部", "财务部", "人事部"]
batch_request = BatchRequest(
base_template="employee_template",
iterations=[
{
"output_name": f"{dept}_report.xlsx",
"filter": {"department": dept},
"variables": {"report_title": f"{dept}月度报表"}
}
for dept in departments
],
parallel=True, # 并行处理
max_workers=4
)
results = batch_generator.generate_batch(batch_request)
```
### 7.2 模板合并
```python
# 合并多个Excel文件
merger = ExcelMerger()
merge_config = MergeConfig(
source_files=[
"report_jan.xlsx",
"report_feb.xlsx",
"report_mar.xlsx"
],
output_file="quarterly_report.xlsx",
merge_strategy="sheets", # 或 "rows", "columns"
sheet_naming=lambda idx, name: f"Month_{idx+1}",
add_source_column=True # 添加来源标识列
)
merger.merge(merge_config)
```
## 8. 高级数据源示例
### 8.1 数据库连接
```python
# PostgreSQL数据源
db_source = DatabaseSource(
connection_string="postgresql://user:pass@localhost/db",
query="""
SELECT
e.id, e.name, d.name as department
FROM
employees e
JOIN departments d ON e.dept_id = d.id
WHERE
e.status = 'active'
""",
parameters={"start_date": "2024-01-01"}
)
request = GenerateTemplateRequest(
templateName="db_report",
dataSource=db_source,
auto_detect_fields=True # 自动检测字段
)
```
### 8.2 API数据源
```python
# REST API数据源
api_source = ApiSource(
url="https://api.example.com/employees",
method="GET",
headers={
"Authorization": "Bearer token",
"Content-Type": "application/json"
},
params={
"page": 1,
"size": 100,
"status": "active"
},
response_path="data.items", # JSON路径
pagination={
"type": "page_based",
"page_param": "page",
"size_param": "size",
"total_path": "data.total"
}
)
```
## 9. 导入功能示例
### 9.1 基础导入
```python
# 导入填写好的Excel
importer = ExcelImporter()
import_config = ImportConfig(
file_path="filled_template.xlsx",
template_config=original_data_struct, # 原始模板配置
validation_mode="strict", # 严格验证
error_handling="skip", # 跳过错误行
output_format="json" # 或 "csv", "database"
)
result = importer.import_excel(import_config)
# 处理导入结果
if result.success:
print(f"成功导入 {result.row_count} 行数据")
for row in result.data:
print(row)
else:
print(f"导入失败: {result.errors}")
```
### 9.2 数据转换
```python
# 导入时的数据转换
import_config = ImportConfig(
file_path="data.xlsx",
transformations=[
{
"field": "date",
"from_format": "DD/MM/YYYY",
"to_format": "YYYY-MM-DD"
},
{
"field": "amount",
"type": "number",
"decimal_places": 2,
"remove_currency": True
},
{
"field": "status",
"type": "mapping",
"map": {
"Y": "Yes",
"N": "No",
"": "Unknown"
}
}
],
custom_processors=[
lambda row: {**row, "import_date": datetime.now()}
]
)
```
## 10. 性能优化示例
### 10.1 流式处理
```python
# 大数据量流式生成
async def generate_large_template():
generator = StreamingGenerator()
async with generator.create_stream("large_report.xlsx") as stream:
# 写入标题
await stream.write_headers(headers)
# 分批处理数据
async for batch in fetch_data_in_batches(batch_size=1000):
await stream.write_batch(batch)
# 写入汇总
await stream.write_summary(summary_formulas)
```
### 10.2 缓存优化
```python
# 启用缓存
cache_config = CacheConfig(
enabled=True,
backend="redis", # 或 "memory", "file"
ttl=3600, # 1小时
key_prefix="jxls_template_",
max_size="100MB"
)
generator = TemplateGenerator(cache_config=cache_config)
# 使用缓存键
request = GenerateTemplateRequest(
templateName="cached_report",
cache_key="report_v1", # 明确指定缓存键
cache_dependencies=["products", "categories"] # 依赖项
)
```
## 总结
这些示例展示了JXLS MCP扩展功能的具体实现方式。每个功能都设计为模块化和可组合的，可以根据实际需求灵活使用。实现这些功能时，建议：
1. **保持API一致性**：新功能应遵循现有的API设计模式
2. **提供默认值**：为所有可选参数提供合理的默认值
3. **错误处理**：提供清晰的错误消息和恢复建议
4. **文档完善**：每个功能都应有完整的文档和示例
5. **测试覆盖**：确保所有功能都有充分的测试
