При работе с системой 1С многие из нас сталкиваются с рутинной задачей загрузки данных из файлов Excel, особенно это касается универсальных передаточных документов (УПД), которые могут содержать огромное количество колонок и нестандартные форматы данных. Это часто приводит к значительным временным затратам на ручное сопоставление данных и их перенос. В данной статье мы рассмотрим различные подходы и методы, которые помогут нам автоматизировать и значительно упростить этот процесс, делая его более дружелюбным для конечного пользователя, даже если мы работаем с нестандартной ("нетленкой") конфигурацией 1С.
Прежде всего, давайте обсудим, как мы можем считывать данные из файлов Excel в 1С. Исторически часто использовался объект COMОбъект("Excel.Application"). Этот метод позволяет нам взаимодействовать с установленным на сервере или клиентской машине приложением Microsoft Excel, открывать файлы, читать ячейки и диапазоны. Однако у этого подхода есть существенные недостатки:
Excel.Application работает только на операционных системах Windows, где установлен сам Microsoft Excel. Это делает решение неприменимым для серверов под управлением Linux, MacOS, а также для веб-клиентов.Современным и рекомендуемым подходом является использование встроенного объекта 1С ТабличныйДокумент. Этот объект позволяет нам считывать данные напрямую из файлов Excel (поддерживаются форматы XLS, XLSX, ODS, а также HTML) без необходимости установки Microsoft Excel или использования COM-объектов. Преимущества ТабличныйДокумент очевидны:
Для работы с ТабличныйДокумент мы должны указать путь к файлу и использовать метод Прочитать(). Затем мы можем получать значения ячеек, обращаясь к ним по адресу или через объекты Область.
Рассмотрим пример того, как мы можем считать данные из файла Excel, используя ТабличныйДокумент:
// Пример чтения данных из Excel с использованием ТабличныйДокумент
Функция ПрочитатьДанныеИзExcel(ПутьКФайлу)
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу);
Исключение
Сообщить("Ошибка при чтении файла Excel: " + ОписаниеОшибки());
Возврат Неопределено;
КонецПопытки;
ДанныеТаблицы = Новый ТаблицаЗначений;
// Предположим, что первая строка - это заголовки
// и данные начинаются со второй строки
НомерСтрокиЗаголовков = 1;
НомерСтрокиДанныхНачало = 2;
// Определяем количество колонок по заголовкам
КоличествоКолонок = 0;
Пока Истина Цикл
ТекстЯчейки = СокрЛП(ТабДок.Область(НомерСтрокиЗаголовков, КоличествоКолонок + 1).Текст);
Если ПустаяСтрока(ТекстЯчейки) Тогда
Прервать;
Иначе
ДанныеТаблицы.Колонки.Добавить(ТекстЯчейки);
КоличествоКолонок = КоличествоКолонок + 1;
КонецЕсли;
КонецЦикла;
// Считываем данные
Для НомерСтроки = НомерСтрокиДанныхНачало По ТабДок.ВысотаТаблицы Цикл
НоваяСтрока = ДанныеТаблицы.Добавить();
Для НомерКолонки = 1 По КоличествоКолонок Цикл
ЗначениеЯчейки = ТабДок.Область(НомерСтроки, НомерКолонки).Значение;
Если ЗначениеЯчейки = Неопределено Тогда
ЗначениеЯчейки = ""; // Или другое значение по умолчанию
КонецЕсли;
НоваяСтрока[НомерКолонки - 1] = ЗначениеЯчейки; // Индекс колонки в ТЗ начинается с 0
КонецЦикла;
КонецЦикла;
Возврат ДанныеТаблицы;
КонецФункции
// Пример вызова
// Данные = ПрочитатьДанныеИзExcel("C:\МоиДокументы\Накладная.xlsx");
// Если Данные <> Неопределено Тогда
// // Далее обрабатываем полученную таблицу значений
// КонецЕсли;
Одна из основных проблем при загрузке данных из внешних файлов — это постоянно меняющийся порядок и названия колонок. Пользователь не хочет каждый раз вручную указывать, что "Номенклатура" находится в колонке B, а "Количество" в колонке D. Нам нужно реализовать механизм автоматического поиска колонок по их наименованию в шапке таблицы Excel.
Суть подхода заключается в том, что мы заранее определяем список ожидаемых нами наименований колонок (например, "Артикул", "Наименование", "Количество", "Цена", "Сумма") и их возможные синонимы. Затем, при чтении файла Excel, мы ищем эти наименования в строке заголовков. Если находим совпадение, то запоминаем номер соответствующей колонки.
Рассмотрим пример функции, которая ищет номера колонок по их названиям в шапке Excel-файла. В исходном сообщении приводится код, использующий Excel.Application. Мы представим его здесь, а затем объясним, как логику можно адаптировать для ТабличныйДокумент.
// Пример использования функции для поиска номеров колонок (оригинал из ветки, использует Excel.Application)
// Здесь Worksheet, WorkBook, Excel - это COM-объекты Excel.Application
// СтруктураНазванийПеременныхИНазванийКолонок - соответствие (ИмяПеременной1: "НазваниеКолонкиВExcel1", ...)
// СтруктураПризнакОбязательностиКолонок - соответствие (ИмяПеременной1: Истина/Ложь, ...)
// НомерСтрокиШапкиТаблицы - номер строки, где находятся заголовки
// НомерПоследнейКолонкиШапкиТаблицы - последний номер колонки для поиска
СтруктураНазванийПеременныхИНазванийКолонок = Новый Структура;
СтруктураПризнакОбязательностиКолонок = Новый Структура;
СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиPickupDateTime", "Дата");
СтруктураПризнакОбязательностиКолонок.Вставить("НомерКолонкиPickupDateTime", Истина);
СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиCMN", "cm");
СтруктураПризнакОбязательностиКолонок.Вставить("НомерКолонкиCMN", Истина);
СтруктураНазванийПеременныхИНазванийКолонок.Вставить("НомерКолонкиRUR", "Стоимость");
СтруктураПризнакОбязательностиКолонок.Вставить("НомерКолонкиRUR", Истина);
// Вызов функции (предполагая, что Worksheet уже получен из Excel.Application)
// СтруктураЗначенийПеременных = НайтиНомераКолонок(Worksheet, СтруктураНазванийПеременныхИНазванийКолонок, СтруктураПризнакОбязательностиКолонок, НомерСтрокиШапкиТаблицы, НомерПоследнейКолонкиШапкиТаблицы);
// Если СтруктураЗначенийПеременных = Неопределено Тогда
// ЗакрытьФайл(WorkBook, Excel); // Предполагаемая функция закрытия
// Возврат;
// КонецЕсли;
// НомерКолонкиPickupDateTime = СтруктураЗначенийПеременных.НомерКолонкиPickupDateTime;
// НомерКолонкиCMN = СтруктураЗначенийПеременных.НомерКолонкиCMN;
// НомерКолонкиRUR = СтруктураЗначенийПеременных.НомерКолонкиRUR;
// Сама функция НайтиНомераКолонок:
// (Важно: эта функция использует COM-объекты Excel.Application)
Функция НайтиНомераКолонок(Worksheet, СтруктураНазванийПеременныхИНазванийКолонок, СтруктураПризнакОбязательностиКолонок, НомерСтрокиШапкиТаблицы, НомерПоследнейКолонкиШапкиТаблицы)
СтруктураЗначенийПеременных = Новый Структура;
Для Счетчик = 1 По НомерПоследнейКолонкиШапкиТаблицы Цикл
НазвКолонки = СокрЛП(Строка(Worksheet.Cells(НомерСтрокиШапкиТаблицы, Счетчик).Value));
Если Не ПустаяСтрока(НазвКолонки) Тогда
Для Каждого стрПН Из СтруктураНазванийПеременныхИНазванийКолонок Цикл
Если СокрЛП(НазвКолонки) = СокрЛП(стрПН.Значение) Тогда
СтруктураЗначенийПеременных.Вставить(стрПН.Ключ, Счетчик);
Прервать; // Нашли соответствие, переходим к следующей колонке Excel
КонецЕсли;
КонецЦикла;
КонецЕсли;
КонецЦикла;
ВсеОбязательныеКолонкиНайдены = Истина;
Для Каждого стрПрПН Из СтруктураПризнакОбязательностиКолонок Цикл
Если НЕ СтруктураЗначенийПеременных.Свойство(стрПрПН.Ключ) Тогда
Если стрПрПН.Значение Тогда // Если колонка обязательная и не найдена
// ОбщегоНазначенияКлиентСервер.СообщитьПользователю("Не найдена колонка """+СтруктураНазванийПеременныхИНазванийКолонок[стрПрПН.Ключ]+""". Возможно, в xls-файле эта колонка сейчас называется по-другому. "+Символы.ПС+ "Скопируйте название колонки из окна сообщения и вставьте в xls-файл. Это необходимо для полного совпадения ожидаемого и реального названия колонки в xls-файле.");
Сообщить("Не найдена обязательная колонка """+СтруктураНазванийПеременныхИНазванийКолонок[стрПрПН.Ключ]+""".");
ВсеОбязательныеКолонкиНайдены = Ложь;
Прервать;
Иначе // Если колонка необязательная и не найдена
СтруктураЗначенийПеременных.Вставить(стрПрПН.Ключ, 0); // Присваиваем 0 или другое значение, указывающее на отсутствие
КонецЕсли;
КонецЕсли;
КонецЦикла;
Если Не ВсеОбязательныеКолонкиНайдены Тогда
Возврат Неопределено;
КонецЕсли;
Возврат СтруктураЗначенийПеременных;
КонецФункции
Приведенный код хорошо иллюстрирует логику поиска колонок. Для использования с ТабличныйДокумент, нам нужно заменить обращения типа Worksheet.Cells(НомерСтрокиШапкиТаблицы, Счетчик).Value на ТабДок.Область(НомерСтрокиШапкиТаблицы, Счетчик).Значение или .Текст. Основной алгоритм перебора колонок и сопоставления названий останется тем же.
Важные аспекты реализации автоматического сопоставления:
СокрЛП() и приводите текст к одному регистру для сравнения, чтобы избежать проблем из-за лишних пробелов или разного регистра.Часто в Excel-файлах, особенно из старых систем или сформированных вручную, можно столкнуться с ситуацией, когда артикул и наименование товара объединены в одной ячейке (например, "Тапки 5 размер"). Это представляет собой вызов для автоматической загрузки. Рассмотрим несколько подходов к решению этой проблемы.
Самый простой, но не всегда автоматизированный подход — это обучить пользователя использовать стандартные функции Excel. Пользователь может самостоятельно разделить текст на несколько колонок с помощью инструмента "Текст по столбцам" (Данные / Текст по столбцам) до того, как загрузит файл в 1С. Это особенно эффективно, если разделитель между артикулом и наименованием всегда один и тот же (пробел, точка с запятой и т.п.).
Если мы не хотим полагаться на действия пользователя, мы можем разделить данные программно уже внутри 1С. Для этого мы будем использовать строковые функции:
СтрРазделить(): Позволяет разделить строку по заданному разделителю и получить массив подстрок.Найти(), Сред(), Лев(), Прав(): Эти функции помогут нам выделить часть строки, если разделитель не является жестким или нужно извлечь данные по шаблону.Пример программного разделения строки:
// Пример программного разделения артикула и наименования
// Предположим, строкаЯчейки = "АРТ001 Тапки белые размер 42"
Функция РазделитьАртикулИНаименование(СтрокаЯчейки)
Результат = Новый Структура;
Результат.Вставить("Артикул", "");
Результат.Вставить("Наименование", "");
// Пример 1: Артикул всегда в начале и отделен пробелом
ПозицияПробела = Найти(СтрокаЯчейки, " ");
Если ПозицияПробела > 0 Тогда
Результат.Артикул = Лев(СтрокаЯчейки, ПозицияПробела - 1);
Результат.Наименование = СокрЛП(Сред(СтрокаЯчейки, ПозицияПробела + 1));
Иначе
// Если пробела нет, возможно, вся строка - это артикул или наименование
Результат.Наименование = СтрокаЯчейки;
КонецЕсли;
// Пример 2: Использование СтрРазделить
// Если формат более сложный или нужен N-й токен
МассивТокенов = СтрРазделить(СтрокаЯчейки, " ", Ложь);
Если МассивТокенов.Количество() > 0 Тогда
// Артикул - первый токен
Результат.Артикул = МассивТокенов[0];
// Наименование - все остальное, объединенное обратно
НаименованиеЧасти = Новый Массив;
Для Индекс = 1 По МассивТокенов.Количество() - 1 Цикл
НаименованиеЧасти.Добавить(МассивТокенов[Индекс]);
КонецЦикла;
Результат.Наименование = СтрСоединить(НаименованиеЧасти, " ");
КонецЕсли;
Возврат Результат;
КонецФункции
// Пример вызова
// ДанныеЯчейки = РазделитьАртикулИНаименование("АРТ001 Тапки белые размер 42");
// Сообщить("Артикул: " + ДанныеЯчейки.Артикул + ", Наименование: " + ДанныеЯчейки.Наименование);
Программное разделение требует четкого понимания структуры данных в ячейке. Необходимо предусмотреть различные варианты и разделители.
Чтобы сделать процесс еще более гибким и не требовать от пользователя навыков программирования, мы можем предоставить ему возможность самостоятельно описывать правила разбора ячеек. Это может быть реализовано в виде простого "мини-языка" или полей для указания параметров. Например, пользователь может указать:
КОЛОНКА АртикулНаим 5: Обозначает, что артикул и наименование находятся в 5-й колонке.СОДЕРЖИТ АртикулНаим Артикул Пробел Наименование: Указывает, что в колонке "АртикулНаим" содержатся два значения, разделенные пробелом, где первое — артикул, второе — наименование.ГДЕ Артикул Артикул*: Обозначает, что артикул следует искать в колонке, начинающейся с текста "Артикул".Реализация такого "мини-языка" потребует парсера, который будет интерпретировать введенные пользователем правила. Это может быть:
ГДЕ, СОДЕРЖИТ, КОЛОНКА).Этот подход дает максимальную гибкость и контроль пользователю, позволяя адаптироваться к новым форматам файлов без участия программиста, но требует более сложной первоначальной реализации.
Концептуальный пример обработки правила из мини-языка:
// Концептуальный пример обработки правила "СОДЕРЖИТ АртикулНаим Артикул Пробел Наименование"
// Это не готовый код, а иллюстрация логики, которую нужно реализовать
Функция ОбработатьПравилоРазбораЯчейки(ТекстПравила, ЗначениеЯчейки)
РезультатРазбора = Новый Структура;
РезультатРазбора.Вставить("Артикул", "");
РезультатРазбора.Вставить("Наименование", "");
Если НачинаетсяС(ТекстПравила, "СОДЕРЖИТ") Тогда
// Разбираем текст правила: СОДЕРЖИТ <имя_поля_в_excel> <что_извлекаем1> <разделитель> <что_извлекаем2>
// Предположим, что мы уже определили:
// ИмяПоляВExcel = "АртикулНаим"
// ЧтоИзвлекаем1 = "Артикул"
// Разделитель = "Пробел" (интерпретируем как " ")
// ЧтоИзвлекаем2 = "Наименование"
Если Разделитель = "Пробел" Тогда
МассивЧастей = СтрРазделить(ЗначениеЯчейки, " ", Ложь);
Если МассивЧастей.Количество() >= 2 Тогда
// Здесь нужно сопоставить "ЧтоИзвлекаем1" и "ЧтоИзвлекаем2" с фактическими именами полей
// Например, если в правиле указано "Артикул" - это первый токен
РезультатРазбора.Артикул = МассивЧастей[0];
РезультатРазбора.Наименование = СтрСоединить(МассивЧастей, " ", 1); // Остальные токены для наименования
ИначеЕсли МассивЧастей.Количество() = 1 Тогда
// Если нет разделителя, то это может быть только артикул или наименование
// Тут нужна доп. логика определения, что это
РезультатРазбора.Наименование = ЗначениеЯчейки; // По умолчанию
КонецЕсли;
КонецЕсли;
КонецЕсли;
Возврат РезультатРазбора;
КонецФункции
Для "нетленки" (самописной или сильно измененной конфигурации) особенно важно создать удобный пользовательский интерфейс, поскольку стандартные механизмы могут отсутствовать. Цель — минимизировать ручной труд и позволить пользователю самостоятельно настраивать процесс загрузки.
Использование механизмов расширений конфигурации, если это возможно, является предпочтительным способом модификации "нетленки", поскольку позволяет добавлять функционал без изменения основного кода конфигурации, что облегчает ее поддержку и потенциальное обновление. Если расширения недоступны, то разработка отдельной внешней обработки, которая взаимодействует с объектами "нетленки", является следующим по предпочтительности вариантом. Таким образом, мы создаем "пережевывающую" обработку для старой конфигурации, как упомянуто в одном из сообщений.
При работе с большими файлами Excel важно обеспечить хорошую производительность загрузки:
Excel.Application, старайтесь минимизировать количество обращений к нему. Открывайте файл один раз, считывайте все необходимые данные, а затем закрывайте.ТабличныйДокумент, и имеет свои особенности настройки драйверов.Применение этих подходов позволит нам создать гибкое, мощное и, главное, удобное для пользователя решение по загрузке данных из Excel-файлов в 1С. Такой подход не только снизит нагрузку на сотрудников, но и повысит точность ввода данных, что является критически важным для эффективного ведения учета.