此 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