syncfusion-aspnetcore-pivot-table

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Implementing Pivot Table (ASP.NET Core)

在ASP.NET Core中实现透视表

Syncfusion ASP.NET Core Pivot Table (PivotView) enables multi-dimensional data visualization and analysis with interactive row/column/value axes, real-time aggregation, flexible filtering, and comprehensive export options.

⚠️ Important: Always verify API class names, properties, and method signatures by consulting the reference files in this skill (
references/*.md
). These are maintained with verified, working examples. Do not assume API details from other sources.

Syncfusion ASP.NET Core透视表（PivotView）支持多维数据可视化与分析，具备交互式行/列/值轴、实时聚合、灵活筛选以及全面的导出选项。

⚠️ 重要提示： 请务必通过查阅此技能中的参考文件（
references/*.md
）来验证API类名、属性和方法签名。这些文件包含经过验证的可用示例。请勿从其他来源假设API细节。

⚠️ Security Warning: Data Source Validation

⚠️ 安全警告：数据源验证

CRITICAL SECURITY NOTICE: When implementing pivot tables, always use trusted data sources. Never fetch or bind data from untrusted or user-provided URLs without proper validation and sanitization.

关键安全通知： 实现透视表时，请始终使用可信数据源。绝对不要在未进行适当验证和清理的情况下，从不信任的来源或用户提供的URL获取或绑定数据。

Security Best Practices:

安全最佳实践：

Use Local Data: Prefer local, in-memory data sources for maximum security
Validate Remote Sources: Only connect to authenticated and authorized API endpoints under your control
Sanitize User Input: Never allow users to specify arbitrary URLs or data sources
Use Configuration: Store connection strings and API URLs in secure configuration (appsettings.json with IConfiguration)
Implement Authentication: Require authentication for all database and API connections
Apply Authorization: Use [Authorize] attributes and role-based access control

使用本地数据：优先选择本地内存数据源以获得最高安全性
验证远程来源：仅连接到您控制下的已认证和授权API端点
清理用户输入：绝不允许用户指定任意URL或数据源
使用配置存储：将连接字符串和API URL存储在安全配置中（使用IConfiguration的appsettings.json）
实现身份验证：所有数据库和API连接都需要身份验证
应用授权：使用[Authorize]属性和基于角色的访问控制

Security Risks:

安全风险：

Indirect Prompt Injection: Untrusted third-party data can contain malicious content that manipulates AI agent behavior
Data Exposure: Unvalidated remote connections may expose sensitive information
SQL Injection: Unsanitized database queries can lead to data breaches
XSS Attacks: Unescaped data rendered in the UI can execute malicious scripts

✅ Safe: Local data, authenticated APIs under your control, parameterized SQL queries ❌ Unsafe: User-provided URLs, unauthenticated endpoints, hardcoded connection strings

间接提示注入：不可信的第三方数据可能包含恶意内容，操纵AI Agent行为
数据泄露：未验证的远程连接可能暴露敏感信息
SQL注入：未清理的数据库查询可能导致数据 breach
XSS攻击：UI中渲染的未转义数据可能执行恶意脚本

✅ 安全做法：本地数据、您控制下的已认证API、参数化SQL查询 ❌ 不安全做法：用户提供的URL、未认证端点、硬编码连接字符串

When to Use This Skill

何时使用此技能

Use this skill when building an ASP.NET Core application and the user needs:

Pivot reporting with row, column, value, and filter axes
Data aggregation with 20+ summary types (Sum, Avg, Count, Percentage, RunningTotals, etc.)
Interactive report building via field list and grouping bar UI
Data source binding (local JSON/CSV, SQL Server, OLAP, relational databases)
Filtering (member, label, value filtering with search/sort)
Sorting (field sorting, custom order, value sorting)
Advanced features (drill-down/up, drill-through, calculated fields, pivot chart)
Formatting (conditional formatting, number formatting, custom styling)
Export (Excel, PDF, CSV, print)
Performance optimization (virtual scrolling, paging, data compression)

Trigger keywords: pivot table, pivotview, PivotView, field list, grouping bar, OLAP, pivot aggregation, pivot export, pivot drill-down, pivot report, ASP.NET Core pivot

当构建ASP.NET Core应用且用户需要以下功能时，使用此技能：

带行、列、值和筛选轴的透视报表
包含20+汇总类型的数据聚合（求和、平均值、计数、百分比、运行总计等）
通过字段列表和分组栏UI进行交互式报表构建
数据源绑定（本地JSON/CSV、SQL Server、OLAP、关系型数据库）
筛选（成员、标签、值筛选，支持搜索/排序）
排序（字段排序、自定义顺序、值排序）
高级功能（钻取/钻取上移、穿透钻取、计算字段、透视图表）
格式化（条件格式化、数字格式化、自定义样式）
导出（Excel、PDF、CSV、打印）
性能优化（虚拟滚动、分页、数据压缩）

触发关键词： pivot table, pivotview, PivotView, 字段列表, 分组栏, OLAP, 透视聚合, 透视导出, 透视钻取, 透视报表, ASP.NET Core pivot

Quick Start Example

快速开始示例

html

@using Syncfusion.EJ2.PivotView

<ejs-pivotview id="pivotview"
    height="450"
    showFieldList="true"
    showGroupingBar="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource">
        <e-rows>
            <e-field name="Country" caption="Country"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sales" caption="Total Sales" type="Sum"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Region" caption="Region"></e-field>
        </e-filters>
        <e-formatsettings>
            <e-field name="Sales" format="C0" currency="USD"></e-field>
        </e-formatsettings>
    </e-datasourcesettings>
</ejs-pivotview>

html

@using Syncfusion.EJ2.PivotView

<ejs-pivotview id="pivotview"
    height="450"
    showFieldList="true"
    showGroupingBar="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource">
        <e-rows>
            <e-field name="Country" caption="Country"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sales" caption="Total Sales" type="Sum"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Region" caption="Region"></e-field>
        </e-filters>
        <e-formatsettings>
            <e-field name="Sales" format="C0" currency="USD"></e-field>
        </e-formatsettings>
    </e-datasourcesettings>
</ejs-pivotview>

Navigation Guide

导航指南

Getting started and setup

入门与设置

📄 Read: references/getting-started.md

Installation and NuGet package setup
Project configuration and namespace registration
Basic PivotView initialization
Themes and styling setup

📄 阅读： references/getting-started.md

安装与NuGet包设置
项目配置与命名空间注册
基础PivotView初始化
主题与样式设置

Pivoting and data binding

透视与数据绑定

📄 Read: references/data-binding.md

Local/remote data binding patterns
CSV and JSON data sources
DataManager configuration
Field mapping and setup

📄 阅读： references/data-binding.md

本地/远程数据绑定模式
CSV和JSON数据源
DataManager配置
字段映射与设置

Data source connections

数据源连接

📄 Read: references/mysql.md

MySQL database setup, connection configuration, Web API implementation
DataAdapter pattern, JSON serialization

📄 Read: references/mssql.md

SQL Server setup, authentication methods, Azure SQL Database integration
T-SQL queries, stored procedures

📄 Read: references/postgresql.md

PostgreSQL connection strings, SSL/TLS configuration
Query patterns, connection pooling

📄 Read: references/oracle.md

Oracle database setup, TNS configuration, authentication
SQL and PL/SQL queries

📄 Read: references/mongodb.md

MongoDB document database, BSON document handling
POCO model mapping, bulk operations

📄 Read: references/elasticsearch.md

Elasticsearch search engine, NEST client library
Query DSL patterns, index management

📄 Read: references/snowflake.md

Snowflake cloud warehouse, virtual warehouse configuration
Cost optimization, semi-structured data

📄 阅读： references/mysql.md

MySQL数据库设置、连接配置、Web API实现
DataAdapter模式、JSON序列化

📄 阅读： references/mssql.md

SQL Server设置、身份验证方法、Azure SQL数据库集成
T-SQL查询、存储过程

📄 阅读： references/postgresql.md

PostgreSQL连接字符串、SSL/TLS配置
查询模式、连接池

📄 阅读： references/oracle.md

Oracle数据库设置、TNS配置、身份验证
SQL和PL/SQL查询

📄 阅读： references/mongodb.md

MongoDB文档数据库、BSON文档处理
POCO模型映射、批量操作

📄 阅读： references/elasticsearch.md

Elasticsearch搜索引擎、NEST客户端库
Query DSL模式、索引管理

📄 阅读： references/snowflake.md

Snowflake云数据仓库、虚拟仓库配置
成本优化、半结构化数据

Advanced data sources

高级数据源

📄 Read: references/olap.md

OLAP cube setup and hierarchical data binding
Dimension and measure configuration
Pivot engine integration

📄 Read: references/server-side-pivot-engine.md

Server-side processing and aggregation
Large dataset optimization for backend
Batch processing workflows

📄 阅读： references/olap.md

OLAP多维数据集设置与分层数据绑定
维度与度量配置
透视引擎集成

📄 阅读： references/server-side-pivot-engine.md

服务器端处理与聚合
针对后端的大数据集优化
批处理工作流

Data shaping and analysis

数据整形与分析

📄 Read: references/aggregation.md

Summary types: Sum, Avg, Count, Min, Max, Product, Median, StDev, Variance
Percentage calculations and running totals
Custom aggregation at runtime

📄 Read: references/calculated-field.md

Define and use calculated fields in formulas
Runtime field creation and updates
Performance considerations

📄 Read: references/grouping.md

Field-level grouping by date, value ranges
Axis grouping for dimension hierarchies

📄 Read: references/filtering.md

Member filtering with include/exclude
Label filtering (string, date, numeric)
Value filtering on aggregated data

📄 Read: references/sorting.md

Member field sorting (ascending/descending)
Custom member order configuration
Value sorting on computed cells

📄 Read: references/drill-through.md

Access underlying data behind pivot cells
Drill-through event handling

📄 阅读： references/aggregation.md

汇总类型：求和、平均值、计数、最小值、最大值、乘积、中位数、标准差、方差
百分比计算与运行总计
运行时自定义聚合

📄 阅读： references/calculated-field.md

在公式中定义和使用计算字段
运行时字段创建与更新
性能考量

📄 阅读： references/grouping.md

按日期、值范围进行字段级分组
维度层次结构的轴分组

📄 阅读： references/filtering.md

包含/排除成员筛选
标签筛选（字符串、日期、数值）
聚合数据的值筛选

📄 阅读： references/sorting.md

成员字段排序（升序/降序）
自定义成员顺序配置
计算单元格的值排序

📄 阅读： references/drill-through.md

访问透视单元格背后的底层数据
穿透钻取事件处理

Visualization and layout

可视化与布局

📄 Read: references/pivot-chart.md

Bind pivot chart to pivot table data
Chart type selection and configuration

📄 Read: references/classic-layout.md

Classic (Compact) layout rendering
Compact vs Outline vs Tree layout options

📄 Read: references/drill-down.md

Drill-down into dimension members
Drill-up navigation between hierarchy levels

📄 Read: references/row-and-column.md

Row and column axis configuration
Value field positioning

📄 阅读： references/pivot-chart.md

将透视图表绑定到透视表数据
图表类型选择与配置

📄 阅读： references/classic-layout.md

经典（紧凑）布局渲染
紧凑布局 vs 大纲布局 vs 树形布局选项

📄 阅读： references/drill-down.md

钻取到维度成员
维度层次结构间的钻取上移导航

📄 阅读： references/row-and-column.md

行与列轴配置
值字段定位

Formatting and display

格式化与显示

📄 Read: references/number-formatting.md

Format value cells with currency, decimals, percentage
Custom number format providers

📄 Read: references/conditional-formatting.md

Apply style rules based on values or conditions
Color scales and data bars

📄 Read: references/hyper-link.md

Hyperlink columns for navigation
Custom URL generation and click handling

📄 阅读： references/number-formatting.md

使用货币、小数、百分比格式化值单元格
自定义数字格式化提供程序

📄 阅读： references/conditional-formatting.md

根据值或条件应用样式规则
颜色刻度与数据条

📄 阅读： references/hyper-link.md

用于导航的超链接列
自定义URL生成与点击处理

User interface controls

用户界面控件

📄 Read: references/grouping-bar.md

Enable/disable grouping bar
Drag-drop field configuration
Grouping bar event handling

📄 Read: references/field-list.md

Field list popup and fixed modes
Field search and sorting
Field grouping by folders

📄 Read: references/defer-update.md

Batch changes without intermediate updates
Improve performance with large field changes

📄 Read: references/tool-bar.md

Toolbar button configuration
Built-in and custom commands
Export and action buttons

📄 Read: references/tool-tip.md

Cell and header tooltips
Custom tooltip content

📄 阅读： references/grouping-bar.md

启用/禁用分组栏
拖放字段配置
分组栏事件处理

📄 阅读： references/field-list.md

字段列表弹窗与固定模式
字段搜索与排序
按文件夹分组字段

📄 阅读： references/defer-update.md

批量更改，无需中间更新
大字段更改时提升性能

📄 阅读： references/tool-bar.md

工具栏按钮配置
内置与自定义命令
导出与操作按钮

📄 阅读： references/tool-tip.md

单元格与表头工具提示
自定义工具提示内容

Editing and data manipulation

编辑与数据操作

📄 Read: references/editing.md

In-place cell editing workflows
Update underlying data from pivot
Cell edit event handling

📄 阅读： references/editing.md

就地单元格编辑工作流
从透视表更新底层数据
单元格编辑事件处理

Performance and optimization

性能与优化

📄 Read: references/virtual-scrolling.md

Virtual scrolling for large pivot tables
Memory-efficient rendering

📄 Read: references/paging.md

Pagination configuration
Page size and navigation

📄 Read: references/data-compression.md

Payload compression for data transfer
Bandwidth optimization

📄 Read: references/performance-best-practices.md

Tuning guidelines for large datasets
Server-side vs client-side rendering decisions
Common performance bottlenecks and fixes

📄 阅读： references/virtual-scrolling.md

大型透视表的虚拟滚动
内存高效渲染

📄 阅读： references/paging.md

分页配置
页面大小与导航

📄 阅读： references/data-compression.md

数据传输的负载压缩
带宽优化

📄 阅读： references/performance-best-practices.md

大数据集调优指南
服务器端 vs 客户端渲染决策
常见性能瓶颈与修复方案

State and persistence

状态与持久化

📄 Read: references/state-persistence.md

Save pivot report state
Load saved configurations
State storage options

📄 阅读： references/state-persistence.md

保存透视报表状态
加载已保存的配置
状态存储选项

Export and printing

导出与打印

📄 Read: references/print.md

Print pivot table with custom settings
Print preview and page layout

📄 Read: references/excel-export.md

Export to Excel workbook
Formatting and multi-sheet export

📄 Read: references/pdf-export.md

Export to PDF document
Page orientation and sizing

📄 阅读： references/print.md

使用自定义设置打印透视表
打印预览与页面布局

📄 阅读： references/excel-export.md

导出到Excel工作簿
格式化与多工作表导出

📄 阅读： references/pdf-export.md

导出到PDF文档
页面方向与尺寸设置

Common Patterns

常见模式

Pattern 1: Complete pivot with all UI:

Enable
```
ShowFieldList
```
,
```
ShowGroupingBar
```
, and toolbar buttons
User can build and refine reports interactively

Pattern 2: Server-side aggregation for large datasets:

Use server-side pivot engine for backend processing
Enable paging and virtual scrolling for responsive UX

Pattern 3: Read-only analytical view:

Disable field list and grouping bar
Set fixed row/column/value configuration
Use drill-down only for exploration

模式1：带完整UI的透视表：

启用
```
ShowFieldList
```
、
```
ShowGroupingBar
```
和工具栏按钮
用户可交互式构建和优化报表

模式2：针对大数据集的服务器端聚合：

使用服务器端透视引擎进行后端处理
启用分页和虚拟滚动以获得响应式用户体验

模式3：只读分析视图：

禁用字段列表和分组栏
设置固定的行/列/值配置
仅使用钻取进行数据探索

Key Properties Overview

关键属性概述

```
DataSourceSettings
```
- Data source and field configuration
```
Rows
```
,
```
Columns
```
,
```
Values
```
,
```
Filters
```
- Axis field arrays
```
ShowFieldList
```
- Toggle field list UI
```
ShowGroupingBar
```
- Toggle grouping bar UI
```
EnableValueSorting
```
- Allow value cell sorting
```
AllowExcelExport
```
,
```
AllowPdfExport
```
- Export capabilities

AllowMemberFilter

AllowLabelFilter

AllowValueFilter

- Filter types

```
EnableSorting
```
,
```
EnableVirtualization
```
- Performance and interaction

```
DataSourceSettings
```
- 数据源与字段配置
```
Rows
```
,
```
Columns
```
,
```
Values
```
,
```
Filters
```
- 轴字段数组
```
ShowFieldList
```
- 切换字段列表UI
```
ShowGroupingBar
```
- 切换分组栏UI
```
EnableValueSorting
```
- 允许值单元格排序
```
AllowExcelExport
```
,
```
AllowPdfExport
```
- 导出功能

AllowMemberFilter

AllowLabelFilter

AllowValueFilter

- 筛选类型

```
EnableSorting
```
,
```
EnableVirtualization
```
- 性能与交互设置