При разработке алгоритмов импорта данных из электронных таблиц Microsoft Excel в 1С разработчики часто сталкиваются с ситуацией, когда обработка считывает пустые строки в конце таблицы. В результате табличная часть документа или справочника наполняется пустыми записями. Выясним причину такого поведения и разберем по шагам надежные способы решения этой проблемы.
Основная причина появления лишних строк связана с особенностями работы самого табличного процессора Excel:
Delete, удаляются только значения ячеек. Границы, заливка, шрифт, числовой формат и выравнивание остаются. Свойства листа UsedRange и методы определения последней строки продолжают считать такие ячейки частью используемого диапазона.Символ(160), обычные пробелы или формулы, возвращающие пустую строку "". Стандартная функция СокрЛП() не очищает неразрывные пробелы, поэтому значение распознается как непустое.Самый простой и быстрый способ обойти проблему — добавить проверку заполненности ключевых реквизитов непосредственно перед добавлением новой строки в табличную часть.
Рассмотрим типовой цикл чтения данных через COM-объект и модифицируем его:
Для сч = НачальнаяСтрока По ФайлСтрок Цикл
// Считываем ключевые поля для проверки
КБК = СокрЛП(Excel.Cells(сч, 1).Value);
Счет = СокрЛП(Excel.Cells(сч, 2).Value);
// Если ключевые реквизиты не заполнены, пропускаем строку
Если ПустаяСтрока(КБК) И ПустаяСтрока(Счет) Тогда
Продолжить;
КонецЕсли;
Стр = Объект.ТМЦ.Добавить();
Стр.КБК = КБК;
Стр.Счет = Счет;
Стр.Количество = СокрЛП(Excel.Cells(сч, 6).Value);
Стр.ЕдИзм = СокрЛП(Excel.Cells(сч, 7).Value);
Стр.КраткоеНаименование = СокрЛП(Excel.Cells(сч, 8).Value);
Стр.Наименование = СокрЛП(Excel.Cells(сч, 9).Value);
Стр.МОЛ = СокрЛП(Excel.Cells(сч, 10).Value);
Стр.Валюта = СокрЛП(Excel.Cells(сч, 12).Value);
Стр.Обеспечение = СокрЛП(Excel.Cells(сч, 14).Value);
Стр.ДебетОстатки = СокрЛП(Excel.Cells(сч, 19).Value);
Стр.КредитОстатки = СокрЛП(Excel.Cells(сч, 20).Value);
Стр.Цена = СокрЛП(Excel.Cells(сч, 21).Value);
КонецЦикла;
Если в файле гарантированно нет разрывов между строками и после первой пустой строки полезные данные гарантированно отсутствуют, оператор Продолжить можно заменить на Прервать, что сэкономит время выполнения обработки.
Чтобы не перебирать тысячи пустых форматированных строк, определим реальную границу заполненных данных средствами COM-объекта Excel. Для этого применим метод Find с поиском от последней ячейки в обратном направлении:
Лист = Книга.Sheets(1);
// Ищем любую ячейку со значением, просматривая снизу вверх
Ячейка = Лист.Cells.Find("*", Лист.Cells(1, 1), -4163, 2, 1, 2);
// -4163 = xlValues, 2 = xlPart, 1 = xlByRows, 2 = xlPrevious
Если Ячейка <> Неопределено Тогда
ФайлСтрок = Ячейка.Row;
Иначе
ФайлСтрок = 0;
КонецЕсли;
Такой подход позволяет вычислить номер последней строки, содержащей реальный текст или числа, полностью игнорируя пустое форматирование.
Построчное чтение через Excel.Cells(сч, кол).Value порождает множество межпроцессных COM-вызовов, что существенно замедляет загрузку больших файлов. Гораздо эффективнее выгрузить весь диапазон в память за одно обращение:
Попытка
Лист = Книга.Sheets(1);
Ячейка = Лист.Cells.Find("*", Лист.Cells(1, 1), -4163, 2, 1, 2);
Если Ячейка = Неопределено Тогда
Возврат;
КонецЕсли;
ФайлСтрок = Ячейка.Row;
// Считываем весь диапазон в массив COMSafeArray
МассивДанных = Лист.Range(Лист.Cells(1, 1), Лист.Cells(ФайлСтрок, 21)).Value;
Для сч = НачальнаяСтрока По ФайлСтрок Цикл
ЗначениеКБК = СокрЛП(МассивДанных.Получить(сч, 1));
ЗначениеСчет = СокрЛП(МассивДанных.Получить(сч, 2));
Если ПустаяСтрока(ЗначениеКБК) И ПустаяСтрока(ЗначениеСчет) Тогда
Продолжить;
КонецЕсли;
Стр = Объект.ТМЦ.Добавить();
Стр.КБК = ЗначениеКБК;
Стр.Счет = ЗначениеСчет;
Стр.Количество = СокрЛП(МассивДанных.Получить(сч, 6));
Стр.ЕдИзм = СокрЛП(МассивДанных.Получить(сч, 7));
Стр.Наименование = СокрЛП(МассивДанных.Получить(сч, 9));
Стр.Цена = СокрЛП(МассивДанных.Получить(сч, 21));
КонецЦикла;
Исключение
Сообщить("Ошибка при чтении файла: " + ОписаниеОшибки());
Финал
Excel.Workbooks.Close();
Excel.Application.Quit();
Excel = Неопределено;
КонецПопытки;
Важно: блок Попытка ... Исключение гарантирует корректное закрытие приложения Excel и предотвращает зависание процессов EXCEL.EXE в операционной системе в случае непредвиденных ошибок.
Если табличная часть уже заполнена и требуется быстро очистить пустые хвостовые строки, применим обратный цикл с шагом -1:
Для Индекс = Объект.ТМЦ.Количество() - 1 По 0 Шаг -1 Цикл
ТекущаяСтрока = Объект.ТМЦ[Индекс];
Если Не ЗначениеЗаполнено(ТекущаяСтрока.КБК) И Не ЗначениеЗаполнено(ТекущаяСтрока.Счет) Тогда
Объект.ТМЦ.Удалить(Индекс);
Иначе
// Прерываем цикл, как только встретили первую заполненную строку с конца
Прервать;
КонецЕсли;
КонецЦикла;
Использование COM-объекта Excel.Application имеет существенные ограничения: оно требует установленного пакета Microsoft Office, не работает на серверах под управлением Linux и часто блокируется политиками безопасности. Платформа 1С:Предприятие позволяет читать файлы Excel напрямую через объект ТабличныйДокумент:
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу);
ВсегоСтрок = ТабДок.ВысотаТаблицы;
Для НомерСтроки = НачальнаяСтрока По ВсегоСтрок Цикл
ЗначениеКБК = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 1).Текст);
ЗначениеСчет = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 2).Текст);
Если ПустаяСтрока(ЗначениеКБК) И ПустаяСтрока(ЗначениеСчет) Тогда
Продолжить;
КонецЕсли;
Стр = Объект.ТМЦ.Добавить();
Стр.КБК = ЗначениеКБК;
Стр.Счет = ЗначениеСчет;
Стр.Количество = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 6).Текст);
Стр.ЕдИзм = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 7).Текст);
Стр.Наименование = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 9).Текст);
Стр.Цена = СокрЛП(ТабДок.ПолучитьОбласть(НомерСтроки, 21).Текст);
КонецЦикла;
Метод ТабличныйДокумент.Прочитать() работает нативно, кроссплатформенно и не оставляет зависших процессов в операционной системе.