Что тут остается делать. Варианта только два: либо пытаться создавать что-то свое, либо увольняться. Альтернативное решение проблемы предоставлено фирмой 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#

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/ru-ru/library/microsoft.office.interop.excel.pivottable.aspx
• http://msdn2.microsoft.com/ru-ru/library/microsoft.office.interop.excel.pivotcache.aspx
• http://msdn2.microsoft.com/ru-ru/library/microsoft.office.interop.excel.pivotfields.aspx

• Тестовый проект на Delphi (659 K)
• Тестовый проект на С# (500 K)