Как правильно считать и развернуть кросс-таблицу («шахматку») из Excel в 1С?

При загрузке данных из MS Excel разработчики часто сталкиваются с файлами в формате кросс-таблиц (или так называемых «шахматок»). В таких файлах номенклатура располагается в строках, контрагенты или склады — в шапке колонок, а на пересечении указано количество или сумма. Для проведения документов или заполнения регистров платформы 1С требуется преобразовать такую матрицу в плоскую структуру вида Контрагент | Товар | Количество.

Рассмотрим подробно алгоритм разворота «шахматки», разберем пример кода и проанализируем основные способы чтения Excel-файлов и технические особенности платформы 1С.

Алгоритм преобразования кросс-таблицы в плоский вид

Разберем по шагам базовую логику обработки двухмерной матрицы. Чтобы развернуть данные из структуры с контрагентами в шапке в линейный список, применим следующий алгоритм:

1. Считывание шапки (контрагентов): обходим первую строку файла, начиная со второй колонки (где расположен первый контрагент). Сохраняем номера колонок и соответствующие им наименования контрагентов во внутреннюю структуру (массив или соответствие).
2. Двойной цикл обхода строк и колонок:
   1. Во внешнем цикле перебираем строки таблицы, начиная со второй строки с данными. Из первой колонки извлекаем наименование товара.
   2. Во внутреннем цикле обходим только те колонки, где расположены контрагенты.
3. Фильтрация и занесение в результат: проверяем значение на пересечении текущей строки и колонки. Если ячейка заполнена и число больше нуля, добавляем в итоговую таблицу значений новую строку с полями Контрагент, Товар и Количество.

Посмотрим на пример реализации этого алгоритма с использованием объекта ТабличныйДокумент:

// Создаем итоговую таблицу значений
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Контрагент", Новый ОписаниеТипов("Строка"));
ТаблицаРезультат.Колонки.Добавить("Товар", Новый ОписаниеТипов("Строка"));
ТаблицаРезультат.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число"));

// Загружаем Excel-файл в табличный документ
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу);

// Считываем контрагентов из шапки (со второй колонки до конца таблицы)
СоответствиеКолонок = Новый Соответствие;
Для НомерКолонки = 2 По ТабДок.ШиринаТаблицы Цикл
    ТекстШапки = СокрЛП(ТабДок.Область(1, НомерКолонки).Текст);
    Если ЗначениеЗаполнено(ТекстШапки) Тогда
        СоответствиеКолонок.Вставить(НомерКолонки, ТекстШапки);
    КонецЕсли;
КонецЦикла;

// Обходим строки с товарами (начиная со второй строки)
Для НомерСтроки = 2 По ТабДок.ВысотаТаблицы Цикл
    Товар = СокрЛП(ТабДок.Область(НомерСтроки, 1).Текст);
    Если НЕ ЗначениеЗаполнено(Товар) Тогда
        Продолжить;
    КонецЕсли;
    
    // Перебираем колонки с контрагентами для текущего товара
    Для Каждого КлючИЗначение Из СоответствиеКолонок Цикл
        КолонкаИндекс = КлючИЗначение.Ключ;
        ИмяКонтрагента = КлючИЗначение.Значение;
        
        ТекстЗначения = ТабДок.Область(НомерСтроки, КолонкаИндекс).Текст;
        
        // Очищаем строку от неразрывных пробелов
        ТекстЗначения = СтрЗаменить(ТекстЗначения, Символы.НПП, "");
        
        Количество = Число(ТекстЗначения);
        Если Количество > 0 Тогда
            НоваяСтрока = ТаблицаРезультат.Добавить();
            НоваяСтрока.Контрагент = ИмяКонтрагента;
            НоваяСтрока.Товар = Товар;
            НоваяСтрока.Количество = Количество;
        КонецЕсли;
    КонецЦикла;
КонецЦикла;

Способы считывания Excel и их особенности

Проанализируем технические варианты загрузки файлов Excel в 1С:

1. Использование объекта ТабличныйДокумент:

   Метод ТабличныйДокумент.Прочитать() работает кроссплатформенно (в том числе под Linux и в тонком клиенте) и не требует наличия установленного приложения Microsoft Excel. Границы таблицы определяются свойствами ВысотаТаблицы и ШиринаТаблицы, а обращение к ячейке происходит через Область(НомерСтроки, НомерКолонки).Текст.

2. Использование COM-объекта (Excel.Application):

   Работает в Windows при наличии MS Office. Для повышения скорости чтения рекомендуется избегать поячеечного обхода и считывать весь диапазон через Sheet.UsedRange.Value в двухмерный массив COMSafeArray с последующей обработкой в оперативной памяти 1С.

3. Прямой разбор ZIP/XML (файлы .xlsx):

   Чтение архива средствами ЧтениеZIPФайла и ЧтениеXML. Это самый быстрый способ загрузки тяжелых файлов без высокой нагрузки на оперативную память.

Типичные подводные камни

Выясним, с какими ошибками часто сталкиваются при обработке данных из Excel:

1. Неразрывные пробелы (ASCII 160): В отформатированных числовых полях Excel разделителем тысячных разрядов часто выступает неразрывный пробел Символы.НПП. Перед преобразованием строки в число необходимо выполнять очистку с помощью СтрЗаменить(Текст, Символы.НПП, "").
2. Ложные непустые ячейки: Ячейки со стиранием текста или форматированием могут завышать показатели ВысотаТаблицы и UsedRange. В циклах всегда используйте проверку ЗначениеЗаполнено(СокрЛП(ТекстЯчейки)).
3. Различия между Value и Value2 в COM: Свойство Value2 считывает данные быстрее, но возвращает даты в виде чисел типа Double, которые требуют ручного перевода в тип Дата платформы 1С.