Чтение файлов Excel с объединенными ячейками — это классическая и часто встречающаяся задача при разработке обработок загрузки прайс-листов, номенклатуры или документов в 1С. Основная сложность заключается в том, что стандартные механизмы чтения часто видят значение только в первой (левой верхней) ячейке объединения, возвращая для всех остальных строк пустые значения. В этой статье мы подробно разберем, как решить эту задачу тремя различными способами в зависимости от используемой архитектуры.
Это наиболее рекомендуемый, быстрый и кроссплатформенный подход. Он отлично работает на серверах под управлением как Windows, так и Linux, не требуя установленного пакета MS Office (Excel). При импорте файла через метод Прочитать() платформа 1С автоматически сопоставляет объединенные ячейки, гарантируя, что значение будет доступно при обращении к левому верхнему углу области.
Выясним важный архитектурный нюанс: метод Прочитать() объекта ТабличныйДокумент работает исключительно на стороне сервера. Поэтому сначала нам необходимо правильно передать файл с клиента на сервер с использованием временного хранилища. Разберем этот процесс по шагам.
Шаг 1. Передача файла на сервер через временное хранилище
На клиенте мы вызываем диалог выбора файла, считываем его в двоичные данные и помещаем во временное хранилище. Затем передаем полученный адрес серверной процедуре.
&НаКлиенте
Процедура ЗагрузитьФайлНаКлиенте()
ДиалогФайла = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
ДиалогФайла.Фильтр = "Файлы Excel (*.xls;*.xlsx)|*.xls;*.xlsx";
ДиалогФайла.Заголовок = "Выберите файл для загрузки";
Если ДиалогФайла.Выбрать() Тогда
ИмяФайла = ДиалогФайла.ПолноеИмяФайла;
ДвоичныеДанныеФайла = Новый ДвоичныеДанные(ИмяФайла);
АдресХранилища = ПоместитьВоВременноеХранилище(ДвоичныеДанныеФайла, УникальныйИдентификатор);
ПрочитатьФайлНаСервере(АдресХранилища);
КонецЕсли;
КонецПроцедуры
Шаг 2. Чтение файла и обработка объединенных областей
На сервере мы извлекаем файл из временного хранилища, сохраняем его во временный файл на диске сервера и считываем в ТабличныйДокумент. При обходе ячеек мы анализируем границы объединения с помощью свойств области Лево, Право, Верх и Низ. Это позволяет нам всегда безошибочно определять значение первой объединенной ячейки в строке, даже если размер объединения динамически меняется.
&НаСервере
Процедура ПрочитатьФайлНаСервере(АдресХранилища)
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресХранилища);
ИмяВременногоФайла = ПолучитьИмяВременногоФайла("xlsx");
ДвоичныеДанные.Записать(ИмяВременногоФайла);
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ИмяВременногоФайла);
Исключение
ВызватьИсключение "Не удалось прочитать файл Excel. Возможно, файл поврежден.";
КонецПопытки;
ВсегоСтрок = ТабДок.ВысотаТаблицы;
ТекущаяКолонка = 1; // Предположим, анализируем первую колонку
СтрокаЦикла = 1;
Пока СтрокаЦикла <= ВсегоСтрок Цикл
// Получаем область текущей ячейки
ОбластьЯчейки = ТабДок.Область(СтрокаЦикла, ТекущаяКолонка);
// Проверяем, объединена ли ячейка
ЯчейкаОбъединена = (ОбластьЯчейки.Лево <> ТекущаяКолонка)
Или (ОбластьЯчейки.Право <> ТекущаяКолонка)
Или (ОбластьЯчейки.Верх <> СтрокаЦикла)
Или (ОбластьЯчейки.Низ <> СтрокаЦикла);
Если ЯчейкаОбъединена Тогда
// Если ячейка объединена, значение гарантированно лежит в её левом верхнем углу
ЗначениеЯчейки = ТабДок.Область(ОбластьЯчейки.Верх, ОбластьЯчейки.Лево).Текст;
// Здесь выполняется алгоритм обработки значения
// ...
// Оптимизация: мы можем пропустить обработку строк внутри этого объединения,
// перепрыгнув сразу на следующую строку за границей текущего объединения.
СтрокаЦикла = ОбластьЯчейки.Низ + 1;
Иначе
// Обычная необъединенная ячейка
ЗначениеЯчейки = ОбластьЯчейки.Текст;
// Обработка обычного значения
// ...
СтрокаЦикла = СтрокаЦикла + 1;
КонецЕсли;
КонецЦикла;
// Обязательно удаляем временный файл после завершения работы
Попытка
УдалитьФайлы(ИмяВременногоФайла);
Исключение
// Логируем ошибку удаления, если необходимо
КонецПопытки;
КонецПроцедуры
Если загрузка происходит в среде операционной системы Windows и на компьютере установлен Microsoft Excel, вы можете использовать COM-соединение. При прямом обращении к ячейке внутри объединения COM-объект вернет пустое значение (за исключением первой ячейки). Чтобы не делать принудительный UnMerge (разъединение ячеек), ухудшающий структуру файла, следует использовать свойство MergeArea.
Рассмотрим пример кода, где с помощью свойства MergeCells проверяется наличие объединения, а через MergeArea извлекается значение из первой ячейки диапазона:
&НаСервереБезКонтекста
Процедура ЗагрузитьЧерезCOM(ПутьКФайлу)
Попытка
ExcelПриложение = Новый COMОбъект("Excel.Application");
Исключение
ВызватьИсключение "Не удалось подключиться к Excel. Убедитесь, что MS Excel установлен.";
КонецПопытки;
ExcelПриложение.Visible = Ложь;
ExcelПриложение.DisplayAlerts = Ложь;
Попытка
Книга = ExcelПриложение.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);
ВсегоСтрок = Лист.UsedRange.Rows.Count;
ТекущаяКолонка = 1; // Анализируемая колонка
Для ТекущаяСтрока = 1 По ВсегоСтрок Цикл
Ячейка = Лист.Cells(ТекущаяСтрока, ТекущаяКолонка);
Если Ячейка.MergeCells Тогда
// Свойство MergeArea возвращает весь диапазон объединения.
// Обращаемся к левой верхней ячейке диапазона: Cells(1, 1)
Значение = Ячейка.MergeArea.Cells(1, 1).Value;
Иначе
Значение = Ячейка.Value;
КонецЕсли;
// Обрабатываем значение
// ...
КонецЦикла;
Книга.Close(Ложь);
Исключение
ExcelПриложение.Quit();
ВызватьИсключение "Ошибка при чтении файла: " + ОписаниеОшибки();
КонецПопытки;
ExcelПриложение.Quit();
КонецПроцедуры
Когда файл содержит десятки тысяч строк, COM-соединение может работать медленно. В таких случаях часто используют чтение файла через ADO как базу данных. Однако драйвер OLE DB возвращает значение только для первой строки объединенного диапазона, а для всех последующих строк в этой колонке возвращает NULL.
Проанализируем ситуацию: чтобы решить эту проблему, мы применим алгоритм кэширования ("Заполнение вниз" или "Fill Down"). Мы будем запоминать последнее непустое значение в переменную-кэш и подставлять его во все последующие строки, пока не встретим новое заполненное значение.
Посмотрим на пример реализации данного алгоритма:
&НаСервереБезКонтекста
Процедура ЗагрузитьЧерезADO(ПутьКФайлу)
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";";
Connection = Новый COMОбъект("ADODB.Connection");
Попытка
Connection.Open(СтрокаПодключения);
Исключение
ВызватьИсключение "Не удалось открыть ADO-соединение с файлом.";
КонецПопытки;
Recordset = Новый COMОбъект("ADODB.Recordset");
ТекстЗапроса = "SELECT * FROM [Лист1$]";
Попытка
Recordset.Open(ТекстЗапроса, Connection, 1, 3);
Исключение
Connection.Close();
ВызватьИсключение "Не удалось прочитать данные с Листа1.";
КонецПопытки;
КэшОбъединенногоЗначения = Неопределено;
ИндексКолонкиОбъединения = 0; // Первая колонка в наборе данных
Пока НЕ Recordset.EOF() Цикл
ТекущееЗначение = Recordset.Fields(ИндексКолонкиОбъединения).Value;
Если ТекущееЗначение = Неопределено Или СокращенноЛП(Строка(ТекущееЗначение)) = "" Тогда
// Если значение в строке пустое — это последующие строки нашего объединения.
// Подставляем ранее сохраненное значение из кэша.
ТекущееЗначение = КэшОбъединенногоЗначения;
Иначе
// Если значение заполнено — это начало нового объединения (или просто новое значение).
// Обновляем наш кэш.
КэшОбъединенногоЗначения = ТекущееЗначение;
КонецЕсли;
// Здесь мы обрабатываем полученное значение
// ...
Recordset.MoveNext();
КонецЦикла;
Recordset.Close();
Connection.Close();
КонецПроцедуры
Для эффективного импорта файлов Excel с объединенными ячейками в 1С разработчику доступно несколько мощных инструментов:
MergeArea.