Работа со сводными таблицами

В этом разделе рассматривается класс Open XML SDK PivotTableDefinition и его связь со схемой SpreadsheetML формата файла Open XML. Дополнительные сведения об общей структуре частей и элементов, составляющих документ SpreadsheetML, см. в разделе Структура документа SpreadsheetML.

Сводная таблица в SpreadsheetML

В следующей информации из спецификации ISO/IEC 29500 представлен PivotTableDefinition элемент (<pivotTableDefinition/>).

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

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

Книга указывает на часть pivotCacheDefinition и является владельцем ее долговечности, а эта часть, в свою очередь, указывает на часть pivotCacheRecords и является ее владельцем. Книга также указывает на часть листа и является ее владельцем, а эта часть указывает на определение части pivotTable и является ее владельцем, если сводная таблица находится на листе (на одном листе могут находиться несколько сводных таблиц). Часть pivotTable указывает на часть pivotCacheDefinition, которую она использует. Поскольку несколько сводных таблиц не могут использовать один кэш, часть pivotTable не является владельцем долговечности части pivotCacheDefinition.

Часть pivotTable подробно описывает макет сводной таблицы на листе. Она определяет, какие поля расположены на оси строк, на оси столбцов, в фильтре отчета и в областях значений сводной таблицы. Она также содержит сведения о форматировании сводной таблицы. Если в сводной таблице используется условное форматирование, это также задано в части pivotTable.

В определении кэша сводной таблицы содержатся определения всех полей сводной таблицы. Если сводная таблица создается на основе обычной таблицы, каждый столбец этой таблицы превращается в поле определения кэша сводной таблицы. Кэш сводной таблицы содержит определения полей и сведения о типе содержимого в этом поле. В разметке кэша также хранятся ссылки на исходные данные, чтобы можно было обновить кэш сводной таблицы вместе с кэшированными данными в части записей кэша сводной таблицы.

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

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

Чтобы создать сводную таблицу, которая будет готова к использованию при открытии книги, потребуется также создать разметку ячеек таблицы. Сводная таблица отображается в ячейках книги, поэтому их также необходимо подготовить. Кроме того, можно потребовать, чтобы пользователь обновлял ячейки сводной таблицы при открытии документа.

В следующей таблице перечислены распространенные классы open XML SDK, используемые при работе с классом PivotTableDefinition .

Элемент SpreadsheetML	Класс пакета SDK Open XML
<pivotField/>	PivotField
<pivotCacheDefinition/>	PivotCacheDefinition
<pivotCacheRecords/>	PivotCacheRecords

Класс PivotTableDefinition пакета SDK Open XML

Класс Open XML SDK PivotTableDefinition представляет элемент определения сводной таблицы (<pivotTableDefinition/>), определенный в схеме формата файлов Open XML для документов SpreadsheetML. Используйте класс для PivotTableDefinition управления отдельными <pivotTableDefinition/> элементами в документе SpreadsheetML.

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

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

Три main фрагментовPivotTableDefinition: расположение таблицы, сведения о отображении кэшированных полей и сведения о расположении кэшированных полей. Дополнительные сведения об этих и других дополнительных элементах, составляющих PivotTableDefinition, см. в спецификации ISO/IEC 29500.

Класс PivotField

Элемент PivotTableDefinition содержит PivotField элементы (<pivotField/>). В следующей информации из спецификации ISO/IEC 29500 представлен PivotField элемент (<pivotField/>).

Представляет одно поле в сводной таблице. Данный элемент содержит сведения об этом поле, в том числе о наборе элементов в этом поле.

Сначала определите коллекцию полей, отображаемых в сводной таблице, с помощью pivotFields элемента . Каждое поле представляет собой кэш для данных этого поля в источнике данных. Отдельно определять кэш не нужно. Вместо этого можно задать item элемент равным default и заставить пользователя обновить таблицу при открытии документа. Атрибут showAll используется для скрытия определенных элементов для этого измерения данных. После определения полей, входящих в таблицу, эти поля добавляются в четыре области сводной таблицы.

Класс PivotCacheDefinition

В следующей информации из спецификации ISO/IEC 29500 представлен PivotCacheDefinition элемент (<pivotCacheDefinition/>).

Часть pivotCacheDefinition определяет каждое поле части pivotCacheRecords, включая имя поля и сведения о содержащихся в нем данных. Часть pivotCacheDefinition также определяет общие элементы сводной таблицы, используемые частями pivotTable и pivotRecords.

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

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

Последняя часть определения кэша определяет поля источника данных с помощью cacheField элемента . Элемент cacheField используется для двух целей: он определяет тип данных и форматирование поля, а также используется в качестве кэша для общих строк. Значения сводной таблицы хранятся в части кэша записей сводной таблицы. Если в качестве значения используется повторяющаяся строка, запись кэша использует ссылку на коллекцию cacheField общих элементов.

Класс PivotCacheRecords

В следующей информации из спецификации ISO/IEC 29500 представлен PivotCacheRecords элемент (<pivotCacheRecords/>).

В части pivotCacheRecords содержатся исходные данные, которые будут агрегироваться. Это кэш сходных данных.

В части кэша записей может храниться любое количество кэшированных записей. В каждой записи определено столько же значений, сколько полей в определении кэша. Каждая запись определяется с r помощью элемента . Эта запись содержит элементы значений как дочерние элементы. Здесь могут использовать типизированные значения, например числовые, логические, в формате даты и времени, или ссылки на общие элементы.