Hare.ru @ Коллективный разум / Hare.ru @ Дикое место

Архив hare.ru 
Мысли, конвертированные в текст

Собственные руки TM


Все статьи раздела

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

Алексей Комиссаров (май 2002)

Довольно часто предпринимаются попытки связать 1С:Предприятие и Excel с целью анализа бизнес-информации. В целом все они сводятся к нескольким направлениям:
  1. Прямое обращение Excel к таблицам базы данных 1С:Предприятия для MS SQL с помощью запросов и дальнейшая обработка полученной информации встроенными средствами VBA
  2. Выгрузка информации во внешний файл формата .xls или .dbf и его анализ заранее подготовленным шаблоном Excel
  3. Управление 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 Макет сводной таблицы

Рис.1 Макет сводной таблицы.


Рис.1 Сформированный отчёт

Рис.2 Сформированный отчёт.


В итоге мы получили отчёт, который позволяет посмотреть как общее состояние отгрузки автомобилей, так и отгрузку в разрезе разнорядок.

Построение диаграммы на основе полученных данных показано в руководстве "1С:Предприятие. Описание встроенного языка. Часть 2", стр.903.

Полученный файл может быть сохранен на жестком диске. Рассмотренный метод поможет избежать длительного построения сложных отчетов за прошедший период и позволит использовать все преимущества пакета MS Excel – такие, как построение графиков и диаграмм, встроенные методы прогнозирования и статистики, и другие.

Партнеры:


Также может быть интересно:
   
 Сайт поддерживается за счет партнеров:
:::... Сайт содержит архив двух версий hare.ru Карта сайта