c# - 数据透视表聚合的 Excel 性能

Question

在我使用 Excel 的过程中，我总是对 Excel 执行以下两个聚合操作的表现感到惊讶:

1. 日期/时间聚合。
2. 不区分大小写的聚合。

Excel 是如何实现这种性能的？他们是否为数据透视相关的信息和聚合存储额外的数据结构？这是否记录在任何地方或我在哪里可以找到更多相关信息？我查看了 Libreoffice 源代码，但实际产品在聚合/数据透视性能方面甚至不接近 Excel。

---

如果了解 Excel 的人可以分享更多有关 Excel 用于实现此性能的低级聚合行为或结构的信息，那就太好了——例如，他们是否将任何标签存储两次——一次在其原生中大小写并且曾经为了聚合目的而降低？虽然我知道这个问题过于宽泛而不是关于代码答案本身，而且它更概念化，但我希望这个答案可以作为优化 excel 样式聚合性能的方法的良好引用。

---

以下是我根据 ARGeo 的一些建议注意到的一些事情 --

(1) Pivot Cache相关的文件有两个——Definitions(field-level info):

(2) 和记录(行/单元格级别信息)——

接下来的几个问题:

• Excel 如何确定何时按原样存储值以及何时将其存储为共享记录。例如，为什么 B2 中的值“LifeLock”(大小写混合的字符串)按原样存储，而 F2 中的值“AZ”按原样存储在 sharedItems (v="0") 中？
• 是否有关于 Excel 在内存中为其 pivotCache 使用的内部 C/C++ Struct 的任何信息(而不是作为存储的各种 XML 文档)？
• 是否有关于 Excel 内部如何使用字段级存储的“帮助信息”的信息？例如，此信息:

.

<cacheField name="numEmps" numFmtId="0"><sharedItems containsString="0" containsBlank="1" containsNumber="1" containsInteger="1" minValue="0" maxValue="20000"/></cacheField>

---

Answer 1

数据透视表性能基于数据透视缓存。虽然关于这个主题的信息很少(我的意思是缺少官方文档)，但我发现了一些有趣的帖子和 MS 文档。

定义:

Pivot Cache 是保存数据透视表记录的特殊内存区域。

When you create a Pivot Table, Excel takes a copy of the source data and stores it in the Pivot Cache. The Pivot Cache is held in Excel’s memory. You can’t see it but that’s the data the Pivot Table references when you build your Pivot Table.

This enables Excel to be very responsive to changes in the Pivot Table but it can also double the size of your file. After all, the Pivot Cache is just a duplicate of your source data so it makes sense that your file size will potentially double.

请使用这个 link还有这个link获取更多信息作为起始引用点。

此外，您还可以阅读 Pivot Cache in Excel 101和 Excel Pivot Cache 101发帖了解它是什么以及它有什么副作用。

这里有一些 VB 代码片段和示例如何使用 PivotCache object .

这是一个用 C# 编写的代码，它允许您使用一些 Pivot Tables 创建 Excel 工作簿，当然，使用 Pivot Cache:

System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
 
namespace ConsoleApplication1 {

    class Program {
 
        static void Main(string[] args) {
 
            Excel.Application objApp;
            Excel.Workbook objBook;
            Excel.Sheets objSheets;
            Excel.Workbooks objBooks;
 
            string command = (@"SELECT * FROM dbo.Client");
 
            using (SqlConnection connection = new SqlConnection(GetConnectionStringByName("CubsPlus"))) {

                DataTable data = new DataTable();

                try {
                    connection.Open();
                }
                catch (Exception e) {
                    StackTrace st = new StackTrace(new StackFrame(true));
                    StackFrame sf = st.GetFrame(0);
                    Console.WriteLine (e.Message + "\n" + "Method" + sf.GetMethod().ToString() + "\n" + "Line" + sf.GetFileLineNumber().ToString());
                }
                try {
                    data = DataTools.SQLQueries.getDataTableFromQuery(connection, command);
 
                    if (data == null) {
                        throw new ArgumentNullException();
                    }
                }
                catch (Exception e) {

                    StackTrace st = new StackTrace(new StackFrame(true));
                    StackFrame sf = st.GetFrame(0);
                    Console.WriteLine (e.Message + "\n" + "Method" + sf.GetMethod().ToString() + "\n" + "Line" + sf.GetFileLineNumber().ToString());
                }
 
                objApp = new Excel.Application();

                try {     
                    objBooks = objApp.Workbooks;
                    objBook = objApp.Workbooks.Add(Missing.Value);
                    objSheets = objBook.Worksheets;
 
                    Excel.Worksheet sheet1 = (Excel.Worksheet)objSheets[1];
                    sheet1.Name = "ACCOUNTS";
                    string message = DataTools.Excel.copyDataTableToExcelSheet(data, sheet1);

                    if (message != null) {
                        Console.WriteLine("Problem importing the data to Excel");
                        Console.WriteLine(message);
                        Console.ReadLine();
                    }
                         
                    //CREATE A PIVOT CACHE BASED ON THE EXPORTED DATA
                    Excel.PivotCache pivotCache = objBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase,sheet1.UsedRange);
 
                    Console.WriteLine(pivotCache.SourceData.ToString());
                    
                    Console.ReadLine();
 
                    //WORKSHEET FOR NEW PIVOT TABLE
                    Excel.Worksheet sheet2 = (Excel.Worksheet)objSheets[2];
                    sheet2.Name = "PIVOT1";
                    
                    //PIVOT TABLE BASED ON THE PIVOT CACHE OF EXPORTED DATA
                    Excel.PivotTables pivotTables = (Excel.PivotTables)sheet2.PivotTables(Missing.Value);
                    Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, objApp.ActiveCell, "PivotTable1", Missing.Value, Missing.Value);
 
                    pivotTable.SmallGrid = false;
                    pivotTable.TableStyle = "PivotStyleLight1";
 
                    //ADDING PAGE FIELD
                    Excel.PivotField pageField = (Excel.PivotField)pivotTable.PivotFields("ParentName");
                    pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
 
                    //ADDING ROW FIELD
                    Excel.PivotField rowField = (Excel.PivotField)pivotTable.PivotFields("State");
                    rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
 
                    //ADDING DATA FIELD
                    pivotTable.AddDataField(pivotTable.PivotFields("SetupDate"), "average setup date", Excel.XlConsolidationFunction.xlAverage);
 
                    ExcelSaveAs(objApp, objBook, @"J:\WBK");
 
                    objApp.Quit();
                }     
                catch (Exception e) {

                    objApp.Quit();
                    Console.WriteLine(e.Message);
                    Console.ReadLine();
                }
            }
        }
 
        static string ExcelSaveAs(Excel.Application objApp, Excel.Workbook objBook, string path) {
            try {
                objApp.DisplayAlerts = false;
                objBook.SaveAs(path, Excel.XlFileFormat.xlExcel7, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                objApp.DisplayAlerts = true;
                return null;
            }
            catch (Exception e) {
                StackTrace st = new StackTrace(new StackFrame(true));
                StackFrame sf = st.GetFrame(0);
                return (e.Message + "\n" + "Method" + sf.GetMethod().ToString() + "\n" + "Line" + sf.GetFileLineNumber().ToString());
            }
        }
        static string GetConnectionStringByName(string name) {
            //ASSUME FAILURE
            string returnValue = null;
 
            //Look for the name in the connectionStrings section
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[name];
 
            // If found, return the connection string
            if (settings != null) {
                returnValue = settings.ConnectionString;
            }
            return returnValue;
        }
    }
}

下面是用 VB 编写的代码，它允许我们为选定的 Pivot Table 创建一个新的 Pivot Cache:

Sub SelPTNewCache()

    Dim wsTemp As Worksheet
    Dim pt As PivotTable
    
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    
    If pt Is Nothing Then
        MsgBox "Active cell is not in a pivot table"
    Else
        Set wsTemp = Worksheets.Add
        
        ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=pt.SourceData).CreatePivotTable _
            TableDestination:=wsTemp.Range("A3"), _
            TableName:="PivotTableTemp"
        
        pt.CacheIndex = wsTemp.PivotTables(1).CacheIndex
        
        Application.DisplayAlerts = False
        wsTemp.Delete
        Application.DisplayAlerts = True
    End If
    
exitHandler:
        Set pt = Nothing

End Sub

1. In your asd.js file there are the following elements:

– s 代表一个字符串值

– n代表一个数值

– d 代表日期值

– x 代表一个索引值

– v 表示一个值本身

那么，让我们用人类语言翻译此表的 F2 单元格中包含的数据:

<x v="0"/>

0 的值是存储美国各州缩写的字符串数组中的零索引。该数组中的第一个索引为我们检索 Arizona。我不知道为什么下一行的单元格包含小写的 az 而所有其他单元格包含大写的 AZ 但我确定这与 Shared Record.

2. I haven't found any useful information on the internal C/C++ Struct that Excel uses in-memory for its pivotCache.

最后:

3. Here's a LINK containing useful info regarding "helper information" in third extra question.

附言

关于大 O 表示法。

Big O notation is used in Computer Science to describe the performance or complexity of an algorithm. Big O specifically describes the worst-case scenario, and can be used to describe the execution time required or the space used (in memory or on disk) by an algorithm. Big O notation is a measure of the complexity of your program in terms of the size of the input.

• O(1) 代表无论输入数据集的大小总是同时执行的算法。

• O(N)代表算法的性能线性增长，并且与输入数据集的大小成正比。

• O(N*N) 表示性能与输入数据集大小的平方成正比的算法。

• T(N) = O(log N) 代表性能取决于对数时间的算法。在 binary trees 上的操作中常见采用对数时间的算法。或者在使用二进制搜索时。

但是好的排序算法是 O(N log N)。具有这种效率的算法示例可以是合并排序，它将数组分成两半，通过递归调用自身对这两半进行排序，然后将结果合并回来成一个数组。

这是一个抽象的 C# 代码片段，展示了 O(N log N) 算法的工作原理(大致相同的方法可用于创建数据透视表):

public static int[] MergeSort(int[] inputItems, int lowerBound, int upperBound) {
    if (lowerBound < upperBound) {
        int middle = (lowerBound + upperBound) / 2;
        MergeSort(inputItems, lowerBound, middle);
        MergeSort(inputItems, middle + 1, upperBound);
 
        int[] leftArray = new int[middle - lowerBound + 1];
        int[] rightArray = new int[upperBound - middle];
 
        Array.Copy(inputItems, lowerBound, leftArray, 0, middle - lowerBound + 1);
        Array.Copy(inputItems, middle + 1, rightArray, 0, upperBound - middle);
 
        int i = 0;
        int j = 0;
        for (int count = lowerBound; count < upperBound + 1; count++) {
            if (i == leftArray.Length) {
                inputItems[count] = rightArray[j];
                j++;
            }
            else if (j == rightArray.Length) {
                inputItems[count] = leftArray[i];
                i++;
            }
            else if (leftArray[i] <= rightArray[j]) {
                inputItems[count] = leftArray[i];
                i++;
            }
            else {
                inputItems[count] = rightArray[j];
                j++;
            }
        }
    }
    return inputItems;
}