Иногда возникает потребность выгрузить данные из системы "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(); ПроцессЭксель = Неопределено;
Всё. Обработка выполнена, сводная таблица Эксель создана.
По вопросу приобретения обработки можно обратиться через форму обратной связи.