Как правильно определить номер последнего столбца в Excel при загрузке данных в 1С?

← На главную

При автоматизации загрузки данных из Excel в 1С (особенно в версии 7.7) программисты часто сталкиваются с проблемой: стандартные методы определения границ таблицы работают некорректно. Обычный вызов SpecialCells(11), который должен возвращать последнюю ячейку, может «споткнуться» на первом же пустом столбце, даже если за ним следуют важные данные. В этой статье мы подробно разберем, почему это происходит, и рассмотрим надежные способы определения реальных границ данных.

Проблема метода SpecialCells(11) и «дырявых» данных

Метод SpecialCells(11) соответствует константе xlLastCell. Теоретически он должен возвращать нижнюю правую ячейку используемого диапазона. Однако на практике Excel часто кэширует состояние листа. Проанализируем ситуацию: если в вашей таблице есть заполненные столбцы с 1 по 85, затем идет полностью пустой 86-й столбец, а нужные данные находятся в 91-м, метод SpecialCells(11) может вернуть 85-й столбец как последний. Это происходит из-за особенностей внутреннего алгоритма Excel по оптимизации используемой области.

Рассмотрим, как обычно выглядит этот «проблемный» код:


// Определение границ через SpecialCells(11)
СтрокаПоследнейЯчейки = Excel.Cells.SpecialCells(11).Row;
СтолбецПоследнейЯчейки = Excel.Cells.SpecialCells(11).Column;

Если этот код выдает неверный результат, значит, структура файла не является монолитной, или Excel не обновил внутренний указатель UsedRange. Выясним, как обойти это ограничение.

Решение 1. Использование объекта UsedRange

Объект UsedRange представляет собой область листа, которая содержит любые данные или форматирование. В отличие от SpecialCells, он более устойчив к пропускам внутри таблицы. Проанализируем пример кода, который позволяет вычислить координаты самой последней ячейки, исходя из границ используемого диапазона:


Excel_Лист = Excel.Worksheets(1); // работаем с первым листом
// Определяем первую используемую строку и столбец (не всегда 1,1)
ПерваяСтрока = Excel_Лист.UsedRange.Row;
ПервыйСтолбец = Excel_Лист.UsedRange.Column;

// Вычисляем общее количество
ВсегоСтрок = ПерваяСтрока + Excel_Лист.UsedRange.Rows.Count - 1;
ВсегоСтолбцов = ПервыйСтолбец + Excel_Лист.UsedRange.Columns.Count - 1;

Важный момент: Мы обязательно добавляем UsedRange.Row и вычитаем единицу. Это необходимо, потому что UsedRange может начинаться не с первой ячейки (A1). Если данные начинаются со второй строки, то Rows.Count вернет количество строк в блоке, и простое использование этого числа приведет к ошибке в координатах.

Решение 2. Анализ свойства Address для вычисления реального столбца

Интересной особенностью является то, что свойство Address объекта SpecialCells(11) иногда содержит более актуальную информацию, чем свойство Column. Рассмотрим ситуацию, когда метод возвращает буквенный адрес (например, "$CQ$115"). Из этой строки можно извлечь имя столбца и преобразовать его в число.

Посмотрим на пример логики (адаптировано для 1С):


АдресПоследнейЯчейки = Excel.Cells.SpecialCells(11).Address;
// Адрес приходит в формате "$Буква$Номер"
// Нам нужно вырезать символы между первым и вторым знаком "$"
// Для 1С 7.7 используем функции работы со строками

Хотя этот метод кажется обходным, в некоторых версиях MS Excel он позволяет получить доступ к столбцам, которые игнорируются при прямом обращении к .Column.

Решение 3. Самый надежный способ — метод Find

Если данные в файле распределены хаотично («мусорное» форматирование, пустые строки в середине), самым надежным способом будет использование метода Find. Мы заставим Excel искать любой символ в обратном порядке, начиная с конца листа.

Разберем, как это реализовать через COM-объект:


// Поиск последней строки
ПоследняяСтрока = Лист.Cells.Find("*", Лист.Cells(1,1), , , 1, 2).Row;
// Поиск последнего столбца
ПоследнийСтолбец = Лист.Cells.Find("*", Лист.Cells(1,1), , , 2, 2).Column;

В параметрах метода Find мы указываем:

"*" — ищем любое значение (маска).
SearchOrder: 1 (xlByRows) для строк или 2 (xlByColumns) для столбцов.
SearchDirection: 2 (xlPrevious), что заставляет Excel искать «назад» от первой ячейки, фактически перепрыгивая в самый конец листа.

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

Как ускорить чтение данных (бонус)

В процессе обсуждения часто возникает вопрос: «Чтение по одной ячейке через Excel.Cells(r, c).Value работает очень медленно, как быть?». Рассмотрим эффективный прием для 1С 7.7 и 8.x — чтение всего диапазона в массив за один вызов.

Вместо цикла по ячейкам используем прямое присваивание:


// Получаем данные всего листа в одну переменную
ДанныеМассивом = Лист.UsedRange.Value;
// Теперь ДанныеМассивом — это двумерный массив в памяти 1С

Проанализируем преимущества этого подхода:

Скорость: Обращение к COM-объекту происходит один раз вместо тысяч обращений в цикле. Это ускоряет загрузку в 10–50 раз.
Удобство: Вы можете перебирать данные в памяти 1С, что гораздо быстрее, чем постоянно «дергать» приложение Excel.

Важно: Помните, что индексы в массиве, полученном из Excel, всегда начинаются с 1, а не с 0. Учитывайте это при написании циклов обработки в 1С.

Итоги

Мы рассмотрели несколько способов определения границ данных. Для простых таблиц достаточно UsedRange. Если же вы работаете с файлами сложной структуры (например, ТТН или ТОРГ-12), где данные могут быть разделены пустыми техническими столбцами, рекомендуется использовать метод Find или предварительно считывать весь лист в массив Value и определять границы уже средствами 1С. Это обеспечит стабильность вашей обработки и высокую скорость работы.