Создание сводного отчета в Excel
Владимир Федченко,
В списке обсуждаемых тем на Круглом столе Королевства 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, или с сайтов:
- 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)