В мире разработки на 1С часто возникает задача импорта данных из файлов Excel. Традиционные методы часто подразумевают трудоемкий и не всегда оптимальный перебор строк и колонок, что может быть утомительным и медленным для больших объемов данных. Однако существуют более изящные и эффективные подходы. Сегодня мы рассмотрим один из таких «потрясающих» методов, который позволяет загрузить содержимое Excel-файла в ТаблицуЗначений 1С, избегая явного циклического перебора, а также разберем его преимущества, недостатки и альтернативные решения.
Представленный метод базируется на использовании встроенных в 1С объектов ТабличныйДокумент и ПостроительОтчета. Он позволяет нам сначала прочитать Excel-файл как табличный документ 1С, а затем использовать его как источник данных для построителя отчета, который уже выгрузит данные в удобную для нас ТаблицуЗначений.
Разберем процесс по шагам.
Шаг 1: Выбор файла на клиенте
Первым делом нам необходимо дать пользователю возможность выбрать нужный Excel-файл. Это действие выполняется на клиенте, поскольку именно там у пользователя есть доступ к локальной файловой системе. Для этого мы используем объект ДиалогВыбораФайла.
В следующем примере мы видим, как инициализируется диалог выбора файла, устанавливается фильтр для файлов Excel и предлагается пользователю выбрать файл. Выбранный путь к файлу сохраняется в переменной Файлик.
&НаКлиенте
Процедура ФайликНачалоВыбора(Элемент, ДанныеВыбора, СтандартнаяОбработка)
Режим = РежимДиалогаВыбораФайла.Открытие;
ДиалогОткрытияФайла = Новый ДиалогВыбораФайла(Режим);
ДиалогОткрытияФайла.ПолноеИмяФайла = "";
Фильтр = "Excel файлы(*.xls;*.xlsx)|*.xls;*.xlsx";
ДиалогОткрытияФайла.Фильтр = Фильтр;
ДиалогОткрытияФайла.Заголовок = "Выберите файл товарной накладной!";
Если ДиалогОткрытияФайла.Выбрать() Тогда
Файлик = ДиалогОткрытияФайла.ПолноеИмяФайла;
Иначе
Файлик = "";
КонецЕсли;
КонецПроцедуры
Шаг 2: Передача файла с клиента на сервер
Поскольку основные операции по чтению и обработке данных, как правило, выполняются на сервере (что является хорошей практикой и обязательным требованием для работы с файлами на сервере), выбранный на клиенте файл необходимо передать на сервер. Оригинальный код из сообщения 0 не демонстрирует эту часть, но она критически важна для корректной работы в клиент-серверном режиме. Мы можем использовать для этого функцию ПоместитьФайлНаСерверАсинх() или НачатьПомещениеФайловНаСервер(), если нужна асинхронная передача.
Вот как это может выглядеть концептуально:
// Предполагаем, что ПолноеИмяФайлаНаКлиенте - это переменная 'Файлик' из предыдущего шага.
// ИмяФайлаДляСервера - будет уникальным именем файла во временном хранилище.
// Пример использования ПоместитьФайлНаСерверАсинх
&НаКлиенте
Процедура ЗагрузитьНаСервер(ПолноеИмяФайлаНаКлиенте)
Если ПустаяСтрока(ПолноеИмяФайлаНаКлиенте) Тогда
Возврат;
КонецЕсли;
ИдентификаторФайлаНаСервере = ПоместитьФайлНаСервер(ПолноеИмяФайлаНаКлиенте, УникальныйИдентификатор); // УникальныйИдентификатор - уникальная строка
// Вызываем серверную процедуру, передавая идентификатор временного хранилища
ЗагрузитьИзФайлаНаСервере2022(ИдентификаторФайлаНаСервере);
КонецПроцедуры
&НаСервере
Процедура ЗагрузитьИзФайлаНаСервере2022(АдресФайлаВоВременномХранилище)
// Извлекаем файл из временного хранилища во временный файл на сервере
ПолноеИмяВременногоФайлаНаСервере = ПолучитьИмяВременногоФайла(".xls"); // Или ".xlsx"
ДвоичныеДанныеФайла = ПолучитьИзВременногоХранилища(АдресФайлаВоВременномХранилище);
ДвоичныеДанныеФайла.Записать(ПолноеИмяВременногоФайлаНаСервере);
// Дальнейший код для чтения Excel будет использовать ПолноеИмяВременногоФайлаНаСервере
// ...
КонецПроцедуры
Или, как часто делают для простоты, передают путь к файлу из временного хранилища сразу:
// В процедуре на клиенте после выбора файла:
Если ДиалогОткрытияФайла.Выбрать() Тогда
Файлик = ДиалогОткрытияФайла.ПолноеИмяФайла;
// Помещаем файл во временное хранилище и получаем его адрес
АдресФайлаВоВременномХранилище = ПоместитьФайлВоВременноеХранилище(Новый ДвоичныеДанные(Файлик));
// Вызываем серверную процедуру
ЗагрузитьИзФайлаНаСервере2022(АдресФайлаВоВременномХранилище);
Иначе
Файлик = "";
КонецЕсли;
// ...
&НаСервере
Процедура ЗагрузитьИзФайлаНаСервере2022(АдресФайлаВоВременномХранилище)
// Здесь мы уже работаем с адресом в хранилище, а не с путем на клиенте.
// Если ТабДок.Прочитать() требует путь к файлу, то его нужно будет получить из хранилища.
// Часто удобнее сначала сохранить во временный файл на сервере.
ИмяВременногоФайлаНаСервере = ПолучитьИмяВременногоФайла(); // Получаем путь к временному файлу на сервере
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресФайлаВоВременномХранилище);
ДвоичныеДанные.Записать(ИмяВременногоФайлаНаСервере);
// Далее используем ИмяВременногоФайлаНаСервере для ТабДок.Прочитать()
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ИмяВременногоФайлаНаСервере, СпособЧтенияЗначенийТабличногоДокумента.Значение);
// ...
КонецПроцедуры
Шаг 3: Чтение и загрузка данных в ТаблицуЗначений на сервере
После того как файл передан на сервер, мы можем приступить к его чтению и загрузке. Здесь вступает в дело комбинация ТабличныйДокумент.Прочитать() и ПостроительОтчета.
Метод ТабличныйДокумент.Прочитать() позволяет прочитать содержимое Excel-файла и представить его в виде объекта ТабличныйДокумент 1С. После этого мы используем ПостроительОтчета, указывая ему в качестве источника данных область нашего ТабличногоДокумента. ПостроительОтчета автоматически распознает структуру колонок (обычно по первой строке) и при выполнении позволяет выгрузить данные в ТаблицуЗначений.
&НаСервере
Процедура ЗагрузитьИзФайлаНаСервере2022(ИмяФайлаНаСервере) // Предполагаем, что сюда приходит имя файла на сервере
Табл.Очистить(); // Очистка существующей таблицы, если есть
ТаблДок.Очистить(); // Очистка существующего табличного документа, если есть
ТабДок = Новый ТабличныйДокумент;
// Читаем Excel-файл как ТабличныйДокумент 1С
ТабДок.Прочитать(СокрЛП(ИмяФайлаНаСервере), СпособЧтенияЗначенийТабличногоДокумента.Значение);
// Определяем область для чтения. Можно указать конкретную область, например,
// ТабДок.Область(НачальнаяСтрока, НачальнаяКолонка, КонечнаяСтрока, КонечнаяКолонка)
// В данном случае читаем всю таблицу
ОбластьЯчеек = ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы);
// Создаем ОписаниеИсточникаДанных из области ячеек
ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьЯчеек);
// Инициализируем ПостроительОтчета
ПостроительОтчета = Новый ПостроительОтчета;
ПостроительОтчета.ИсточникДанных = ИсточникДанных;
// Выполняем построитель отчета
ПостроительОтчета.Выполнить();
// Выгружаем результат в ТаблицуЗначений
ТабЗначений = ПостроительОтчета.Результат.Выгрузить();
КонецПроцедуры
Этот метод действительно заслуживает внимания благодаря нескольким ключевым достоинствам:
ПостроительОтчета способен автоматически определить имена и количество колонок, основываясь на первой строке выбранной области табличного документа. Это удобно, когда формат файла стандартизирован.Несмотря на свою привлекательность, данный метод имеет ряд существенных ограничений и может быть неэффективен в более сложных сценариях:
ПостроительОтчета может некорректно их определить или пропустить данные.ТабличныйДокумент.Прочитать() и ПостроительОтчета может работать медленно или вызывать зависания, что было отмечено в обсуждении. Определение "последней строки" как 1 048 576 даже при наличии нескольких строк данных - это частая проблема, которая может сильно замедлить процесс.ТабличныйДокумент.Прочитать() не может работать с файлами или листами, защищенными паролем, что приводит к ошибкам.Учитывая ограничения основного метода, давайте рассмотрим другие подходы к работе с Excel в 1С, которые предлагают большую гибкость и контроль, хотя и могут быть сложнее в реализации.
Этот метод позволяет 1С напрямую взаимодействовать с установленным на компьютере приложением Microsoft Excel. Вы можете создать объект COMОбъект("Excel.Application") и управлять им, как если бы вы работали с Excel вручную.
Концептуальный пример:
&НаСервере
Процедура ЗагрузитьЧерезCOM(ИмяФайлаНаСервере)
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.Visible = Ложь; // Скрываем Excel
Excel.DisplayAlerts = Ложь; // Отключаем предупреждения
Книга = Excel.Workbooks.Open(ИмяФайлаНаСервере);
Лист = Книга.Sheets(1); // Доступ к первому листу
// Чтение данных из ячеек или диапазона
// Например, чтение значения ячейки A1:
ЗначениеЯчейки = Лист.Cells(1, 1).Value;
// Или чтение диапазона в массив или ТаблицуЗначений
// Для этого обычно требуется явный перебор или создание ТаблицыЗначений 1С
// и заполнение ее по строкам/колонкам из Excel
Книга.Close(0); // Закрываем книгу без сохранения изменений
Excel.Quit(); // Закрываем Excel
Исключение
Сообщить("Ошибка при работе с Excel через COM: " + ОписаниеОшибки());
Если Excel <> Неопределено Тогда
Excel.Quit();
КонецЕсли;
КонецПопытки;
КонецПроцедуры
Метод ADO позволяет подключаться к файлу Excel как к полноценной базе данных с помощью OLE DB драйверов и выполнять SQL-запросы для извлечения данных.
Существуют сторонние внешние компоненты, разработанные специально для 1С, которые позволяют читать файлы Excel (как .xls, так и .xlsx) напрямую, без необходимости установки Microsoft Office.
Файлы формата .xlsx на самом деле представляют собой ZIP-архивы, внутри которых находятся XML-файлы с данными, структурой и форматированием. Можно программно распаковать такой архив и парсить XML-файлы, чтобы получить данные.
Когда это возможно, преобразование Excel-файла в формат CSV (Comma Separated Values) и последующая работа с ним как с текстовым файлом может быть гораздо более эффективным и простым решением.
ТекстовыйДокумент).При работе с внешними файлами данных мы неизбежно сталкиваемся с различными сложностями. Давайте разберем, как можно решать наиболее частые из них.
Описание проблемы: Метод с ПостроителемОтчета предполагает, что заголовки колонок находятся в первой строке. Если это не так, или если заголовки содержат недопустимые символы для имен колонок 1С (например, пробелы, точки, знаки препинания), или если заголовки дублируются, это может привести к некорректной выгрузке данных или ошибкам.
Решение:
ПостроителюОтчета, что данные начинаются с этой строки:
// ОбластьЯчеек = ТабДок.Область(1, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы); // Исходная строка
ОбластьЯчеек = ТабДок.Область(10, 1, ТабДок.ВысотаТаблицы, ТабДок.ШиринаТаблицы); // Если заголовки с 10-й строки
Однако, в этом случае заголовки будут определены по 10-й строке. Если нам нужны данные, а заголовки не важны или нужно их переименовать, можно использовать подход, предложенный в сообщении 45.
ТаблицуЗначений мы можем программно переименовать колонки или добавить нужные.В следующем примере мы видим функцию, которая читает табличный документ, а затем принудительно создает колонки в результирующей таблице значений с именами "Номер_1", "Номер_2" и так далее. Это полезно, когда названия колонок в Excel нестабильны, или они не важны, и нам просто нужны данные.
Функция ПрочитатьТабличныйДокумент(ТабличныйДокументИсточник)
КоличествоСтолбцов = ТабличныйДокументИсточник.ШиринаТаблицы;
Если КоличествоСтолбцов = 0 Тогда
Возврат Новый ТаблицаЗначений;
КонецЕсли;
ТабличныйДокумент = Новый ТабличныйДокумент;
Для НомерСтолбца = 1 По КоличествоСтолбцов Цикл
ТабличныйДокумент.Область(1, НомерСтолбца, 1, НомерСтолбца).Текст = "Номер_" + Формат(НомерСтолбца,"ЧГ=0");
КонецЦикла;
ТабличныйДокумент.Вывести(ТабличныйДокументИсточник);
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());
Построитель.Выполнить();
ТаблицаЗначенийРезультат = Построитель.Результат.Выгрузить();
Возврат ТаблицаЗначенийРезультат;
КонецФункции
Эта функция сначала создает временный ТабличныйДокумент, в который "вставляет" нумерованные заголовки, а затем выводит в него содержимое исходного табличного документа. Таким образом, ПостроительЗапроса (аналогично ПостроителюОтчета) гарантированно получает понятные имена колонок.
Описание проблемы: Иногда после автоматической загрузки порядок колонок в результирующей ТаблицеЗначений не соответствует ожидаемому или некоторые колонки из Excel вовсе не попали в ТЗ. В обсуждении это было отмечено как проблема.
Решение:
ТаблицыЗначений, мы можем создать новую ТЗ с правильной структурой и затем перебирать строки исходной ТЗ, заполняя новую с учетом соответствия колонок.
НоваяТЗ = Новый ТаблицаЗначений;
НоваяТЗ.Колонки.Добавить("ПравильноеИмя1");
НоваяТЗ.Колонки.Добавить("ПравильноеИмя2");
Для Каждого СтрокаТЗ Из ТабЗначений Цикл
НоваяСтрока = НоваяТЗ.Добавить();
НоваяСтрока.ПравильноеИмя1 = СтрокаТЗ["ИсходноеИмяКолонкиИзExcel"];
// ...
КонецЦикла;
Если ТабЗначений.Колонки.Найти("ИмяКолонки") <> Неопределено Тогда.Описание проблемы: Как упоминалось, обработка очень больших Excel-файлов (сотни тысяч строк) методом ТабличныйДокумент.Прочитать() и ПостроительОтчета может быть очень медленной или привести к зависанию. Точное определение последней строки Excel как 1 048 576, даже если данных меньше, усугубляет проблему.
Решение:
ТабДок.Область(), чтобы не читать пустые строки.Excel.Visible = Ложь) и отключайте предупреждения (Excel.DisplayAlerts = Ложь), чтобы повысить производительность.Описание проблемы: Методы 1С напрямую не могут прочитать защищенный паролем Excel-файл или лист.
Решение:
Единственным надежным способом является использование COM-объекта. При открытии книги через Excel.Workbooks.Open() можно указать пароль в параметрах, если он известен.
// Пример открытия защищенной книги через COM
Книга = Excel.Workbooks.Open(ИмяФайла, , , , "ПарольКниги");
// Если защищен лист, можно снять защиту:
Лист.Unprotect("ПарольЛиста");
Описание проблемы: При чтении данных из Excel могут возникать ошибки преобразования типов (например, Excel хранит число как текст) или общие ошибки чтения, если файл поврежден или имеет нестандартную структуру.
Решение:
ТабДок.Прочитать() (и другие операции с файлами) в конструкцию Попытка...Исключение для корректной обработки возможных ошибок.ТаблицыЗначений, при работе со значениями из колонок, явно преобразуйте их к нужному типу 1С (Число(), Строка(), Дата(), Булево()), используя проверки ТипЗнч() или Попытка...Исключение для каждого преобразования.Описание проблемы: В 64-битной среде 1С могут возникнуть проблемы при попытке использовать 32-битные COM-объекты (например, 32-битный Microsoft Office) и наоборот. Это является частой причиной ошибок "класс не зарегистрирован" или других проблем совместимости.
Решение:
Таким образом, хотя метод с ПостроителемОтчета и ТабличныйДокумент.Прочитать() удобен своей лаконичностью и идеально подходит для простых и стандартизированных Excel-файлов, для более сложных сценариев, требующих гибкости, полного контроля над структурой Excel, обработки ошибок или высокой производительности, нам придется обратиться к другим подходам, таким как OLE-автоматизация, ADO, внешние компоненты или даже прямое парсинг OpenXML. Выбор метода всегда зависит от конкретных требований задачи, сложности структуры Excel-файла и имеющихся ресурсов.