Работа с внешними файлами Excel — одна из самых частых задач разработчика 1С. Будь то загрузка прайс-листов, остатков или данных для сверки, нам часто требуется не просто прочитать файл, а выбрать конкретную страницу и отфильтровать строки по определенному условию, например, если колонка Сумма содержит значения больше нуля. В этой статье мы подробно разберем несколько способов решения этой задачи, проанализируем их плюсы и минусы, а также напишем надежный код для обработки данных.
Начнем с самого современного и производительного метода. Начиная с последних версий платформы 8.3, 1С позволяет работать с файлами Excel напрямую через объект ТабличныйДокумент. Это значительно быстрее, чем использование COM-объектов, так как данные считываются платформой на уровне двоичного потока.
Рассмотрим пошагово, как реализовать выбор конкретного листа и загрузку данных. Сначала нам нужно получить список имен всех листов в файле, чтобы пользователь мог выбрать нужный в реквизите формы.
// Получаем имена листов для выбора пользователем
ИменаЛистов = ПолучитьИменаЛистовExcel(ПутьКФайлу);
СписокЛистов = Новый СписокЗначений;
СписокЛистов.ЗагрузитьЗначения(ИменаЛистов);
// После выбора пользователем конкретного листа (переменная ВыбранныйЛист) переходим к чтению
После того как пользователь выбрал имя листа, мы можем прочитать именно его, используя метод Прочитать(). Важным моментом здесь является указание номера или имени листа в параметрах метода.
ТабДок = Новый ТабличныйДокумент;
// Параметры: ПутьКФайлу, СпособЧтения, ИмяЛиста
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияТабличногоДокумента.Excel2007, ВыбранныйЛист);
ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("Сумма", Новый ОписаниеТипов("Число"));
// Перебор строк табличного документа
Для НомерСтроки = 1 По ТабДок.ВысотаТаблицы Цикл
ЗначениеСуммы = ТабДок.Область(НомерСтроки, НомерКолонкиСуммы).Текст;
ЧислоСуммы = 0;
// Преобразуем строку в число и проверяем условие
Попытка
ЧислоСуммы = Число(СтрЗаменить(ЗначениеСуммы, " ", ""));
Исключение
Продолжить; // Пропускаем, если в ячейке текст
КонецПопытки;
Если ЧислоСуммы > 0 Тогда
НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.Сумма = ЧислоСуммы;
// Заполняем остальные данные...
КонецЕсли;
КонецЦикла;
Если нам требуется более сложная логика (например, работа с формулами, форматированием или макросами), мы используем COMОбъект. Это классический метод, который требует установленного Microsoft Excel на компьютере пользователя.
Проанализируем ситуацию: как выбрать лист по имени? В объектной модели Excel за это отвечает коллекция Worksheets. Обратиться к листу можно как по индексу (порядковому номеру), так и по его названию.
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Excel не установлен!");
Возврат;
КонецПопытки;
Excel.Visible = Ложь;
Excel.DisplayAlerts = Ложь;
Книга = Excel.Workbooks.Open(ПутьКФайлу);
// Выбираем лист по значению переменной ВыбранныйЛист
Попытка
Лист = Книга.Worksheets(ВыбранныйЛист);
Исключение
Сообщить("Лист с названием " + ВыбранныйЛист + " не найден!");
Книга.Close(Ложь);
Excel.Quit();
Возврат;
КонецПопытки;
Разберем теперь вторую часть задачи: эффективную проверку колонки Сумма. Огромная ошибка начинающих разработчиков — обращаться к каждой ячейке в цикле через Лист.Cells(x, y).Value. Это работает крайне медленно, так как каждое обращение инициирует межпроцессное взаимодействие. Вместо этого мы считаем всю область данных в массив одним махом.
// Получаем заполненную область
Область = Лист.UsedRange;
// Считываем все данные в двумерный массив 1С
ДанныеФайла = Область.Value.Выгрузить();
// Теперь работаем с массивом ДанныеФайла, это в сотни раз быстрее
КоличествоСтрок = ДанныеФайла.ВГраница(2); // Индекс второй размерности
Для СтрокаМассива = 1 По КоличествоСтрок Цикл
// Предположим, колонка Сумма вторая (индекс 2)
ЗначениеСуммы = ДанныеФайла[2][СтрокаМассива];
Если ЗначениеЗаполнено(ЗначениеСуммы) И ТипЗнч(ЗначениеСуммы) = Тип("Число") Тогда
Если ЗначениеСуммы > 0 Тогда
// Добавляем в нашу Таблицу Значений
НоваяСтрока = ТаблицаРезультат.Добавить();
// Заполнение данных...
КонецЕсли;
КонецЕсли;
КонецЦикла;
Книга.Close(Ложь);
Excel.Quit();
При проверке ячеек в Excel важно учитывать нюансы типов данных. Рассмотрим подробнее, что может находиться в ячейке, которая кажется пустой:
Неопределено." ". В этом случае ЗначениеЗаполнено() вернет Ложь, если внутри только пробелы, но лучше использовать СокрЛП().Поэтому для проверки колонки Сумма рекомендуется использовать явное преобразование к числу через Число() с предварительной очисткой строки от лишних символов (неразрывных пробелов и прочего).
Выясним причину, почему в некоторых случаях один метод лучше другого. Если ваш код выполняется на сервере (например, загрузка происходит в фоновом задании), использовать Excel.Application категорически нельзя. Это приведет к зависанию процессов EXCEL.EXE в памяти сервера и возможным сбоям прав доступа DCOM.
Для серверного кода используйте:
ТабличныйДокумент.Прочитать().Рассмотрим пример SQL-запроса через ADO для быстрого получения только нужных строк (где сумма > 0):
Connection = Новый COMОбъект("ADODB.Connection");
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
Connection.Open(ConnectionString);
// Запрос позволяет сразу отфильтровать данные на уровне драйвера
ТекстЗапроса = "SELECT * FROM [" + ВыбранныйЛист + "$] WHERE [Сумма] > 0";
Recordset = Connection.Execute(ТекстЗапроса);
Пока НЕ Recordset.EOF Цикл
// Обработка строк
Recordset.MoveNext();
КонецЦикла;
Connection.Close();
Подводя итог нашему разбору, выделим основные правила эффективной загрузки:
ТабличныйДокумент. Он быстрее и не требует установки MS Office.Excel.Visible = Ложь и загружайте данные в массив через UsedRange.Value.Попытка...Исключение, так как пользователи часто меняют названия листов в файлах.Следуя этим инструкциям, вы сможете создать надежный и быстрый механизм импорта данных из Excel в любую конфигурацию 1С.