此 Demo 展示如何创建 Excel 数据透视表。

如果这不是您想要的 Demo，您可以通过填写表格获取免费定制 Demo。

如您有与我们产品相关的其他技术问题，请联系 该Email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用JavaScript。；销售相关的问题，请联系 该Email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用JavaScript。。

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data;using Spire.Xls;namespace DemoOnlineCode{class CreatePivotTableInExcel{public void demoCreatePivotTableInExcel(string filePath,string resultFilePath){Workbook workbook = new Workbook();workbook = CreatePivotTable(filePath);workbook.SaveToFile(resultFilePath);}public Workbook CreatePivotTable(string filePath){Workbook workbook = new Workbook();workbook.LoadFromFile(filePath,ExcelVersion.Version2007);Worksheet sheet = workbook.Worksheets[0];sheet.Name = "Data Source";Worksheet sheet2 = workbook.CreateEmptySheet();sheet2.Name = "Pivot Table";CellRange dataRange = sheet.Range["A1:G17"];PivotCache cache = workbook.PivotCaches.Add(dataRange);PivotTable pt = sheet2.PivotTables.Add("Pivot Table",sheet.Range["A1"],cache);var r1 = pt.PivotFields["Vendor No"];r1.Axis = AxisTypes.Row;pt.Options.RowHeaderCaption = "Vendor No";var r2 = pt.PivotFields["Name"];r2.Axis = AxisTypes.Row;pt.DataFields.Add(pt.PivotFields["Area"],"Average of Area",SubtotalTypes.Average);pt.DataFields.Add(pt.PivotFields["Sales"],"SUM of Sales",SubtotalTypes.Sum);pt.DataFields.Add(pt.PivotFields["OnHand"],"Max of OnHand",SubtotalTypes.Max);pt.DataFields.Add(pt.PivotFields["OnOrder"],"Min of OnOrder",SubtotalTypes.Min);pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;workbook.Worksheets[1].Remove();sheet = workbook.Worksheets[1];sheet.Columns[0].AutoFitColumns();sheet.Columns[0].AutoFitRows();sheet.AllocatedRange.AutoFitColumns();sheet.AllocatedRange.AutoFitRows();return workbook;}}}

Imports System.Collections.Generic Imports System.Linq Imports System.Text Imports System.Threading.Tasks Imports System.Data Imports Spire.XLS Namespace DemoOnlineCode Class CreatePivotTableInExcel Public Sub demoCreatePivotTableInExcel(filePath As String,resultFilePath As String) Dim workbook As New Workbook() workbook = CreatePivotTable(filePath) workbook.SaveToFile(resultFilePath) End Sub Public Function CreatePivotTable(filePath As String) As Workbook Dim workbook As New Workbook() workbook.LoadFromFile(filePath,ExcelVersion.Version2007) Dim sheet As Worksheet = workbook.Worksheets(0) sheet.Name = "Data Source" Dim sheet2 As Worksheet = workbook.CreateEmptySheet() sheet2.Name = "Pivot Table" Dim dataRange As CellRange = sheet.Range("A1:G17") Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange) Dim pt As PivotTable = sheet2.PivotTables.Add("Pivot Table",sheet.Range("A1"),cache) Dim r1 = pt.PivotFields("Vendor No") r1.Axis = AxisTypes.Row pt.Options.RowHeaderCaption = "Vendor No" Dim r2 = pt.PivotFields("Name") r2.Axis = AxisTypes.Row pt.DataFields.Add(pt.PivotFields("Area"),"Average of Area",SubtotalTypes.Average) pt.DataFields.Add(pt.PivotFields("Sales"),"SUM of Sales",SubtotalTypes.Sum) pt.DataFields.Add(pt.PivotFields("OnHand"),"Max of OnHand",SubtotalTypes.Max) pt.DataFields.Add(pt.PivotFields("OnOrder"),"Min of OnOrder",SubtotalTypes.Min) pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12 workbook.Worksheets(1).Remove() sheet = workbook.Worksheets(1) sheet.Columns(0).AutoFitColumns() sheet.Columns(0).AutoFitRows() sheet.AllocatedRange.AutoFitColumns() sheet.AllocatedRange.AutoFitRows() Return workbook End Function End Class End Namespace