Анализ деятельности офиса

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

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

Рассмотрим объектную модель, используемую при программной работе со сводными таблицами, - основные классы, их свойства и методы. Для краткости я буду называть соответствующие объекты " Pivot -объектами". Нужно заметить, что модель Pivot -объектов достаточно сложная, она включает несколько классов, многие из которых имеют достаточно большой набор свойств и методов. Замечу, что эта объектная модель в Office 2000 претерпела существенные изменения в сравнении с предыдущей версией. Существенное влияние на нее оказал новый подход к доступу данных, основанный на OLE DB и ADO. С этой точки зрения, знакомство с ADO поможет нам и при изучении модели Pivot-объектов.

Замечу, документация по этим объектам, как мне кажется, оставляет желать лучшего. Она не полна, не всегда точна. Замечу еще, что и использование такого средства, как Macrorecorder, не дает правильного представления о том, как использовать новые возможности Pivot -объектов, поскольку Macrorecorder работает, основываясь на старой модели.

Два основных класса объектов - PivotCache и PivotTable и две коллекции этих объектов - PivotCaches и PivotTables играют центральную роль в программном создании сводных таблиц. Кроме них существует еще несколько классов, так или иначе связанных со сводными таблицами:

• PivotField и коллекция PivotFields - определяют поля сводной таблицы,
• PivotItem и коллекция PivotItems - определяют данные, хранимые в полях сводной таблицы,
• PivotLayout и коллекция PivotLayouts - определяют расположение полей сводной таблицы,

Поговорим об основных объектах. Прежде всего, разберемся с тем, почему понадобились два объекта (два класса), чтобы описать одну сущность - сводную таблицу. Объект PivotCache задает кэш-память, сводную таблицу, хранимую в оперативной памяти, ее данные. Работа с этим объектом, помимо всего прочего, позволяет оптимизировать память, отводимую сводной таблице. Объект PivotTable задает представление сводной таблицы на рабочем листе, задает отчет. До сих пор я не использовал термин "отчет" сводной таблицы, хотя он широко используется, в том числе и в документации. Теперь пришла пора и для этого термина. Действительно, объект PivotTable вполне соответствует понятию отчета, задаваемого сводной таблицей. Таким образом, два объекта PivotCache и PivotTable задают внутренне и внешнее представление сводной таблицы. В основе объекта PivotTable лежит объект PivotCache, являясь источником данных для объекта PivotTable.

Правильная технология работы при программном создании сводной таблицы состоит в том, чтобы вначале создать объект PivotCache, а затем на его основе создать объект PivotTable. Заметьте, если объект PivotCache создан, но не создан объект PivotTable, ссылающийся на этот кэш памяти, то при закрытии документа память освобождается и объект PivotCache автоматически удаляется перед сохранением рабочей книги.

Есть некоторая разница и в коллекциях этих объектов. Коллекция PivotCaches связана с самой книгой - объектом Workbook, в то время как коллекция PivotTables связывается с рабочим листом - объектом WorkSheet. А теперь перейдем к деталям и рассмотрим подробнее основные свойства и методы этих классов объектов.

Объект PivotCache и коллекция PivotCaches

Коллекция PivotCaches возвращается при вызове одноименного свойства объекта Workbook. Как и большинство коллекций, она устроена достаточно просто. У нее типичные для коллекций свойства: Application, Count, Creator, Parent. Методы также классические - Item и Add. Из всего этого набора заслуживает рассмотрения только метод Add, позволяющий создать новый объект. Вот его синтаксис:

Expression.Add(SourceType, SourceData)

Здесь Expression - выражение, возвращающее объект класса PivotCaches. Аргументы метода имеют следующий смысл:

• SourceType - задает тип источника данных. Значение аргумента может быть одной из следующих четырех констант: xlCosolidation, xlDatabase, xlExternal, xlPivotTable. Каждая из этих констант задает один из четырех возможных типов, которые уже обсуждались, когда речь шла о первом шаге работы Мастера сводных таблиц, смотри, например, рис. 8.1.
• SourceData - возможный аргумент, который не следует задавать, если первый аргумент имеет значения xlExternal, то есть задает внешний источник данных. Для остальных трех значений первого аргумента параметр SourceData определяет источник данных. Тип этого аргумента, естественно зависит от значения первого аргумента. Так, если первый аргумент определяет базу данных Excel, то аргумент задается объектом Range, определяющим эту базу. Если речь идет о диапазонах консолидации, то аргумент задается массивом из объектов Range. В случае, когда речь идет о другой сводной таблице, как источнике данных, то аргумент задается строкой текста, представляющей имя сводной таблицы.

Конечно, более всего, для нас интересен случай, когда источником построения сводной таблицы является база данных. Понятно, что в этом случае метод Add определяет пустой объект PivotCache, еще не заполненный данными, поскольку второй параметр при выполнении метода не задается и источник данных, следовательно, не определен. Как же происходит заполнение объекта PivotCache данными в этом случае? Ответ прост - при установлении свойств объекта. Ситуация во многом напоминает работу с объектом Command из библиотеки ADO. У объекта PivotCache имеются свойства - Connection, CommandType, CommandText, аналогичные одноименным свойствам объекта Command.

Давайте перейдем к рассмотрению свойств и методов объекта PivotCache.