Собственные руки TM
Все статьи раздела
Создание сводной таблицы в Excel средствами V7.
Алексей Комиссаров (май 2002)
Довольно часто предпринимаются попытки связать 1С:Предприятие и Excel с
целью анализа бизнес-информации. В целом все они сводятся к нескольким направлениям:
- Прямое обращение Excel к таблицам базы данных 1С:Предприятия для MS SQL
с помощью запросов и дальнейшая обработка полученной информации встроенными средствами VBA
- Выгрузка информации во внешний файл формата .xls или .dbf и его анализ
заранее подготовленным шаблоном Excel
- Управление Excel непосредственно из 1С:Предприятия с помощью OLE AutomationБ.
Первые два метода достаточно известны в кругах V7-разработчиков для, третий же
почему-то считается громоздким и сложным в отладке, и поэтому редко применяется на
практике. На самом же деле ничего таинственного в этом методе нет.
Рассмотрим использование OLE Automation на примере.
Допустим, имеется регистр остатков "УчетДоговоров", который состоит из следующих
измерений:
- Модель (тип Справочник)
- Разнорядка (тип Документ).
Регистр имеет один ресурс Количество (типа Число).
Необходимо построить отчет по состоянию отгрузки моделей автомобилей в
разрезе разнорядок.
Код будет выглядеть следующим образом:
Процедура Сформировать()
//Создадим объект 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;
КонецПроцедуры //Сформировать
|
Иллюстрации:
Рис.1 Макет сводной таблицы.
Рис.2 Сформированный отчёт.
В итоге мы получили отчёт, который позволяет посмотреть как общее состояние отгрузки
автомобилей, так и отгрузку в разрезе разнорядок.
Построение диаграммы на основе полученных данных показано в руководстве
"1С:Предприятие. Описание встроенного языка. Часть 2", стр.903.
Полученный файл может быть сохранен на жестком диске. Рассмотренный метод поможет
избежать длительного построения сложных отчетов за прошедший период и позволит
использовать все преимущества пакета MS Excel такие, как построение графиков
и диаграмм, встроенные методы прогнозирования и статистики, и другие. |
Партнеры:
Также может быть интересно:
Канал Россия 1 на http://spbtvonline.ru/
|