Создание сводной таблицы в Excel

Довольно часто предпринимаются попытки связать 1С:Предприятие и Excel с целью анализа бизнес-информации. В целом все они сводятся к нескольким направлениям:
Прямое обращение Excel к таблицам базы данных 1С:Предприятия для MS SQL с помощью запросов и дальнейшая обработка полученной информации встроенными средствами VBA
Выгрузка информации во внешний файл формата .xls или .dbf и его анализ заранее подготовленным шаблоном Excel
Управление Excel непосредственно из 1С:Предприятия с помощью OLE AutomationБ.
Первые два метода достаточно известны в кругах V7-разработчиков для, третий же почему-то считается громоздким и сложным в отладке, и поэтому редко применяется на практике. На самом же деле ничего таинственного в этом методе нет.



Процедура Сформировать()
//Создадим объект Excel
РабочаяКнига = СоздатьОбъект("Excel.Application");
//Ограничим книгу Excel одним листом
РабочаяКнига.SheetsInNewWorkbook = 1;
//Установим заголовок книги
РабочаяКнига.Caption = "Отчет по договорам отгрузки";
//Создадим новую рабочую книгу
NewBook = РабочаяКнига.Workbooks.Add;

СписокСтолбцов = СоздатьОбъект("СписокЗначений");
СписокСтолбцов.ДобавитьЗначение("Модель");
СписокСтолбцов.ДобавитьЗначение("Разнорядка");
СписокСтолбцов.ДобавитьЗначение("По договору");
СписокСтолбцов.ДобавитьЗначение("Отгрузили");
СписокСтолбцов.ДобавитьЗначение("Осталось");

//Заполним наименование столбцов: Модель, Разнорядка, Количество
Для Столбец = 1 По СписокСтолбцов.РазмерСписка() Цикл
Ячейка = РабочаяКнига.Cells(1,Столбец);
Ячейка.Value = СписокСтолбцов.ПолучитьЗначение(Столбец);
КонецЦикла;

//Формируем запрос по регистру
//Создание объекта типа Запрос
Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса =
"//{{ЗАПРОС(Сформировать)
|Период с ДатаНач по ДатаКон;
|Разнорядка = Регистр.УчетДоговоров.Разнорядка;
|Модель = Регистр.УчетДоговоров.Модель.Группа;
|Колво = Регистр.УчетДоговоров.Количество;
|Функция КолвоПриход = Приход(Колво);
|Функция КолвоРасход = Расход(Колво);
|Функция КолвоОст = КонОст(Колво);
|Группировка Разнорядка;
|Группировка Модель;
|"//}}ЗАПРОС
;
// Если ошибка в запросе, то выход из процедуры
Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
Возврат;
КонецЕсли;

НомерСтроки = 1;
Пока Запрос.Группировка("Разнорядка") = 1 Цикл
Пока Запрос.Группировка("Модель") = 1 Цикл
Модель = Запрос.Модель;
Разнорядка = Запрос.Разнорядка;
КолПоДоговору = Запрос.КолвоПриход;
КолОтгрузили = Запрос.КолвоРасход;
КолОсталось = Запрос.КолвоОст;
НомерСтроки = НомерСтроки + 1;
//Организум заполнение ячеек Excel
Для Ст = 1 По СписокСтолбцов.РазмерСписка() Цикл
Ячейка = РабочаяКнига.Cells(НомерСтроки,Ст);
Если Ст = 1 Тогда
Ячейка.Value = Модель;
ИначеЕсли Ст = 2 Тогда
Ячейка.Value = Строка(Разнорядка);
ИначеЕсли Ст = 3 Тогда
Ячейка.Value = КолПоДоговору;
ИначеЕсли Ст = 4 Тогда
Ячейка.Value = КолОтгрузили;
ИначеЕсли Ст = 5 Тогда
Ячейка.Value = КолОсталось;
КонецЕсли;
КонецЦикла;
КонецЦикла;
КонецЦикла;

//Теперь создаем сводную таблицу на основе выгруженных данных
СводнаяТаблица = РабочаяКнига.ActiveSheet.PivotTableWizard;

//Разворачиваем макет сводной таблицы
СводнаяТаблица.SmallGrid = 0;

//Теперь разнесем ячейки сводной таблицы
СводнаяТаблица.PivotFields(1).Orientation = 1; //Модель
СводнаяТаблица.PivotFields(2).Orientation = 3; //Разнорядка
СводнаяТаблица.PivotFields(3).Orientation = 4; //По договору
СводнаяТаблица.PivotFields(4).Orientation = 4; //Отгрузили
СводнаяТаблица.PivotFields(5).Orientation = 4; //Осталось
// Где:
// 1 - Строка
// 2 - Столбец
// 3 - Страница
// 4 - Данные (см. Рис1)

//Теперь немного отредактируем полученную таблицу
СводнаяТаблица.Format(1); // (см. Рис.2)

//Закрываем панель инструментов сводной таблицы
РабочаяКнига.CommandBars("PivotTable").Visible = 0;

РабочаяКнига.Visible = 1;
РабочаяКнига = 0;

КонецПроцедуры //Сформировать