Иногда возникает потребность выгрузить данные из системы "1С Предприятие" в "Майкрософт Эксель" и на основе этих данных построить сводную таблицу.

Выполним эту задачу – создадим обработку, которая будет выгружать данные о выручке и себестоимости продаж из программы "1С Управление торговлей 11.2". Весь процесс создания я описывать не буду, а остановлюсь на взаимодействии с "Майкрософт Эксель".

Создаём рабочий процесс Эксель.

Попытка	
	ПроцессЭксель = Новый COMОбъект("Excel.Application");
Исключение
	Сообщить("Не удалось создать процесс Excel");
ВызватьИсключение;
КонецПопытки;

Затем создаём новую рабочую книгу Эксель и объявляем переменную Лист. Эта переменная – рабочий лист книги Эксель.

Книга = ПроцессЭксель.Workbooks.Add();
Лист = Книга.WorkSheets(1);

Теперь подготавливаем заголовки для столбцов листа Эксель.

СписокСтолбцов = Новый СписокЗначений;

СписокСтолбцов.Добавить("Менеджер", "Менеджер");
СписокСтолбцов.Добавить("Контрагент", "Контрагент");
СписокСтолбцов.Добавить("Договор", "Договор");
СписокСтолбцов.Добавить("Заказ", "Заказ");
СписокСтолбцов.Добавить("Валюта", "Валюта");
СписокСтолбцов.Добавить("Номенклатура", "Номенклатура");
СписокСтолбцов.Добавить("Артикул", "Артикул");
СписокСтолбцов.Добавить("ПериодДень", "Период День");
СписокСтолбцов.Добавить("ПериодМесяц", "Период Месяц");
СписокСтолбцов.Добавить("ПериодКвартал", "Период Квартал");
СписокСтолбцов.Добавить("ПериодГод", "Период Год");
СписокСтолбцов.Добавить("Количество", "Количество");
СписокСтолбцов.Добавить("Выручка", "Выручка");
СписокСтолбцов.Добавить("Себестоимость", "Себестоимость");
СписокСтолбцов.Добавить("ДопРасходы", "Доп. расходы");
СписокСтолбцов.Добавить("Валовая прибыль", "Валовая прибыль");

Заполняем названия столбцов на листе Эксель.

// Счётчик для заполнения наименований столбцов
Количество = 1;
	
Для Каждого Столбец Из СписокСтолбцов Цикл
Ячейка = Лист.Cells(1,Количество);
Ячейка.Value = Столбец.Представление;
Количество = Количество + 1;
КонецЦикла;

Далее мы заполняем лист Эксель данными - построчно, в цикле. Источник данных – выборка из результата запроса. В данной обработке используется модифицированный запрос из встроенного отчёта "Выручка и себестоимость продаж". Заполняем лист со второй строки, так как в первой у нас наименования столбцов.

СтрокаТаблицы = 2;
	
Пока Выборка.Следующий() Цикл
		
	Лист.Cells(СтрокаТаблицы,1).Value = Строка(Выборка.Менеджер);
	Лист.Cells(СтрокаТаблицы,3).Value = Строка(Выборка.Контрагент);
	Лист.Cells(СтрокаТаблицы,4).Value = Строка(Выборка.Договор);
	Лист.Cells(СтрокаТаблицы,5).Value = Строка(Выборка.ЗаказКлиента);
	Лист.Cells(СтрокаТаблицы,6).Value = Строка(Выборка.Валюта);
	Лист.Cells(СтрокаТаблицы,7).Value = Строка(Выборка.Номенклатура);
	Лист.Cells(СтрокаТаблицы,8).Value = Строка(Выборка.Артикул);
	Лист.Cells(СтрокаТаблицы,9).NumberFormat = "ДД.ММ.ГГГГ";
	Лист.Cells(СтрокаТаблицы,9).Value = Выборка.ПериодДень;
	Лист.Cells(СтрокаТаблицы,10).Value = Выборка.ПериодМесяц;
	Лист.Cells(СтрокаТаблицы,10).NumberFormat = "ММ.ГГГГ";
	ПериодКвартал = Формат(Выборка.ПериодКвартал, "ДФ='к ""кв."" гггг'");
	Лист.Cells(СтрокаТаблицы,11).Value = ПериодКвартал;
	Лист.Cells(СтрокаТаблицы,12).Value = Выборка.ПериодГод;
	Лист.Cells(СтрокаТаблицы,12).NumberFormat = "ГГГГ";
	Лист.Cells(СтрокаТаблицы,13).Value = Число(Выборка.Количество);
	Лист.Cells(СтрокаТаблицы,14).Value = Число(Выборка.Выручка);
	Лист.Cells(СтрокаТаблицы,15).Value = Число(Выборка.Себестоимость);
	Лист.Cells(СтрокаТаблицы,16).Value = Число(Выборка.ДопРасходы);
	Лист.Cells(СтрокаТаблицы,17).Value = Число(Выборка.ВаловаяПрибыль);
		
	СтрокаТаблицы = СтрокаТаблицы + 1;
				
КонецЦикла;

Как видно из кода выше – для некоторых полей таблицы мы используем форматирование. Нужно обращать внимание на то, чтобы назначение форматирование происходило после того, как в ячейку таблицы помещены данные. В противном случае форматирование может не примениться.

После того, как мы заполнили лист Эксель данными – создаём из него кэш для сводной таблицы.

Кэш = Книга.PivotCaches().Add(1, Книга.ActiveSheet.UsedRange);

А теперь на основе этого кэша формируем сводную таблицу.

СводнаяТаблица = Кэш.CreatePivotTable("", "Выручка_И_Продажи");

Развернём макет сводной таблицы для настройки:

СводнаяТаблица.SmallGrid = 0;

Теперь настроим сводную таблицу. Укажем, в каких областях должны находиться поля сводной таблицы.

Цифровые коды для настройки:

1 - Строка
2 - Столбец
3 - Фильтр
4 - Данные

СводнаяТаблица.PivotFields(1).Orientation = 1; //Менеджер 
СводнаяТаблица.PivotFields(3).Orientation = 1; //Контрагент
СводнаяТаблица.PivotFields(5).Orientation = 1; //ЗаказКлиента 
СводнаяТаблица.PivotFields(14).Orientation = 4; //Выручка
СводнаяТаблица.PivotFields(15).Orientation = 4; //Себестоимость
СводнаяТаблица.PivotFields(16).Orientation = 4; //ДопРасходы
СводнаяТаблица.PivotFields(17).Orientation = 4; //ВаловаяПрибыль

Изменить настройку вывода выбранных полей можно и вручную. Также в ручную можно отобразить другие доступные поля.

В сводной таблице непосредственно из 1С можно создавать вычисляемые поля.

Создадим вычисляемое поле "Рентабельность":

ИмяПоляРентабельность = "Рентабельность";
ФормулаПоляРентабельность = "=ОКРУГЛ(ЕСЛИ(Выручка<0,01;ЕСЛИ(Валовая прибыль<0;-100;ЕСЛИ(Валовая прибыль<0,01;0;100));Валовая прибыль/Выручка*100); 2)";
ВычисляемоеПолеРентабельность = СводнаяТаблица.CalculatedFields().Add(ИмяПоляРентабельность, ФормулаПоляРентабельность);
Лист.Columns(18).Value = ВычисляемоеПолеРентабельность;
СводнаяТаблица.PivotFields(18).Orientation = 4; //Рентабельность

Отформатируем сводную таблицу.

СводнаяТаблица.Format(1);

Сохраняем и закрываем книгу.

// Путь сохраняемого файла в зависимости от версии Windows
Если СтрНайти(ДанныеОС[0].Caption, "XP") > 0 Тогда
	ИмяФайлаЭксель = "C:\Documents and Settings\" + ДанныеСеанса.UserName + "\Мои документы\Export_" + Формат(ТекущаяДата(), "ДФ=ггггММддЧЧммсс") + ".xlsx";
Иначе
	ИмяФайлаЭксель = "C:\Users\" + ДанныеСеанса.UserName + "\Мои документы\Export_" + Формат(ТекущаяДата(), "ДФ=ггггММддЧЧммсс") + ".xlsx";
КонецЕсли;

Попытка
	Книга.SaveAs(ИмяФайлаЭксель, 51);
	Книга.Close();
Исключение
	Сообщить("Не удалось записать файл");
КонецПопытки;

Цифровой код 51, указанный в методе SaveAs означает, что файл должен быть сохранён в формате "xlsx".

Завершаем рабочий процесс Эксель:

ПроцессЭксель.Quit();
ПроцессЭксель = Неопределено;

Всё. Обработка выполнена, сводная таблица Эксель создана.

По вопросу приобретения обработки можно обратиться через форму обратной связи.

Комментарии

  • pavlo 18.09.2018 13:59
    У меня по второму варианту не получилось. При такой настройке появилась колонка построчно со словами названия колонки ресурса.

    Подробнее...

     
  • Altavista- 17.09.2018 20:51
    Не бесспорное решение: 1. В регистр логичнее добавить 2й ресурс ("вТрансфертныхЦенах"); 2. РС "Наценки"? Однозначно, нет. Это реквизит справочника; 3. Реквизита "Контрагент" в условии задачи тоже нет и ...

    Подробнее...

     
  • Андрей 02.08.2018 09:55
    Большое спасибо! По "Варианту 2": в выбранных полях группировки колонок (у меня их несколько) кроме поля самой колонки пришлось добавить и ресурс.

    Подробнее...