Как выгрузить номенклатуру из 1С:Управление торговлей 11.4 в Excel с учетом остатков, розничной цены и артикула?

При повседневной работе в 1С:Управление торговлей (УТ), особенно в версии 11.4, пользователи и администраторы часто сталкиваются с необходимостью выгрузки данных о номенклатуре. Однако стандартные отчеты не всегда позволяют достичь нужной гибкости: например, получить список товаров только с положительными остатками, вывести их артикулы и текущие розничные цены. В таких случаях требуется более специализированное решение, которое мы рассмотрим в этой статье.

Мы детально разберем процесс создания внешней обработки, которая поможет нам эффективно выгрузить необходимые данные в файл Excel. Мы вместе проанализируем, какие объекты и методы платформы 1С:Предприятие 8.3 нам понадобятся, как правильно составить запрос к базе данных и как программно управлять приложением Microsoft Excel для формирования отчета.

Постановка задачи: Выборочная выгрузка номенклатуры в Excel

Давайте сформулируем нашу цель (чтобы получить готовый прайс-лист по остаткам и доступности товаров): разработать механизм для выгрузки информации о номенклатуре из 1С:УТ 11.4 в Excel, соблюдая следующие условия:

1. Будет отображаться только та номенклатура, по которой зафиксирован положительный остаток на складах на текущую дату.
2. Для каждой позиции номенклатуры нам потребуется ее артикул (можно также вывести дополнительные колонки в справочнике номенклатуры, такие как штрихкод).
3. Необходимо получить актуальную розничную цену на дату выгрузки (также бывает полезно выполнить сравнение остатков и цен товаров между БП и УТ).
4. Результат должен быть представлен в виде таблицы Excel, готовой для дальнейшего анализа или использования (аналогично тому, как происходит загрузка прихода из файла xls).

Для решения этой задачи мы будем создавать собственную внешнюю обработку. Этот подход является универсальным и позволяет реализовать любую сложную логику без внесения изменений в типовую конфигурацию, что очень важно для сохранения возможности ее стандартного обновления.

Шаг 1: Создание основы внешней обработки

Для начала работы нам потребуется создать новую внешнюю обработку в режиме "Конфигуратор".

1. Откроем 1С:Предприятие в режиме "Конфигуратор".
2. В главном меню выберем "Файл" -> "Новый...".
3. В открывшемся окне "Новый" выберем тип объекта "Внешняя обработка" и нажмем "ОК".
4. Сохраним созданную обработку в удобное для нас место на диске, присвоив ей понятное имя, например, "ВыгрузкаНоменклатурыВЭксель.epf".

Далее нам необходимо создать форму для этой обработки, через которую пользователь сможет запускать процесс выгрузки.

1. В окне объекта "Внешняя обработка" перейдем на вкладку "Формы".
2. Нажмем кнопку "Добавить" и создадим новую форму обработки.
3. На форме разместим элемент управления "Кнопка" и дадим ей заголовок, например, "Выгрузить в Excel".
4. В свойствах этой кнопки создадим обработчик события "Нажатие". Именно в модуле этой формы, в процедуре, соответствующей нажатию кнопки, мы и будем писать весь основной код.

Этот начальный этап является фундаментом для нашей будущей функциональности.

Шаг 2: Инициализация параметров для запроса

Прежде чем приступить к построению запроса, нам нужно определить ключевые данные, которые будут служить критериями отбора информации. Эти параметры будут передаваться в запрос для динамического формирования выборки.

1. Дата среза: Мы будем получать остатки и цены на конкретную дату. В нашем случае это будет текущая дата на момент выполнения обработки. Для этого мы используем стандартную функцию ТекущаяДата().
2. Вид цены: Мы четко определили, что нам нужна "Розничная" цена. Нам необходимо найти соответствующий элемент справочника ВидыЦенНоменклатуры. Важно учесть сценарий, при котором вид цены с таким наименованием может отсутствовать в базе.

Рассмотрим, как эти параметры инициализируются в коде нашей процедуры:

Процедура ВыгрузитьВExcel(Команда)
    // 1. Инициализация параметров
    // Определяем дату, на которую будут получены остатки и цены.
    ДатаСреза = ТекущаяДата();

    // Находим ссылку на вид цены "Розничная" в справочнике "Виды цен номенклатуры".
    // Важно, чтобы такой вид цены существовал в базе данных.
    ВидЦены = Справочники.ВидыЦенНоменклатуры.НайтиПоНаименованию("Розничная");

    // Проверяем, удалось ли найти вид цены. Если нет, сообщаем пользователю
    // и прерываем выполнение обработки, так как дальнейшая работа без этого параметра невозможна.
    Если ВидЦены = Неопределено Тогда
        Сообщить("Критическая ошибка: Вид цены 'Розничная' не найден в справочнике 'Виды цен номенклатуры'. Пожалуйста, проверьте настройки.", СтатусСообщения.Важное);
        Возврат; // Выходим из процедуры
    КонецЕсли;

    // Здесь будет располагаться дальнейший код по формированию запроса и выгрузке в Excel.

КонецПроцедуры

Использование переменных и явная передача параметров в запрос значительно улучшает читаемость кода, упрощает его отладку и повышает безопасность, предотвращая инъекции и улучшая производительность за счет кэширования планов запросов на стороне СУБД.

Шаг 3: Разработка запроса для получения данных

Для того чтобы собрать все необходимые данные — номенклатуру, ее артикул, актуальный остаток и розничную цену — нам потребуется сформировать запрос к базе данных 1С. Мы будем использовать объект Запрос и объединять информацию из нескольких ключевых регистров и справочников.

Выбор источников данных:

1. Регистр накопления ТоварыНаСкладах: Этот регистр является основным источником информации об остатках товаров. Мы будем работать с его виртуальной таблицей Остатки, которая позволяет получить актуальные остатки на заданную дату с учетом различных измерений. Важно задать условие, что ВНаличииОстаток > 0, чтобы исключить номенклатуру без остатков.
2. Регистр сведений ЦеныНоменклатуры: В этом регистре хранятся сведения о ценах номенклатуры. Мы будем использовать его виртуальную таблицу СрезПоследних для получения самых актуальных цен на указанную дату среза и по определенному виду цены.
3. Справочник Номенклатура: Артикул является реквизитом элемента справочника "Номенклатура". Мы получим его, обратившись к свойству Артикул через ссылку на номенклатуру, полученную из регистра остатков.

Построение запроса:

Мы используем оператор ЛЕВОЕ СОЕДИНЕНИЕ для связи регистра остатков с регистром цен. Это позволит нам получить всю номенклатуру, по которой есть остаток, даже если для какой-то позиции цена не была установлена (в этом случае поле цены будет иметь значение NULL или Неопределено).

Давайте рассмотрим полный текст запроса и его выполнение:

// 2. Формирование и выполнение запроса
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
|    ТоварыНаСкладахОстатки.Номенклатура.Артикул КАК Артикул,
|    ТоварыНаСкладахОстатки.ВНаличииОстаток КАК Остаток,
|    ЦеныНоменклатурыСрезПоследних.Цена КАК ЦенаРозничная
|ИЗ
|    РегистрНакопления.ТоварыНаСкладах.Остатки(
|        &ДатаСреза,
|        ВНаличииОстаток > 0 // Условие отбора по положительному остатку
|    ) КАК ТоварыНаСкладахОстатки
|        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
|            &ДатаСреза,
|            ВидЦены = &ВидЦены // Условие отбора по виду цены
|        ) КАК ЦеныНоменклатурыСрезПоследних
|        ПО ТоварыНаСкладахОстатки.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
|ГДЕ
|    ТоварыНаСкладахОстатки.ВНаличииОстаток > 0"; // Дополнительное условие ГДЕ для случаев, когда виртуальная таблица не поддерживает прямое условие или для явности

// Устанавливаем ранее определенные параметры запроса.
// Это важно для безопасности и производительности запроса.
Запрос.УстановитьПараметр("ДатаСреза", ДатаСреза);
Запрос.УстановитьПараметр("ВидЦены", ВидЦены);

// Выполняем запрос к базе данных и получаем результат в виде объекта "Выборка".
Выборка = Запрос.Выполнить().Выбрать();

В секции ВЫБРАТЬ мы используем псевдонимы (например, КАК Артикул), чтобы присвоить более читаемые имена полям результата запроса. Это упрощает дальнейшую работу с выборкой.

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

Шаг 4: Выгрузка данных в Excel с использованием COM-объекта

После успешного выполнения запроса и получения выборки данных, следующим критически важным этапом является перенос этих данных в файл Excel. Мы будем использовать COMОбъект("Excel.Application") — мощный инструмент, который предоставляет полный контроль над приложением Microsoft Excel непосредственно из кода 1С.

Пошаговый алгоритм работы с Excel через COM-объект:

1. Создание экземпляра Excel: Мы инициализируем новый объект приложения Excel.
2. Видимость приложения: Для удобства отладки и визуального контроля мы можем сделать окно Excel видимым. В рабочем сценарии это может быть опционально.
3. Создание рабочей книги и листа: Добавляем новую рабочую книгу и получаем ссылку на первый лист, куда будем записывать данные.
4. Запись заголовков: В первой строке листа записываем наименования столбцов (заголовки).
5. Итерация и запись данных: В цикле обходим полученную выборку из запроса и построчно записываем значения полей в соответствующие ячейки листа Excel, начиная со второй строки.
6. Форматирование: При необходимости, мы можем применить базовое форматирование, например, автоматическую подгонку ширины столбцов.
7. Сохранение файла: Сохраняем готовую рабочую книгу в файл на диске. Важно предусмотреть сохранение во временный файл или предоставить пользователю выбор пути.
8. Обработка ошибок и завершение работы: Обеспечиваем корректное завершение процесса Excel и обрабатываем возможные исключения, которые могут возникнуть в процессе работы с COM-объектом.

Рассмотрим реализацию этого алгоритма в коде:

    // 3. Выгрузка результатов запроса в Excel
    Попытка
        // Создаем новый COM-объект для взаимодействия с Microsoft Excel.
        // Для успешной работы на компьютере должен быть установлен Excel.
        Excel = Новый COMОбъект("Excel.Application");
        Excel.Visible = Истина; // Делаем окно Excel видимым для пользователя. Можно установить в Ложь для фоновой работы.

        // Добавляем новую рабочую книгу в Excel.
        Книга = Excel.Workbooks.Add();
        // Получаем ссылку на первый лист новой книги.
        Лист = Книга.Sheets(1);

        // Записываем заголовки столбцов в первую строку листа Excel.
        Лист.Cells(1, 1).Value = "Номенклатура";
        Лист.Cells(1, 2).Value = "Артикул";
        Лист.Cells(1, 3).Value = "Остаток";
        Лист.Cells(1, 4).Value = "Цена Розничная";

        // Инициализируем номер строки, с которой будут записываться данные (после заголовков).
        СтрокаНачалаДанных = 2;

        // Обходим каждую запись в выборке, полученной из запроса.
        Пока Выборка.Следующий() Цикл
            // Записываем данные из выборки в соответствующие ячейки Excel.
            Лист.Cells(СтрокаНачалаДанных, 1).Value = Выборка.Номенклатура.Наименование; // Наименование номенклатуры
            Лист.Cells(СтрокаНачалаДанных, 2).Value = Выборка.Артикул;                  // Артикул
            Лист.Cells(СтрокаНачалаДанных, 3).Value = Выборка.Остаток;                  // Остаток
            Лист.Cells(СтрокаНачалаДанных, 4).Value = Выборка.ЦенаРозничная;            // Розничная цена

            // Переходим к следующей строке для записи следующей записи.
            СтрокаНачалаДанных = СтрокаНачалаДанных + 1;
        КонецЦикла;

        // Автоматически подгоняем ширину столбцов A по D для лучшей читаемости данных.
        Лист.Columns("A:D").AutoFit();

        // Формируем имя временного файла для сохранения выгруженных данных.
        // В реальных условиях рекомендуется предоставить пользователю выбор пути и имени файла.
        ИмяФайлаВыгрузки = ПолучитьИмяВременногоФайла(".xlsx");
        // Сохраняем активную рабочую книгу по указанному пути.
        Excel.ActiveWorkbook.SaveAs(ИмяФайлаВыгрузки);

        // Сообщаем пользователю об успешном завершении операции и местоположении файла.
        Сообщить("Данные о номенклатуре успешно выгружены в Excel. Файл доступен по пути: " + ИмяФайлаВыгрузки);

    Исключение
        // Обработка любых ошибок, возникших в процессе работы с Excel.
        Сообщить("Произошла ошибка при выгрузке данных в Excel: " + ОписаниеОшибки(), СтатусСообщения.Важное);
        // Важно закрыть процесс Excel, если он был открыт, чтобы избежать зависания.
        Если Excel <> Неопределено Тогда
            Excel.Quit();
        КонецЕсли;
    КонецПопытуры;
КонецПроцедуры

Ключевые моменты при работе с COMОбъект("Excel.Application"):

1. Обработка исключений: Использование блока Попытка...Исключение является обязательным. Работа с внешними COM-объектами может быть нестабильной, если на компьютере не установлен Excel, или если возникнут проблемы с доступом. Блок Попытка позволяет корректно перехватывать ошибки и информировать пользователя.

2. Завершение работы с Excel: После окончания всех операций обязательно вызывайте метод Excel.Quit(). Если этого не сделать, процесс Excel может остаться активным в фоновом режиме, что приведет к нежелательному потреблению системных ресурсов и потенциальным проблемам при повторных запусках.

3. Путь сохранения файла: В примере используется ПолучитьИмяВременногоФайла(".xlsx") для упрощения. В реальной эксплуатации мы рекомендуем использовать диалог выбора файла (например, ДиалогВыбораФайла с режимом Сохранение), чтобы пользователь мог сам указать, куда сохранить отчет.

4. Производительность: При выгрузке очень больших объемов данных (десятки тысяч строк) запись ячеек по одной может быть медленной. В таких случаях можно рассмотреть выгрузку всего массива данных в Excel за одну операцию (например, через Range.Value = ТаблицаЗначений.Выгрузить()), что значительно ускорит процесс.

Альтернативные подходы к выгрузке данных в Excel

Хотя использование COMОбъект("Excel.Application") является одним из наиболее мощных и гибких способов, существуют и другие методы выгрузки данных из 1С в Excel, каждый из которых имеет свои преимущества и недостатки.

1. Выгрузка через объект ТабличныйДокумент

   Платформа 1С:Предприятие имеет встроенный объект ТабличныйДокумент, который является универсальным инструментом для формирования различных печатных форм и отчетов. Мы можем сначала сформировать табличный документ в 1С, а затем сохранить его в формате Excel.

   Преимущества:

   • Не требует установленного Microsoft Office на клиентской машине. 1С сама генерирует файл, совместимый с Excel.
   • Проще в использовании для базовых табличных отчетов.
   • Высокая производительность для стандартных задач.

   Недостатки:

   • Меньшая гибкость в форматировании и работе со сложными элементами Excel (графики, формулы, макросы).
   • Не позволяет напрямую управлять поведением приложения Excel.

   Пример сохранения ТабличногоДокумента:

   Скопировать код

   
   ТабДок = Новый ТабличныйДокумент;
   // ... Код для заполнения ТабДок данными и заголовками,
   // например, с использованием метода "Вывести" из макета
   // или программного заполнения ячеек ТабДок.Область(Строка, Колонка).Текст = Значение;
   
   ИмяФайлаВыгрузки = "C:\МоиДокументы\ОтчетПоНоменклатуре.xlsx"; // Указываем путь и имя файла
   ТабДок.Записать(ИмяФайлаВыгрузки, ТипФайлаТабличногоДокумента.XLSX);
   Сообщить("Отчет сохранен в файл: " + ИмяФайлаВыгрузки);
   

2. Использование внешних библиотек (OpenXML SDK, OLE DB)

   Для очень сложных и высокопроизводительных задач, особенно при серверной выгрузке, когда нет возможности использовать COM-объект Excel на сервере, можно рассмотреть сторонние решения:

   • OpenXML SDK: Это библиотека от Microsoft для программной работы с файлами Office Open XML (форматы .docx, .xlsx, .pptx). Она позволяет создавать и модифицировать файлы Excel на низком уровне без использования установленного Excel. Требует установки .NET Framework и соответствующих библиотек на сервер или клиентскую машину.
   • OLE DB: Можно использовать OLE DB провайдер для Excel для чтения и записи данных в файл Excel, обрабатывая его как базу данных. Это подходит для простых табличных операций, но имеет свои ограничения.

   Преимущества:

   • Высокая производительность для больших объемов данных.
   • Независимость от установленного Microsoft Office.
   • Возможность работы на серверной стороне (для OpenXML SDK).

   Недостатки:

   • Значительно более сложная реализация.
   • Требует установки дополнительных компонентов и глубоких знаний программирования.
   • Меньшая распространенность и поддержка в сообществе 1С для типовых задач.

   Эти методы обычно применяются в случаях, когда стандартные возможности 1С и COM-объекты не могут удовлетворить специфические требования по производительности или интеграции.

3. Доработка типовых отчетов и универсальные выгрузки

   В некоторых ситуациях можно попытаться доработать существующие типовые отчеты в конфигурации 1С, добавляя в них нужные поля и параметры отбора. Также существуют универсальные обработки для обмена данными, например, "Универсальный обмен данными XML". Однако они обычно ограничены по функциональности, когда речь заходит о специфическом форматировании или комбинировании данных из разных источников с сложными условиями.

Заключение

Мы успешно разобрали, как создать функциональную внешнюю обработку в 1С:Управление торговлей 11.4 для выгрузки номенклатуры в Excel, учитывая такие важные параметры, как остатки, артикул и розничная цена. Мы подробно рассмотрели этапы инициализации параметров, формирования запроса к регистрам и, что особенно важно, программную работу с Microsoft Excel через COM-объект.

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

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