Использование компонентов Microsoft Office в приложениях

Часть 2. Компонент PivotTable List

Алексей Федоров, Наталия Елманова

Средства создания сводных таблиц Microsoft Excel (PivotTable Services)

Класс TOLEContainer

Использование компонента PivotTable List

    Объектная модель

    Вычисление итоговых значений и применение формул

    Изменение цветов различных частей сводной таблицы

    Использование фильтров

    Просмотр кубов OLAP

    Получение данных из компонента PivotTable List

    Экспорт итоговых таблиц и сохранение их в файлах

    Печать сводных таблиц

    Несколько слов о поставке приложений, использующих Web-компоненты Microsoft Office

Заключение

В статье Алексея Федорова «Использование компонентов Microsoft Office в приложениях», опубликованной в № 11’2000 нашего журнала, было рассмотрено, каким образом можно использовать в приложениях компоненты ChartSpace и Spreadsheet. Помимо них среди Web-компонентов Microsoft Office имеется еще один компонент, представляющий интерес в плане использования его в приложениях. Это PivotTable List, предназначенный для анализа, фильтрации и суммирования данных, получаемых из различных источников, а также для представления их в удобном для интерпретации виде.

В настоящей статье мы кратко рассмотрим средства создания сводных таблиц (PivotTable Services) Microsoft Excel, что позволит понять, где и как следует использовать компонент PivotTable List, а затем познакомимся с несколькими примераим его применения.

Средства создания сводных таблиц Microsoft Excel (PivotTable Services)

Прежде чем применять компонент PivotTable List, следует, как минимум, понять основы применения средств создания сводных таблиц Microsoft Excel.

Сводная таблица Excel (pivot table) представляет собой интерактивную таблицу, применяемую для суммирования или статистического анализа большого количества исходных данных, обычно содержащихся в одном из диапазонов ячеек Excel либо являющихся результатом запроса к какой-либо базе данных. Строки и столбцы подобной интерактивной таблицы базируются на данных из нескольких столбцов исходной таблицы. Ячейки сводной таблицы представляют собой суммы значений одного из числовых полей исходной таблицы. При этом суммирование производится для тех строк исходной таблицы, в которых значения столбцов, образовавших столбцы и строки сводной таблицы, совпадают с именами строк и столбцов сводной таблицы, на пересечении которых находится данная ячейка. В некоторых источниках сводные таблицы иногда называют перекрестными таблицами или кросс-таблицами (crosstabs).

В Excel поля сводной таблицы можно менять местами, добавлять или удалять, а сами суммируемые данные можно фильтровать по значению других наборов столбцов исходной таблицы с целью более подробного рассмотрения какой-либо конкретной области этих значений. Это позволяет создавать различные способы отображения суммарных значений для одной и той же исходной таблицы (рис. 1).

Рассмотрим в качестве примера исходную таблицу, содержащую данные о продажах торговой компании:

Для приведенных выше данных мы можем создать сводную таблицу Excel, воспользовавшись мастером сводных таблиц (PivotTable Wizard), который можно активизировать с помощью пункта меню Excel Данные | Сводная таблица (Data | PivotTable and PivotChart Report). Строки этой таблицы могут, к примеру, содержать значения поля Product исходной таблицы, столбцы сводной таблицы — значения поля Salesperson исходной таблицы, а в качестве возможных значений фильтра может быть использовано поле City исходной таблицы (в Excel такие поля могут также называться полями страниц — page fields):

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

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

Следует отметить, что сводная таблица Excel может быть построена на основании данных любого OBDC- или OLE DB-источника данных, в том числе данных OLAP-кубов (OLAP, On-line Analytical Processing — аналитическая обработка данных), доступных с помощью соответствующих OLE DB-провайдеров. Помимо этого Excel допускает использование более одного поля исходной таблицы для формирования строк, столбцов или фильтров (например, имена строк могут состоять из сочетаний имен полей Country, Region и City одновременно), что позволяет создавать сводные таблицы с иерархическими метаданными.

Заметим, однако, что поведение компонента PivotTable List отличается от поведения сводной таблицы Excel: в то время как последняя скрывает все исходные данные, компонент PivotTable List позволяет отображать их и производить их суммирование интерактивно на этапе выполнения.

Класс TOLEContainer

Подобно другим Web-компонентам Microsoft Office 2000, компонент PivotTable List представляет собой элемент управления ActiveX. Однако использовать его так же, как и другие элементы управления ActiveX, в Delphi 5 не удается — хотя его и можно установить в палитру компонентов Delphi, приложение, содержащее его, оказывается неработоспособным (об этом уже было сказано в предыдущей статье, посвященной Web-компонентам Microsoft Office). Мы не смогли найти этому приемлемого объяснения. Возможно, этот компонент нуждается в дополнительных интерфейсах, не реализованных в классе Delphi TForm; не исключено, что реализация поддержки COM в Delphi не идеальна; могут быть и другие причины такого поведения данного компонента. Тем не менее использовать компонент PivotTable List в Delphi при желании можно, для этого нужно поместить его в компонент TOleContainer со страницы System палитры компонентов Delphi. Вероятно, этот компонент добавляет к форме Delphi какие-то интерфейсы, требующиеся для корректной работы компонента PivotTable List, но отсутствующие в текущей реализации класса TForm. Это предположение подтверждается следующим фактом: простое помещение компонента TOleContainer на форму, содержащую компонент PivotTable List, также приводит к тому, что этот компонент начинает вести себя в этой форме вполне корректно.

Компонент TOleContainer применяется в приложениях Delphi главным образом как визуальный контейнер для OLE-документов. Однако он может быть использован и как контейнер для некоторых элементов управления ActiveX. Единственная трудность подобного применения заключается в том, что в этом случае нельзя использовать его метод InsertObjectDialog — по вполне очевидным причинам компоненты ActiveX отсутствуют в списке серверов OLE-документов. Вместо этого мы можем использовать метод CreateObject этого компонента:

 var
    PT      : Variant;
   ...
// Создаем OLE-объект
 OleContainer1.CreateObject('OWC.PivotTable.9',False);
// И обращаемся к нему
 PT := OleContainer1.OleObject;

Теперь наш компонент PivotTable List необходимо активировать. Для этого можно воспользоваться методом DoVerb компонента TOleContainer. Контейнеры OLE-документов обычно поддерживают несколько заранее определенных действий, например отображение или активацию OLE-объекта, а также некоторые другие действия, характерные для данного OLE-сервера (список этих действий можно получить с помощью свойства ObjectVerbs компонента TOleContainer). В приведенном ниже фрагменте кода показано, как сделать компонент PivotTable List видимым:

With OleContainer1 do
 Begin
   AutoActivate := aaManual;
      DoVerb(ovShow);
 End;

Теперь компонент PivotTable List отображается в контейнере и готов к использованию.

Использование компонента PivotTable List

Объектная модель

Поместив компонент PivotTable List на форму внутри OLE-контейнера, мы можем обращаться к его свойствам и методам.

Первое свойство, которым обладает компонент PivotTable List, — это ActiveView, предоставляющее доступ к объекту PivotView. Данный объект соответствует конкретному отображению данных в сводной таблице, представленной в компоненте PivotTable List. Объект PivotView содержит несколько коллекций, представляющих поля, значения которых используются для создания строк, столбцов и фильтров, а также формирования суммируемых данных. Помимо этого компонент PivotTable List обладает свойством PivotData, позволяющим обращаться к ячейкам сводной таблицы, а также к ее членам (то есть значениям вдоль осей сводной таблицы). Упрощенная версия объектной модели компонента PivotTable List представлена на рис. 2.

В нашем первом примере в качестве источника данных мы воспользуемся базой данных NorthWind из комплекта поставки Microsoft Access 2000 (NorthWind.mdb). Для начала установим свойство ConnectionString компонента PivotTable List:

const
    DataSourcePath = 'c:\data\northwind.mdb'; 
...
 PT.ConnectionString := 'PROVIDER=MICROSOFT.JET.OLEDB.4.0;' +
   'DATA SOURCE='+ DataSourcePath;

Теперь следует указать, что именно из этой базы данных (таблицу, результат запроса) мы будем использовать в качестве исходных данных. Это можно сделать с помощью либо свойства DataMember, либо свойства CommandText. В нашем примере мы используем представление 'Product Sales for 1997', возвращающее сведения о продажах торговой компании NorthWind Traders с 1 января по 31 декабря 1997 года. С помощью свойства DataMember это можно сделать так:

PT.DataMember := '[Product Sales for 1997]';

А с помощью свойства CommandText — следующим образом:

PT.CommandText := 'SELECT   * FROM [Product Sales for 1997]';

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

В объектной модели компонента PivotTable List поля сводной таблицы образуют несколько коллекций PivotFieldSets объектов PivotFieldSet, являющихся свойствами объектов PivotDataAxis, PivotFilterAxis и PivotGroupAxis. Эти объекты представлены в следующей таблице:

Первые три объекта поддерживают метод InsertFieldSet, применяемый для определения данных этого набора полей. Для определения полей данных и сумм мы можем использовать методы InsertFieldSet или InsertTotal соответственно. Ниже показано, как определить все категории полей для сводной таблицы:

 Var
      View   : Variant;
   ...
 View := PT.ActiveView;
 // Перемещаем поля на соответствующие оси
 View.RowAxis.InsertFieldSet    (View.FieldSets['CategoryName']);
 View.ColumnAxis.InsertFieldSet(View.FieldSets['ShippedQuarter']);
 View.DataAxis.InsertFieldset   (View.FieldSets['ProductName']);
 View.DataAxis.InsertFieldset   (View.FieldSets['ProductSales']);

В приведенном фрагменте кода мы определили, что поле CategoryName используется для формирования имен строк, поле ShippedQuarter — для формирования имен столбцов, а поля ProductName и ProductSales — для создания области данных и суммирования их пользователем на этапе выполнения в интерактивном режиме.

Вычисление итоговых значений и применение формул

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

 // Добавляем итоги
 Total := View.AddTotal('Sales Total',
 View.FieldSets['ProductSales'].Fields[0],plFunctionSum);
 View.DataAxis.InsertTotal(Total);
 View.Totals['Sales Total'].NumberFormat :='Currency';

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

Изменение цветов различных частей сводной таблицы

Внешний вид нашей сводной таблицы можно улучшить, применив разное цветовое оформление к различным ее частям. Для этой цели можно изменить значения свойств TotalBackColor, FieldLabelBackColor, MemberBackColor и FieldLabelFont объекта PivotView, а также значение свойства SubTotalBackColor объекта PivotField:

 // Раскрашиваем части сводной таблицы в разные цвета
   View.TotalBackColor := 'CornSilk';
   View.FieldLabelBackColor := 'DarkBlue';
   View.FieldLabelFont.Color := 'White';
   View.ColumnAxis.FieldSets[0].Fields[0].SubTotalBackColor := 'LightSteelBlue';
   View.RowAxis.FieldSets[0].Fields[0].SubTotalBackColor := 'LightSteelBlue';
   View.MemberBackColor := 'LightGrey';

Наконец, ограничим возможность манипулировать сводной таблицей на этапе выполнения:

 // Удаляем заголовок компонента PivotTable List
   View.TitleBar.Visible := False;
// Прячем инструментальную панель и запрещаем вывод панели свойств
   PT.DisplayToolbar := False;
   PT.AllowPropertyToolbox := False;

Результат добавления итогов и изменения цветов различных частей сводной таблицы показан на рис. 3.

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

Использование фильтров

Формирование списка полей фильтров мало чем отличается от формирования списка полей для строк или столбцов. Для добавления поля исходной таблицы в список полей фильтров следует использовать свойство FilterAxis объекта PivotView, например:

View.FilterAxis.InsertFieldSet(View.FieldSets['CategoryName']);

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

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

Просмотр кубов OLAP

Вместо запросов к реляционным СУБД в качестве источника исходных данных можно использовать кубы OLAP, созданные с помощью Microsoft Excel, Microsoft SQL Server или других инструментов. Для обращения к локальным кубам OLAP следует в свойстве ConnectionString компонента PivotTable List сослаться на Microsoft OLE DB Provider for OLAP Services (MSOLAP) и указать местоположение куба. Ниже приведен пример установки свойства ConnectionString для этого случая:

PT.ConnectionString := 
   'PROVIDER = MSOLAP; DATA SOURCE = C:\DATA\CUBE.CUB';

В случае обращения к кубам OLAP, хранящимся в Microsoft SQL Server 7.0, свойство ConnectionString должно содержать ссылку на тот же самый OLE DB-провайдер и на имя соответствующей многомерной базы данных:

PT.ConnectionString := 
   'PROVIDER = MSOLAP; DATA SOURCE = maindesk;' +
   'INITIAL CATALOG=FoodMart';

При обращении к кубам OLAP, хранящимся в Microsoft SQL Server 2000, свойство ConnectionString должно ссылаться на Microsoft OLE DB Provider for OLAP Services 8.0 (MSOLAP.2):

PT.ConnectionString := 
   'PROVIDER = MSOLAP.2; DATA SOURCE = maindesk;' +
   'INITIAL CATALOG=FoodMart 2000';

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

Получение данных из компонента PivotTable List

С целью получения данных, особенно итогов, мы можем обратиться к объекту PivotData и коллекциям PivotRowMembers и PivotColumnMembers для извлечения данных из компонента PivotTable List. Добавим в наш пример таблицу с итоговыми значениями продаж каждого продукта в каждом квартале. Для этого используем компонент TListView со страницы Win32 палитры компонентов Delphi. Фрагмент кода, решающего эту задачу, приведен в следующем листинге:

var
   View   : Variant;             // PivotTable View
   Total : Variant;          // Totals
   R,C    : Integer; // Row and Column Counters
   Cell   : Variant; // One cell
   Agg    : Variant; // Aggregate value
   LI      : TListItem; // ListView Item
   CI      : TListColumn; // ListView Column
   ...
   
   // Определим заголовки столбцов
    CI := ListView1.Columns.Add;
   CI.Caption   := 'Category';
   CI.AutoSize := True;
   For C := 0 to PT.ActiveData.ColumnMembers.Count-1 do
   Begin
       CI := ListView1.Columns.Add;
       CI.Caption := PT.ActiveData.ColumnMembers[C].Caption;
       CI.AutoSize := True;
   End;
   // Для каждой строки
    For R := 0 to PT.ActiveData.RowMembers.Count-1 do
   Begin
      LI := ListView1.Items.Add;
      LI.Caption := PT.ActiveData.RowMembers[R].Caption;
   // Для каждого столбца
      For C := 0 to PT.ActiveData.ColumnMembers.Count-1 do
      Begin
   // Получим значение ячейки
       Cell :=
          PT.ActiveData.Cells[PT.ActiveData.RowMembers[R],
          PT.ActiveData.ColumnMembers[C]];
   // Получим итоговое значение
       Agg := Cell.Aggregates.Item['Sales Total'].Value; //    or [0]
   // И отобразим его
       LI.SubItems.Add(IntToStr(Agg));
      End;
   End;

Результат извлечения итоговых значений из сводной таблицы показан на рис. 4.

Итак, в приведенном выше примере мы извлекли имена строк и столбцов, а также итоговые значения, вычисленные компонентом PivotTable List, и затем поместили их в компонент TListView.

Экспорт итоговых таблиц и сохранение их в файлах

Используя метод Export компонента PivotTable List, мы можем сохранить нашу сводную таблицу в файле, а затем, если необходимо, открыть ее в Microsoft Excel. У метода Export имеется два параметра. Первый содержит имя файла, в котором следует сохранить сводную таблицу, а второй указывает на то, какие именно действия нужно выполнить в процессе экспорта. Мы можем выбрать, например, в качестве значения этого параметра plExportActionNone (= 0, просто сохранить файл) или plExportActionOpenInExcel (= 1, сохранить файл и открыть его в Excel). Если имя файла не указано, будет создан временный файл с именем PivotTableXXXX в каталоге WINDOWS\TEMP. Сводная таблица при этом сохраняется в формате XML.

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

• имя файла с графическим изображением. Если этот аргумент не указан, изображение будет сохранено в файле PIVOT.GIF;
• тип графического файла. В текущей версии компонента PivotTable List поддерживается только формат GIF, поэтому данный аргумент может быть опущен;
• ширина изображения в пикселах;
• высота изображения в пикселах. Эти два аргумента также могут быть не указаны.

Следующий фрагмент кода иллюстрирует, как сохранить сводную таблицу в файле CUSTOMERS.GIF в каталоге C:\DATA:

PT.ExportPicture('c:\data\customers.gif');

Обратите внимание на то, что сводная таблица при этом сохраняется в ее текущем состоянии и в GIF-файле окажется только ее видимая на экране часть. Вот уж воистину — What You See Is What You Get!

Печать сводных таблиц

В текущей версии компонента PivotTable List нельзя напрямую напечатать ее содержимое. Тем не менее реализовать такую возможность несложно.

Для копирования в буфер обмена данных сводной таблицы мы можем использовать метод Copy компонента PivotTable List. Затем содержимое буфера обмена мы перенесем в Microsoft Excel и воспользуемся возможностями печати данных Excel с помощью автоматизации (более подробно об автоматизации Microsoft Excel и других приложений Microsoft Office можно прочесть в цикле статей Наталии Елмановой «Автоматизация приложений Microsoft Office в примерах» в нашем журнале за текущий и предыдущие месяцы).

Для начала объявим несколько переменных, используемых для автоматизации Excel:

var
 Excel         : Variant; // Экземпляр MS Excel
 WBook         : Variant; // Рабочая книга
 WSheet       : Variant; // Лист
 PageSetup   : Variant; // Объект PageSetup

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

 // Копируем содержимое PivotTable List data в буфер обмена
 PT.Copy(PT.ActiveView);
// Создаем копию Excel 
 Excel   := CreateOLEObject('Excel.Application');
// Добавляем новую рабочую книгу
 WBook   := Excel.Workbooks.Add;
// Получаем ссылку на ее первый лист
 WSheet := WBook.WorkSheets[1];
// Помещаем содержимое буфера обмена на этот лист
WSheet.Paste(WSheet.Range['A1', 'A1']);
// Приводим ширину колонок в соответствие с данными
WSheet.UsedRange.Columns.AutoFit;

Теперь наша сводная таблица находится внутри листа рабочей книги и мы можем организовать ее печать. Для этого используем свойство PageSetup объекта WorkSheet. В данном примере мы расположим лист вертикально, определим содержимое колонтитулов и укажем, что нужно печатать линии сетки. Затем мы сделаем экземпляр Microsoft Excel видимым и позволим пользователю манипулировать им:

 // Получаем ссылку на объект PageSetup 
 PageSetup := WSheet.PageSetup;
// Ориентация страницы: 1 — Landscape, 2 — Portrait
 PageSetup.Orientation := 2;
// Данные для колонтитулов
 PageSetup.CenterHeader := PT.ActiveView.TitleBar.Caption;
 PageSetup.CenterFooter := 'Page &P of &N';
// Печатаем сетку
 PageSetup.PrintGridlines := True;
// Показываем созданный экземпляр Excel на экране
 Excel.Visible := True;
// Позволим пользователю им манипулировать
 Excel.UserControl := True;
// И покажем окно Print Preview
 WSheet.PrintPreview;

Окно Print Preview, появляющееся на экране в результате выполнения этого кода, показано на рис. 5.

Обратите внимание, что вместо функции PrintPreview, показывающей окно Print Preview, мы можем вызвать функцию PrintOut. В этом случае делать копию Microsoft Excel видимой необязательно.

Несколько слов о поставке приложений, использующих Web-компоненты Microsoft Office

В предыдущей статье, посвященной Web-компонентам Microsoft Office, было сказано, что эти компоненты можно использовать на компьютерах с установленной Microsoft Office Microsoft Access 2000 любой версии. Однако для корректной работы этих компонентов в приложениях при установке Microsoft Office достаточно выбрать только раздел «Microsoft Office Web Components». Данное замечание не относится к последнему примеру — в нем используется еще и Microsoft Excel, который в этом случае также должен быть установлен. Подробности о поставке приложений, использующих Web-компоненты Microsoft Office, можно найти на Web-сервере корпорации Microsoft по адресу: http://www.microsoft.com/Office.

Заключение

В настоящей статье мы рассмотрели, как использовать компонент PivotTable List, входящий в состав Web-компонентов Microsoft Office. В частности, теперь мы знаем, как определить источник исходных данных для этого компонента и сформировать строки и столбцы сводной таблицы, как установить фильтры и указать, что будет отображаться в области данных, как вычислять и отображать итоговые значения, изменять цветовое оформление различных частей сводной таблицы, а также как извлекать значения из ячеек сводной таблицы и каким образом ограничить возможности изменения содержимого сводной таблицы пользователем (или, наоборот, предоставить ему такую возможность). И наконец, теперь мы знаем, как можно сохранить в файле или распечатать сводную таблицу.

Примеры

В следующей статье мы расскажем, как применять автоматизацию Microsoft Excel и Microsoft PivotTable Services для добавления OLAP-функциональности в приложения, созданные с помощью Delphi. Ждите новых номеров...

КомпьютерПресс 12'2000