Производительность Excel при агрегировании сводных таблиц

Производительность сводной таблицы основана на Pivot Cache. Хотя информации на эту тему очень мало (я имею в виду отсутствие официальной документации), я нашел несколько интересных постов и документацию по 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.

Пожалуйста, используйте этот связь и этот связь для получения дополнительной информации в качестве отправной точки.

Кроме того, вы можете прочитать сообщения Сводной кэш в Excel 101 и Сводной кэш Excel 101, чтобы узнать, что это такое и какие у него побочные эффекты.

Вот несколько фрагментов кода VB и примеры использования Объект PivotCache.

Вот код, написанный на C#, который позволяет вам создать книгу Excel с некоторыми Pivot Tables, которые, конечно же, используют 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 Cache для выбранных Pivot Table:

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 — это zero index в массиве строк, где хранятся сокращения штатов США. Первый индекс в этом массиве извлекает для нас 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.

P.S.

Об обозначении Big 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) обозначает алгоритм, производительность которого зависит от логарифмического времени. Алгоритмы, использующие логарифмическое время, обычно встречаются в операциях над бинарные деревья или при использовании бинарного поиска.

Но хорошие алгоритмы сортировки жестко 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;
}

• Сводные таблицы, вопреки распространенному мнению, не просто Excel функция, но присутствует во многих приложениях, имеющих дело с табличными структурированные числовые данные – сводные таблицы являются визуальными и интерактивный результат общей концепции агрегации данных в зависимости по категориям.
• Сводные таблицы всегда связаны с данными, из которых они получены.
• При создании сводной таблицы Excel создает специальный кэш памяти. содержащий ваши данные в фоновом режиме. Этот сводной кэш хранит копия данных, находящихся в диапазоне исходных данных.
• Сводные таблицы совместно используют сводной кеш, если они ссылаются на один и тот же источник. диапазон данных. Это помогает уменьшить размер файла и предотвращает для обновления каждой сводной таблицы, которая использует один и тот же диапазон исходных данных.

• Отношения между сводными таблицами и сводными кэшами могут стать сложно. Тем более, что сводной кеш хранится в фон, и нет возможности увидеть, какие сводные таблицы совместно используются сводной кэш в книге.

Анатомия файла электронной таблицы

• Класс PivotCache PivotCache. Когда объект сериализуется как xml, его полное имя — x:pivotCache.

• Члены PivotCache (Excel) Представляет кэш памяти для Отчет сводной таблицы.

• Представляет базовый класс, в котором все элементы в Офис OpenXML документ происходит от.

• Спецификация OpenXML — большой и сложный зверь.

• cacheField (поле PivotCache) Представляет одно поле в Сводной кэш. Это определение содержит информацию о поле, таких как его источник, тип данных и расположение на уровне или иерархия. Элемент sharedItems хранит дополнительную информацию о данных в этом поле. Если нет общих элементов, то значения хранятся непосредственно в части pivotCacheRecords.

• Определяет Класс SharedItems. Когда объект сериализуется как xml, его полное имя — x:sharedItems.

• Как создать сводную таблицу на С++

• Как обновить данные сводной таблицы из кода С#
• Используйте C++, чтобы показать использование памяти в сводной таблице Excel
• Как создать сводную таблицу excel из C++ (ole/com без mfc)
• Как создать сводную таблицу в Excel в коде C#.NET
• Как экспортировать данные на один лист и создать сводную таблицу в Другой на основе данных
• Как программно добавить сводные таблицы и слайсеры в MS Excel