Владимир Федченко
дата публикации 03-11-2006 05:38

---

Версия для печати

В списке обсуждаемых тем на Круглом столе Королевства часто возникает вопрос о построении сводных таблиц. Сводная таблица представляет собой очень удобный инструмент для отображения и анализа данных, возвращаемых запросом к базе данных. Можно, конечно, для этой цели использовать различные пакеты для построения отчетов (вроде FastReport). Но с генераторами отчетов возникает масса вопросов (отсутствие каких либо библиотек, проблемы с экспортом, отсутствие необходимой документации и т.д.). А начальник требует выдать ему отчет приблизительно такого вида: чтобы были видны все продажи, по всем сотрудникам, по всем регионам, по всем товарам за указанный период времени (скажем, за два года), но денег на покупку генератора отчетов не дает. А как бы было хорошо выдать что-нибудь типа вот такой формы:

Что тут остается делать. Варианта только два: либо пытаться создавать что-то свое, либо увольняться. Альтернативное решение проблемы предоставлено фирмой Microsoft уже очень давно. Называется оно PivotTable (Сводная таблица) и доступно в меню "Данные" приложения Excel. Осталось только научиться пользоваться этой возможностью. Для этого нам понадобиться:

1. Delphi 7 (проект создан именно в этой версии);
2. Установленный M$ Excel;
3. Учебная база M$ Access Norhwind.mdb (прилагается в архиве);
4. Немного свободного времени;
5. Много желания понять как это делается.

Итак, начинаем. Существует два типа связи с Excel - раннее и позднее. Об их отличиях речь неоднократно шла на Королевстве. Будем использовать раннее связывание, т.к. при позднем компьютер впадает в состояние комы. О том как подключиться к Excel и добавить книгу подробно описано в материалах Королевства. Объявим следующие переменные:

WB:_WorkBook;//рабочая книга
WS:_WorkSheet;//лист Excel куда помещается сводная таблица
PC:PivotCache;//кеш для данных сводной таблицы
PT:PivotTable;//собственно сама сводная таблица
i:byte;

XLS.EnableEvents:=False;

PC:=WB.PivotCaches.Add(xlExternal,emptyparam)

Этот метод имеет два параметра SourceType и SourceData. Но так как мы используем внешние данные (SourceType = xlExternal), то второй параметр нужно оставить пустым. Кэш создан, но не подключен к источнику данных. Надо восполнить этот пробел. Укажем строку подключения, тип подключения и зададим сам запрос:

PC.Connection:=Format('OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%snorthwind.mdb',
                      [ExtractFilePath(ParamStr(0))]);

PC.CommandType:=xlCmdSQL;
PC.CommandText:='select salesperson, country, city, productname,'+
                'orderdate, year(orderdate) as yy, month (orderdate) as mm, '+
                'quantity, extendedPrice from invoices';

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

PT:=PC.CreatePivotTable(WS.Range['A3',emptyparam],
                        'PivotTable1',emptyparam,xlPivotTableVersionCurrent).

Три заданных параметра означают следующее: ячейка в которую поместим сводную таблицу, имя сводной таблицы и версия сводной таблицы (зависит от установленной версии M$ Office, в данном случае установлена текущая версия). Пустой параметр называется ReadData. Он указывает на то, читать ли в кэш все данные из внешнего источника (нам это не надо). Вот шаблон и готов. Но что такое шаблон без данных?

В сводной таблице существует несколько типов полей данных: поля колонок, поля строк, поля данных, поля страниц (в данной статье не рассматриваются).

Надо их разместить. Начнем с полей (колонок) таблицы. Тут стоит оговориться, что Excel имеет ограничения на количество полей на одном листе (255). Поскольку данные берутся из базы за период в три года, то количество полей будет существенно больше этого ограничения. Отсюда ясно, почему в запросе был выделен год и месяц. Наши данные будут группироваться сначала по году, затем - по месяцу, затем - по дате. Для того чтобы не возникло ошибки в связи в вышеуказанным ограничением будем прятать детализацию для каждого уровня группировки в цикле по всем полям детализации (кроме последнего, т.к. детализация по нему не предусмотрена):

with (PT.PivotFields('yy') as PivotField) do
begin
  Caption:='Год';
  Orientation:=xlColumnField;
  for i:=1 to PivotItems(emptyparam).Count do PivotItems(i).ShowDetail:=False;
end;

with (PT.PivotFields('mm') as PivotField) do
begin
  Caption:='Месяц';
  Orientation:=xlColumnField;
  for i:=1 to PivotItems(emptyparam).Count do PivotItems(i).ShowDetail:=False;
end;

with (PT.PivotFields('orderdate') as PivotField) do
begin
  Caption:='Дата';
  Orientation:=xlColumnField;
end;

Аналогично заполним строки. В них ограничения составляют 65535 записей на лист. По этой причине можно не сворачивать детализацию:

with (PT.PivotFields('salesperson') as PivotField) do
begin
  Caption:='Сотрудник';
  Orientation:=xlRowField;
end;

with (PT.PivotFields('country') as PivotField) do
begin
  Caption:='Страна';
  Orientation:=xlRowField;
end;

with (PT.PivotFields('city') as PivotField) do
begin
  Caption:='Город';
  Orientation:=xlRowField;
end;

with (PT.PivotFields('productname') as PivotField) do
begin
  Caption:='Товар';
  Orientation:=xlRowField;
end;

PT.AddDataField(PT.PivotFields('quantity'),'Кол-во',xlSum);

with PT.AddDataField(PT.PivotFields('extendedPrice'),'Продано на сумму',xlSum) do
begin
  //слегка отформатируем вывод суммы на экран
  if not XLS.UseSystemSeparators then
     NumberFormat:='#'+XLS.ThousandsSeparator+'##0'+XLS.DecimalSeparator+'00'
  else
     NumberFormat:='#'+ThousandSeparator+'##0'+DecimalSeparator+'00';
end;

PT.ManualUpdate:=True;

Вот, собственно, и все. Осталось нажать кнопочку F9, немного подождать и порадовать начальника новой формой отчета. Пусть сидит и забавляется. Стоит отметить, что данный отчет абсолютно независим от данных из БД, т.к. все, что вернул запрос, храниться в самой книге Excel. Отчет можно отправить по сети, по электронной почте или перенести любым доступным способом. Сворачивать/разворачивать детализацию по дате можно двойным кликом по данным колонки/строки (только не по серым кнопочкам с заголовками полей). Нажатие на заголовок поля приводит к появлению фильтра по данным выбранной колонки/строки. Ниже приведен код на C# (перевод с Delphi сделал Shabal, за что ему большое спасибо):

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.Globalization;
using Excel = Microsoft.Office.Interop.Excel;

namespace WinApp1
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      const string cmdSelect = 
        "select OrderDate, Year(OrderDate) as yy,\n" + 
        "Month(OrderDate) as mm, Country, City, ProductName,\n" +
        "SalesPerson, Quantity, ExtendedPrice from Invoices";

      Excel.PivotCache pivotCashe;
      Excel.PivotTable pivotTable;
      Excel.PivotField pivotField;
      Excel.Worksheet oSheet;
      Excel.Application xlApp = new Excel.Application();

      string dataSource = Application.StartupPath + @"\..\..\Northwind.mdb";

      button1.Enabled = false;
      label1.Visible = true;
      try
      {
        xlApp.Workbooks.Add(Type.Missing);
        xlApp.Visible = true;
        xlApp.Interactive = false;
        xlApp.EnableEvents = false;
        oSheet = (Excel.Worksheet)xlApp.ActiveSheet;
        oSheet.get_Range("A1", Type.Missing).Value2 = "Сводный отчет";
        oSheet.get_Range("A1", Type.Missing).Font.Size = 12;
        oSheet.get_Range("A1", Type.Missing).Font.Bold = true;
        oSheet.get_Range("A1", Type.Missing).Font.Italic = true;
        oSheet.get_Range("A1", Type.Missing).Font.Underline = true;

        // создаем запрос
        pivotCashe = ((Excel.PivotCaches)xlApp.ActiveWorkbook.PivotCaches()).
          Add(Excel.XlPivotTableSourceType.xlExternal, Type.Missing);
        pivotCashe.Connection = string.Format("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", dataSource);
        pivotCashe.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdSql;
        pivotCashe.CommandText = cmdSelect;
        
        // создаем сводную таблицу на основе запроса (пока без полей)
        pivotTable = pivotCashe.CreatePivotTable(oSheet.get_Range("A3", Type.Missing),
          "MyPivotTable1", Type.Missing, Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent);

        pivotTable.DisplayImmediateItems = false;
        pivotTable.EnableDrilldown = true;
        pivotTable.ManualUpdate = true;
        // настраиваем поля
        // поля колонок
        pivotField = (Excel.PivotField)pivotTable.PivotFields("yy");
        pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;
        pivotField.Caption = "Год";
        // сворачиваем данные по годам, чтобы влезли все данные
        for (int i = 1; i <= ((Excel.PivotItems)pivotField.PivotItems(Type.Missing)).Count; i++)
        {
          ((Excel.PivotItem)pivotField.PivotItems(i)).ShowDetail = false;
        }

        pivotField = (Excel.PivotField)pivotTable.PivotFields("mm");
        pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;
        // сворачиваем данные по месяцам, чтобы влезли все данные
        for (int i = 1; i <= ((Excel.PivotItems)pivotField.PivotItems(Type.Missing)).Count; i++)
        {
          ((Excel.PivotItem)pivotField.PivotItems(i)).ShowDetail = false;
        }
        pivotField.Caption = "Месяц";

        pivotField = (Excel.PivotField)pivotTable.PivotFields("OrderDate");
        pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;
        pivotField.Caption = "Дата заказа";

        // поля строк
        pivotField = (Excel.PivotField)pivotTable.PivotFields("SalesPerson");
        pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
        pivotField.Caption = "Продавец";

        pivotField = (Excel.PivotField)pivotTable.PivotFields("Country");
        pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
        pivotField.Caption = "Страна";

        pivotField = (Excel.PivotField)pivotTable.PivotFields("City");
        pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
        pivotField.Caption = "Город";

        pivotField = (Excel.PivotField)pivotTable.PivotFields("ProductName");
        pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
        pivotField.Caption = "Изделие";
        //
        // поля данных
        pivotField = pivotTable.AddDataField(pivotTable.PivotFields("Quantity"), "Кол-во",
          Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum);
        //pivotField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
        // возможна персональная настройка формата вывода данных (не забываем о "культуре")
        //
        pivotField = pivotTable.AddDataField(pivotTable.PivotFields("ExtendedPrice"), 
          "Сумма продаж", Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum); 
        // настроим "культуру" на англ., чтоб не зависить от локальных настроек
        int savedCult = Thread.CurrentThread.CurrentCulture.LCID;
        Thread.CurrentThread.CurrentCulture = new CultureInfo(0x0409, false);
        Thread.CurrentThread.CurrentUICulture = new CultureInfo(0x0409, false);
        try
        {
          // установим "американский" формат данных
          pivotField.NumberFormat = "#,##0.00"; 
          // возможно задать формат сразу всей области даных!
          //pivotTable.DataBodyRange.NumberFormat = "#,##0.00";
        }
        finally
        {
          // восстановим пользовательскую "культуру" для отображения всех данных в
          // привычных глазу форматах
          Thread.CurrentThread.CurrentCulture = new CultureInfo(savedCult, true);
          Thread.CurrentThread.CurrentUICulture = new CultureInfo(savedCult, true);
        }

        // убираем спиcок полей с экрана
        xlApp.ActiveWorkbook.ShowPivotTableFieldList = 
          !(pivotTable.Version == Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion10);
        // рассчитаем таблицу
        pivotTable.ManualUpdate = false;
        xlApp.ActiveWorkbook.Saved = true;
      }
      finally
      {
        // отсоединяемся от Excel'я
        pivotField = null;
        pivotTable = null;
        pivotCashe = null;
        oSheet = null;
        xlApp.Interactive = true;
        xlApp.ScreenUpdating = true;
        xlApp.UserControl = true;
        xlApp = null;
        button1.Enabled = true;
        label1.Visible = false;
      }
    }

    private void Form1_FormClosing(object sender, FormClosingEventArgs e)
    {
      e.Cancel = !button1.Enabled;
    }
  }
}

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

Проект создавался и тестировался на Delphi 7, BDS 2006 и Excel2003. Исходные тексты программы на Delphi, база данных и пример отчета находятся в архиве PivotTable.zip. Исходные тексты на C# (VS2005) и база данных находятся в архиве WinApp1.zip. Более детальную информацию можно получить из файла vbaxl9.chm для Microsoft Excel 2000 или vbaxl10.chm для Microsoft Excel 2002, или с сайтов

• http://exceltip.com/excel_tips/Excel_Pivot_Tables/32.html
• http://msdn2.microsoft.com/de-de/library/microsoft.office.interop.excel.pivottable.aspx
• http://msdn2.microsoft.com/de-de/library/microsoft.office.interop.excel.pivotcache.aspx
• http://msdn2.microsoft.com/de-de/library/microsoft.office.interop.excel.pivotfields.aspx

• Тестовый проект на Delphi (659 K) обновление от 11/3/2006 5:42:00 AM
• Тестовый проект на С# (500 K) обновление от 11/3/2006 5:42:00 AM