В списке обсуждаемых тем на Круглом столе Королевства Delphi
часто возникает вопрос о построении сводных таблиц. Сводная таблица
представляет собой очень удобный инструмент для отображения и анализа
данных, возвращаемых запросом к базе данных. Можно, конечно, для этой
цели использовать различные пакеты для построения отчетов (вроде
FastReport). Но с генераторами отчетов возникает масса вопросов
(отсутствие каких либо библиотек, проблемы с экспортом, отсутствие
необходимой документации и т.д.). А начальник требует выдать ему отчет
приблизительно такого вида: чтобы были видны все продажи, по всем
сотрудникам, по всем регионам, по всем товарам за указанный период
времени (скажем, за два года), но денег на покупку генератора отчетов
не дает. А как бы было хорошо выдать что-нибудь типа вот такой формы:
Что тут остается делать. Варианта только два: либо
пытаться создавать что-то свое, либо увольняться. Альтернативное
решение проблемы предоставлено фирмой Microsoft уже очень давно.
Называется оно PivotTable (Сводная таблица) и доступно в меню "Данные"
приложения Excel. Осталось только научиться пользоваться этой
возможностью. Для этого нам понадобиться:
- Delphi 7 (проект создан именно в этой версии);
- Установленный M$ Excel;
- Учебная база M$ Access Norhwind.mdb (прилагается в архиве);
- Немного свободного времени;
- Много желания понять как это делается.
Итак, начинаем. Существует два типа связи с Excel -
раннее и позднее. Об их отличиях речь неоднократно шла на Королевстве.
Будем использовать раннее связывание, т.к. при позднем компьютер
впадает в состояние комы. О том как подключиться к Excel и добавить
книгу подробно описано в материалах Королевства. Объявим следующие
переменные:
WB:_WorkBook;//рабочая книга
WS:_WorkSheet;//лист Excel куда помещается сводная таблица
PC:PivotCache;//кеш для данных сводной таблицы
PT:PivotTable;//собственно сама сводная таблица
i:byte;
Отключим реакцию Excel на события (для ускорения работы):
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;
Ну и наконец, вернем к жизни сам Excel.
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, или с сайтов:
К материалу прилагаются файлы: